3

I am a developer who has been asked to look at a database slowness issue. I have had a look at the waits on the server and retrieved the following using a query from Red Gate's wait stats white paper. The results are:

Wait type         wait_S            Resource_S     Signal_S     WaitCount      Percentage

PAGEIOLATCH_SH      393375.51       391289.91      2085.59      76733247       32.60

CXPACKET            367110.76       334340.19      32770.57     34663540       30.42

OLEDB               84136.77        84136.77       0.00         78106          6.97

SOS_SCHEDULER_YIELD 77896.38        15.97          77880.41     254840824      6.46

PAGEIOLATCH_EX      65944.98        65550.98       394.00       16298376       5.46

ASYNC_NETWORK_IO    45579.23        32512.01       13067.22     125659899      3.78

WRITELOG            37415.61        35457.02       1958.59      18559390       3.10

LCK_M_IX            36602.81        36587.52       15.29        67118          3.03

LCK_M_X             35503.99        35494.55       9.44         32114          2.94

LCK_M_U             30464.81        30452.64       12.17        28824          2.52

Does anyone have any advice on what to do next?

Does CXPacket suggest a maxdop change? The server is a virtual server showing 4 CPU's in windows task manager.

  • Don't go changing Maxdop because you have high CXPacket waits. CXPacket waits are normal when you have parallelism, but if they're higher than they normally are, then it is most likely a symptom of something else (usually bad thread distribution in parallel queries, caused by bad estimates, caused by out of date statistics). I'd be more concerned about the top wait: sqlperformance.com/2014/06/io-subsystem/… – Aaron Bertrand Feb 4 '15 at 11:52
  • 1
    (The main problem here is that waits alone often don't tell the whole story, especially if you don't have a baseline of what "normal" is.) – Aaron Bertrand Feb 4 '15 at 11:53
  • 1
    Its not easy to make recommendation by just looking at the wait stats. If I go by top wait pageiolatch_sh it means session is facing delay in obtaining latch on a page in buffer pool. This points to disk slowness issue but again reason can be many. Lots of request want access to page in BPOOL which is not available so disk I/O is needed to bring it in and if disk is slow it could cause others to be slow – Shanky Feb 4 '15 at 12:15
  • Hi Shanky, the disks are apparently SSD and so should be fast however in performance monitor the disk read queues seem to be very high during the day. Again, no baselines and no-one responsible for providing them. – Geoff Bowden Feb 4 '15 at 15:56
  • Hello Aaron, thank you for more of your help. Your advice and the article are helpful. – Geoff Bowden Feb 4 '15 at 15:59
2

While by no means a dead cert, PAGEIOLATCH_SH and CXPACKET in equal weight can be indicative of parallel table scans. Without a baseline to compare with I'd suggest looking at the top CPU and IO consumers and looking for those which appear in both.

  • Thank you for those queries, the server is 2005 and falls over on the sys.dm_exec_query_stats.query_hash column. I am not too sure yet how to alter the queries. – Geoff Bowden Feb 4 '15 at 15:49
  • @GeoffBowden Try the 2005 version of Glenn Berry's DMV scripts. – Mark Storey-Smith Feb 4 '15 at 15:55
0

I'd run sp_whoisactive a few times to get a sample of what's going on on the server. Spend your time on the queries that are running most of the time when you take a sample. Those are the hot queries.

Not, that CPU usage is not (directly) reflected in the wait stats. Wait stats are not a profile of what the server does. It is a profile of what the server waits on.

Sampling queries is a profile of what's taking wall clock time. This usually is what's most interesting.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.