Two of the first graphs I look at to understand system resource utilization are active parallel threads against CPU utilization, and active requests against CPU utilization. One or both relationships should stay fairly well correlated - as parallel threads and/or active queries increase, CPU utilization should increase until reaching CPU saturation.
*Unless* a wait type or a group of wait types starts to dominate the workload, such that time is passing with *no* runnable thread in the lot for a scheduler or more than one scheduler.
In the graphs below(30 second perfmon interval), the time period from 12:45 am to about 1:20 am, and from 1:35 am to 2:00 am look fairly healthy from that standpoint.
But the yellow box is confounding. I personally hate CPU sawtooth patterns like that - just about my least favorite pattern to come out of graphing system resource utilization. Only thing I like less is a straight horizontal line at saturation.
I don't know much about this particular system yet. But... I can't help myself. I must know. I know its a 2 socket physical server, with 32 cores.
There are no added Resource Governor Resource Pools or Workload Groups - just Default and Internal.
Well - could it be something other than SQL Server that is chewing up CPU? I guess...
Ooohhh. Nope, I take that back. Definitely SQL Server chewing up that CPU.
My money is on 1:20 - 1:35 am being a case of exorbitant management cost. Spinlock contention, probably. Maybe related to handling a system rowset object, or the plan cache... or it could even be good old spinlock contention surrounding CMEMTHREAD waits.
I'm not sure right now. But considering the system is at a low point for concurrent queries and parallel threads at that time - its hard for me to believe waits are keeping CPU utilization lower than the trend. Rather, it looks more likely that somehow, during a time of low concurrency something is artificially increasing CPU utilization with management work.
This is particularly intriguing. About 4 GB of tempdb in use during the CPU sawtooth, but almost no granted memory used. Hmmmm.
There was memory granted at the time.
This is also quite intriguing. Plan cache was at its plateau during this unusual period of time. And when a significant part of plan cache was released shortly after 1:35 am, a more expected relationship between CPU utilization and queries/parallel threads was restored.
Stay tuned... when I figure it out I'll let you know...
(to be continued...)
#SQLServer: Are waits keeping CPU utilization down? Or is Management Cost inflating CPU utilization? Part II
http://sql-sasquatch.blogspot.com/2017/04/sqlserver-are-waits-keeping-cpu_13.html
No comments:
Post a Comment