SQL Server - 如何透過DMV進行效能評估與監控

        SQL Server有許多監控的方式，較常用的就是透過活動監視器(Activity Monitor)觀察目前使所有的Session，藉以找出那一個Session使用的CPU、IO或執行最久，但是從SQL Server 2005之後，就推出了DMV(Dynamic Management Views)可以找出從SQL Server開機以後到目前的資訊，所以只要有效的利用這個資訊，在系統發生問題時，就可以利用這項功能有效的找出。

        底下我就透過一個例子讓你可以藉以找出目前有問題的語法，當然我也整理了一些常用的DMV用法，但是在管理與分析上，其實不只是透過DMV之外，最好也可以透過Performance Counter進行交叉比對，藉以找出語法執行過慢的問題並加以加析。

事件模擬：
下列我會開一個視窗啟動一個交易，並於交易中新增一筆記錄，但是並不下Commit或Rollback，藉以模擬交易鎖定的狀態。
begin tran
insert into [test_db2].[dbo].[Table_1]
([name] ,[clsid])
values('caryhsu', newid())

另外再開一個視窗，查詢同一個表格，由於此表格並未執行Commit或Rollback，所以會有鎖定的情況發生，請執行下列的語法。
select * from table_1
接下來只有查詢此表格的查詢都會有上述的情況發生，所以接下來我們就透過DMV來進行解析，藉以找出問題。

1、列出目前在執行的Process中有遇到Blocked的部份。
select * from sys.sysprocesses
where blocked <> 0


從上述的查詢中可以看出，目前Session ID 53，被Session ID 54給鎖定住，鎖定類型為LCK_M_S，當然另外我們也要注意waittime的部份，如果鎖定的越久，代表影響到其他的查詢相對也會越久。

PS：請特別注意，透過上述語法查出資料時，並不一定就代表有問題，因為鎖定本來就是合理與預期的動作，但需特別注意的事，但如果查詢出來的資料中waittime過大時，那就代表需特別注意。

2、另外我們可以透過下列的查詢，找出目前系統中等待的前10名類型為何，而查詢出的wait_type可以到下列的網址查詢類型的說明。
select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc
Wait Type參考連結：
sys.dm_os_wait_stats (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms179984.aspx

3、由於我們在第一個查詢知道Session ID 53被54給鎖定住，所以我們可以透過下列的語法找出Session ID 54正在進行的語法與內容。
dbcc inputbuffer(54)

透過上述的方式可以快速且簡單的找出目前的問題，另外下方我也提供了三個語句給大家，希望大家藉以分析並找出目前系統的瓶頸所在。

1、找出最耗用IO的語法
SELECT TOP 10
total_logical_reads,
total_logical_writes,
execution_count,
total_logical_reads+total_logical_writes AS [IO_total],
st.text AS query_text,
db_name(st.dbid) AS database_name,
st.objectid AS object_id
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_logical_reads+total_logical_writes> 0
ORDER BY [IO_total] DESC
2、列出目前最耗用CPU的前50個查詢
SELECT  TOP 50
qs.total_worker_time / qs.execution_count AS[Avg CPU Time],
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 AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
ORDER BY [Avg CPU Time] DESC;

3、列出目前最耗用Worker Time的前50個查詢
SELECT   TOP 50 sum(qs.total_worker_time) AS total_cpu_time,
                sum(qs.execution_count) AS total_execution_count,
                count(*) AS '#_statements',
                qt.dbid,
                qt.objectid,
                qs.sql_handle,
                qt.[text]
FROM     sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt
GROUP BY qt.dbid, qt.objectid, qs.sql_handle, qt.[text]
ORDER BY sum(qs.total_worker_time) DESC, qs.sql_handle;


鎖定類型參考：

Lastwaittype	Waittype	描述
LCK_M_SCH_S	0x01	結構描述穩定性
LCK_M_SCH_M	0x02	結構描述修改
LCK_M_IS	0x03	轉譯分享
LCK_M_SIU	0x04	要更新的轉譯分享
LCK_M_IS_S	0x05	共用轉譯分享 (機碼範圍鎖定)
LCK_M_IX	0x06	轉譯排除
LCK_M_SIX	0x07	共用轉譯排除
LCK_M_S	0x08	共用
LCK_M_U	0x09	更新
LCK_M_II_NL	0x0A	轉譯插入 NULL (機碼範圍鎖定)
LCK_M_II_X	0x0B	轉譯插入排除 (機碼範圍鎖定)
LCK_M_IU	0x0C	轉譯更新鎖定
LCK_M_IS_U	0x0D	轉譯分享更新 (機碼範圍鎖定)
LCK_M_X	0x0E	排除
LCK_M_BU	0x0F	大量更新

參考連結：
sys.sysprocesses (Transact-SQL)
http://support.microsoft.com/kb/244455/zh-tw


關鍵字：SQL Server、DMV、Dynamic Management Views、Performance Monitoring、性能分析、性能優化