Question

Indexing Fact Tables for a data warehouse

SQL Server 2005, Data warehouse

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

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.