Overview

The SQL Server Performance Event Monitor focuses on the operational metrics for your SQL Server databases. It includes options to monitor the most important SQL Server performance counters giving you a clear view of metrics like transactions per second, average lock wait time, page lookups per second, and the current number of user connections. The event monitor collects graph data points on each run, giving you long-term views of database performance. For each metric, you can define thresholds for warning, error, and critical alerts to make sure your IT staff is notified when database performance degrades or expected performance attributes are detected.

For some counters, it is possible to offer general tips for choosing thresholds, but many performance statistics vary from one installation to another depending on database design, application design, and usage patterns. A good strategy often involves letting the event monitor run with thresholds that are deliberately set high. After 24 hours, you can then use the collected graph data to observe typical averages and maximums and then set thresholds based on that.

Use Cases

• Alerting on specific counters to help maintain the operational metrics for your databases

Monitoring Options

This event monitor provides the following options:

Instance Name

Enter the instance name of the SQL Server database server you want to connect to. If you want to connect to a default unnamed instance, leave the value blank.

Alert based on "MSSQL:Buffer Manager: Page lookups/sec"

This counter keeps track of the number of requests per second to find a page in the buffer pool. It's a good general indication of the overall level of activity on the database server.

Alert based on "MSSQL:Buffer Manager: Page reads/sec"

This counter keeps track of the rate of physical page reads across all databases. Each page read involves requires disk access which is expensive so ideally, this value will be fairly low. Many tuning guides suggest that the rate should be less than 90. If it is higher, it may indicate that the server requires more physical memory.

Alert based on "MSSQL:Buffer Manager: Page writes/sec"

This counter keeps track of the rate of page writes across all databases. If its buffers are full, the SQL Server buffer manager writes pages to disk in order to make room before reading new pages. Ideally, this rate will be fairly low and most tuning guides recommend that it should be less than 90. If you are seeing values that are higher than 90, it may indicate that the system requires more physical memory.

Alert based on "MSSQL:Databases: Active Transactions"

A transaction is a group of database operations like queries and updates that are executed together and will either succeed or be rolled back if there is a failure at any point along the way. Ideally, the "start to finish" time for each transaction is very short and there are very few active transactions at any given time. The best thresholds for this counter will vary from one installation to another depending on database design and usage patterns. We recommend that you let the event monitor run with high thresholds for 24 hours and then use the collected graph data to determine appropriate thresholds.

Alert based on "MSSQL:Databases: Transactions/sec"

Similar to the number of active transactions, this counter keeps track of the number of transactions that are started and completed per second. A high value indicates that there is a high level of activity on the database server, but this may not indicate a problem because an efficient database application can sustain a high transaction rate. This counter provides a good overview of database activity levels. A large spike in its value can indicate unexpected stress loads.

Alert based on "MSSQL:General Statistics: Logins/sec"

A login involves authenticating an incoming request and allocating resources to support the requests that will be delivered by the application making the connection. As a result, a login is an expensive operation in terms of CPU, memory, and other system resources. The rate of logins per second will ideally be very low. Rates above 10 logins/sec can indicate high traffic levels and may suggest that design changes are necessary to use connection pooling.

Alert based on "MSSQL:General Statistics: Logouts/sec"

The logouts/sec will generally correspond to the login/sec rate. If it is significantly less than the login rate, it could indicate a spike of new connections to the database server.

Alert based on "MSSQL:General Statistics: User Connections"

This counter tracks how many connections are currently open. Normal values are highly dependent on database and application design, but large spikes in the number of connections are a good indication that other counters will see corresponding increases.

Alert based on "MSSQL:Locks: Average Wait Time (ms)"

SQL Server will lock objects whenever it needs to read or write data and ensure that the data is not updated at the same time by two or more connections. For example, before updating a row in a table, SQL Server will lock the row or page that it resides in before doing the update. When the update is complete, the lock is released. This counter measures the average time in milliseconds that each connection has waited before obtaining a requested lock.

Alert based on "MSSQL:Locks: Number of Deadlocks/sec"

Deadlocks occur when two or more transactions have locked resources and each of them wants the other. When this occurs, SQL Server will cancel and rollback both transactions with a deadlock error, forcing each caller to try again. Ideally, this value will always be zero as any significant number of deadlocks will result in performance degradation and usually indicates that database and application design changes are required.

Alert based on "MSSQL:SQL Statistics: SQL Compilations/sec"

Compilations occur when a stored procedure is updated. SQL Server uses compilation to optimize the performance of the stored procedure so that it runs as fast and efficiently as possible. Compilation requires time and resources, but the result is cached so future runs of the stored proc will benefit right away. This value will typically be very low, usually close to zero. If it is higher, it can indicate that application design changes are required.

Alert based on "MSSQL:SQL Statistics: SQL Re-Compilations/sec"

Re-compilations are the same as compilations, but they occur when database structure or database data usage patterns have changed significantly. SQL Server automatically detects these conditions and uses recompilation or optimizes stored procedures for the new conditions it detected. This value should also be very low, usually close to zero.

Authentication and Security

The account used for authentication must be a member of the Performance Monitor Users group or have admin rights.

Protocols

Data Points

The performance counters you choose will appear as data points in the data point chooser.

Sample Output

Tutorial

To view the tutorial for this event monitor, click here.