Problem with Waittypes PAGELATCH_EX, PAGELATCH_UP, CXPACKET and NETWORKIO

• tzertuche

  Right there with Babe

  Points: 782

  More actions

  June 12, 2008 at 10:06 am

  #121958

  Problem:

  Client Server applications and COM+ applications have UNACCEPTABLE response times when using a specific database server(DB SERVER1).

  Specific of DB SERVER 1:

  I have a 8 way (3.00 Ghz) SQL SERVER 2000 box with Hyper Threading turned on

  I have 32 GIG (2.99 Ghz) of RAM

  Local Disks

  RAID1 73GBx2 20GB OS

  RAID5 300GBx4 836GB Backups

  Array

  RAID1 300GBx2 278GB SQL

  RAID0 300GB 278GB TempDB

  RAID10 300GBx6 836GB DB1

  RAID10 300GBx4 557GB Logs

  When I run the following command (select * from master..sysprocesses) to discern what makes up my CPU usage, 60-75% of the SPIDs have a Wait Type or Last Wait Type of PAGELATCH_EX, PAGELATCH_UP, CXPACKET and NETWORKIO. If I sort by "CPU used" PAGELATCH_EX and CXPACKET dominate the CPU time.

  DBCC SQLPERF (Waitstats) confirms that PAGELATCH_EX and CXPACKET again have the highest Wait Times. We redo our stats every morning at 7:00am.

  The Server that has MAX DEGREE of PARALLELISM set to 32. It seems to me that this should be set to 8.

  I hope to get some guidance on ways to decrease the WaitTimes on this server.

  THanks for your Help.

  Edited: Today @ 4:46:04 PM by tzertuche