Detect missing indexes

  • Hi all,

    I have a script to detect missing indexes in my database, but I'm not sure about info in this script to help me determine to create these indexes. Could you explain the meaning of the info and which main factors will help us determine to create missing indexes?

    Thanks,

    SELECT sys.objects.name

    , (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact

    , 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL

    THEN ''

    ELSE CASE WHEN mid.equality_columns IS NULL

    THEN ''

    ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL

    THEN ''

    ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement

    , mid.equality_columns

    , mid.inequality_columns

    , mid.included_columns

    FROM sys.dm_db_missing_index_group_stats AS migs

    INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()

    INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID

    WHERE (migs.group_handle IN

    (

    SELECT TOP (500) group_handle

    FROM sys.dm_db_missing_index_group_stats WITH (nolock)

    ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))

    AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1

    ORDER BY Impact DESC

  • definitely above query will provide you some hint BUT not tell you exact picture, i will suggest you to pick the long running or resource intensive queries, have a detailed look into it with the help of exec plan and then decide which tables need index modification or additions?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • You don't create indexes just based on that. The missing index DMV are suggestions, nothing more. Create an index on a test server, run a representative load, see if the index improved performance. If so, create on prod. If not, discard and test further.

    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
  • Thanks for all,

    I will consider them before applying.

  • GilaMonster (1/22/2013)


    You don't create indexes just based on that. The missing index DMV are suggestions, nothing more. Create an index on a test server, run a representative load, see if the index improved performance. If so, create on prod. If not, discard and test further.

    BTW, I have another question. Do we should create a non-clustered/clustered index on GUI column?

    I'm monitoring a database and see that they always use GUI column as parent-child relationship. I remember that indexes GUI columns are frequently fragmentation. Is is right?

  • Dung Dinh (1/22/2013)


    Do we should create a non-clustered/clustered index on GUI column?

    WHat are they ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (1/22/2013)


    Dung Dinh (1/22/2013)


    Do we should create a non-clustered/clustered index on GUI column?

    WHat are they ?

    I mean that the columns with data type = uniqueidentifier.

  • Dung Dinh (1/22/2013)


    I mean that the columns with data type = uniqueidentifier.

    people use them as a key part though it is not recommended to have lengthy keys or indexes it leads to fragmentation

    BUT you cant decide whether or which guid typed column will be part of index without looking into query.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 8 posts - 1 through 7 (of 7 total)

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