April 2, 2014 at 9:59 am
Hi Guys,
I have an index consolidating issue which I have not been able to find a definitive answer to.
I have a clustered index and two non-clustered indexes on a table with the following columns:
CX Index: Column A, Column B
NC Index 1: Column A, Column C
NC Index 2: Column A, Column D INCLUDE Column C
Weird combination but going over the read stats which have been gathered daily for a month, NC Index 1 has 2x to 3x the amount of reads when compared to NC Index 2. Writes on the NC indexes are very similar.
Is it possible to combined NC Index 1 and NC Index 2, maybe into a new index and have performance on queries remain relatively unaffected?
New Index:
NC Index 3: Column A, Column C, Column D ?
Thanks,
April 3, 2014 at 11:30 am
whojdysz (4/2/2014)
Hi Guys,I have an index consolidating issue which I have not been able to find a definitive answer to.
I have a clustered index and two non-clustered indexes on a table with the following columns:
CX Index: Column A, Column B
NC Index 1: Column A, Column C
NC Index 2: Column A, Column D INCLUDE Column C
Weird combination but going over the read stats which have been gathered daily for a month, NC Index 1 has 2x to 3x the amount of reads when compared to NC Index 2. Writes on the NC indexes are very similar.
Is it possible to combined NC Index 1 and NC Index 2, maybe into a new index and have performance on queries remain relatively unaffected?
New Index:
NC Index 3: Column A, Column C, Column D ?
Thanks,
Yes you can do that. But first test it well in the test environment.
Script the indexes before dropping them so that they can be re-created in case of any issues. Alternatively you can disable them too.
--
SQLBuddy
April 16, 2014 at 6:39 am
The big test case to ensure that your new consolidated index is doing what it should would be to execute queries on Column A and Column D and see if the NC index is properly used.
CX Index: Column A, Column B
NC Index 3: Column A, Column C, Column D
With these indexes, you will cover a query on A, A and B, A and C, or A, C, and D. If you execute queries exclusively on A and D, then these indexes may not be enough---but if not, you'll be good to go.
Again, test thoroughly---the combination of indexes, queries, and the optimizer may not always lead to 100% what you expect.
April 16, 2014 at 7:47 am
Your suggested index is not a replacement for the other two. It may be good enough for your workload, but you should test carefully.
Part of it will depend on how selective ColumnA is alone. If very selective (few duplicated values), then the new index may be fine. If there are lots and lots of duplicates then I'd be a little more cautious.
The existing index fully supports queries of these forms
WHERE ColumnA = @a and ColumnD = @D
WHERE ColumnA = @a and ColumnD > @D
The revised index only partially supports them, the queries can do a seek predicate on ColumnA but will have to do a secondary filter on ColumnD and hence will be less efficient.
If ColumnA is very selective, that's probably fine. If it's not selective and there are lots of queries or important queries filtering on that combo, then you may have a performance degradation. Do test carefully.
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
April 16, 2014 at 7:52 am
My first question would be... is the CX a UNIQUE index???
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply