底下我就透過一個例子讓你可以藉以找出目前有問題的語法，當然我也整理了一些常用的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、性能分析、性能優化
如何抓到目前正在執行的SQL語法
回覆刪除