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.
CONVERT (decimal (28,1),
(migs.user_seeks + migs.user_scans))
'CREATE INDEX missing_index_' +
CONVERT (varchar, mig.index_group_handle) +
CONVERT (varchar, mid.index_handle) +
' ON ' +
' (' + ISNULL (mid.equality_columns,'') +
CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL
END + ISNULL (mid.inequality_columns, '') +
ISNULL (' INCLUDE (' + mid.included_columns + ')',
'') AS create_index_statement,
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.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost *
(migs.user_seeks + migs.user_scans) DESC
Excellent post and thanks for the script.
Thanks a lot for sharing :)