Web Software Architecture and Engineering – Life on the Bleeding Edge

I have a love/hate relationship with MS SQL Server. While its easy to use and setup, its a pain to grow and manage. The tools seems to cover only 20% of the normal use case.

Anyways, wouldn’t it be nice if MS SQL Server actually told you where you are missing indexes, and where performance can be improved.

Well, it can! Actually, it won’t tell you, unless you ask… so to ask, run this script, and it will even have a column with the CREATE INDEX script for you!

 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 mig

    INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

    WHERE 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
    

More details found here: http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

Advertisements

Comments on: "Have SQL Server Tell You About Missing Indexes" (2)

  1. This is a good thing indeed. I’ve blogged about similar scripts on http://blog.1smartsolution.com myself. One thing to note though is that not all indexes should be added blindly. Too many indexes is same bad as no indexes. So often this may be a back and forth process where some indexes are added, then the server is monitored and if needed some indexes are deleted, then other added etc. But it worth the time spent.

  2. Sami,
    I saw a post from you on Luis Majano’s blog about your deployment process and it sounds very similar to the process I have been working to set up. Would you take some time to write a post about your process and describe what it does, how to set it up, and the benefits you have realized by using the process?
    Kind Regards,
    Micah Knox

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: