2012年11月21日 星期三

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;


鎖定類型參考:
LastwaittypeWaittype描述
LCK_M_SCH_S0x01結構描述穩定性
LCK_M_SCH_M0x02結構描述修改
LCK_M_IS0x03轉譯分享
LCK_M_SIU0x04要更新的轉譯分享
LCK_M_IS_S0x05共用轉譯分享 (機碼範圍鎖定)
LCK_M_IX0x06轉譯排除
LCK_M_SIX0x07共用轉譯排除
LCK_M_S0x08共用
LCK_M_U0x09更新
LCK_M_II_NL0x0A轉譯插入 NULL (機碼範圍鎖定)
LCK_M_II_X0x0B轉譯插入排除 (機碼範圍鎖定)
LCK_M_IU0x0C轉譯更新鎖定
LCK_M_IS_U0x0D轉譯分享更新 (機碼範圍鎖定)
LCK_M_X0x0E排除
LCK_M_BU0x0F大量更新


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


關鍵字:SQL ServerDMVDynamic Management ViewsPerformance Monitoring性能分析性能優化

1 則留言: