Odd behavior on CPU Queue length with SQL

Discussion in 'Multiprocessing Systems' started by Grimlaking, Jun 12, 2019 at 4:38 PM.

  1. Grimlaking

    Grimlaking 2[H]4U

    Messages:
    2,754
    Joined:
    May 9, 2006
    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
     
  2. Orddie

    Orddie 2[H]4U

    Messages:
    2,447
    Joined:
    Dec 20, 2010
    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?
     
  3. Grimlaking

    Grimlaking 2[H]4U

    Messages:
    2,754
    Joined:
    May 9, 2006
    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.
     
  4. Orddie

    Orddie 2[H]4U

    Messages:
    2,447
    Joined:
    Dec 20, 2010
    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?
     
  5. Grimlaking

    Grimlaking 2[H]4U

    Messages:
    2,754
    Joined:
    May 9, 2006
    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.
     
  6. Orddie

    Orddie 2[H]4U

    Messages:
    2,447
    Joined:
    Dec 20, 2010
    with that much ram, what is the system swap set to and where is swapping? what is the memory usage?
     
  7. Grimlaking

    Grimlaking 2[H]4U

    Messages:
    2,754
    Joined:
    May 9, 2006
    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.
     
  8. sethk

    sethk [H]ard|Gawd

    Messages:
    1,586
    Joined:
    May 3, 2005
    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?
     
  9. Grimlaking

    Grimlaking 2[H]4U

    Messages:
    2,754
    Joined:
    May 9, 2006
    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.