Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 24 down vote accepted
+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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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