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 |
|
JoeAndMax
Starting Member
1 Post |
 Posted - 2007-08-02 : 14:07:18
|
| OK ... how many of you run dbcc SQLPERF(waitstats) to display analyze SQL Server 2000 resource utilization? How about sp_waitstats? This is a very useful stored procedure that takes the sqlperf output and aggregates it over common wait types. If you've had to work on a sick SQL Server box, I've found this pretty useful to help pinpoint bottlenecks.While there's plenty written about the various wait types, there seems to a shortage of stuff written about acceptable or expected values! I am experiencing some performance problems with some procedures running on the EE of SQL 2000 w/SP3 on a server with 12 GB of RAM & 8 CPUs. Since no major locking issues stood out, I ventured to see what types of waits were occurring. Using both dbcc SQLPERF(waitstats) and sp_waitstats I found 98% of the waits where the OLEDB type. First I've never seen a single wait type be responsible for such a large %. For this type of wait, SQL Server is waiting for a OLE DB provider to service its request. High transactions or slow connection speeds in a distributed env are a big cause of these. This can be caused by slow authentication, outdate MDAC or incorrect DB Providers. (OK, I have to admit I haven't seen this type of wait before)Has anyone else encountered this type of wait? I've seen various I/O Latch waits in the past but this is the first time I've seen OLEDB. If you've seen this, what were the steps taken to remedy this situation?Thanks,JoeT |
|
|
|
|
|
dbcc SQLPERF Waitstates & OLDEB