April 3, 2014 at 4:09 am
I have a table with what I would consider way too many indexes on it (35 - and the table isn't particularly wide ). So I am looking to remove the redundant indexes which have no reads against them. Some of the indexes appear to very similar for example the two described below:
Ix_Covering30
Index Key columns: CreatedDate, Status
Included columns: UserID, CallerName, UserBA, LookupID
and then very similar to the one above :
Ix_Covering29
Index Key Columns: CaseCurrentID, CreatedDate, Status
Included columns: UserID, CallerName, UserBA
So if I where to remove Ix_Covering30 and add the LookUpID into the included column of Ix_Covering29 would the queries that where using Ix_Covering30 now use Ix_Covering29 as it still is indexed on CreatedDate and Status but with the addition of CaseCurrentID in the index key column ?
April 3, 2014 at 4:33 am
Probably not. You'll note that the leading edge of the key, the first column, is different between the two indexes. That's one of the bigger driving factors for index selection since it's the column used to create the histogram in the statistics. While the index is still covering if you do what you suggested, the change to the keys suggests that you may lose some functionality. But, testing will tell you more than speculation.
"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
April 3, 2014 at 4:43 am
thanks Grant 🙂
The bit about the leading edge of the index always confused me, but I think you've just cleared that up !
April 3, 2014 at 5:32 am
Not a problem. Happy to pitch in.
"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
April 3, 2014 at 11:52 am
SQL actually keeps track of how often an index gets used, and that info is accessible via the dm_db_index_usage_stats DMV.
Take a look here: https://www.simple-talk.com/blogs/2012/08/08/a-quick-look-at-dm_db_index_usage_stats/
April 3, 2014 at 9:03 pm
Rather than adding the column to the '"leading edge" you might want to consider adding it to the end of the index...this may help with the overall selectivity of the index.
idx_NewCovering
Index Key columns: CreatedDate, Status, CaseCurrentID
Included columns: UserID, CallerName, UserBA, LookupID
If the queries that typically use the index follow the same order (like below)
WHERE CreatedDate = @Date AND Status = @status
And then you add a new condition:
WHERE CreatedDate = @Date AND Status = @status AND CaseCurrentID = @CCID
It should pick up and use the same index, without a loss in functionality
Of course, as the general rule of thumb goes "It depends", so test accordingly.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 4, 2014 at 3:50 am
MyDoggieJessie (4/3/2014)
Rather than adding the column to the '"leading edge" you might want to consider adding it to the end of the index...this may help with the overall selectivity of the index.idx_NewCovering
Index Key columns: CreatedDate, Status, CaseCurrentID
Included columns: UserID, CallerName, UserBA, LookupID
If the queries that typically use the index follow the same order (like below)
WHERE CreatedDate = @Date AND Status = @statusAnd then you add a new condition:
WHERE CreatedDate = @Date AND Status = @status AND CaseCurrentID = @CCIDIt should pick up and use the same index, without a loss in functionality
How would you add an extra predicate to a query without changing the behaviour?
An index on CaseCurrentID, CreatedDate, Status and an index on CreatedDate, Status, CaseCurrentID are two different indexes which can support different queries. They're not equivalent
The first one supports any where clauses of the forms:
WHERE CaseCurrentID = @1
WHERE CaseCurrentID > @1
WHERE CaseCurrentID = @1 AND CreatedDate = @2
WHERE CaseCurrentID = @1 AND CreatedDate > @2
WHERE CaseCurrentID = @1 AND CreatedDate = @2 AND Status = @3
WHERE CaseCurrentID = @1 AND CreatedDate = @2 AND Status > @3
The second supports where clauses of the forms
WHERE CreatedDate = @1
WHERE CreatedDate > @1
WHERE CreatedDate = @1 AND Status = @2
WHERE CreatedDate = @1 AND Status > @2
WHERE CreatedDate = @1 AND Status = @2 AND CaseCurrentID = @3
WHERE CreatedDate = @1 AND Status = @2 AND CaseCurrentID > @3
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply