March 29, 2010 at 6:38 am
I've come across a problem with an index that runs contrary to what I know about indexes. I'm hoping someone here can help. Here's the problem:
In addition to running regular checks on fragment levels, I check to see what suggestions SQL comes up with. One suggestion in particular has me perplexed. The suggestion is something like this [table1] (col1) INCLUDE (col2, col3). When I see a suggestion that has a high unique_compiles, I check to see if an existing index can be tweaked so I can save on some disk IO by creating a new index. The problem is there's an existing index that has all three columns as key columns. The only difference between the suggestion and the existing index is that col2 and col3 are key columns rather than included columns.
We're a small company of 5 developers so I'd have to do a bit of work to track down the query(s) that's causing the suggestion. Of course, from there I could find out more information. However, even if I were to do that work, my question would still be valid (I think). So I'm hoping someone can shed some light on the subject. For good measure, here's the query I'm using to get index suggestions; I believe it to be a common query if I'm not mistaken.
DECLARE @runtime datetime;
SELECT CONVERT (varchar, @runtime, 126) AS runtime, mig.index_group_handle, mid.index_handle, CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure, mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 AND NOT mid.database_id = 20 AND unique_compiles > 200
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;
Thank you,
John
March 29, 2010 at 7:24 am
There will be a reason, but there is not enough detail in your post to say what it is 🙂 To be fair, answering the question would probably require direct access to your database.
The suggestions made for missing indexes should always be taken as a broad general hint that there might be something worth looking at. Often, the suggestion is spurious, or present simply due to a limitation of the process that generates them.
In this particular case, I would be very tempted to disregard it, and move on to my next task.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 29, 2010 at 8:58 am
sqlsc-1053844 (3/29/2010)
In addition to running regular checks on fragment levels, I check to see what suggestions SQL comes up with. One suggestion in particular has me perplexed. The suggestion is something like this [table1] (col1) INCLUDE (col2, col3). When I see a suggestion that has a high unique_compiles, I check to see if an existing index can be tweaked so I can save on some disk IO by creating a new index. The problem is there's an existing index that has all three columns as key columns. The only difference between the suggestion and the existing index is that col2 and col3 are key columns rather than included columns.
What's the order of the columns in the existing index? If col1 is the leading column, then don't there's no need for the new index. If col1 is not the leading column, then the missing index and the existing are not the same.
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
March 29, 2010 at 11:40 am
My concern is that it has close to 10k compiles so it seems there's a need there. The seek is large as well but I don't remember the number.
March 29, 2010 at 1:37 pm
What is the order of columns in the existing index? Post the index's definition.
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
March 29, 2010 at 8:30 pm
sqlsc-1053844 (3/29/2010)
The suggestion is something like this [table1] (col1) INCLUDE (col2, col3)...the only difference between the suggestion and the existing index is that col2 and col3 are key columns rather than included columns.
John,
Unless you are omitting some crucial detail, it seems spurious.
It you want a more precise answer, give us some more detail 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 30, 2010 at 11:54 am
The existing index has four keys and no includes. I think you're asking how the suggestion relates to the existing. Here's that information.
Suggestion:
[myTable] (sugKey) INCLUDE (sugInclude1, sugInclude2)
Existing:
col1 (sugInclude1)
col2
col3 (sugInclude2)
col4 (sugKey)
March 30, 2010 at 11:57 am
sqlsc-1053844 (3/30/2010)
The existing index has four keys and no includes. I think you're asking how the suggestion relates to the existing. Here's that information.Suggestion:
[myTable] (sugKey) INCLUDE (sugInclude1, sugInclude2)
Existing:
col1 (sugInclude1)
col2
col3 (sugInclude2)
col4 (sugKey)
So the suggested index would support an index seek for a query like ...WHERE sugKey = x...the existing index might not, since sugKey is not the leading column in the index. Does that make sense to you?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 30, 2010 at 12:16 pm
So the key position needs to match the query? Or a better question might be, do you have any links to where I can read up on this?
March 30, 2010 at 12:24 pm
sqlsc-1053844 (3/30/2010)
So the key position needs to match the query? Or a better question might be, do you have any links to where I can read up on this?
Sure:
Introduction to Indexes by Gail Shaw[/url]
That comes in three parts...links at the bottom.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 30, 2010 at 12:39 pm
Thank you
March 30, 2010 at 12:46 pm
Also http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
Not as well written as the articles.
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply