Database Reference
In-Depth Information
You can analyze various system performance counters from the
PhysicalDisk
object to obtain information about
current I/O activity, such as the number of requests and the amount of data being read and written. Those counters,
however, are the most useful when compared against the baseline, which we will discuss later in this chapter.
Performance counters from the
Buffer Manager
Object provide various metrics related to the buffer pool and data
page I/O. One of the most useful counters is
Page Life Expectancy
, which indicates the average time a data page stays
in the buffer pool. Historically, Microsoft suggested that values above 300 seconds are acceptable and
good enough
,
however this is hardly the case with modern servers that use large amounts of memory. One approach to defining
the lowest-acceptable value for the counter is by multiplying 300 seconds for every 4GB of buffer pool memory. For
example, a server that uses 56GB of memory for the buffer pool should have a
Page Life Expectancy
greater than 4,200
seconds (56/4*300). However, as with other counters, it is better to compare the current value against a baseline,
rather than relying on a statically defined threshold.
Page Read/Sec
and
Page Write/Sec
counters show the number of physical data pages that were read and written
respectively.
Checkpoint Pages/Sec
and
Lazy Writer/Sec
indicate the activity of the checkpoint and lazy writer
processes that save dirty pages to disks. High numbers in those counters and a low value for
Page Life Expectancy
could be a sign of memory pressure. However, a high number of checkpoints could transpire due to a large number of
transactions in the system, and you should include the
Transactions/Sec
counter in the analysis.
The
Buffer Cache Hit Ratio
indicates the percentage of pages that are found in the buffer pool without the
requirement of performing a physical read operation. A low value in this counter indicates a constant buffer pool flush
and is a sign of a large amount of physical I/O. However, a high value in the counter is meaningless. Read-ahead reads
often bring data pages to memory, increasing the
Buffer Cache Hit Ratio
value and masking the problem. In the end,
Page Life Expectancy
is a more reliable counter for this analysis.
■
You can read more about performance counters from the buffer manager object at:
Note
Full Scans/Sec
and
Range Scan/Sec
performance counters from the
Access Methods
Object provide you with
information about the scan activity in the system. Their values, however, can be misleading. While scanning a
large amount of data negatively affects performance, small range scans or full scans of small temporary tables are
completely acceptable. As with other performance counters, it is better to compare counter values against a baseline
rather than relying on absolute values.
There are several ways to detect I/O intensive queries using standard SQL Server tools. One of the most common
approaches is by capturing system activity using SQL Trace or Extended Events, filtering the data by the number of
reads and/or writes or duration.
■
the longest running queries are not necessarily the most i/o intensive ones. there are other factors that can
increase query execution time. think about locking and blocking as an example.
Note
This approach, however, requires you to perform additional analysis after the data is collected. You should check
how frequently queries are executed when determining targets for optimization.
Another very simple and powerful method of detecting resource-intensive queries is the
sys.dm_exec_query_stats
data management view. SQL Server tracks various statistics including the number of executions and I/O operations,
elapsed and CPU times, and exposes them through that view. Furthermore, you can join it with other data management
objects and obtain the SQL Text and execution plans for those queries. This simplifies the analysis, and it can be helpful
during the troubleshooting of various performance and plan cache issues in the system.
Listing 27-5 shows a query that returns the 50 most I/O intensive queries, which have plan cached at the moment
of execution.
