Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Finding missing indexes in sql server 2005 Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 2:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 25, 2006 11:24 PM
Points: 9, Visits: 1
Comments posted to this topic are about the item Finding missing indexes in sql server 2005
Post #401718
Posted Wednesday, November 14, 2007 3:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:29 AM
Points: 1,189, Visits: 787
Thanks for the sql.

However, why not going through the next step and return the index details at the same time...
Something like

SELECT TOP 20 mid.statement as table_name, equality_columns, inequality_columns, included_columns
, CAST(index_advantage AS INT) AS index_advantage -- cast to hint just to make it more readable
FROM (
SELECT (user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage
, migs.group_handle
FROM sys.dm_db_missing_index_group_stats migs
) as migs_adv,
sys.dm_db_missing_index_groups mig,
sys.dm_db_missing_index_details mid
WHERE migs_adv.group_handle = mig.index_group_handle
AND mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC

;)
Post #422012
Posted Wednesday, August 6, 2008 3:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 5:16 AM
Points: 49, Visits: 372
I'm sorry, a bit in the dark. What does the outcome tell me?
Post #547342
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse