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

 一.  概述

　　這次介紹實例級別資源等待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_ms
from sys.dm_os_wait_stats
where 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：正在等待獲取架構修改鎖　如添加列或刪除列 這個時候使用的架構修改鎖。

      下麵表格是統計分析

　　註意： 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秒 以及誰阻塞了它的會話ID
SELECT spid,blocked #monitorlock FROM sys.sysprocesses 
where blocked>0 and    waittime>2000 

-- 通過while或游標來一行行獲取臨時表的 會話ID,阻塞ID，通過exec動態執行來獲取sql語句文本 進行存儲
exec('DBCC INPUTBUFFER('+@spid+')') 

exec('DBCC INPUTBUFFER('+@blocked+')')