Identify Missing Indexes in Your Windows Azure SQL Database

Windows Azure SQL Database is a Cloud flavored version of SQL Server. As of today (June 29^th 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