Post reply

sys.dm_db_missing_index_group_stats - why does avg_user_impact is greater than 1000 sometinmes

  • October 4, 2011 at 6:43 pm

    #259900

    Sometimes avg_user_impact is greater than 1000 for most of time it is less than 100%( which i expect it to be), can someone please tell me why is the value more than 1000 sometimes? Is this a bug? Shouldnt this value always be less than 100?

  • October 4, 2011 at 10:09 pm

    #1390492

    If you are seeing an avg_user_impact of 1000, then adding the appropriate index could improve your query performance by 10x. If a query takes 1 second to run with those indexes, and you remove the index, you could drop performance down to 10 seconds which would indicate an avg_user_impact of 1000.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • October 5, 2011 at 8:03 am

    #1390698

    SQLRNNR (10/4/2011)

    If you are seeing an avg_user_impact of 1000, then adding the appropriate index could improve your query performance by 10x. If a query takes 1 second to run with those indexes, and you remove the index, you could drop performance down to 10 seconds which would indicate an avg_user_impact of 1000.

    Thanks.what would you say about the one which show less than 100%?

  • October 5, 2011 at 8:36 am

    #1390716

    Same principle - you will get a performance gain but it will only be less of an impact.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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