Impact value about missing indexes

  • msimone

    SSCommitted

    Points: 1647

    is there relation between Impact from sys.dm_db_missing_index_group_stats and Impact from cache plan?

    Thanks for all.

     

  • Grant Fritchey

    SSC Guru

    Points: 396692

    The information put into the missing index DMVs is taken from the information put into execution plans. So, yes. they are directly correlated.

    I strongly recommend you abandon the missing index DMVs. They have zero correlation with queries. You can't tell which query it's suggesting the missing index for (and they are very much suggestions, don't simply assume they're correct and implement them without testing). Without that correlation, you may be adding an index, and all the associated management and storage overhead, for a query that was run one time and will never be run again.

    If you want to use the missing index information, I suggest querying the plans in cache or the plans in Query Store to find missing index data there. The reason for this is you can correlate the missing index to a query.

    Also, side note, the impact statement is just a calculation based on the statistics available to the optimizer. It's not an accurate measurement based on your query, your data and your machine. I'm not saying don't use it. Do. Just understand what it's telling you.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • msimone

    SSCommitted

    Points: 1647

    Hello Grant, thank you very much for your answer, it was a doubt that I had.

     

     

     

  • Grant Fritchey

    SSC Guru

    Points: 396692

    Not a problem. We're all here to help out when and where we can.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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