Identify Missing Indexes in Your Windows Azure SQL Database

June 29, 2013 — 3 Comments

Windows Azure SQL Database is a Cloud flavored version of SQL Server. As of today (June 29th 2013), it isn’t 100% equivalent with the SQL Server SKUs that we are used to working with. Microsoft is continually adding new features to Windows Azure SQL Database so don’t get discouraged just yet. In my opinion, many of the "limitations" that are present in Windows Azure SQL Database are actually a good thing, because they force us as developers to use best practices in order to use it properly.

One of these limitations, is that we cannot profile the database by using the SQL Server Profile.

The teams at Microsoft have built an SQL Database Manager that can be access from your Windows Azure Management Portal ( http://manage.windowsazure.com ). Using this tool you can find missing indexes by browsing through the query statistics.

As you can imagine, this can take some time. Consequently, I set out to find a better way to find missing indexes without wasting a lot of time.

I found the following query on the sys.dm_db_missing_index_details (Transact-SQL) MSDN page.
This is an example script to see missing indexes as used by Microsoft Customer Support.

SELECT CONVERT (varchar, getdate(), 126) AS runtime,
       mig.index_group_handle,
       mid.index_handle,
       CONVERT (decimal (28,1),
        migs.avg_total_user_cost *
        migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans))
        AS improvement_measure,
       'CREATE INDEX missing_index_' +
       CONVERT (varchar, mig.index_group_handle) +
       '_' +
       CONVERT (varchar, mid.index_handle) +
       ' 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 CONVERT (decimal (28,1),
                   migs.avg_total_user_cost *
               migs.avg_user_impact *
              (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

3 responses to Identify Missing Indexes in Your Windows Azure SQL Database

  1. 

    Excellent post and thanks for the script.

    Like

  2. 

    Thanks a lot for sharing :)

    Like

Trackbacks and Pingbacks:

  1. Steps Towards Optimized Windows Azure SQL Databases | Alexandre Brisebois - November 30, 2013

    […] Identifying missing indexes requires that your database is experiencing “normal” load. Otherwise your findings may be skewed unexpected queries. Furthermore, you should analyze and consider each of the missing indexes identified by Windows Azure SQL Database. Blindly creating these indexes may create overlap, resulting in indexes that require more space than is really need. […]

    Like

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