December 16, 2014 at 8:28 am
Hi, we have a table, with 10 indexes. This is the columns included
[font="Courier New"]
Name |Writes | Reads | Columns | IncludedColumns
-------------------------------------------------------------------------------
INDEX A| 15.112|2.117| BB,AA with included columns CC,EE
INDEX B| 15.112| 2.823| EE
INDEX C| 17.591| 10.621| DD with included columns AA,CC,EE,GG
INDEX D| 10.335| 35.722| FF
INDEX E| 17.591| 45.569| DD,AA,GG,FF,CC
INDEX F| 15.112| 52.871| AA,EE,CC
INFEX G| 15.171| 110.782| AA with included columns BB,[FechaC],[UsarFechaC]
INDEX H |15.112| 126.668| AA,BB,CC,EE
INDEX I| 15.112|1.325.456| AA,EE,CC,BB
INDEX J| 10.335|4.669.429| AA,FF,BB
[/font]
Can i drop all indexes, and create a unique index with the same functionality and performance
INDEX X WITH COLUMNS, AA,BB,CC,DD,EE,FF,GG WITH INCLUDED COLUMNS [FechaC],[UsarFechaC]
Included columns are DateTimes and other are Integers.
December 16, 2014 at 9:12 am
jvila (12/16/2014)
Hi, we have a table, with 10 indexes. This is the columns included[font="Courier New"]
Name |Writes | Reads | Columns | IncludedColumns
-------------------------------------------------------------------------------
INDEX A| 15.112|2.117| BB,AA with included columns CC,EE
INDEX B| 15.112| 2.823| EE
INDEX C| 17.591| 10.621| DD with included columns AA,CC,EE,GG
INDEX D| 10.335| 35.722| FF
INDEX E| 17.591| 45.569| DD,AA,GG,FF,CC
INDEX F| 15.112| 52.871| AA,EE,CC
INFEX G| 15.171| 110.782| AA with included columns BB,[FechaC],[UsarFechaC]
INDEX H |15.112| 126.668| AA,BB,CC,EE
INDEX I| 15.112|1.325.456| AA,EE,CC,BB
INDEX J| 10.335|4.669.429| AA,FF,BB
[/font]
Can i drop all indexes, and create a unique index with the same functionality and performance
INDEX X WITH COLUMNS, AA,BB,CC,DD,EE,FF,GG WITH INCLUDED COLUMNS [FechaC],[UsarFechaC]
Included columns are DateTimes and other are Integers.
No, that just won't be the same. There's a pretty good case to be made dropping indexes F through J and finding one that will work across them because index F and Index I are almost identical in every possible regard, just the slight degree of added selectivity caused by column BB, and the others are all very similar. The leading edge for F through J is the AA column. That will the only column used in the histogram for the statistics on all those indexes which is one of the primary decision points used by the optimizer. So finding a single index to replace those might be possible.
Similar arguments could be made for indexes C & E.
But, indexes A, B & D are unique within the set. I suspect the minimum you could get away with, assuming all indexes are in use, would be 5 indexes, one for the AA set, one for the DD set and the last three.
However, it's actually much more complicated than that. It could be that because, just picking one as an example, index F is smaller in size than index I, it might be used for some queries and performs faster than if you used index I, even though, effectively, index I and index F are the same for the first set of columns.
Deciding to drop indexes is not a science. First, use the system dmv sys.dm_db_index_usage_stats to see how the indexes are currently being used on your system. That will tell you even more than just looking at the structures. But, understand that the DMV gets reset regularly, so some of the indexes may only be used once a quarter or once a year, but won't show up in the usage stats. So, if you decide to drop any, keep the scripts handy for recreating them as needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2014 at 11:31 am
Adding to the good stuff Grant wrote, be sure you search ALL of your "source code" for each index name you may intend to DELETE!! Failure to do so can cause said code to start throwing errors if anyone has hinted an index you drop!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 16, 2014 at 11:44 am
Excellent point. Not something I tend to think of since I try so hard to avoid hints.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 18, 2014 at 7:26 am
Thank you very much.
this is what i want to test.
Indexes A, B and D remain untouched.
Combinate D & E creating one new index and drop existing.
D & E => (From D ) DD AA GG FF CC and EE (from C) with no included columns.
F to J => AA EE CC BB ( index I and contains F) FF (from J) EE (from H) with included columns the two date indexes.
What do you think?
December 18, 2014 at 7:42 am
Sorry when i write "Combinate D & E creating one new index and drop existing." i want to write "C & E"
December 18, 2014 at 8:00 am
I think testing is the way to go on any of this. But, also check the index usage stats to see if they've been used. If an index has been used, be very cautious in replacing it. Other than that, I think your plan is relatively sound. I'd just be curious if you've looked at the code too. Maybe just using the INCLUDE is all that's needed rather than that compound key, which makes the index much bigger and slower across the B-tree side of it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply