October 4, 2007 at 9:48 am
I'm sure it's been asked thousands of times. Here's my situation.
The server is Windows 2000 with SP4
SQL is SQL2000 Standard with SP3 (software vendor doesn't want us to push the SP)
I'm running several SQL Server 2000 databases on a single server, but there are no blocking conflicts that I can find. Originally SQL Server was set to use more than 2 GB memory (the server has 4 GB), but being that we are running Standard, that is pointless so I reconfigured so that it only attempts to use 2 GB max. The paging file is set to 1.5 times the size of the physical memory (6 GB). Where possible, I've moved the database files and the log files to separate physical partitions. The physical hard drives are SCSI setup in a RAID configuration. There are no obvious failures in the physical hard drive hardware. And to top it off, most (if not all) the processes are sleeping most of the time, so it's not like there is a huge amount of activity. Also, there are no other server applications running on this box other than SQL Server.
After checking what I could, The pages / sec are still through the roof (over 3,000) and the physical drive % is level between 96 and 98%. I'd really like to bring these stats down, but I really am not sure what else to check. Any advice or thoughts about what to check next would be appreciated.
Thanks
October 5, 2007 at 7:37 am
See what the following counters look like:
under physical disk
Transfers/sec (for each of your drives seperatly)
under process (for SQL at least. Other processes if you think they may be contributing to the paging)
Page faults/sec
Page file bytes/sec
Working Set
Virtual Bytes
under SQL buffer manager
checkpoint pages/sec
cache hit ratio
lazy writes/sec
under sql memory
total server memory
target server memory
What else is running on the server (antivirus, ...)
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
October 5, 2007 at 9:15 am
How much activity is there on the server? CPU and memory being used? Gail has some good counters to check above.
Also, are the data and log on different physical arrays? Different drives in Windows doesn't = different physical drives.
Are all drives affected?
October 5, 2007 at 9:23 am
Thank you both for your replies. I'm in the process of gathering the requested statistics. As for the other concerns....
The only other program running is a Symantec Antivirus Client (Symantec Corporate 9.0)
The processor utilization is consistently below 25% (average around 7 or 8%)
And they are on different physical devices. The log device has a very low utilization. The data drive is near 98%.
Again, thank you both for your time and effort
October 6, 2007 at 11:29 am
Are the database files excluded from the antivirusscanner?
October 8, 2007 at 8:35 am
Here are some of the stats (stated average stat over the workday):
Physical Disk:
Transfers/sec (C:) 2.719
Transfers/sec (D:) 234.794
Process (sqlsrvr)
Page Faults / sec 1.52
Page file bytes/sec 1,776,027,404
Working set 1,779,016,096
Virtual Bytes 1,847,296,356
SQL Buffer manager
cache hit ration 99.807
lazy writes/sec 0.095
SQL server memory
total server memory 1,684,766
target server memory 1,684,766
Thank you again for any input. I'm in the process of looking into the AV and whether or not the databases are included. My guess is that they are, but I don't have the password for the AV software so I can't really check the config until I get the password.
November 9, 2007 at 2:17 pm
If you only have SQL running on machine. Put /3GB switch in boot.ini. Set your max and min memory to 2.7Gbs you should see an improvement. (Look at the memory requirements of your other processes, you may need to lower max memory from 2.7Gb.)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply