sql server 效能調優 資源等待之 LCk

標籤：關於   交易隔離等級   max   修改   HERE   bubuko   技術   ali   重設   

 一.  概述

　　這次介紹執行個體層級資源等待LCK類型鎖的等待時間，關於LCK鎖的介紹可參考 “sql server 鎖與事務撥雲見日”。下面還是使用sys.dm_os_wait_stats 來查看，並找出耗時最高的LOK鎖。

select wait_type,waiting_tasks_count,wait_time_ms ,max_wait_time_ms,signal_wait_time_msfrom sys.dm_os_wait_statswhere wait_type like ‘LCK%‘ order by  wait_time_ms desc

 查出如所示：

   1.  分析介紹

 　　重點介紹幾個耗時最高的鎖含義：

　   LCK_M_IX： 正在等待擷取意向排它鎖。在增刪改查中都會有涉及到意向排它鎖。
　　LCK_M_U： 正在等待擷取更新鎖定。 在修改刪除都會有涉及到更新鎖定。
　　LCK_M_S：正在等待擷取共用鎖定。 主要是查詢，修改刪除也都會有涉及到共用鎖定。
　　LCK_M_X：正在等待擷取排它鎖。在增刪改中都會有涉及到排它鎖。
　　LCK_M_SCH_S：正在等待擷取架構共用鎖定。防止其它使用者修改如表結構。
　　LCK_M_SCH_M：正在等待擷取架構修改鎖　如添加列或刪除列 這個時候使用的架構修改鎖。

      下面表格是統計分析

鎖類型	鎖等待次數	鎖等待總時間（秒）	平均每次等待時間(毫秒)	最大等待時間
LCK_M_IX	26456	5846.871	221	47623
LCK_M_U	34725	425.081	12	6311
LCK_M_S	613	239.899	391	4938
LCK_M_X	4832	77.878	16	4684
LCK_M_SCH_S	397	77.832	196	6074
LCK_M_SCH_M	113	35.783	316	2268

　　注意： wait_time_ms 時間裡，該時間表包括了signal_wait_time_ms訊號等待時間，也就是說wait_time_ms不僅包括了申請鎖需要的等待時間，還包括了線程Runnable 的訊號等待。通過這個結論也能得出max_wait_time_ms 最大等待時間不僅僅只是鎖申請需要的等待時間。

 

2. 重現鎖等待時間

--  重設DBCC SQLPERF (‘sys.dm_os_wait_stats‘, CLEAR);  

　

--  會話1 更新SID=92525000， 未提交begin tran update [dbo].[PUB_StockTestbak] set model=‘mmtest‘ where sid=92525000

-- 會話2 查詢該ID， 由於會話1更新未提交 佔用x鎖，這裡查詢將阻塞select * from [PUB_StockTestbak] where sid=92525000

 　　手動取消會話2的查詢，佔用時間是61秒,如：

　　再來統計資源等待LCK，如 ：

　　總結：可以看出資源等待LCK的統計資訊還是非常正確的。所以找出效能消耗最高的鎖類型，去最佳化是很有必要。比較有針對性的解決阻塞問題。

3. 造成等待的現象和原因

現象：

　　(1)  使用者並發越問越多，效能越來越差。應用程式運行很慢。

　　(2)  用戶端經常收到錯誤 error 1222 已超過了鎖請求逾時時段。

　　(3)  用戶端經常收到錯誤 error 1205 死結。

　　(4)  某些特定的sql 不能及時返回應用端。

原因：

　　(1) 使用者並發訪問越多，阻塞就會越來越多。

　　(2) 沒有合理使用索引，鎖申請的數量多。

　　(3) 共用鎖定沒有使用nolock， 查詢帶來阻塞。 好處是必免髒讀。

　　(4) 處理的資料過大。比如：一次更新上千條，且並發多。

　　(5) 沒有選擇合適的交易隔離等級,複雜的交易處理等。

4.  最佳化鎖的等待時間

　　 在最佳化鎖等待最佳化方面，有很多切入點 像前幾篇中有介紹 CPU和I/O的耗時排查和處理方案。 我們也可以自己寫sql來監聽鎖等待的sql 語句。能夠知道哪個庫，哪個表，哪條語句發生了阻塞等待，是誰阻塞了它，阻塞的時間。

　　從上面的平均每次等待時間(毫秒)，最大等待時間 作為參考可以設定一個閥值。 通過sys.sysprocesses 提供的資訊來統計， 關於sys.sysprocesses使用可參考 "sql server 效能調優 從使用者工作階段狀態分析"。 通過該視圖 監聽一段時間內的阻塞資訊。可以設定每10秒跑一次監聽語句，把阻塞與被阻塞儲存下來。

 　　思想如下：

-- 例如 找出被阻塞會話ID 如時間上是2秒 以及誰阻塞了它的會話IDSELECT spid,blocked #monitorlock FROM sys.sysprocesses where blocked>0 and    waittime>2000 -- 通過while或遊標來一行行擷取暫存資料表的 會話ID,阻塞ID，通過exec動態執行來擷取sql語句文本 進行儲存exec(‘DBCC INPUTBUFFER(‘+@spid+‘)‘) 

exec(‘DBCC INPUTBUFFER(‘[email protected]+‘)‘) 

 

sql server 效能調優 資源等待之 LCk