Appendix A: Monitoring SQL Server health
Updated: 2009-04-30
Microsoft SQL Server 2005 offers some tools for monitoring databases. One of the ways is dynamic management views. Dynamic management views (DMVs) and dynamic management functions (DMFs) return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
General server dynamic management objects include the following:
dm_db_*: Databases and database objects
dm_exec_*: Execution of user code and associated connections
dm_os_*: Memory, locking, and scheduling
dm_tran_*: Transactions and isolation
dm_io_*: Input/output on network and disks
This section shows some commonly used queries against these dynamic management views and functions to monitor your SQL Server health.
Sample query
You can run the following query to get all DMV and DMF names:
SELECT * FROM sys.system_objects
WHERE name LIKE 'dm_%'
ORDER BY name
Monitoring CPU bottlenecks
A CPU bottleneck is often caused by a non-optimal query plan, a poor configuration, poor design factors, or insufficient hardware resources. The following are some commonly used queries to help you identify what causes a CPU bottleneck.
The following query gives you a high-level view of which currently cached batches or procedures are using the most CPU.
SELECT TOP 50
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
COUNT(*) AS number_of_statements,
qs.sql_handle
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC
The following query shows the aggregate CPU usage by cached plans with SQL text.
SELECT
total_cpu_time,
total_execution_count,
number_of_statements,
s2.text
--(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM
(SELECT TOP 50
SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
COUNT(*) AS number_of_statements,
qs.sql_handle --,
--MIN(statement_start_offset) AS statement_start_offset,
--MAX(statement_end_offset) AS statement_end_offset
FROM
sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC) AS stats
CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2
The following query shows the top 50 SQL statements with high average CPU consumption.
SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC
The following shows DMV queries to find out excessive compiles/recompiles.
select * from sys.dm_exec_query_optimizer_info
where
counter = 'optimizations'
or counter = 'elapsed time'
The following sample query gives you the top 25 stored procedures that have been recompiled. The plan_generation_num indicates the number of times the query has recompiled.
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc
An inefficient query plan may cause increased CPU consumption.
The following query shows which query is using the most cumulative CPU.
SELECT
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
The following query shows some operators that may be CPU intensive, such as ‘%Hash Match%’, ‘%Sort%’ to look for suspects.
select *
from
sys.dm_exec_cached_plans
cross apply sys.dm_exec_query_plan(plan_handle)
where
cast(query_plan as nvarchar(max)) like '%Sort%'
or cast(query_plan as nvarchar(max)) like '%Hash Match%'
If you have detected inefficient query plans and that cause high CPU consumption, run UPDATE STATISTICS on the tables involved in the query and check to see if the problem persists. Then, gather the data and report the problem to PerformancePoint Planning support.
If your system has excessive compiles and recompiles, it could result in a CPU-bound performance problem on the system.
You can run the following DMV queries to find out excessive compiles/recompiles.
select * from sys.dm_exec_query_optimizer_info
where
counter = 'optimizations'
or counter = 'elapsed time'
The following sample query gives you the top 25 stored procedures that have been recompiled. The plan_generation_num indicates the number of times the query has recompiled.
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc
If you have detected excessive compilation or recompilation, gather as much data as you can and report it to Planning support.
Memory bottlenecks
Before you start memory pressure detection and investigation, make sure you have enabled the advanced options in SQL Server. Run the following query on the master database to turn on this option first.
sp_configure 'show advanced options'
go
sp_configure 'show advanced options', 1
go
reconfigure
go
Run the following query to check memory-related configuration options first.
sp_configure 'awe_enabled'
go
sp_configure 'min server memory'
go
sp_configure 'max server memory'
go
sp_configure 'min memory per query'
go
sp_configure 'query wait'
go
Run the following DMV query to see the CPU, scheduler memory, and buffer pool information.
select
cpu_count,
hyperthread_ratio,
scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb,
bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info
I/O bottlenecks
Identify I/O bottlenecks by examining the latch waits. Run the following DMV query to find I/O latch wait statistics.
select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%' and waiting_tasks_count > 0
order by wait_type
Identify an I/O problem if your waiting_task_counts and wait_time_ms change significantly from what you see normally. It is important to get a baseline of performance counters and key DMV query outputs when SQL Server is running smoothly.
These wait_types can indicate whether your I/O subsystem is experiencing a bottleneck.
Use the following DMV query to find currently pending I/O requests. Execute this query periodically to check the health of I/O subsystem and to isolate physical disk(s) that are involved in the I/O bottlenecks.
select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
The query usually returns nothing in the normal situation. You need to investigate further if this query returns some rows.
You can also find I/O bound queries by executing the following DMV query.
select top 5 (total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_physical_reads,
Execution_count, statement_start_offset, p.query_plan, q.text
from sys.dm_exec_query_stats
cross apply sys.dm_exec_query_plan(plan_handle) p
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by (total_logical_reads + total_logical_writes)/execution_count Desc
The following DMV query can be used to find which batches/requests are generating the most I/O. A DMV query like the following can be used to find the top five requests that generate the most I/Os. Tuning those queries will improve the system performance.
select top 5
(total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
statement_start_offset as stmt_start_offset,
sql_handle,
plan_handle
from sys.dm_exec_query_stats
order by (total_logical_reads + total_logical_writes) Desc
Blocking
Run the following query to determine the blocking sessions.
select blocking_session_id, wait_duration_ms, session_id from
sys.dm_os_waiting_tasks
where blocking_session_id is not null
Use this call to find out which SQL is returned by the blocking_session_id. For example, if the blocking_session_id is 87, run this query to get the SQL.
dbcc INPUTBUFFER(87)
The following query shows SQL waits analysis and top 10 resources waited on.
select top 10 *
from sys.dm_os_wait_stats
--where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
order by wait_time_ms desc
To find out which spid is blocking another spid, create the following stored procedure in your database and then execute the stored procedure. This stored procedure reports the blocking situation. Type sp_who to find out @spid; @spid is optional parameter.
create proc dbo.sp_block (@spid bigint=NULL)
as
select
t1.resource_type,
'database'=db_name(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address and
t1.request_session_id = isnull(@spid,t1.request_session_id)
The following are examples of using this stored procedure.
exec sp_block
exec sp_block @spid = 7
Download this book
This topic is included in the following downloadable book for easier reading and printing:
See the full list of available books at Downloadable content for PerformancePoint Planning Server.