CPU_Delta > 3 000 000 ms, and tasks = 1

[rrg92]

How this is possible ?

This resultset is from a run using @delta_interval = 1 ... With just one task, in on second, how is possible get more than 1000ms in delta?

Context: I ran it (latest verison, 11.32) on a very busy CPU system (24 cores, virtual machine).

[amachanic]

What version of SQL Server? The slightly-off numbers (at least in the 1,000 range) are easy to rationalize based on timing. Obviously 3MM milliseconds in a second would be a bit tougher to swallow. Will need a bit more info, though, before I can tell whether it's a bug in sp_whoisactive or a DMV issue.

[jzabroski]

@amachanic Welcome to GitHub. I recommend setting up Issue Templates so people fill in things like SQL Server Version, possibly using Glen Barry's version details script to get OS details (always tricky due to VM host lying about guest OS details), etc

[amachanic]

Thanks @jzabroski. I'll get to work on that.

[rrg92]

What version of SQL Server? The slightly-off numbers (at least in the 1,000 range) are easy to rationalize based on timing. Obviously 3MM milliseconds in a second would be a bit tougher to swallow. Will need a bit more info, though, before I can tell whether it's a bug in sp_whoisactive or a DMV issue.

I remember saw this behavior in most recent versions, like 2014 and 2016. Take a look on this recent collect from an Azure Managed Instance:

The delta interval is just 1 second with 8 CPUs... So, it is conceptually impossible this CPU deltas!
I noted that whenever this occurs, the SQL is using high CPU.

Maybe this issue is not with whoisactive, and with some DMV that you use to calculate the CPU difference (i don't look the code yet, but i guess that you query from sys.dm_os_workers and sys.dm_os_threads, right!?)

Sorry for long time to answer, and thanks for your attention

[amachanic]

Correct, sys.dm_os_threads/workers. I think however that my complex workaround is not necessary in new versions of SQL Server, and that the correct thing to do here is to not delta this way any longer.

At some point the DMVs got better at updating CPU time even in parallel queries. Around SQL Server 2016, I think. I recall that there was a blog post by Pedro Lopes on the topic, but I can't find it at the moment.

Is anyone able to share the relevant info so that a version check can be added?

[rrg92]

Nice. ANother suggestion is add a parameter (e.g: @cpu_delta_source), where the user can choose if want see delta from this low level dmv or from sys.dm_exec_requests...