Database Reference
In-Depth Information
the buffer pool without being referenced. This indicates how well SQL Server is able to manage its memory,
since a longer life means that a piece of data in the buffer will be there, available, waiting for the next reference. However,
a shorter life means that SQL Server is moving pages in and out of the buffer quickly, possibly suggesting a memory
bottleneck.
You will see which counters to use in analyzing each type of bottleneck shortly.
Bottleneck Resolution
Once you have identified bottlenecks, you can resolve them in two ways.
•
You can increase resource capacity.
•
You can decrease the arrival rate of requests to the resource.
Increasing the capacity usually requires extra resources such as memory, disks, processors, or network adapters.
You can decrease the arrival rate by being more selective about the requests to a resource. For example, when you
have a disk subsystem bottleneck, you can either increase the capacity of the disk subsystem or decrease the number
of I/O requests.
Increasing the capacity means adding more disks or upgrading to faster disks. Decreasing the arrival rate means
identifying the cause of high I/O requests to the disk subsystem and applying resolutions to decrease their number.
You may be able to decrease the I/O requests, for example, by adding appropriate indexes on a table to limit the
amount of data accessed or by writing the T-SQL statement to include more or better filters in the WHERE clause.
Memory Bottleneck Analysis
Memory can be a problematic bottleneck because a bottleneck in memory will manifest on other resources, too. This
is particularly true for a system running SQL Server. When SQL Server runs out of cache (or memory), a process within
SQL Server (called
lazy writer)
has to work extensively to maintain enough free internal memory pages within SQL
Server. This consumes extra CPU cycles and performs additional physical disk I/O to write memory pages back to disk.
SQL Server Memory Management
SQL Server manages memory for databases, including memory requirements for data and query execution plans, in a
large pool of memory called the
buffer pool.
The memory pool used to consist of a collection of 8KB buffers to manage
memory. Now there are multiple page allocations for data pages and plan cache pages, free pages, and so forth. The
buffer pool is usually the largest portion of SQL Server memory. SQL Server manages memory by growing or shrinking
its memory pool size dynamically.
You can configure SQL Server for dynamic memory management in SQL Server Management Studio (SSMS).
Go to the Memory folder of the Server Properties dialog box, as shown in Figure
2-3
.
