The 4 pillars of SQL Server performance tuning

The most important performance counters to watch when tuning SQL Server performance are CPU usage, memory usage, cache hit ratios, and disk I/O. Even though CPU capacity and available memory are crucial, disk I/O is frequently the cause of bottlenecks in most systems. Paying extra attention to disk I/O and cached disk access is worthwhile.

SQL Server CPU utilization

Measuring the amount of time the CPU is busy running non-idle SQL Server threads is an important aspect of SQL Server performance monitoring and tuning. A continually high CPU usage rate may indicate the need for a CPU upgrade or the addition of multiple processors. Alternatively, a high CPU usage rate may indicate a poorly tuned application or SQL code. Optimizing the application can lower CPU utilization. A consistent state of 80 to 90 percent may indicate the need for a CPU upgrade or the addition of more processors. When you examine processor usage, consider the type of work the instance of SQL Server is performing. If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor's time may not seem unusual. If this causes the performance of other applications to suffer, try changing the timing of the workload.

SQL Server memory utilization

By default, SQL Server changes its memory requirements dynamically based on available system resources. If SQL Server needs more memory, it queries the operating system to determine whether free physical memory is available and then uses the memory available. If SQL Server does not need the memory currently allocated to it, it releases the memory to the operating system. However, the option to dynamically use memory can be overridden using the min server memory, max server memory, and working set size server configuration options.

The more memory your server has the more cached disk reads and writes it can perform. Lack of system memory can cause high non-cached disk reads and writes. Adding memory to your server can help lower physical disk access.

SQL Server Cache Hit Ratios

SQL Server cache hit ratios indicate the percentage of data pages read from the SQL Server cache (memory) vs. those read from the physical disk. The SQL Server cache consists of the buffer cache and the procedure cache.

The Buffer cache is the memory pool used to store SQL Server data. The buffer cache hit ratio indicates the percentage of data pages read from the buffer cache vs. physical disk. Even though CPU capacity and available memory are crucial, disk I/O is frequently the cause of bottlenecks in most systems. Paying extra attention to cached disk I/O is crucial when monitoring the performance of SQL Server. A value of 90% cache hit ratio indicates pages were retrieved from memory 90% of the time. The other 10% were read from disk. A consistent value below 90% indicates that more physical memory is needed on the server.

The Procedure cache is the memory pool used to store SQL Server execution plans. The procedure cache hit ratio indicates the percentage of execution plan pages read from the procedure cache vs. disk.

SQL Server disk I/O

SQL Server disk I/O is frequently the cause of bottlenecks in most systems. The I/O subsystem includes disks, disk controller cards, and the system bus. If disk I/O is consistently high, consider:

• Move some database files to an additional disk or server.
• Use a faster disk drive or a redundant array of inexpensive disks (RAID) device.
• Add additional disks to a RAID array, if one already is being used.
• Tune your application or database to reduce disk access operations. Consider index coverage, better indexes, and/or normalization.

Microsoft SQL Server uses Microsoft Windows I/O calls to perform disk reads and writes. SQL Server manages when and how disk I/O is performed, but the Windows operating system performs the underlying I/O operations. Applications and systems that are I/O-bound may keep the disk constantly active.

Different disk controllers and drivers use different amounts of CPU time to perform disk I/O. Efficient controllers and drivers use less time, leaving more processing time available for user applications and increasing overall throughput.