Execution plan with multiple missing indexes

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.

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.

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

StackExchange Database Administrators Q#131383, answer score: 28