iT邦幫忙

2019 iT 邦幫忙鐵人賽

DAY 21
1
自我挑戰組

IT人員面面觀系列 第 20

SQL Server常見問題之一

有時會突然發現SQL Server的反應怎麼愈來愈慢,

直覺第一步會先去查看SQL Error Log是不是有產生什麼問題的紀錄,

一看沒有發生什麼異常?! 那麼我接下來就會透DMV查出當下執行的SQL指令情況

果不其然有幾個語法也被Lock住,而且隨著使用者數的增加,愈來愈多語法被Lock...

因此首先先找出Lock的源頭SQL指令來解決,這麼一來其它被Lock的SQL指令自然會被順利執行。

模擬的範例如下:

USE AdventureWorks2008

--先建立測試用表格
CREATE TABLE Test
(ID INT IDENTITY,Name VARCHAR(20))
--塞入測試資料
INSERT INTO Test VALUES('1111'),('2222'),('3333'),('4444'),('5555')

--接著開啓交易後不要Commite!
BEGIN TRAN
UPDATE Test
    SET NAME = 'testtest'
    WHERE ID = 5

以上己模擬出未Commite的交易己經產生Lock,接著再開新的查詢輸入以下指令

use AdventureWorks2008

SELECT * from Test

你會發現狀態行會出現"正在執行查詢中",沒有任何查詢結果出現,
因為Test這張表己經被剛才交易鎖定了,可透過以下DMV進行查詢當下SQL指令的執行情況

use AdventureWorks2008
--找鎖定的物件、類型以及被誰Blocking
SELECT      r.scheduler_id ,
            status         ,
            r.session_id   as SPID,
            r.blocking_session_id as BlockBy,
            substring(
				ltrim(q.text),
				r.statement_start_offset/2+1,
				(CASE
                 WHEN r.statement_end_offset = -1
                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
                 ELSE r.statement_end_offset
                 END - r.statement_start_offset)/2)
                 AS [正在執行SQL命令],
            r.cpu_time      as [CPU Time(ms)],
            r.start_time    as [Start Time],
            r.total_elapsed_time as [Execute Total Time],
            r.reads              as [Reads],
            r.writes             as [Writes],
            r.logical_reads      as [Logical Reads],
            -- q.text, /* 完整的 T-SQL 指令碼 */
            d.name               as [DataBase]
FROM        sys.dm_exec_requests r 
			CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
			LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE       r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY    r.total_elapsed_time desc

https://ithelp.ithome.com.tw/upload/images/20181021/20107408z2xZ0X8doI.jpg
由上圖可看出SPID 56被SPID 54 Block住,可以用exec sp_who2找出SPID裡的內容
https://ithelp.ithome.com.tw/upload/images/20190520/20107408EEoXERZEjc.jpg
此時相信聰明的你應該知道要怎麼做了,通常應急的做法就是把SPID 54先Kill掉,

再來找SPID 54執行的那段SQL指令為什麼會造成Block,

但要小心這樣子Kill Process是否會產生長時間的RollBack,

如果有就必需要花時間去等待RollBack交易的完成。

以上將同步發表於https://shareitnote.blogspot.com/


上一篇
SQL Profiler和SQL Trace的介紹 Part 2
下一篇
[SQL Server] Performance DashBoard Report的介紹
系列文
IT人員面面觀28

尚未有邦友留言

立即登入留言