May 8, 2009 at 2:37 pm
I have the sql below to tell me what are the missing indexes
SELECT
avg_user_impact AS average_improvement_percentage,
avg_total_user_cost AS average_cost_of_query_without_missing_index,
'CREATE INDEX ix_' + [statement] +
ISNULL(equality_columns, '_') +
ISNULL(inequality_columns, '_') + ' ON ' + [statement] +
' (' + ISNULL(equality_columns, ' ') +
ISNULL(inequality_columns, ' ') + ')' +
ISNULL(' INCLUDE (' + included_columns + ')', '')
AS create_missing_index_command
FROM sys.dm_db_missing_index_details a INNER JOIN
sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats c ON
b.index_group_handle = c.group_handle
WHERE avg_user_impact > = 89
Any good way to determine what sql (tsql, sp, etc) is running that is creating this these results?
May 8, 2009 at 2:51 pm
May 10, 2009 at 5:22 am
I have yet to find a mechanism for reading the missing indexes DMV's and then connecting it to actual queries and query plans. Instead, I've used the fact that the missing index information is stored with the execution plan to pull it all together at once. My queries are still a bit primitive, but they ought to help a bit. Here was the last one that I posted[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply