附錄 A:監控 SQL Server 狀況
更新日期: 2009-04-30
Microsoft SQL Server 2005 提供一些工具來監視資料庫。其中一種方式是動態管理檢視。動態管理檢視 (DMV) 和動態管理功能 (DMF) 傳回的伺服器狀態資訊可以用來監視伺服器執行個體的狀況、診斷問題,以及微調效能。
一般伺服器動態管理物件包括下列各項:
dm_db_ *:資料庫和資料庫物件
dm_exec_ *:使用者程式碼和相關連線的執行
dm_os_ *:記憶體、鎖定及排程
dm_tran_ *:交易和隔離
dm_io_ *:網路和磁碟的輸入/輸出
本節顯示一些針對這些動態管理檢視和功能的常用查詢,用以監視 SQL Server 狀況。
範例查詢
您可以執行下列查詢以取得所有 DMV 和 DMF 名稱:
SELECT * FROM sys.system_objects
WHERE name LIKE 'dm_%'
ORDER BY name
監視 CPU 瓶頸
CPU 瓶頸通常是由於沒有最佳化的查詢計劃、不良組態、不良設計因素或硬體資源不足所造成。下列是一些常用的查詢,可以協助您識別造成 CPU 瓶頸的原因。
下列查詢提供目前哪些快取批次或程序使用最多 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
下列查詢顯示具有 SQL 文字之快取計劃的彙總 CPU 使用率。
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
下列查詢顯示前 50 個具有高平均 CPU 耗用量的 SQL 陳述式。
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
下列會顯示 DMV 查詢,以找出過多的編譯/重新編譯。
select * from sys.dm_exec_query_optimizer_info
where
counter = 'optimizations'
or counter = 'elapsed time'
下列範例查詢提供前 25 個已重新編譯的預存程序。plan_generation_num 指出重新編譯查詢的次數。
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
無效率的查詢計劃可能會增加 CPU 耗用量。
下列查詢顯示哪個查詢正在使用最大的累積 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
下列查詢顯示一些可能 CPU 密集的運算子 (例如 '%Hash Match%'、'%Sort%'),以尋找可疑項目。
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%'
如果您已偵測到無效率的查詢計劃,且這些查詢計劃造成高 CPU 耗用量,請在與查詢相關的資料表上執行 UPDATE STATISTICS,並檢查問題是否仍然存在。然後,收集資料,並將問題回報給 PerformancePoint 規劃支援小組。
如果您的系統有過多的編譯和重新編譯,則可能會導致系統發生 CPU 繫結效能問題。
您可以執行下列 DMV 查詢,以找出過多的編譯和重新編譯。
select * from sys.dm_exec_query_optimizer_info
where
counter = 'optimizations'
or counter = 'elapsed time'
下列範例查詢提供前 25 個已重新編譯的預存程序。plan_generation_num 指出重新編譯查詢的次數。
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
如果您已偵測到過多的編譯或重新編譯,請盡量收集最多的資料,並將它們回報給規劃支援小組。
記憶體瓶頸
在啟動記憶體壓力偵測和調查之前,請確定已啟用 SQL Server 中的進階選項。請先在 master 資料庫上執行下列查詢,以開啟此選項。
sp_configure 'show advanced options'
go
sp_configure 'show advanced options', 1
go
reconfigure
go
請先執行下列查詢,以檢查記憶體相關的組態選項。
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
請執行下列 DMV 查詢,以查看 CPU、排程器記憶體和緩衝集區資訊。
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 瓶頸
檢查閂鎖等候,以識別 I/O 瓶頸。請執行下列 DMV 查詢,以尋找 I/O 閂鎖等候統計資料。
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
如果 waiting_task_counts 和 wait_time_ms 發生異於平常的明顯變更,請識別 I/O 問題。您必須在 SQL Server 順利執行時,取得效能計數器和重要 DMV 查詢輸出的基準。
這些 wait_types 可以指出 I/O 子系統是否遭遇瓶頸。
請使用下列 DMV 查詢,以找出目前暫止的 I/O 要求。請定期執行此查詢,以檢查 I/O 子系統狀況,並找出與 I/O 瓶頸相關的實體磁碟。
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
在正常情況下,查詢通常不會傳回任何項目。您需要進一步調查此查詢是否傳回一些資料列。
您也可以執行下列 DMV 查詢,以找出 I/O 繫結查詢。
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
下列 DMV 查詢可以用來找出哪些批次/要求產生最大的 I/O。而與下列類似的 DMV 查詢可以用來找出前 5 個產生最大 I/O 的要求。調整這些查詢會改善系統效能。
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
封鎖
執行下列查詢,以判定封鎖工作階段。
select blocking_session_id, wait_duration_ms, session_id from
sys.dm_os_waiting_tasks
where blocking_session_id is not null
使用此呼叫可找出 blocking_session_id 傳回的 SQL。例如,如果 blocking_session_id 為 87,請執行此查詢以取得 SQL。
dbcc INPUTBUFFER(87)
下列查詢顯示 SQL 等候分析和前 10 個等候資源。
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
若要找出哪個 spid 會封鎖另一個 spid,請在資料庫中建立下列預存程序,然後執行預存程序。此預存程序會報告封鎖情況。請鍵入 sp_who 以找出 @spid;@spid 是選用參數。
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)
下列是使用此預存程序的範例。
exec sp_block
exec sp_block @spid = 7