SSMS 18.4 (SQL 2014) Activity Monitor Always Blank

  • wellen_reiter wrote:

    next update:

    if i watch the CPU in taskmanager, after i openend processes in activity monitor, the vCPUs goes from 10 % to 30%. So it seems that the query don't waits but really calculates something in this time...

    i'm no expert on this one - but I suspect that 30% is a virtual figure of your provisioned CPU, but due to the way VM works it has directed the resources elsewhere.

    to be honest, i'm out of ideas... when it comes to VMware. I don't know how else to help you...sorry

    MVDBA

  • MVDBA (Mike Vessey) wrote:

    wellen_reiter wrote:

    next update:

    if i watch the CPU in taskmanager, after i openend processes in activity monitor, the vCPUs goes from 10 % to 30%. So it seems that the query don't waits but really calculates something in this time...

    i'm no expert on this one - but I suspect that 30% is a virtual figure of your provisioned CPU, but due to the way VM works it has directed the resources elsewhere.

    to be honest, i'm out of ideas... when it comes to VMware. I don't know how else to help you...sorry

    This all comes back to the code that is being executed.  The original code uses ROW_NUMBER

    LEFT OUTER JOIN

    (

    -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as

    -- waiting for several different threads. This will cause that thread to show up in multiple rows

    -- in our grid, which we don't want. Use ROW_NUMBER to select the longest wait for each thread,

    -- and use it as representative of the other wait relationships this thread is involved in.

    SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num

    FROM sys.dm_os_waiting_tasks

    ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1

    On larger systems - the row_number() causes the query to take longer than the timeout value and then activity monitor pauses.  Why this DMV is impacted or why this code is impacted isn't clear...what is clear though is that the processes tab works fine on smaller systems but fails consistently on larger systems regardless of the number of active processes.

    Just for example - this problem exists on my test system which has minimal users and processes, but does not exist on another system that is much smaller.  And by smaller, I mean fewer databases and less memory...

    The code I provided switches out that row_number() for an OUTER APPLY and TOP 1 and it completes in less than a second.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • you could try changing the server setting to maxdop 1, but I really don't think that is the problem

    MVDBA

  • @ Jeffrey

    thanks, i hope someone from Microsoft reads this and fixes the bug in the Activity monitor.

    I think i have a special non standard configuration on my servers, the same like your test-server that causes this behaviour.

    Because it seems to me strange that noone from Micrsoft has seen this problem before...

     

    @ Mike

    i cannot change maxdop because both servers are production-servers

     

  • I also have not seen this on any of my servers from 2008 through 2019

    i do get a few issues with CPU in perfmon (related to perfmon counters not installed) - my honest opinion it is your VMserver and "over provisioning"

    I can't suggest anything else - time to get a better DBA into the conversation 🙂

     

     

    MVDBA

  • @ Jeffrey

    your test - SQL server is virtualized?

    @ Mike

    i don't belive its the virtualization, because in vsphere the processors haves ca. 40% free resources, so the processors aren't under pressure. Thanks anyway for your suggestions!

  • wellen_reiter wrote:

    @ Jeffrey

    thanks, i hope someone from Microsoft reads this and fixes the bug in the Activity monitor.

    I think i have a special non standard configuration on my servers, the same like your test-server that causes this behaviour.

    Because it seems to me strange that noone from Micrsoft has seen this problem before...

    @ Mike

    i cannot change maxdop because both servers are production-servers

    Both my test and prod systems are physical - and the VM's that I have on 2016 (or higher) are all 'smaller' systems and do not suffer with this problem.

    The issue was reported to Microsoft: https://feedback.azure.com/forums/908035-sql-server/suggestions/37050118-ssms-17-9-1-activity-monitor-pauses-when-open-proc

    It looks like it is an issue baked into SSMS for any version of SSMS greater than SSMS 14.  If you use SSMS 18 to view the activity monitor of a 2012 system - SSMS sends a different query, and if you use SSMS 2012 to view the activity monitor on a 2016 system - it sends that same working version of the query - but use a 2014 or higher version of SSMS and the 'bad' query is sent causing a timeout error and forcing activity monitor to pause.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 7 posts - 31 through 36 (of 36 total)

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