Azure SQL performance tuning

After recently migrating an application from on-premises to Azure, I have been keeping a close eye on its performance.

The application uses Azure SQL. The Azure portal puts forth recommendations to tune the database e.g. create new indices or drop duplicates. That works well.

However I found another SQL query, which provides more recommendations

This site ‘Tune your database’ Tune applications and databases for performance in Azure SQL Database and Azure SQL Managed Instance provided some useful guidance.

Running this query on the ‘Master’

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 AS mig
   INNER JOIN sys.dm_db_missing_index_group_stats AS migs
      ON migs.group_handle = mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details AS mid
      ON mig.index_handle = mid.index_handle
 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

provides a suggestion for an index creation

CREATE INDEX missing_index_5006_5005 ON [dbo].[missingindex] ([col2])

It also provides a numeric value as an indication of improvement. You can check the before and after Query path as well. I’ve tried this on a database I was working on and could see performance jump.

Note to self: some indexes might take 30-40 mins for creation; so best run after hours :)