May 10, 2013 at 6:07 am
I was checking in my database and found lots of unused indexes. I found it based on summing up the index seeks, index scans and index lookups and comparing this with index updates. Now with this data and by checkig at the number of indexes within the table, I can say which index is not required and is only adding cost by only getting used in writes.
Now the issue I face is that for every such change, my management asks me to do some profit analysis and show significant profit with the change. Now how is it possible to show some process's benefit by just removing unused index. If I show them the numbers calculated as mentioned above, then there is a logic put up that it might cause creating bad execution plans and blah blah. Have anyone faced such issues or it's just with me ?
May 10, 2013 at 6:32 am
Could you find the inserts / updates for this table and baseline the I/O, execution time for them WITH the indexes in place then baseline WITHOUT. Convert the figures into a % and extrapolate over a period of time?
Not sure how to show this in a monetary value, but performance/time saved should be easy enough to show.
May 10, 2013 at 6:40 am
This is a tough one. It would seem that your management team is looking for a tangible value on something that is intangible!
I would take the approach that your proposed change will increase productivity. I can only guess that there is an application or report that takes longer to run with all of these useless indexes in place. If removing these indexes will help to improve performance for the end user enlist their assistance in building a proof of concept.
If you have a Development Region to play in make your recommend changes and then have a business user run their processes against the modified database and measure the difference.
The bigger problem it would seem you are facing is that management does not see the value in clearing out the "junk drawer". If you're not using it then get rid of it, thus you avoid becoming a digital pack rat 😀 .
Regards, Irish
May 10, 2013 at 6:57 am
sqlnaive (5/10/2013)
I was checking in my database and found lots of unused indexes. I found it based on summing up the index seeks, index scans and index lookups and comparing this with index updates. Now with this data and by checkig at the number of indexes within the table, I can say which index is not required and is only adding cost by only getting used in writes.
Absolutely sure? Over what period have you done that index analysis? A couple months? Included the year end process? Other reports only run occasionally?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2013 at 7:06 am
Also make sure that your analysis have not excluded Foreign keys. Because lot many scripts available online do not consider FK while doing unused Index analysis.
I do agree with Gail that, for what period of time you have done this analysis.
And again this is not a small change. It is really-2 a big change.
May 10, 2013 at 8:37 am
GilaMonster (5/10/2013)
sqlnaive (5/10/2013)
I was checking in my database and found lots of unused indexes. I found it based on summing up the index seeks, index scans and index lookups and comparing this with index updates. Now with this data and by checkig at the number of indexes within the table, I can say which index is not required and is only adding cost by only getting used in writes.Absolutely sure? Over what period have you done that index analysis? A couple months? Included the year end process? Other reports only run occasionally?
Gail, It has been around an year or little more than that. The tables on which these indexes are, getting used on daily basis. And I'm sure that these indexes are not getting used as the clustered indexes on these tables contains the columns which are there in randomly created small small non clustered indexes.
May 10, 2013 at 8:38 am
Neeraj Dwivedi (5/10/2013)
Also make sure that your analysis have not excluded Foreign keys. Because lot many scripts available online do not consider FK while doing unused Index analysis.I do agree with Gail that, for what period of time you have done this analysis.
And again this is not a small change. It is really-2 a big change.
Neeraj, As I know my system, I can say removing these indexes will be no big change. For a matter a fact, I would agree that removing or creating an index is always big change from performance's perspective.
May 10, 2013 at 9:02 am
sqlnaive (5/10/2013)
It has been around an year or little more than that
That you've been monitoring and recording the index usage stats, taking care to persist the results before server reboots for over a year?
And I'm sure that these indexes are not getting used as the clustered indexes on these tables contains the columns which are there in randomly created small small non clustered indexes.
The clustered index contains all columns in the table (because it is the table), this doesn't make it the replacement for all nonclustered indexes, even if it has lots of those columns as key columns. SQL can only seek on a left-based subset of the index key.
So let's say you have this...
CREATE CLUSTERED INDEX idx1 on SomeTable (Col1, Col2, Col3)
CREATE NONCLUSTERED INDEX idx2 on SomeTable (Col2)
CREATE NONCLUSTERED INDEX idx3 on SomeTable (Col3)
The two nonclustered indexes are not redundant and removing them could well affect queries running against that table, depending on what queries there are. Of course, if all queries filter on Col1 and sometimes Col1 and Col2, then those nonclustered indexes are unnecessary
This is not a small change. Removing indexes is far harder than adding indexes. If you want to prove to your manager that the removal won't cause problems, identify the queries that run against those tables and show that removing the indexes doesn't result in a worse performance. IO statistics, time statistics and execution plan will help there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2013 at 1:54 am
GilaMonster (5/10/2013)
The clustered index contains all columns in the table (because it is the table), this doesn't make it the replacement for all nonclustered indexes, even if it has lots of those columns as key columns. SQL can only seek on a left-based subset of the index key.
So let's say you have this...
CREATE CLUSTERED INDEX idx1 on SomeTable (Col1, Col2, Col3)
CREATE NONCLUSTERED INDEX idx2 on SomeTable (Col2)
CREATE NONCLUSTERED INDEX idx3 on SomeTable (Col3)
The two nonclustered indexes are not redundant and removing them could well affect queries running against that table, depending on what queries there are. Of course, if all queries filter on Col1 and sometimes Col1 and Col2, then those nonclustered indexes are unnecessary
This is not a small change. Removing indexes is far harder than adding indexes. If you want to prove to your manager that the removal won't cause problems, identify the queries that run against those tables and show that removing the indexes doesn't result in a worse performance. IO statistics, time statistics and execution plan will help there.
Gail, This above example is the case in my environment. Seems well before I joined they have made this a best practice and created same kind of indexes on almost all tables. As fas as queries are concerned, almost 98% are using Col1, Col2, Col3 (considering the above example). So i feel that having non clustered indexes on Col2 or Col3 are unnecessary.
One more thing, even if these indexes are getting used, the stats from dmv sys.dm_db_index_usage_stats says that index_seek+index_scan_index_lookups are almost none as compared to index_write. The question here is also that should we consider these stats or not ? (My analysis is entirely based on my experience with the system as well as these stats)
May 13, 2013 at 5:05 am
sqlnaive (5/13/2013)
One more thing, even if these indexes are getting used, the stats from dmv sys.dm_db_index_usage_stats says that index_seek+index_scan_index_lookups are almost none as compared to index_write. The question here is also that should we consider these stats or not ?
Depends how long you've tracked those stats for. They're not persistent.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2013 at 1:11 am
GilaMonster (5/13/2013)
sqlnaive (5/13/2013)
One more thing, even if these indexes are getting used, the stats from dmv sys.dm_db_index_usage_stats says that index_seek+index_scan_index_lookups are almost none as compared to index_write. The question here is also that should we consider these stats or not ?Depends how long you've tracked those stats for. They're not persistent.
Thanks Gail as always. These details are from quite long. So I'm considering performing the action based on these stats. 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply