System Troubleshooting - Pro SQL Server Internals - page 519

Ad-hoc queries and recompilations contribute to CPU load and increase plan cache size, which in turn leaves
less memory for the buffer pool. It is also increases I/O subsystem load due to the extra physical I/O required.
Let's look at different issues frequently encountered in systems and discuss how we can detect and
troubleshoot them.

I/O Subsystem and Non-Optimized Queries
The most common root-cause of issues related to a slow and/or overloaded I/O subsystem is non-optimized queries,
which require SQL Server to scan a large amount of data. When SQL Server does not have enough physical memory to
cache all of the required data in the buffer pool, which is typically the case for large systems, physical I/O occurs and
constantly replaces data in the buffer pool.

■ You can add or allocate more physical memory to the server that hosts SQl Server when an i/o subsystem is
overloaded. extra memory increases the size of the buffer pool and the amount of data SQl Server can cache. it reduces
the physical i/o required to scan the data. While it did not fix the root-cause of the problem, it could work as an emergency
band-aid technique and buy you some time. remember that non-enterprise editions of SQl Server have limitations in the
amount of memory that they can utilize.

Tip

Figure 27-6 illustrates the situation with non-optimized queries, and it shows the metrics and tools that can be
used to diagnose and fix these problems.

Figure 27-6. Non-optimized queries troubleshooting

PAGEIOLATCH_* wait types occur when SQL Server is waiting for an I/O subsystem to bring a data page from disk
to the buffer pool. A large percentage of those waits indicate heavy physical I/O activity in the system. Other I/O wait
types, such as IO_COMPLETION , ASYNC_IO_COMPLETION , BACKUPIO , WRITELOG , and LOGBUFFER relate to non-data pages
I/O. Those wait types may occur for various reasons. IO_COMPLETION often indicates slow tempdb I/O performance
during sort and hash operators. BACKUPIO is a sign of slow performance of a backup disk drive, and it often occurs with
an ASYNC_IO_COMPLETION wait type. WRITELOG and LOGBUFFER waits are a sign of bad transaction log I/O throughput.
When all of those wait types are present together, it is easier to focus on reducing PAGEIOLATCH waits and
data-related I/O. This will reduce the load on the I/O subsystem and, in turn, it can improve the performance of
non-data-related I/O operations. Otherwise, when PAGEIOLATCH waits are not very significant, you need to look at I/O
subsystem performance and configuration and correlate the data from the various metrics we are about to discuss.