During performance monitoring for Load Test on few servers. I have configured following counters in system performance monitor. Can anyone tell me the ideal values or percentage level for this counters, from a good performance point of view. Memory a. Available Kbytes: b. Available Mbytes: c. Pages/sec: Physical Disk a. %Disk Read Time b. %Disk Write Time: c. Avg. Disk Queue Length: Processor a. %Processor Time: SQL Server: Access Methods a. Page Splits/sec: SQL Server: Buffer Manager a. Buffer Cache Hit Ratio: b. Page Life Expectancy: SQL Server: Memory Manager a. Connections Memory(KB): b. Target Server Memory(KB): c. Total Server Memory(KB): SQL Server: SQL Statistics a. SQL Compilations/sec: b. SQL Re-Compilations/sec: Thanks, - apat
There are seldom hard and fast rules about this. Generally it's "as low as possible" or "as high as possible".. but anyway Memory - pages/sec shouldn't be high (200/sec + is a bit of a concern) Available MBytes should be plenty (100+) Physical disk - Ave queue - depends on your array or SAN. As low as possible. % Disk Time values are generally useless for modern disk arrays, you end up with values like 250% which is meaningless. Concentrate on queueing and disk throughput - counters like Disk Bytes/Transfer, Disk Transfers/sec, Disk Bytes/sec, to calculate your throughput. This should, of course, be as high and as fast as possible Processor % time - as low as possible. Anything sustained over 50% is a concern Page Splits/sec - low as poss Buffer Cache Hit Ratio - this should ideally be 99% or above nearly all the time. If it frequently drops below 95% you may need more RAM Page Life Expectancy - high as poss SQL Server memory - monitor Process/sqlservr/Private Bytes and ensure SQL Server has the correct amount of RAM. Target Server Memory should be nearly the same as Total Server Memory if sql server has sufficient RAM. Compilations/Recompilations - shouldn't be too high, but will vary considerably depending on whether your app uses SPs or ad-hoc sql, etc etc etc etc This may be vague and none of the above is hard and fast - other users will almost certainly disagree with some of my judgements - which will go to prove that this is an art, not a science, generally, and many judgements are subjective. A lot depends on your hardware, your app, and you particular server's config.
Also add System/Processor Queue Length - if this is non-zero it will show you have processor queueing. some queuing every now and then is fine, but sustained non-zero values can be a cause for concern.