Azure SQL Data Base: Missing Index

Question

Azure SQL Data Base: Missing Index

Fernando Gutierrez 325

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!

Sai Raghunadh M 4,575 Microsoft External Staff Moderator

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.

Sai Raghunadh M 4,575 Reputation points Microsoft External Staff Moderator

2024-09-13T16:28:01.6466667+00:00

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.
Sai Raghunadh M 4,575 Reputation points Microsoft External Staff Moderator

2024-09-16T11:21:23.35+00:00

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.

1 answer

Your answer

Sai Raghunadh M 4,575 Reputation points Microsoft External Staff Moderator

2024-09-12T15:04:58.1166667+00:00

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.
Sai Raghunadh M 4,575 Reputation points Microsoft External Staff Moderator

2024-09-13T16:28:01.6466667+00:00

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.
Sai Raghunadh M 4,575 Reputation points Microsoft External Staff Moderator

2024-09-16T11:21:23.35+00:00

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.

Answer 1

Alberto Morillo 35,506 MVP Volunteer Moderator

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.

0 comments

Share via

Azure SQL Data Base: Missing Index

1 answer

Your answer