April 24, 2012 at 8:14 am
Hi All,
we have a few large fact tables (100 million to 820 million of rows) and most of the foreign keys were indexed when the data warehouse was first rebuilt.
We have done some analysis on the indexes and found that most of the indexes haven't been used. ( they were monitored for about 3 months). In fact we have a few tables that are only used to populate other fact tables and not surprisingly none of the non clustered indexes has been used.
Now, we are dropping those indexes but at the same time I going thorugh the referenced sql objects such as views, stored procedures etc focusing on joins and where clauses. I am also thinking that I can perhaps create a covering index for all the columns that are referenced in join columns. I have not done any tests yet but I am wondering whether I should leave the non clustered indexes intact despite the fact that they haven't been used before or drop all indexes and create a covering index including all the columns on join conditions.
Any thought on this please?
Enis
April 24, 2012 at 8:23 am
If the indexes that you have were not in use for 3 months as you wrote, then there is no reason to have them and you can drop them.
As for the covering indexes – no one can tell you without knowing anything about the tables and queries that you are working with. There are some queries that covering indexes reduces the runtime a lot, but there are also other queries that won't benefit much from a covering index. In short, regardless of any advice that you'll get here, you'll have to test it and decide according to your results.
One last remark – In your post you wrote about covering index, but you also wrote "I can perhaps create a covering index for all the columns that are referenced in join columns". This is not a covering index. A covering index is an index that covers the whole query (e.g. It has all the columns that participate in all the query's clauses. Not just in the from clause).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 24, 2012 at 9:32 am
Hi Adi,
Thank you for your advice. And yes you are right. What I meant was all the columns that in out put list of all columns on join condition.
We are going to have to investigate the queries individually.
Appreciate your comments.
Enis
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply