May 27, 2015 at 1:16 pm
I inherited a mess and now I'm trying to clean it up. It appears like many indexes were automatically created by the DTA which is making me cringe. I am by no means an expert on Indexes but I'd like to think I have a pretty decent grasp of things. So I have a situation here where I was just about to disable a couple of indexes I thought were duplicate but thought I'd better at least give it a mention. It certainly wouldn't be the first time I would have missed something.
Clustered Index -> ID, Version (both keys)
Non Clustered Index 1 -> ID, Version + 1 Included Column
Non Clustered Index 2 -> ID, Version + 6 Included Columns (including included column from Index 1)
I didn't even list the names of the included columns because as far as I can see they don't even matter. All three are indexes are keyed on the exact same two columns in the exact same order. I also know that it's possible for the optimizer to use duplicate indexes so usage doesn't give you the full picture.
Before I disable the two Non Clustered Index does anybody know why it would ever make sense to create them in the first place?
May 27, 2015 at 1:28 pm
As I understand it, the one difference it could make is when the rows are extremely wide and the non-clustered index would be covering for a query that uses a range scan.
If the rows in the table are quite large, then range scans using the non-clustered index could potentially read the same number of rows with far fewer IO than if they went to the clustered index, due to the smaller size of the non-clustered index.
Now, whether that difference is exploited by your workload is another question entirely. So, as is usual, it depends. 🙂
May 27, 2015 at 1:52 pm
Hmmm, I hadn't considered that. I have 17 columns and looking at the data types only one raises an eyebrow which is a LOB used to store images.
July 23, 2015 at 2:26 pm
Download sp_BlitzIndex from
http://www.brentozar.com/blitzindex/
and read
http://www.brentozar.com/blitzindex/sp_blitzindex-duplicate-indexes/
July 23, 2015 at 2:47 pm
My guts tell me that the non-clustered indexes are redundant and certainly seem to come from the DTA which will suggest covering indexes for the query without considering modifications to current indexes.
The LOB shouldn't be an issue AFAIK because it's not stored with the rest of the table. (I might be wrong).
Have you checked the usage of these indexes?
July 24, 2015 at 9:53 am
Try monitoring sys.dm_db_index_usage_stats for a while. Should tell you if the indexes are used often enough to keep them around..
July 24, 2015 at 10:05 am
lptech (7/24/2015)
Try monitoring sys.dm_db_index_usage_stats for a while. Should tell you if the indexes are used often enough to keep them around..
When you have duplicate indexes, all of them will show usage. Doesn't mean they're worth keeping.
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
July 24, 2015 at 3:49 pm
If these 2 nonclustered indexes have only index seeks in sys.dm_db_index_usage_stats then most likely you can drop them without sacrificing performance too much, but if there are a lot of index scans then it may be better to keep them. At least until you identify what queries and why scan these indexes.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply