Understanding which missing indexes should be created
-
These are two of the missing indexes that are being suggested for one of the tables in my db:
CREATE INDEX [missing_index_118_117_CheckIng] ON [dbo].[CheckIng] ([ShippingID])
CREATE INDEX [missing_index_116_115_CheckIng] ON [dbo].[CheckIng] ([ShippingID]) INCLUDE ([ID1], [ID2])
Can anyone explain why I would want to create both? Why wouldn't just the second one be sufficient?
Thanks!
-
I can't think of any reason why you would need more that the second one.
Edit: I meant that I thought that the second index was the correct one and that the first index was not needed.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
In this case you definitely don't want the 2 indexes. Only 1 is required.
-
You don't want both. You want the second.
This is one of the major drawbacks of the missing index DMV. The optimiser considers each query in isolation when it optimises and hence it will put near-duplicates into the missing index DMV.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
What is the improvement measure for each of them ?
Thank You,
Best Regards,
SQLBuddy
-
I'm impressed that people clearly states that you need the sencond. I find it extremely unlikely that you need both, but which index you need is a different matter.
I'd say that you normally only need the second index, but it depends on the number of updates, and the number of queries that would benefit from each index.
If very few executions will need the second index, it may be more efficient to only have the first index and take the cost of a bookmark lookup when the included columns are needed. Especially if one or more of the included columns are frequently updated. In other cases, I'd say as the rest of the folks, go for the second index.
-
Maybe you need both for deadlock avoidance.
This happens roughly as follows: Any query that updates one or more included values in one or more rows of the included values has an locks on the second index. A query that uses the second index and is held on that lock has to wait for the transaction with the first query to end. If the two queries don't update the same columns and don't have to share an index, they don't interact. So if the held query doesn't access included values, it can use the first index and doesn't need to wait for access to the second.
Even if when there isn't a deadlock issue, tou may find that response times are improved by eliminating those lock clashes.
Bart Duncan has a series of Blog entries on Deadlock Troubleshooting that starts here and if I remember correctly he explains this better than I can.
edit:- If it were me, I'd add the second index and then run the adviser again - but run it on all you queries that access any of that data, not just on one, and see what comes out.
Tom
-
Tom.Thomson (9/17/2011)
Maybe you need both for deadlock avoidance.It's possible, but I wouldn't start with both on the off chance that a deadlock may occur.
The bottom line is that the missing index detection is very simplistic. If I ran these three queries (and there were no non-clustered indexes existing) I'd get three entries in the missing index DMV even though one index is sufficient.
SELECT ID FROM SomeTable Where Col2 = @Var
SELECT ID, Col1, Col2 FROM SomeTable Where Col2 = @Var
SELECT ID, Col1, Col2 FROM SomeTable Where Col1 = @Var1 and Col2 = @Var2
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (9/17/2011)
Tom.Thomson (9/17/2011)
Maybe you need both for deadlock avoidance.It's possible, but I wouldn't start with both on the off chance that a deadlock may occur.
Looks as if you were replying while I was editing my comment to sa y first add the second one and see what happens. I guess we agree on that.
The bottom line is that the missing index detection is very simplistic. If I ran these three queries (and there were no non-clustered indexes existing) I'd get three entries in the missing index DMV even though one index is sufficient.
SELECT ID FROM SomeTable Where Col2 = @Var
SELECT ID, Col1, Col2 FROM SomeTable Where Col2 = @Var
SELECT ID, Col1, Col2 FROM SomeTable Where Col1 = @Var1 and Col2 = @Var2
Is it really that flakey? WOuld you get the same three if you fed it the same queries in a different order?
If so maybe I should have been less hard on people who proliferated indexes as if they came for free - maybe they were just doing what MS's adviser software told them.
Tom
-
Tom.Thomson (9/17/2011)
Is it really that flakey? WOuld you get the same three if you fed it the same queries in a different order?Yes. (though those 3 are actually too simple to populate missing indexes, they get the trivial plan optimisation and hence aren't considered)
If so maybe I should have been less hard on people who proliferated indexes as if they came for free - maybe they were just doing what MS's adviser software told them.
It's not DTA (database tuning advisor) that this is coming from. DTA's a little smarter, it does index analysis at the workload level.
These suggestions come from the Missing Index DMV. That's populated one index at a time by the query optimiser. It considers each query in isolation at compile time and puts into the DMV the index that it could have used to do a better job on that and only that query. In other words, it's index analysis on the statement level.
It is incredibly common to find near-duplicate indexes in that DMV, and near-duplicates of existing indexes.
I would never, never say to create all indexes in Missing Indexes (as some people unfortunately do recommend) as that'll create more redundant and useless indexes than even DTA can do.
2 include columns (unless they are absolutely huge or the DB gets thousands of inserts a second) are unlikely to make a noticeable impact on DB size or insert performance over an index without them, and in general queries that do seek + key lookup are more prone to deadlocks than ones that don't
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Adding a second table in to disqualify them from trivial plan optimisation...
CREATE TABLE t1 (ID INT IDENTITY PRIMARY KEY,
Col1 int,
Col2 int
)
go
CREATE TABLE t2 (
ID INT IDENTITY PRIMARY KEY,
t1_ID INT
)
INSERT INTO t1 (Col1, Col2)
SELECT o.object_id, column_id
FROM sys.columns AS c CROSS JOIN sys.objects AS o
INSERT INTO t2 (T1_ID)
SELECT ID FROM t1
--
SELECT t1.ID, Col1, Col2 FROM t1 INNER JOIN t2 ON t1.id = t2.t1_ID WHERE Col1 = 95 AND Col2 = 1
SELECT t1.ID, Col1, Col2 FROM t1 INNER JOIN t2 ON t1.id = t2.t1_ID WHERE Col2 = 1
SELECT t1.ID FROM t1 INNER JOIN t2 ON t1.id = t2.t1_ID WHERE Col2 = 1
GO
SELECT * FROM sys.dm_db_missing_index_details AS ddmid WHERE object_id = OBJECT_ID('t1')Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Rambling on a bit...
One of the keys to good indexing is to find the fewest number of indexes that support as many of the queries in the database as possible. Hence if I have a choice between a single column index (which has relatively few uses) and a slightly wider index, I'll chose the wider in almost every case. The times I wouldn't is when those extra couple of columns are huge (LOB columns, massive strings, etc).
That's not to say I'd create indexes that include every column, that's just silly in most cases (though in data warehouse systems it may be useful), but 2 or 3 extra include columns of smaller data types. Honestly, except in the heaviest load systems around that's not going to be noticable over the likely improvement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
DTA is not very good either. I've had a few cases where my customers had applied the suggestions from DTA, and I had to undo those operations to get the load down on the server (yes, the load on the server increased, and the performance dropped after using DTA). Both the DMVs and DTA can be great tools, but only if you actually know indexing. Otherwise you may run into "all kinds of problems".
-
Very Good advice from Gail.. Many Times Missing Indexes DMVs gives overlapping indexes (i.e indexes that are subsets of a large index) and one should be very careful in choosing the right index..
Missing Index query by MS is an excellent way to identify the missing indexes and you need to select the index that has the highest Improvement Measure from various combinations.. Suppose if an index has improvement of 10000 and another combination has an improvement of 50 .. we can definitely ignore the later one on the same table ...
Wider indexes may cover many combinations and Narrow indexes may be much quicker for crucial queries .. so the judgement factor should be the improvement measure (based on user seeks , user scans ..) and how the executions plans are going to be improved..
Both Wider and Narrow indexes have their own pros and cons ... Each one has its own place..
But the best method is to analyze the Missing Index Recommendation by comparing with the actual query execution plan if the query is known.. Recommended method is to not create any index blindly .. Creating indexes blindly will only invite troubles..
Also DTA is an excellent tool to fine tune queries ... and again it gives overlapping indexes .. If you are using DTA to fine tune individual queries it will be immensely helpful provided you come to a conclusion based on the execution plans ..
Again if you use DTA based on the profiler workload, the recommendations should be judged very cautiously ..
So always analyze the missing index recommendations by comparing with the query executions plans and
only after knowing how its going to help the query performance ( i.e how it avoids Key Lookups, Index scans etc..)
And try to analyze the performance step by step and don't create all the indexes at once..
Thank You,
Best Regards,
SQLBuddy
-
GilaMonster (9/17/2011)
Adding a second table in to disqualify them from trivial plan optimisation...Any subquery expression will do. This simpler (no join) form does not qualify for trivial plan:
SELECT t1.ID, t1.Col1, (SELECT t1.Col2) AS Col2
FROM dbo.t1 WHERE t1.Col1 = 95 AND t1.Col2 = 1
In case anyone else was wondering why this demo needs to avoid a trivial plan: missing index detection only occurs during full (cost-based) optimization. Simplification and trivial plan stages come before that.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply