Help needed with sys.dm_db_missing_index_details DMV

  • Hi All,

    I am working on an OLTP system with 28 GB RAM given to the SQL Instance. But, it is running very slow and getting frequent timed out errors at the application end.

    While investigating on the slowness issue, I have found that several indexes are missing on the tables which are having huge select statements running on them on a very frequent basis. , I am finding it hard to find the corresponding SQL statements that are being used by the columns which are missing the indexes.

    I am wondering if there are any script with which I can use for this purpose to find out the SQL statements and their corresponding missing indexes. Else, if anyone can put some light on how this can be achieved, then it will be a really great help for me.

    Additional information :

    1. The sql instance is a SQL server 2008 64 bit instance.

    2. Application : Application is a Opentext java based product with a very high amount of select statements running on the databases.

    Please help me out in this issue.

    Regards,

    Shovan.

  • Try the below query to find the missing index:

    Note: You may have to really evaluate the proposals from the execution to decide the needs.

    SELECT user_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

    WHERE mid.database_id = DB_ID() and OBJECT_NAME(object_id) = '<Table_Name.'

    ORDER BY index_advantage DESC ;

  • shovankar (1/10/2012)


    I am working on an OLTP system with 28 GB RAM given to the SQL Instance. But, it is running very slow and getting frequent timed out errors at the application end.

    Optimise your queries. See

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    I am finding it hard to find the corresponding SQL statements that are being used by the columns which are missing the indexes.

    Can't do that from missing indexes. They show what indexes the optimiser thinks might be useful, but there's no tie back to the queries that ran.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • There's an article here[/url] that describes how to extract the cached plans that contain missing indexes information. Doesn't do exactly what you're after, but could get you started.

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • Forget the missing index results for now and focus on working through the two articles Gail posted. Adding missing indexes is great and all but what if the real problem is several poorly written stored procedures and views? You wouldn't be fixing the underlying problem. I go back and forth with some developers on this from time to time. They usually just want to throw a couple indexes at a slow performing query to to see if it fixes the problem. They change code at a last resort because it takes more time to do so. It's worth it to do though. If at the end of the process you find out it was just a missing index that was causing the slowness at least you'll know you're working with optimal code. For me anyways, that rarely ever happens. It's usually a combination of things.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply