Why activity monitor showing different wait statistics then DMV directly?


  • Hello,

    Why activity monitor showing completly different wait statistics compared to statistics coming from DMV directly? I cleared statistics first with DBCC command then run for a minute or so and now if I look at statistics in Activity Monitor top wait is Logging and yet it's number 5 coming from DMV

    Friday, January 24, 2014 9:33 PM

All replies

  • Hello,

    This is because the DMVs are cumulative until restart or until they are (some can be) cleared through the DBCC SQLPERF command. Activity monitor, however, shows what the state is between refreshes of the screen.

    If you would like to see how Microsoft does it, run an extended events or profiler trace and capture the tsql stack, you'll see exactly how it is done.

    Sean Gallardy | Blog | Twitter

    • Proposed as answer by John Sansom Sunday, January 26, 2014 5:59 PM
    Sunday, January 26, 2014 2:52 AM
  • I did reset with DBCC statistics for DMV and immediately checked and it's still not consistent with what is shown on a screen (not even close)
    Sunday, January 26, 2014 3:32 AM
  • Hello,

    Please re-read my post. The values in activity monitor are not cumulative whereas the DMVs are.

    Sean Gallardy | Blog | Twitter

    Sunday, January 26, 2014 4:24 PM
  • I understand that.

    Instantaneous values were consistenttly showing LOG_WAITS on top after 1 min of running . Cumulative values were reset via DBCC command at the start of the run and they shall also show LOG_WAITS at top, instead of that CXPACKET is at top and logging is on 5th place.

    Sunday, January 26, 2014 4:28 PM
  • Hello,

    This is due to multiple things, but mostly the fact that the DMV holds ALL waits including those that have 0 and activity monitor only shows certain waist that are grouped together. Activity monitor and the DMVs are not 1 to 1 as you've noticed.

    Activity monitor does not just do a 'select top 10' from the DMV, it groups "certain" waits together, sums them, and shows them in the categories.

    If you want to know WHICH and WHY, please run a profiler trace or extended events session again the server that you've connected activity monitor to. Then you will see exactly how it works.

    Sean Gallardy | Blog | Twitter

    Sunday, January 26, 2014 4:44 PM
  • It's not only not 1 to 1 but it's completely different and do not make sense. How something which shows on top every say 10s on Activity monitor will end up on 5th place after 1 minute and waits which did not even make to top 5 in instantenous reading occupying top spot in cumulative? Math does not add up.
    Sunday, January 26, 2014 4:49 PM
  • Hello,

    This is my last response as it's obvious you aren't reading or understanding my posts.

    Activity monitor is an application and does different things to the data. Part of that is filtering out waits stats that mean nothing such as the lazy writer sleep. I'm only going to post this one more time:

    Create a profiler trace or extended events session to SEE what activity monitor is doing, you'll understand after that. I'm not going to do this for you.

    It's the application (a.k.a. Activity Monitor) that is presenting the data differently and thus it will never be the same. The actual values are in the DMV, activity monitor IMHO should not be used for anything such as this because of the logic behind it, you will never get the full picture.

    Sean Gallardy | Blog | Twitter

    • Proposed as answer by John Sansom Sunday, January 26, 2014 5:59 PM
    Sunday, January 26, 2014 4:56 PM
  • I'm reading your posts but I don't understand them. The reason is not technical but purely common sense.

    There is a tool forefront which Microsoft put out there for everybody to use - activity monitor. It's screaming at me that for last 1 minute top wait for write log, yet despite that fact according to your statement, this all irrelevant and actual data is in DMV and according to DMV write log is not even top 5.

    So if you are just saying please stop using Activity Monitor as any sort of indicator of actual waits then I would take that as answer. This was supposed to be first answer to entire thread, not only that it shall be escalated to Microsoft to remove this tool altogether.

    Sunday, January 26, 2014 5:14 PM