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 |
|
netic
Starting Member
7 Posts |
 Posted - 2008-04-02 : 17:19:22
|
| Thanks in advance !We are having a situation (PROBLEM) with our DW - SQL SERVER 2000 SP4 ENT X86, it suddenly slowed a lot.CPU Usage : 20%IO : AVR DISK QUEUE LENGHT Between 1.5 and 2 (rarelly spikes (20/40)).MEM : 8 GB, 6GB to SQL SERVER.All the code that is running against the DB is fined tuned ( Most tables are Index Seeked with Clustered Indexes ), biggest table 450 GB.No Lock Waits ( Except the main process(spid) that is showed has locked by it's own spid ( latch_xx (sh,ix)).I guess that the problem is related to storage, because it is only doing 1-3MB sec (sometimes 20MB/sec).PerfMon ( PROCESS SQLSERVER ( DISK Read Bytes ( between 1-3MB sometimes 20 MB/s), Disk Bytes Write Bytes/Sec same as Reads).No more sql server instances or other software hogging the server.I can't understand why the SQL SERVER is never using the IO it is used to use (25MB/S).I don't see any DISK QUEUE on the Partitions that are used to store the datafiles/logfiles)No Pages splits in the SQL SERVER ACCESS METHODS.Sometimes I get about 1 GB in dirty buffers.98 % Cache Hit Ratio.ALL points to IO contention, but the counters from windows don't indicate this.All help is REALLY appreciated.Best Regards. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-02 : 17:45:38
|
| Did you check performance counters on Memory issues? |
 |
|
|
netic
Starting Member
7 Posts |
Posted - 2008-04-02 : 17:55:43
|
quote:
Originally posted by sodeep
Did you check performance counters on Memory issues?
Buffer cache hit ratio = 98 %.Sometimes with 1GB of dirty buffersç |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-02 : 19:49:50
|
| That is good but what about other counters for memory like memory pages/sec and other. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-04-02 : 22:01:25
|
| >> the main process(spid) that is showed has locked by it's own spid Is it in parallel? Try reduce number of threads if so. By the way, did you check table fragmentation? |
|
|
|
netic
Starting Member
7 Posts |
Posted - 2008-04-03 : 04:54:02
|
quote:
Originally posted by sodeep
That is good but what about other counters for memory like memory pages/sec and other.
Pages \ sec = 0 |
|
|
|
|
|
|
Dataware Performance Degradation - Urgent!