Describing several performance monitoring counters

Tags:
Performance counters
Performance monitoring
SQL Server
Could you please describe the following performance monitoring counters, what they mean and the range to which they are acceptable? Full Scans/sec Checkpoint pages/sec Transactions/sec User Connections Average Wait Time (ms) Lock Waits/sec Number of Deadlocks/sec Target Server Memory(KB) Total Server Memory (KB) Batch Requests/sec
ASKED: June 19, 2008  8:16 PM
UPDATED: June 25, 2008  3:33 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Here are the basic definitions.

Full Scans/sec – Number of table or index scans done per second.
Checkpoint pages/sec – Number of data pages written to disk per second during the checkpoint process
Transactions/sec – Number of active transactions per second in the database
User Connections – Number of sessions currently active in the server
Average Wait Time (ms) = Average amount of time sessions are waiting for resources to become available (locks, disk, cpu, etc)
Lock Waits/sec – The number of locks which are initialed per second
Number of Deadlocks/sec – The number of deadlocks per second
Target Server Memory(KB) – The maximum amount of memory the SQL Server can use.
Total Server Memory (KB) – The total amount of memory the server has installed
Batch Requests/sec – The number of baches being executed per second against the SQL Server instance.

Here are some numbers to keep in mind.

Full Scans/sec – As low as possible. The closer to 0 the better.
Checkpoint pages/sec – This is really out of your control.
Transactions/sec – This is really out of your control
User Connections – This is really out of your control
Average Wait Time (ms) – You want this as low as possible. The longer this is the longer your queries take to run
Lock Waits/sec – You want this as low as possible. The longer this is the longer your queries take to run
Number of Deadlocks/sec – This should be 0.
Target Server Memory(KB) – How ever much memory you set SQL Server for. About 75% of physical memory.
Total Server Memory (KB) – This is how much memory is installed.
Batch Requests/sec – This is really out of your control

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following