1

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

Improve this question
2
  • 2
    You most likely have a lot to learn before being able to ask a narrow enough question here - try googling "Accidental DBA" and you'll find an abundance of resources to help you figure out what you need to proactive about. This is more common than anyone likes to admit.
    – LowlyDBA - John M
    Feb 14, 2018 at 16:26
  • @LowlyDBA thanks it would not come into my mind to google "accidental dba" lol going to read it tomorrow
    – whd
    Feb 14, 2018 at 18:04

2 Answers 2

Reset to default
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.

Improve this answer
0

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.

Improve this answer
1
  • 1
    I wouldn't ignore any of these, though they do depend on one's specific workload quite a bit IMO.
    – LowlyDBA - John M
    Feb 14, 2018 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.