Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CSteinhardt
Starting Member
2 Posts |
 Posted - 2010-02-06 : 20:47:45
|
| I run a high-availability online game with SQL Server 2005. I'd put my level of SQL knowledge at around high intermediate, meaning that I know enough to identify a lot of common problems problems, figure out what indexing is helpful, hand-optimize procedures that need them, and catch things like parameter sniffing problems. In other words, enough to get myself into some serious trouble when something more complicated comes along. :) But also enough to be able to follow directions if you can give me some good advice. What I need though is to learn how to diagnose what appears to be a disk problem so I can figure out what needs to be fixed and whether the problem is purchasing better hardware or something in the configuration or database. So, any help would be greatly appreciated!I'm seeing a large number of PAGEIOLATCH_SH waits, resulting in everything from the site being slow to background processes being delayed. If I increase the degree of parallelism, these PAGEIOLATCH_SH waits are mixed with CXPACKET. Our database consists of about a 400 GB archive of read-only data and a 150 GB operational database running on a 4-processor machine with 32 GB of RAM running Windows Server, SQL Server 2005, and nothing else.The disk is a SAN, RAID-configured, with 1 TB build from 10k RPM, 146 GB disks. Both databases off the same RAID array.Some data from the Performance Monitor, at about 75% of peak load:% Processor Time: 15.478Avg. Disk Queue Length: 20.838Avg. Disk sec/Read: 0.042Avg. Disk sec/Write: 0.023Buffer cache hit ratio: 99.498Checkpoint pages/sec: 987.523Lazy writes/sec: 0.680Latch waits/sec: 198.497Average Latch Wait Time (ms): 26.045Lock waits/sec: 5.354Average Lock Wait Time (ms): 131.016Full Scans/sec: 591.853I believe most of the full scans are from a pair of commonly-accessed tables that have either 1 or 2 total rows and contain configuration data.I know very little about how to properly assemble disks, but I'm happy to learn. Is this a disk problem or a database structure problem? What can I do to improve things?Thanks for any help! |
|
|
|
|
|
|
|
Performance problems, PAGEIOLATCH_SH