[SQL]T-SQL, Find out SQL Missing Index through DMVs.

2014-07-09

The source was from bartduncan, here.


SELECT migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
    ,'CREATE INDEX [missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE 
        WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
    ,migs.*
    ,mid.database_id
    ,mid.[object_id]
FROM sys.dm_db_missing_index_groups migINNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handleINNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handleWHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC


Note: The DMVs T-SQL scripts just show the best way for the current query against the database. it won’t tell you that you’ve had other indexes covered columns. Details, please see the link above.

1 comment

olianaebben said...

There at the moment are|are actually} classic slots that offer bonus features properly as|in addition to} extras. These are A, K, Q and J, as properly quantity of|numerous|a selection of} diamonds, spades or hearts, and 배당사이트 10 and nine royals. Although they are frequent in many of} video games, their graphics and themes may be more expressive. NetEnt's Dead or Alive II slots will give symbols a Wild West look. There might be a selected variety of symbols displayed on the reels. You will win if could get} the best combination of symbols.

Newer Older