Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Explain why indexes were skipped

Feature request: expose information about what indexes the optimizer considered while compiling an execution plan. Specifically, the index names and WHY the index was rejected for that particular query. This could potentially be included in per-operator execution plan properties (see attached mockup), an extended event target, or some other avenue.

Reason: many developers and DBAs wonder why certain indexes on their tables aren't being chosen for specific queries. This gives them better tools to figure that out and make better use of the SQL Server product.

I've blogged about this feature request in more detail here: http://joshthecoder.com/2018/06/21/feature-request-index-rejection-reasons.html

This request was inspired by a feature of the RavenDB document database: https://ayende.com/blog/183425-C/ravendb-4-1-features-explain-that-choice

148 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

Joshua Darnell shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

2 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Daniel L commented  ·   ·  Flag as inappropriate

    This is a great Idea and a feature I am missing from my days with SAPs SQL Anywhere (ASA).

    "Interactive SQL" which is a T-SQL Editor that can run and analyze statments, allows you to see the following:

    Indexes considered

    -- seq --
    RowsReturned
    7560
    RunTime
    0.018905
    CPUTime
    0.018905
    DiskReadTime
    0
    DiskWriteTime
    0
    DiskRead
    0
    DiskWrite
    0

    -- Table1 UNIQUE (Col1,Col2) --
    Selectivity 0.013227513%
    RowsReturned
    1
    RunTime
    2.5667e-005
    CPUTime
    1e-005
    DiskReadTime
    1.5667e-005
    DiskWriteTime
    0
    DiskRead
    0.0015873
    DiskWrite
    0

    This is a List of the different indexes or "ways to access the requested data".
    And it is awesome to see what was skipped and why.

Feedback and Knowledge Base