This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(16, 16%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EFG%3C/text%3E%3C/svg%3E)
Hello
I would like to know which script to run to check if there is a Missing Index in an Azure SQL Database.
In the Azure Portal it shows me recommendations for Indexes to apply, but it does not tell me when there is a Missing Index.
I would like to know which script within SQL Management Studio can be used to check if there are Missing Indexes.
Thanks!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(198.4, 69%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESR%3C/text%3E%3C/svg%3E)
Hi @Fernando Gutierrez,
Thanks for the question and using MS Q&A platform.
Try the below script.
SELECT
DB_NAME(database_id) AS DatabaseName,
OBJECT_NAME(object_id, database_id) AS TableName,
equality_columns AS EqualityColumns,
inequality_columns AS InequalityColumns,
included_columns AS IncludedColumns,
'CREATE INDEX [missing_index_' + CONVERT(VARCHAR, index_handle) + '] ON ' +
OBJECT_NAME(object_id, database_id) +
' (' + ISNULL(equality_columns,'') +
CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN ',' ELSE '' END +
ISNULL(inequality_columns, '') +
')' +
ISNULL(' INCLUDE (' + included_columns + ')', '') AS CreateIndexStatement
FROM
sys.dm_db_missing_index_details
ORDER BY
DatabaseName, TableName;
This script will generate a list of missing indexes.
I hope this information helps, please do let us know if you have any Queries.
Hi @Fernando Gutierrez,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Hi @Fernando Gutierrez,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
The best you can do is to grab missing indexes from query plans in the plan cache as shown in this support article and consolidate yourself the required index with existing indexes on the database. Creating indexes suggested on Azure Portal often ends with a proliferation of duplicated indexes.