October 7, 2014 at 9:32 am
I have a SQL 2012 server on VMWare that has 1 vCPU (I wasn't the DBA involved in the build so no lectures please.) It will occasionally take too long to respond to a ping routine that involves querying the SQL server resulting in all web servers being dropped from the pool and booting all users. CPU doesn't spike when this happens and we're running the vendor's recommended specs on this box.
The only thing that may point at a CPU issue is that the only wait stat that pops in Paul Randal's wait stats script (http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/) is SOS_SCHEDULER_YIELD at 99.99% of all waits. However, only a small fraction of a percent of the waits are signal waits. From what I've read that doesn't indicate that we're CPU bound, even with this wait type.
I want to recommend that they add a CPU but I want to be up front as to whether it's a guess based on best practice or we're seeing data that indicate things will get better. Thanks.
October 7, 2014 at 10:27 am
I'm not sure how I didn't find this article when I was researching this yesterday but I found Paul Randal's article on this wait:
http://sqlperformance.com/2014/02/sql-performance/knee-jerk-waits-sos-scheduler-yield
It's frequently not a sign of CPU pressure but since we have one vCPU and a high number of implicit conversions resulting in scans (Unicode passed in, ASCII on disk) I'm thinking adding another CPU will at least help with the issue. And since we don't own the code and the vendor isn't likely to provide a quick fix to the implicit conversion issue it'll likely be our solution. If anyone has any other thoughts or can poke holes in my thinking please do.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply