Indexing Fact Tables for a data warehouse

pts.
Tags:
Data warehouse
SQL Server 2005
Hi I am reviewing the indexing the fact tables of a data warehouse and it currently has a clustered primary key and quite a few VERY LARGE covering indexes comprising lots of columns. The selectivity of the columns other than the primary key is very low; mostly close to 0% with a couple around 15 to 20%. From the SQL 2005 documentation it appears pointless to index these columns. It also has boolean columns indexed? Would it be better to delete these large (Some are over 80GB in size) indexes and replace them with single column indexes on the foreign keys (to the Dimension tables) only? Or is there some benefit in keeping some of them? Many thanks in advance Regards Gary

Answer Wiki

Thanks. We'll let you know when a new response is added.

If the indexes aren’t being used then you are probably safe to delete them. However if they are being used at all then your performance will suffer when those queries are run if you remove the indexes.

Boolean columns are typically useless to index unless it’s the last column in the list of columns in the index. As Boolean columns (BIT datatype) have only two options if an index contains only a Boolean column the index won’t be of much use as the index will need to be scanned almost every time.

If your indexes are 80GB running a single nonindexed query against the database will take a lot of CPU and disk resources to process.

When deciding to remove an index you need to weigh the cost of the index against the cost of running the query or queries without the index. Which ever one costs less is the right method to use.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following