Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to convince people for small changes Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 6:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
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 ?
Post #1451544
Posted Friday, May 10, 2013 6:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 5:16 PM
Points: 1,597, Visits: 1,151
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.


There's no kill switch on awesome!
Post #1451550
Posted Friday, May 10, 2013 6:40 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:55 PM
Points: 149, Visits: 1,027
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
Post #1451554
Posted Friday, May 10, 2013 6:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 42,802, Visits: 35,917
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1451564
Posted Friday, May 10, 2013 7:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:30 AM
Points: 811, Visits: 1,165
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.
Post #1451570
Posted Friday, May 10, 2013 8:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
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.
Post #1451628
Posted Friday, May 10, 2013 8:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
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.
Post #1451629
Posted Friday, May 10, 2013 9:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 42,802, Visits: 35,917
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1451636
Posted Monday, May 13, 2013 1:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 5:20 AM
Points: 3,546, Visits: 2,651
GilaMonster (5/10/2013)[quote]
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)
Post #1451991
Posted Monday, May 13, 2013 5:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:25 AM
Points: 42,802, Visits: 35,917
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1452043
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse