監控 SQL Server (2005/2008) 的運作狀況--來自微軟TetchNet - 邀月 - 部落格園

<a href="http://www.cnblogs.com/downmoon/">邀月工作室</a>

<a href="http://www.cnblogs.com/downmoon/archive/2009/08/12/1544764.html">監控 sql server (2005/2008) 的運作狀況--來自微軟tetchnet</a>

原文位址：

<a href="http://technet.microsoft.com/zh-cn/library/bb838723.aspx">http://technet.microsoft.com/zh-cn/library/bb838723.aspx</a>

microsoft sql server 2005 提供了一些工具來監控資料庫。方法之一是動态管理視圖。動态管理視圖 (dmv) 和動态管理函數 (dmf) 傳回的伺服器狀态資訊可用于監控伺服器執行個體的運作狀況、診斷問題和優化性能。

正常伺服器動态管理對象包括：

dm_db_*：資料庫和資料庫對象

dm_exec_*：執行使用者代碼和關聯的連接配接

dm_os_*：記憶體、鎖定和時間安排

dm_tran_*：事務和隔離

dm_io_*：網絡和磁盤的輸入/輸出

此部分介紹為監控 sql server 運作狀況而針對這些動态管理視圖和函數運作的一些常用查詢。

摘錄部分精彩sql如下：

下面的查詢顯示 cpu 平均占用率最高的前 50 個 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

下面的查詢顯示一些可能占用大量 cpu 使用率的運算符（例如 ‘%hash match%’、‘%sort%’）以找出可疑對象。

<a></a>

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%'

運作下面的 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

下面的示例查詢顯示已重新編譯的前 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 &gt; 1

order by plan_generation_num desc

下面的 dmv 查詢可用于查找哪些批處理/請求生成的 i/o 最多。如下所示的 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_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

……………………………………