February 5, 2020 at 4:10 pm
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
February 5, 2020 at 8:28 pm
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 6, 2020 at 8:43 am
you could try changing the server setting to maxdop 1, but I really don't think that is the problem
MVDBA
February 6, 2020 at 9:19 am
@ 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
February 6, 2020 at 9:37 am
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
February 6, 2020 at 1:34 pm
@ 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!
February 6, 2020 at 9:38 pm
@ 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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
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