May 28, 2010 at 2:36 am
Hi,
I'm using the SQL Server 2008 sys.dm_db_missing_index_details and sys.dm_db_missing_index_group_stats views to determine what kind of indexes my DB is missing.
There's a OrderDetail table in the DB which has CustomerNo and OrderNo columns and there's a nonclustered index (without included columns or anything) on each of them.
But when i run the dmv's it tells me i need a index on the CustomerNo column in the OrderDetails table. Equality_Columns = CustomerNo and both Inequality_Columns and Included_Columns are NULL and the Avg_User_impact = 98.
I'm not getting why the view tells me to create a index on the CustomerNo column because it already has that index.
Does anybody know an explanation for this?
May 28, 2010 at 9:11 am
Do you have a single index on both Customer and OrderNo or two separate NC indexes?
-- Gianluca Sartori
May 29, 2010 at 3:19 am
Well...actually we have both.
There's a single index on the customerNo column, a single index on the OrderNo column and an index on both columns together
May 29, 2010 at 6:57 am
Known bug, fixed in SQL11, will not be fixed in 2008 or 2005:
Lesson: don't use dynamic cursors!!!
Confused by the link?
See:
http://www.sqlskills.com/BLOGS/PAUL/post/Missing-index-DMVs-bug-that-could-cost-your-sanity.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply