September 28, 2012 at 4:22 am
Quick one I have the following index in place followed by a missing index. With a MASSIVE impact advantage missing index (second row).
How do I consolidate these 2 together, this table is heavily written to, so I don't want to just add another index if there is room for cosolidation I would prefer that.
Index Covering ColumnsIncluded
Current (LIVE)A,B D,E,F
Missing IndexA D,B,E,F
Column b is a varchar(20).
September 28, 2012 at 5:35 am
emile.milne (9/28/2012)
Quick one I have the following index in place followed by a missing index. With a MASSIVE impact advantage missing index (second row).How do I consolidate these 2 together, this table is heavily written to, so I don't want to just add another index if there is room for cosolidation I would prefer that.
Index Covering ColumnsIncluded
Current (LIVE)A,B D,E,F
Missing IndexA D,B,E,F
Column b is a varchar(20).
Can you post the actual plan as a .sqlplan attachment? Looks to me that the missing index requirement is actually met by the current index. The missing index' leading column A is the leading column in the existing index, and the include columns of the missing index are present. The "missing index" widget is known for throwing up false positives.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 28, 2012 at 5:38 am
The existing index isn't perfect for the query (and the missing index DMV lists the perfect index), but it may well be good enough. Is the query slow?
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
September 28, 2012 at 6:13 am
My post wasn't clear
Current Index,
Covering Index Columns
A,B
Included
D,E,F
Missing
Index Column
A
Included
D,B,E,F
GilaMonster
I thought the same the query should be covered by the index in place.
is the query slow, everything is slow, I have taken over a db with virtually no indexes. The odd NON_Clustered PK every now and then.
September 28, 2012 at 6:18 am
In that case, you don't need to add another index, the current index will completely satisfy that query.
Now, it's not the perfect index, which is why missing indexes shows that a 'better' index could be created. This is why you don't trust 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 recoverability
September 28, 2012 at 6:21 am
emile.milne (9/28/2012)
With a MASSIVE impact advantage missing index (second row).
Why do you think there would be a massive impact?
September 28, 2012 at 6:31 am
SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL
THEN ''
ELSE CASE WHEN mid.equality_columns IS NULL
THEN ''
ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL
THEN ''
ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
ORDER BY 2 DESC , 3 DESC
When I run this that missing index is top with 3BN advantage, server last restarted in June.
I use the DMV as a guide, and see if consolidation is possible before adding. Also way up 1 or to other factors. Updates etc on the table.
September 28, 2012 at 6:54 am
emile.milne (9/28/2012)
When I run this that missing index is top with 3BN advantage, server last restarted in June.
I use the DMV as a guide, and see if consolidation is possible before adding. Also way up 1 or to other factors. Updates etc on the table.
In this situation, I'd create the index and test with a couple of queries - plans saved before and after - and compare. Preferably on a dev or test box first. As Gail pointed out, the existing index isn't ideal, but it's close, and I think the estimated lift is wildly out.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 28, 2012 at 8:28 am
ChrisM@Work (9/28/2012)
As Gail pointed out, the existing index isn't ideal, but it's close, and I think the estimated lift is wildly out.
The only thing that's not ideal about the existing index is that it's wider than necessary, but that won't be much of an overhead for queries using it. To be honest, if I was looking at that scenario (and assuming it's exactly as shown), I wouldn't even consider adding the new index. There's just not enough gain.
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply