SQL Server – Copy & Paste ‘Missing Index’ from Execution Plan in SSMS

sql serverssms

Notwithstanding that blindly creating suggested missing indices is less than ideal, is there a way to copy and paste the suggested missing indices from the "Execution plan" tab in SQL Server Management Studio?

You get a tool-tip on mouse hovering over it but there does not appear to be a right-click menu copy them. For years I've just typed out any I needed but am curious if the base form can be easily gotten from the tab.

Using SSMS 2017

Best Answer

You can use the right click feature, but it's limited. Not just because, like you mentioned, the missing index requests are kinda lame, but also because it will only ever show you one, and the one it shows you might not be the best one.

Take this query:

SELECT COUNT(*) AS records
FROM   dbo.Votes AS v
JOIN   dbo.Comments AS c
ON v.PostId = c.PostId
WHERE  c.Score > 1
AND    v.CreationDate >= '2008-01-01';

When I run it and look at the query plan, there's one missing index request listed with an estimated impact of ~16%.

NUTS

But if I go into the XML, there's another request with a higher estimated impact:

NUTS

If you want to see all of the missing index requests in a plan, you need to go outside of SSMS.

Free tools like SentryOne Plan Explorer, and an open source script I contribute to, sp_BlitzCache will list out all of the missing index requests in the XML.

In sp_BlitzCache, the missing index XML column is clickable:

EXEC dbo.sp_BlitzCache @DatabaseName = 'StackOverflow2010'

NUTS

And will bring up information like this:

NUTS

So you may not want to rely on SSMS and the right click. It could be hiding a lot of information from you.

Related Question