15

If you run a query with 'Include Actual execution plan' the plan will also suggest indexes which are missing. The index details are inside MissingIndexes tag in the XML. Is there a situation when the plan includes multiple index suggestions? I tried different sql queries but couldn't come up with any query which generates two or more missing indexes.

27
+250

The Query Optimizer in SQL Server can make multiple missing index suggestions for individual queries. However the part of SQL Server Management Studio (SSMS) which displays execution plans visually only displays a single missing index suggestion; it looks like a bug. However these multiple index suggestions are visible in SSMS, eg in the properties for the first operator (eg SELECT), press F4.

Viewing multiple missing index suggestions in SSMS

As you've also noted, multiple suggestions are visible in the plan XML, or third-party tools like SQL Sentry Plan Explorer. Multiple suggestions will also be visible in the missing index DMVs (eg sys.dm_db_missing_index_details )

This simple query resulted in multiple suggestions for me:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.t1') IS NOT NULL DROP TABLE dbo.t1
CREATE TABLE dbo.t1
(
    rowId INT IDENTITY
)
GO

IF OBJECT_ID('dbo.t2') IS NOT NULL DROP TABLE dbo.t2
CREATE TABLE dbo.t2
(
    rowId INT IDENTITY
)
GO


INSERT INTO dbo.t1 DEFAULT VALUES
GO 100000

INSERT INTO dbo.t2 DEFAULT VALUES
GO 100000



SELECT *
FROM dbo.t1 t1
    INNER JOIN dbo.t2 t2 ON t1.rowId = t2.rowId
WHERE t2.rowId = 999;

HTH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.