locked
sys.dm_db_index_operational_stats don't match with sys.dm_os_wait_stats RRS feed

  • Question

  • Hi,

    Why waits in sys.dm_db_index_operational_stats don't match with waits in sys.dm_os_wait_stats ? E.g., the below queries return different values even though the first and second should return very similar value; similarly the third and fourth queries.

     

    SELECT SUM(page_io_latch_wait_in_ms + tree_page_io_latch_wait_in_ms) FROM sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL);
    SELECT SUM(wait_time_ms) FROM sys.dm_os_wait_stats WHERE wait_type LIKE 'pageiolatch%'; -- Significantly bigger than value from the above query
    
    SELECT SUM(page_latch_wait_in_ms + tree_page_latch_wait_count) FROM sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL);
    SELECT SUM(wait_time_ms) FROM sys.dm_os_wait_stats WHERE wait_type LIKE 'pagelatch%'; -- Significantly bigger than value from the above query
    
    

    Even if I consider sys.dm_db_index_operational_stats don't include internal objects the numbers are still too much different. Why ?

     

     

    Thank you


    Sunday, July 24, 2011 6:46 PM

Answers

  • Hi,

    I don't think you can rely on the numbers of sys.dm_db_index_operational_stats to match with other DMVs.

    It is documented that the data in the above DMV is NOT persisted nor is transactionally consistent. Also as the metadata cache is moved in and out, sometimes you may never see values in that DMV also if a index was used earlier.

    Ref: http://msdn.microsoft.com/en-us/library/ms174281.aspx

    Look for How the Counters in the Metadata Cache Are Reset


    --Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/
    • Marked as answer by MD_2011 Monday, July 25, 2011 6:40 AM
    Monday, July 25, 2011 12:56 AM
  • Read more on this here, 

    Quote

    The metadata cache does not contain any user tables when the server is first started or, for a database, when it is first attached.  Moreover, if a database contains many tables, the cache may not be large enough to store all of them at once and some may be evicted.  If a table is evicted, it will cease to appear in the DMV output and its counters will be reset to zero.

    UnQuote

    http://blogs.msdn.com/b/craigfr/archive/2009/07/29/correction-to-my-prior-post-on-sys-dm-db-index-operational-stats.aspx

     


    Rgds, Krishna Http://Blogs.SQLServer.in/

    • Marked as answer by MD_2011 Monday, July 25, 2011 6:40 AM
    Monday, July 25, 2011 1:33 AM

All replies

  • Hi,

    I don't think you can rely on the numbers of sys.dm_db_index_operational_stats to match with other DMVs.

    It is documented that the data in the above DMV is NOT persisted nor is transactionally consistent. Also as the metadata cache is moved in and out, sometimes you may never see values in that DMV also if a index was used earlier.

    Ref: http://msdn.microsoft.com/en-us/library/ms174281.aspx

    Look for How the Counters in the Metadata Cache Are Reset


    --Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/
    • Marked as answer by MD_2011 Monday, July 25, 2011 6:40 AM
    Monday, July 25, 2011 12:56 AM
  • Read more on this here, 

    Quote

    The metadata cache does not contain any user tables when the server is first started or, for a database, when it is first attached.  Moreover, if a database contains many tables, the cache may not be large enough to store all of them at once and some may be evicted.  If a table is evicted, it will cease to appear in the DMV output and its counters will be reset to zero.

    UnQuote

    http://blogs.msdn.com/b/craigfr/archive/2009/07/29/correction-to-my-prior-post-on-sys-dm-db-index-operational-stats.aspx

     


    Rgds, Krishna Http://Blogs.SQLServer.in/

    • Marked as answer by MD_2011 Monday, July 25, 2011 6:40 AM
    Monday, July 25, 2011 1:33 AM