除了打開管理工具中的活動監視器(Activity Monitor)外,來試試幾個動態管理檢視(DMV)的組合(dm_tran_locks x dm_os_waiting_tasks x dm_exec_connections)。
建立環境
建立一個簡單的Table T1,然後寫入5筆很厲害的語言資料。
USE tempdb
CREATE TABLE T1
(ID INT IDENTITY,NAME NVARCHAR(20), PRIMARY KEY(ID))
INSERT INTO T1 VALUES('JAVA'),('C#'),('PYTHON'),('R'),('COBOL')
SELECT * FROM T1
好的,現在我們有5個很厲害的語言了。
C#真棒!
接著進入活動最精彩的喊口號時間: C#真棒! .NET好威呀!
1.Ctrl + N新開一個SQL查詢視窗(建立連線1,鎖定資料,但不Commit)
BEGIN TRAN
UPDATE T1
SET NAME = 'C#真棒!'
WHERE ID = 3
2.Ctrl + N新開第二個SQL查詢視窗(建立連線2,執行查詢)。
SELECT * from T1
執行如下,左邊是Session id=52的執行,右邊是Session = 53的執行。
session id=53,持續執行中,資料表當中的資料被鎖定,暫時被Block
3.透過動態管理物件(Dmv)組合來找當下資料庫內相互之間的鎖定及阻塞關係:
首先取得目前系統中被鎖定的交易(dm_tran_locks)以及被鎖定交易的session id,接著從鎖定擁有者的位址關聯到等待資源的情形(dm_os_waiting_tasks),從中找出封鎖者的session id(blocking_session_id)後,最後再從系統目前執行的交易連接中(dm_exec_connections)找到最後執行的T-SQL指令。
找出被鎖定物件以及語法:
use tempdb
--找鎖定的物件、類型以及被誰Blocking
SELECT
tl.request_session_id AS [我的SID]
,tl.resource_type AS [資源類型]
,DB_NAME(tl.resource_database_id) AS [資料庫名稱]
,(CASE resource_type
WHEN 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id)
ELSE (SELECT
OBJECT_NAME(object_id)
FROM sys.partitions
WHERE hobt_id = resource_associated_entity_id)
END) AS [物件名稱]
,tl.resource_description AS [資源說明]
,tl.request_mode AS [鎖定類型]
,tl.request_status AS [狀態]
,wt.blocking_session_id AS [被阻塞SID]
,c.connect_time AS [連接時間]
,txt.text AS [最近執行語法]
,lock_txt.text AS [被阻塞的執行語法]
FROM sys.dm_tran_locks AS tl
LEFT JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
LEFT JOIN sys.dm_exec_connections AS c
ON tl.request_session_id = c.session_id
LEFT JOIN sys.dm_exec_connections AS d
ON wt.blocking_session_id = d.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) txt
OUTER APPLY sys.dm_exec_sql_text(d.most_recent_sql_handle) lock_txt
WHERE resource_type != 'DATABASE'
AND tl.request_session_id > 50
ORDER BY tl.request_session_id
GO
可以從查詢結果集中發現sid=53的交易準備對T1作共用鎖(S:share lock),但被sid=52的交易 block,因為她已經將資料獨佔鎖中(X:Exclusive lock )。
sid=53的語法是SELECT * FROM T1
sid=52的語法是BEGIN TRAN UPDATE T1...
下一步就是動手調校語法了。
征服者世界奇觀:黃金塔
參考:
以動態管理物件觀察 SQL Server(2)─找到最耗資源的執行計畫