Odd behavior on CPU Queue length with SQL

G

Grimlaking

2[H]4U
Joined
May 9, 2006
Messages
3,246
I replaced some 2008 R2 SQL 2008 servers that were 8 core 16 thread with 96 GB of ram with some...

24 core 48 thread 196GB of ram systems running 2012 R2 with SQL 2008.

An anomalous behavior we are noting is a CPU QUEUE length happening of up to 7 from time to time on one of the cores and we are working to isolate why.

Oh I should note the CPU speed went up by a GHZ per core as well.

Thoughts? We're scratching our heads wondering why this faster CPU with A unit SSD storage array practically dedicated to it is having any queueing at all
 
just because its SSD does not always mean faster.

what data store type did you come from and to? raid type?
is this a VM or dedicated hardware?
is storage local or remote?
 
Dedicated hardware, all local. Storage is connected via dual 16gb fiber cards.

Two paths to the storage. Storage latency is sub 1ms. Normally around .26ms. my IOPS are really only peaking around 4k.

We also have the busier LUN's conncted through a different SP than the 'less busy' LUNs.

I'm not seeing any evidence of an I/O wait. I am thinking the only real way to capture the problem in this case is going to be with a trace.
 
forgive me, its has been a bit since i ran 2012.

perfmon should help ya see whats taking so long. also resource monitor could tell ya disk IO and active time. My gut is telling me storage. Assume all the best practice was followed for setting up the disks?
 
Yea the disks are solid and performance monitor has ZERO complaint. I misspoke on the SQL server version it's actually 2008 Enterprise.

the only play we see queueing is randomly on CPU threads. I'll see if the tool identifies the SQL or process causing it.
 
with that much ram, what is the system swap set to and where is swapping? what is the memory usage?
 
Orddie said:
with that much ram, what is the system swap set to and where is swapping? what is the memory usage?
Click to expand...

I'm not the DBA that tuned it but swap use is basically zero at least for pages going to swap according to Spotlight. It's using almost all of the Ram for SQL we leave about 16 gig for other OS and associated apps.
 
Are you monitoring the queries? Any increase in execution times? Is there an actual perceived throughput or responsiveness issue?
If your application profile is running a lot of queries instead of a few high cost queries, have you tried setting max parallelism to 1 to avoid breaking up a query across multiple threads? You may have a wait state on one thread holding up other queries on a different thread. Also what are the NUMA settings?
 
I was just watching the metrics and we have one thread going into a wait state.. Every so often but not frequently. Interesting. I think I'm going to leave this to the dba.
 
You must log in or register to reply here.
Back
Top