Post reply

Missing Index suggested in execution plan even though index with same exact columns already exists!

  • November 18, 2011 at 9:15 am

    #248132

    OK, this is weird.

    I was analyzing the cached exec plan of a stored proc and found this missing index:

    <MissingIndexes>

    <MissingIndexGroup Impact="99.122">

    <MissingIndex Database="[ForecastDB]" Schema="[dbo]" Table="[MYTABLE1]">

    <ColumnGroup Usage="EQUALITY">

    <Column Name="[BatchID]" ColumnId="3" />

    </ColumnGroup>

    </MissingIndex>

    </MissingIndexGroup>

    </MissingIndexes>

    This index already exists!

    In fact, it was added a couple of days ago.

    Now, looking at the index-usage stats, I see this index has only been used for a couple of scans since its creation.

    But it is very much there.

    Why does the exec plan suggest it as a missing index?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • November 18, 2011 at 9:18 am

    #1410014

    It's a known bug. I don't know if and when it was fixed since I'm still heaviliy stuck on 2K5.

  • November 18, 2011 at 9:27 am

    #1410020

    Ninja's_RGR'us (11/18/2011)

    It's a known bug. I don't know if and when it was fixed since I'm still heaviliy stuck on 2K5.

    Thank you, I will google it further.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • November 18, 2011 at 9:28 am

    #1410022

    http://sqlskills.com/BLOGS/PAUL/post/Missing-index-DMVs-bug-that-could-cost-your-sanity.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 18, 2011 at 12:00 pm

    #1410119

    GilaMonster (11/18/2011)

    http://sqlskills.com/BLOGS/PAUL/post/Missing-index-DMVs-bug-that-could-cost-your-sanity.aspx

    Wow, thanks!

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply