July 22, 2008 at 5:27 pm
Due to performance complaints, I am running file and wait stats delta between 8-5 on a 2K5 DB. I consistently get the following top 3 for resource waits
PAGEIOLATCH_SH : 40% (max of 3 secs) 554 secs over 100K requests
CXPACKET : 31% (max of 3 secs) 430 secs over 249K requests
WRITELOG : 15% (max of 2 secs) 216 secs over 35K requests
and the following top 3 for signal waits
CXPACKET : 79% 37 secs over 249K requests
SOS_SCHEDULER_YIELD : 8.7% 4 secs over 197K requests
ASYNC_NETWORK_IO : 2.1% 1 sec 50K requests
I researched online and gathered that PAGEIOLATCH_SH is usually when the disk is slow and CXPACKET is when processory is slow. This is a dual core machine with 4G of RAM connected to a SAN array (RAID5), maxdop is not enabled and dedicated SQL (not clustered). Also, we have 2 different mounts and LUN for data and log file.
I am at a loss to troubleshoot further, any guidance is appreciated.
thanks
July 23, 2008 at 12:45 am
CXPacket is caused by parallel skew, when a process parallels and the part on the one processor runs faster than the other (usually due to having an imbalanced split in work). The process that ran faster then has to wait for the other to catch up before a merge. That wait is the CXPacket wait.
If that's significant, consider setting maxdop to 1. It means that processes won't run parallel at all any more.
As for the disks, could you please run perfmon for a few hours at a busy time and see what the max, min and avg values are for the following counters (per physical disk)
Physical disk: Avg sec/read
Physical disk: Avg sec/write
Physical disk: % idle time
Physical disk: Avg disk queue length
SQL server Buffer manager:Cache hit ratio
SQL server Buffer manager:page life expectency
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply