2. CPU pressure
How do you measure CPU pressure for a SQL Server
system? While classic Performance Monitor counters such as Processor:%
Processor Time and System:Processor Queue Length provide a general
overview, they are insufficient on their own to use in forming the
correct conclusion. For that, we need to look a little further, with
signal waits a critical consideration.
Signal waits
We looked at how SQLOS uses schedulers in allocating CPU time with processes (SPIDs) moving between three
states: running, suspended, and runnable. There can be only a single
SPID in the running status of a given scheduler at any one time, with
the runnable queue containing SPIDs that are ready to run. The classic
analogy used when discussing this model is the supermarket checkout
line; that is, SPIDs in the runnable queue can be considered in the same
manner as people lining up in the checkout queue: they have their
groceries and are ready to leave, pending the availability of the
checkout operator.
As we saw earlier, the sys.dm_os_wait_stats DMV
includes a signal_wait_time_ms column, which indicates the amount of
time, in total, processes spent in the runnable status for each wait
type. Calculating the sum total of the signal wait time for all wait
types as a percentage of the overall wait time gives a good indication
of the depth of the runnable queue and therefore an indication of CPU
pressure, from a SQL Server perspective.
When calculating the signal wait percentage, you
should consider excluding certain wait types, LAZYWRITER_SLEEP, for
example.
Generally speaking, a signal wait percentage of
more than 25 percent may indicate CPU pressure, particularly in
combination with a sustained high value for Processor:% Processor Time
(> 80 percent). However, in some cases, CPU percentage may be well
below 100 percent even though there is still significant CPU pressure.
In such cases, the SOS_SCHEDULER_YIELD wait type is more than likely in
the mix.
SOS_SCHEDULER_YIELD
As we covered earlier, a single SQLOS scheduler
is created for each CPU core that a SQL Server instance has access to.
When a request is sent to SQL Server for execution, it's assigned to a
scheduler for execution and remains on that scheduler until complete.
Despite SQL Server using various mechanisms for balancing load across
the available schedulers, various situations may lead to a
disproportionate load being assigned to a single scheduler.
Consider the following example: at a given
point, all schedulers are equally busy, and two large CPU bound queries
are submitted for execution. If they land on the same scheduler, they
must remain on that scheduler until complete, even if load drops off the
others. When multiple CPU bound tasks are executing on one scheduler,
they yield to each other in order to
ensure each task receives equal amounts of CPU time. In our example, the
two large CPU bound tasks would be yielding to each other, despite the
availability of a number of idle schedulers.
In this case, the total CPU percentage (as reported by Performance
Monitor) may be well below 100 percent, even though there is significant
contention on some CPU cores.
Of course, if a system is completely CPU bound
(all CPU cores), then the yielding process, exposed with the
SOS_SCHEDULER_YIELD wait type, would be occurring on all schedulers. The
important point is that the yielding process may be occurring on only
some schedulers, even when overall CPU usage appears low. This makes the
SOS_SCHEDULER_YIELD wait type an important consideration in an overall
assessment of CPU pressure.
|
Scheduler details can be inspected with the
sys.dm_os_schedulers DMV. Querying this DMV will reveal one scheduler
per CPU core that the instance has access to, a number of system
schedulers, and one for the dedicated administrator connection (DAC).
Included columns reveal a number of scheduler-level details such as the
number of tasks and yield count.
|
In closing this section on CPU pressure, let's
look at some DMV queries that can be used in supplementing information
from the waits and queues analysis.
DMV queries
The two DMV queries in this section identify signal wait percentage and top CPU consumers.
Listing 5
can be used to detect signal waits and resource waits as a percentage
of the total wait time. As discussed previously, a high signal wait time
usually indicates CPU pressure.
Example 5. Signal wait time
-- Wait Analysis; Signal vs. Resource Waits
SELECT
SUM(wait_time_ms - signal_wait_time_ms) as [ResourceWaitTotal]
, CAST(100.0 * sum(wait_time_ms - signal_wait_time_ms)
/ SUM(wait_time_ms) as numeric(20, 2)) AS [ResourceWait%]
, SUM(signal_wait_time_ms) AS [SignalWaitTotal]
, CAST (100.0 * sum(signal_wait_time_ms)
/ SUM (wait_time_ms) AS numeric(20, 2)) AS [SignalWait%]
FROM sys.dm_os_wait_stats
WHERE
wait_type not in (
'CLR_SEMAPHORE'
, 'LAZYWRITER_SLEEP'
, 'RESOURCE_QUEUE'
, 'SLEEP_TASK'
, 'SLEEP_SYSTEMTASK'
, 'WAITFOR'
)
|
Notice that the script excludes a number of wait types not relevant to a performance-tuning process. The get/track_waitstats stored procedures we covered earlier perform the same exclusions.
Listing 6
lists the top 50 queries ordered by CPU time. It includes the
execution_count column to indicate how many times this query has been
executed. Frequently executed queries with large CPU consumption are
targets for optimization.
Example 6. Top 50 queries by CPU consumption
-- Top 50 Queries by CPU Consumption
SELECT TOP 50
queryStats.total_worker_time/queryStats.execution_count AS [Avg CPU
Time]
, queryStats.execution_count
, SUBSTRING(queryText.text,queryStats.statement_start_offset/2,
(CASE WHEN queryStats.statement_end_offset = -1
THEN len(convert(nvarchar(max), queryText.text)) * 2
ELSE queryStats.statement_end_offset end -
queryStats.statement_start_offset) / 2)
AS query_text
, dbname=db_name(queryText.dbid)
FROM sys.dm_exec_query_stats queryStats
CROSS APPLY sys.dm_exec_sql_text(queryStats.sql_handle) AS queryText
ORDER BY
[Avg CPU Time] DESC
|
One of the things that will become obvious as we
move throughout this section is that common design problems affect a
number of resources. For example, poor index selection and maintenance
can have a dramatic impact on both memory and disk I/O.
3. Index-related memory pressure
The correct selection and maintenance of indexes are crucial from a
query-performance perspective. Numerous unused indexes have a large
maintenance overhead, and missing or poorly maintained indexes have a
double impact on resources: additional disk I/O and a reduction in the
available buffer cache.
From a performance-monitoring perspective, the
following performance counters are of interest in assessing the impact
of poor index design and maintenance:
SQL Server:Buffer Manager - Page Life Expectancy—This
counter indicates the average time (in seconds) that data pages remain
in memory. A common occurrence is for this value to drop suddenly in
response to a large query that requires a lot of disk access, flushing
data pages from memory to make way for the required data from disk.
Missing indexes are a common contributor to this type of event. A system
with adequate memory and good indexing should see this value in excess
of 500 seconds, without frequent sudden drops during normal activity.
SQL Server:Buffer Manager - Buffer Cache Hit Ratio—This
counter indicates the percentage of time required pages are found in
the buffer cache. The higher the value, the better, as memory access is
obviously much faster than disk access. Once a SQL instance has been up
and running for a period of time covering typical activity, values lower
than 95 percent indicate memory pressure, one cause of which may be
additional disk I/O required to fulfill queries without the appropriate
indexes.
SQL Server:Access Methods - Full Scans/Sec—This
counter represents the number of full table (or index) scans per
second. There are no benchmark numbers to compare this value against. In
some cases, a table (or index) scan is actually preferred over an index
lookup;
however, one thing to look out for here is a sudden increase in this
value, possibly indicating that an index is no longer being used. As
with many other counters, baseline analysis is critical in being able to
accurately detect a significant increase/decrease.
SQL Server:Access Methods - Index Searches/Sec—Similar to Full Scans/Sec (but in the opposite direction), sudden decreases in this value may indicate an index is no longer being used.
SQL Server:Access Methods - Page Splits/Sec—When a record is inserted into an index, it must be inserted in order.
If the data page is full, the page splits in order to maintain the
appropriate order. A high value for this counter may warrant the
consideration of a lower fill factor.
In addition to increased memory pressure, poor index selection and maintenance have a direct and measurable impact on disk I/O.
4. Disk bottlenecks
Throughout this book we've covered a number of
best practices pertaining to the layout of data and transaction log
files, tempdb configuration, and sizing files to avoid autogrow
operations. We've also covered the importance of striping data across
multiple disks (spindles) and using RAID volumes for both performance
and redundancy. With these things in mind, let's explore the waits and
queues of significance in a disk bottleneck.
Waits
A classic performance-tuning dictum is there will always be a bottleneck somewhere,
the idea being to address/reduce each bottleneck until performance is
acceptable. As the slowest component, the bottleneck is usually on disk,
on both high- and low-performing systems. The following wait types
usually occupy the top two wait-list positions (after excluding system
background waits) on systems experiencing disk bottlenecks:
PAGEIOLATCH—As
pages are read into the buffer cache from disk, SQL Server uses a
series of latches (lightweight locks) on the buffer pages as they are
filled with data and released to the requesting process. Both
PAGEIOLATCH_SH and PAGEIOLATCH_EX are used as part of this process, and
the appearance of these wait types in the top wait positions may be an
indication of a disk I/O bottleneck, particularly when seen in
combination with high disk sec/transfer counters, which we'll cover
shortly.
ASYNC/IO_COMPLETION—Both
ASYNC_IO_COMPLETION and IO_COMPLETION indicate waits on disk I/O, with
the async version typically associated with operations such as backups
and restores.
WRITELOG—This
wait type is associated with writes to the transaction log. As covered
throughout this book, locating transaction logs on dedicated disk
volumes, preferably with a large battery-backed write cache, is
essential in any high-volume database solution.
A top ranking of these waits necessitates the inspection of a number of related queues.
Queues
The classic disk-related Performance Monitor counters are PhysicalDisk:Avg. Disk Sec/Read and PhysicalDisk:Avg. Disk Sec/Write, with the commonly accepted ranges for performance as follows:
These counters measure the time in milliseconds
for a read or write operation to disk and should be measured for each of
the applicable disk volumes. For high-throughput applications, you need
to pay particular attention to these counters for the transaction log
disk, which should be well under 10ms.
As we covered earlier in the book, you should
use the SQLIO and SQLIOSIM tools before commissioning any SQL Server
system for production use to verify both the throughput and validity of
the I/O system and compare the results to published vendor performance
expectations. In addition to these counters, additional counters of
interest are as follows:
Physical Disk:% Disk Time—This
counter measures the percentage of time the disk was busy servicing
reads and writes. The generally accepted idea is that more than 50
percent may represent a bottleneck for the measured disk.
Physical Disk:Avg./Current Disk Queue Length—A
sustained value of more than 2 indicates the disk is struggling to
service its queue. When measuring these counters, you need to consider
the number of disks in the array. For example, a volume with 10 disks
could reasonably service a queue of up to 20.
Physical Disk:Avg. Disk Reads & Writes /Sec—As
with the disk queue-length counters, you need to measure these counters
in awareness of the disk volume's underlying disk count. Values
approaching 90 percent of the disk's published read/writes per second
capacity may indicate an approaching bottleneck.
Bear in mind that during disk-bound operations
such as backups and restores, it's perfectly normal and reasonable to
see sustained disk activity, with cause for concern centered on the
speed or duration of the operation. For example, backing up a terabyte
database to disk will obviously bottleneck on disk; however, the
performance can be maximized by using dedicated backup disks, multiple
backup files, and so forth.
In closing our brief look at disk I/O, let's examine a couple of DMV queries.
DMV queries
The two DMV queries in this section identify queries with the largest I/O usage and database files with the highest stall rates.
Listing 7 lists the top 50 queries ordered by I/O usage.
Example 7. Top 50 queries by I/O usage
-- Top 50 Queries by I/O Consumption
SELECT TOP 50
(total_logical_reads + total_logical_writes) / execution_count AS [Avg
IO]
, substring (qt.text,qs.statement_start_offset/2, (
CASE WHEN qs.statement_end_offset = -1
THEN len(convert(nvarchar(max), qt.text)) * 2
ELSE qs.statement_end_offset end - qs.statement_start_offset)/2
) AS query_text
, qt.dbid
, qt.objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
ORDER BY [Avg IO] DESC
|
Listing 8 uses the sys.dm_io_virtual_file_stats function to inspect the stall rate
of disk I/O per file. A stall occurs when a process waits for I/O to
complete. By determining which files are stalling the most,
opportunities arise for rebalancing I/O. A good example of this is
multiple high-transaction-rate databases using the same physical
disk(s). By segregating their database files on separate disks, you
should be able to improve the throughput (and reduce the stall rate).
Example 8. Database file I/O stalls
-- Identify database files with the highest stall rate
SELECT
db_name(database_id)
, file_id
, io_stall_read_ms
, num_of_reads
, cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as
'avg_read_stall_ms'
, io_stall_write_ms
, num_of_writes
, cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as
'avg_write_stall_ms'
, io_stall_read_ms + io_stall_write_ms as io_stalls
, num_of_reads + num_of_writes as total_io
, cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads +
num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
FROM sys.dm_io_virtual_file_stats(null,null)
WHERE database_id > 4
ORDER BY
database_id, avg_io_stall_ms DESC
|
In closing this section on common performance problems, let's turn our attention to blocking.
5. Blocking
A block occurs
when one query wants to access data that is locked by another. Despite
blocks and locks being normal, fundamental components of any relational
database management system, they present a significant problem in poorly
designed databases and transactions.
In this
section, we'll cover blocking from a waits perspective.
LCK_* waits
SQL Server Books Online lists all of the wait types that may be encountered, including an impressive collection beginning with LCK_. All of these represent a wait on a particular lock being released. For example, a transaction with a shared lock on a row will block a separate transaction that requires an exclusive lock on the same row. In this case, the registered wait type will be LCK_M_X.
When the volume of locks and subsequent blocks
increases, overall transaction throughput decreases, often accompanied
by a reduction in Performance Monitor counters such as SQL Server SQL
Statistics:Batch Requests/Sec. To the untrained eye, a severe blocking
problem is often met with confusion; users complain of poor performance,
but a quick check of the classic Performance Monitor counters (CPU,
disk, and so forth) reveals little in the way of server load. When the
blocking transaction completes, activity returns to normal.
A fundamental component of a good database
design is short transaction length with the appropriate isolation level
(and usually with an optimistic locking mode). Such topics are beyond
the scope of this book: however, should you identify blocking as a top
wait type, the sys.dm_db_index_operational_stats function can assist you in investigating the problem further.
sys.sm_db_index_operational_stats
One of the purposes of the sys.dm_db_index_operational_stats function is to determine the tables and indexes with the highest occurrence of row lock waits, as shown in listing 9.
Example 9. Tables/indexes with high lock waits
— Identify tables and indexes with the highest number of row lock waits
SELECT
db_name(db_id())
, object_name(s.object_id) as objectname
, i.name as indexname
, row_lock_count
, row_lock_wait_count
, cast (100.0 * row_lock_wait_count /
(1 + row_lock_count) as numeric(15,2)) as [block %]
, row_lock_wait_in_ms
, cast (1.0 * row_lock_wait_in_ms /
(1 + row_lock_wait_count) as numeric(15,2)) as [avg row lock waits in
ms]
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) s
INNER JOIN sys.indexes i on s.object_id = i.object_id
AND s.index_id = i.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
ORDER BY row_lock_wait_count desc
|
In combination with the SQL Profiler blocked
process report event, this script can be used to identify the source of
common blocking problems. The avg row lock waits in ms
column, as the name suggests, returns the average lock wait time. This
value can be used in estimating which value to set for the sp_configure 'blocked process threshold'
value, although it should be noted that the sp_configure value is set
in seconds, whereas the value returned from the above script is in
milliseconds.
