Database Reference
In-Depth Information
Table 27-1.
Common problems, symptoms, and solutions
Problem
Symptoms / Monitoring Targets
Further Actions
PAGEIOLATCH
,
IO_COMPLETION
,
WRITELOG
,
LOGBUFFER, BACKUPIO
waits
sys.dm_io_virtual_file_stats
stalls
Low
Page Life Expectancy
, High
Page Read/
Sec
,
Page Write/Sec
performance counters
Overloaded I/O
Subsystem
Check I/O subsystem configuration and
throughput, especially in cases of non-data
page I/O waits. Detect and optimize I/O
intensive queries using
sys.dm_exec_query_
stats
, SQL Trace, and Extended Events.
High CPU load,
SOS_SCHEDULER_YIELD
waits, high percentage of signal waits
CPU Load
Possible non-efficient T-SQL code. Detect and
optimize CPU intensive queries using
sys.dm_
exec_query_stats
, SQL Trace, and Extended
Events. Check recompilation and plan reuse in
OLTP systems.
RESOURCE_SEMAPHORE
waits. Non-zero
Memory Grants Pending
value. Pending
requests in sys.dm_exec_memory_grants.
Query Memory
Grants
Detect and optimize queries that require large
memory grants. Perform general query tuning.
CXMEMTHREAD
waits
HEAP Memory
Allocation
contention
Enable the “Optimize for Ad-hoc Workloads”
configuration setting. Analyze what memory
objects consume the most memory, and
switch to per-CPU partitioning with the
T8048
trace flag if appropriate. Apply the latest
service pack.
CXPACKET
waits
Parallelism in OLTP
systems
Find the root-cause of parallelism; most likely
non-optimized or reporting queries. Perform
query optimization for the non-optimized
queries that should not have parallel plans.
Tune and increase
Cost Threshold for
Parallelism
value.
LCK_M_*
waits. Deadlocks.
Locking and
Blocking
Detect queries involved in blocking with
sys.dm_tran_locks
,
Blocking Process Report
,
and
Deadlock Graph
. Eliminate root-cause of
blocking, most likely non-optimized queries
or client code issues.
ASYNC_NETWORK_IO
waits, Network
performance counters
ASYNC_NETWORK_
IO waits
Check network performance. Review and
refactor client code (loading excessive amount
of data and/or loading and processing data
simultaneously).
THREADPOOL
waits
Worker thread
starvation
Detect and address root-cause of the problem
(blocking and/or load). Upgrade to 64-bit
version of SQL Server. Increasing Maximum
Working Thread number may or may not help.
PAGELATCH
waits
Allocation maps
contention
Detect resource that lead to contention using
sys.dm_os_waiting_tasks
and
sys.dm_exec_
requests
. Add more data files. In the case
of
tempdb
, use T1118 and utilize temporary
object caching.
