Microsoft SQL Server is more than 30 years old now, and remains one of the most popular commercial relational databases in use today. It runs very efficiently with only minimal tweaking, but can also be tuned for optimal performance. Before fine tuning your SQL Server database, you first have to monitor its performance over a broad spectrum of conditions and workloads. In today’s tip, we’ll review a few of the most instructive metrics to monitor in order to gauge server performance.

Benefits of Performance Monitoring

To keep your database server running smoothly, it’s crucial to monitor its performance on a regular basis. A good SQL Server monitoring plan can help you stay on top of:

• Performance: Monitoring database performance can help uncover possible bottlenecks and other issues as soon as they happen, so that you’re better prepared for future occurrences. Beyond being proactive, performance metrics can help guide you in deciding whether or not performance increase is warranted. For example, monitoring queries as they are executed might reveal stalwarts that require modification.


• Growth: Database traffic tends to increase faster than predicted. By observing user and traffic patterns, you can anticipate future upgrades.


• Security: People tend to associate the term “database security” with auditing. While auditing is instrumental in tracking down the source of unauthorized database use and, depending on the product used, can potentially stop it in its tracks. However, performance monitoring can help confirm that adequate security measures have been applied.

Performance Metrics

SQL Server performance metrics generally target one of four components: Disk Activity, Processor Utilization, Memory, and the Server itself:

Disk Activity

• % Disk Time: This counter monitors the portion of time the disk is busy with read/write activity. Its value is the Average Disk Queue Length value represented in percents (i.e. multiplied by 100). If Average Disk Queue Length is 1, % Disk Time is 100%. If the value is higher than 90% per disk, additional investigation is needed. First, check the Current Disk Queue Length value. If it’s higher than the threshold of 2 per physical disk, monitor if the high values occur frequently.


• Average Disk Queue Length: The number of I/O operations waiting. For example, in a 6 disk array the Current Disk Queue Length value of 12 means that the queue is 2 per disk. The number of pending I/O requests should not rise consistently over 1.5 to 2 times the number of spindles of the physical disk.


• Page reads/sec and page writes/sec: The SQL Server Buffer Manager metrics Page reads per second and page writes per second show how many times the pages were read/written from/to disk, in one second. This is a server-level metric, hence the number indicates page reads for all databases on the instance. The recommended page reads/sec and page writes/sec value should be under 90. Higher values usually indicate insufficient memory and indexing issues.

Processor Utilization

• % Processor time: The percentage of time that the processor spends on executing user processes such as SQL Server. In other words, this is the percentage of processor non-idle time spent on user processes. Note that multiprocessor systems have a separate instance for each CPU. The recommended % Processor Time value is 80%, hence a consistent 80-90% is too high and should be addressed.


• % Privileged time: Indicates the time spent on Windows kernel commands (i.e. SQL Server I/O requests). If both this and Physical Disk counters are high, you may require a faster disk or lower the load for this server.


• % user time: The percentage of time the CPU spends on user processes.


• Queue Length: The number of threads waiting for processor time. A high number may indicate the need for faster or additional processors.

In part 2, we’ll move on to metrics that measure Memory and Server operations.