Dmvs to show missing indexes

Question

Post reply

Dmvs to show missing indexes

SQL_Surfer

SSCrazy Eights

Points: 8318
More actions
January 5, 2020 at 1:44 am

#3712423

Any dmvs(sripts) to show missing indexes in azure sql db?
January 6, 2020 at 3:48 am

This was removed by the editor as SPAM
August 10, 2020 at 9:41 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Grant Fritchey SSC Guru Points: 398713 More actions · Answer 2

Yes. The exact same queries you use to look at missing indexes in the boxed SQL Server product. Under the covers, Azure SQL Database is just SQL Server. If ever you have a doubt about a DMV being in Azure SQL Database, look at the name. If it's sys.dm_db_*, I guarantee it's there. The others, may or may not be. Most of them are. However, if it's a database scoped DMV, it's in Azure SQL Database.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398713 More actions · Answer 3

Side note:

You know the missing index DMVs are just suggestions, right?

Further, there is no correlation between the missing index information and queries. Therefore, you can't know if the missing index suggestion was for one query that will never ever get run again, or for a query that is called 10,000 times a minute. A better way to use the missing index information is to query the plans in plan cache or in Query Store for their missing index information. That way, you can correlate the suggested missing indexes to actual queries.

Further further, the missing index suggestions should not be taken at face value. You must test them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

To Grant's point, I wouldn't even call them "suggestions". They're a "clue" that some code somewhere might (strong emphasis in "might") benefit from an index. I don't know about lately but I have seen missing index hints that suggest a new index needs to be created even when an identical (except for the name) index already exists.

It also doesn't suggest that certain indexes could be combined to reduce the load on INSERTs, UPDATEs, and DELETEs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)