In the graph below, there is at least some relationship between buffer page lookups/sec (logical IO) and CPU utilization in the shaded area. Before the shaded area begins, though CPU utilization is extremely high and logical IO rate is extremely low. Whenever I see that, I always try to rule out spinlock conditions - and if there are known applicable spinlock conditions I try to mitigate them (this is one of the reasons I try to learn about as many trace flags as I can).
xDuring the shaded timeperiod notice that the log flush bytes/sec is much higher than previously, as well as higher buffer lookups/sec.
Log flushes/sec trends with log flush bytes/sec throughout the time range.
Because query concurrency and parallel worker concurrency is much lower during the shaded productive area than the preceding high CPU/low logical IO timeperiod, I definitely think that a spinlock issue can be involved.
I've got two spinlock issues in mind:
1. the global database lock on login, relieved by startup trace flag 1236. Although I think trace flag 1236 will be somewhat helpful, in this particular case I don't think the login rate is high enough to trigger spinlock contention to this extreme level.
https://support.microsoft.com/kb/2926217
2. Transaction log pool global access, relieved by startup trace flag 9024. That seems like a good suspect. This system is a 4 vcpu vm, with SQL Server seeing a single "NUMA Node". So, trace flag 9024 promoting log cache spinlock access from global to node level won't help by itself. But, with trace flag 8048 also enabled, log cache spinlock access gets promoted all the way to CPU level. *That* may help. There's not a lot of information about trace flag 9024, the conditions where relief is expected, or the symptoms that indicate it may be helpful. The kb article doesn't even indicate that it is a startup trace flag yet. But I'm hopeful it'll help in this case. I'll update this blog post with what I learn.
http://support.microsoft.com/kb/2809338
No comments:
Post a Comment