January 8, 2014 at 12:15 pm
Hi all,
Hopefully someone can shed some light on this issue I'm seeing.
I'm using Glenn Berry's scripts to get recommended indexes from a particular instance/database, and I have implemented the index (so i think). However, the query still returns it as a "missing" index.
Code to find missing indexes:
SELECTuser_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,
migs.last_user_seek,
mid.statement AS [Database.Schema.Table],
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.unique_compiles,
migs.user_seeks,
migs.avg_total_user_cost,
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;
The index that is recommended:
index_advantageDatabase.Schema.Table
147830591.8[DB1].[dbo].[table1]
equality_columns
[state]
inequality_columns
[completedate]
included_columns
[messageid], [type], [haschildren]
unique_compiles
1
user_seeks
742688
The index that is present has this definition, and it appears to be used (albeit not as much as expected based on the index_advantage) based on the index usage stats, but I can't figure out why it keeps coming up in the results when looking for recommended indexes.
CREATE NONCLUSTERED INDEX [idx_nc_sql_portal_state_completedate] ON [dbo].[table1]
(
[state] ASC,
[completedate] ASC
)
INCLUDE ( [messageid],
[type],
[haschildren]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
Index usage stats:
Index Name
idx_nc_sql_portal_state_completedate
Total Writes
3887142
Total Reads
40948
Thanks in advance for any help!
Steve
January 17, 2014 at 1:53 pm
Here you go.
http://www.sqlskills.com/blogs/paul/missing-index-dmvs-bug-that-could-cost-your-sanity/[/url]
Just as with the performance tuning wizard you should take the recommended indexes as a suggestion, not as a rule. You still need to verify that the index is sane, not duplicating something that's already covered, and you should test to verify it will actually help performance and not hurt more.
January 18, 2014 at 10:18 am
benjamin.reyes (1/17/2014)
Here you go.http://www.sqlskills.com/blogs/paul/missing-index-dmvs-bug-that-could-cost-your-sanity/[/url]
Just as with the performance tuning wizard you should take the recommended indexes as a suggestion, not as a rule. You still need to verify that the index is sane, not duplicating something that's already covered, and you should test to verify it will actually help performance and not hurt more.
Great link, thanks for sharing! And I agree with Paul's title, it has been costing my sanity in this instance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply