SQLTeam.com | Weblogs | Forums

Waits troubleshooting


Hello guys,
I've enabled data collection on sql server 2012 sp1 because in the last time users are complaining is slow.
On the output report I see that top waits are PAGEIOLATCH_SH,CXPACKET AND CLR_SEMAPHORE.

The server is a HP proaliant dl380 with two separate raid 1 on scsi disk (10k) with 1Gb of fwbc cache distributed 75% on write and 25% on read, so I cannot explain why this behavior.
Sometime I've see on the SQL Log "memory pressure" , but database is about 14Gbyte and ram on server is 16Gbyte so it would be enough for put in cache all db.
How I can find statements or procedure that cause this issue?



PAGEIOLATCH_SH. Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.

CXPACKET means queries are using parallelism -- usually a good thing

Happens when a task is currently performing CLR execution and is waiting for a semaphore.

good info here: Wait Stats


So pageiolatch Could be caused also to memory less?
Because on this server ram is always all in use. Maybe buffer is not enough for I/O disk request..

Thank again.