SQL Server Wait Types - Professional SQL Server 2005 Performance Tuning - page 76

LOGBUFFER 5 109 62 0

LATCH_SH 914 78 15 78

CMEMTHREAD 16 46 31 15

session_id cpu_time total_elapsed_time
---------- ----------- ------------------
52 2719393 3521547 (40:21)

You can remove the top three waits because they are just system threads waiting for events and the
bottom six because they're negligible values or waiting system threads. This results in this shorter table:

waitType waitingTasks waitTimeMs maxWaitTimeMs signalWaitTimeMs
---------------- ------------ ---------- ------------- ----------------
CXPACKET 42045 605798 452 17362

WRITELOG 12883 68874 733 5694

PAGEIOLATCH_UP 2880 11419 156 312

SOS_SCHEDULER_YIELD 773663 11388 249 10904

LATCH_EX 35127 9516 109 5647

session_id cpu_time total_elapsed_time
---------- ----------- ------------------
52 2719393 3521547 (40:21)

CXPACKET means parallelism is occurring, which will drive the CPUs hard. In an OLTP system this
shouldn't be present or at least shouldn't be a significant wait type, so finding the source will be the top
priority. SOS_SCHEDULER_YIELD backs up the evidence for a CPU bottleneck. It's a very common
wait to see as it represents threads that are voluntarily yielding processor time, but to see it high on a
list of waits suggest a CPU bottleneck. The final evidence is found in the signal wait times, which if you
remember from the architecture description represent time in the runnable queue and therefore constitute
a pure CPU wait. The total elapsed time represents the total time to run the workload. We've added the
minutes:seconds measurement in brackets from Management Studio to save you from having tocalculate
it from the results.

Investigation of the actual execution plan for usp_loopMarriageUpdate reveals that a table scan is being
executed in parallel. Figure 4-6 shows part of the graphical execution plan. The circle with the two arrows
pointing left on the Parallelism and Table Scan icons indicates a parallel activity. Figure 4-7 is the details
screen from the parallel table scan.

Figure 4-6