Required values for the performance counters

Question

That will sound ludicrously, by the accident I became a 'DBA' and have to handle one big database around 2TB data. I would like to know what are optimal values for the following performance counters:

• Average Disk sec/Transfer
• Current Queue Length D
• Disk Bytes/sec
• Transfer/sec
• PLE

In case of PLE I have read that in old times it was 300 sec per 4GB of RAM. We have 128GB of RAM, and PLE fluctuates from 0 to 15k. Anyway, I don't think it is possible to have high PLE value when everyday around 50-70GB of new data is coming in, same amount is being deleted and we store data from last 30 days, so users can read and manipulate it

Answer 1

After searching for "Accidental DBA" as suggested by LowlyDBA you will see that a lot of us got the job because we happened to stand next to the database server as it started going up in flames.

So I want to congratulate you on the promotion and suggest starting here: Accidental DBA Series - SQLskills.com

Regarding your actual question. There is no optimal value for most of the performance counters (besides maybe stuff like 100% CPU all the time might be not good). You need to find out what is normal for YOUR server. This baseline will help you to know where to look when it is not performing as expected (see the "Baseline" section in the "Accidental DBA Series").

That being said I would suggest stepping away from perfmon for a moment and have a look at your servers wait stats first. These stats will tell you if and where it hurts. If your server is not waiting on disk resources it does not matter if some disk specific perfmon counters are "too high". Wait stats analysis is explained in the "Wait Statistics Analysis" section of the sqlskills article.

Again, a baseline will help a lot. Have a look at Brent's solution for monitoring wait stats. It is lightweight, extremely helpful and free.

Answer 2

PLE - Ignore.

Current Disk Queue Length - Ignore.

Disk Bytes/sec - Ignore.

Transfer/sec - Ignore.

Average Disk Sec/Transfer: This is the overall latency of the disk. Lower is better. Under 5 ms is considered good for non-SSD drives. Over 20 ms is considered "bad", but that should be taken with a grain of salt. Typically you'd want to look at the avg disk sec/read and avg disk sec/write separately. These counters will tell you how well your disks are handling your database load.

Transfers/sec and Disk Bytes/sec only measure how busy your database is.