sql server 阻塞查詢

標籤：就是   NPU   spi   nbsp   來講   database   processes   運行   sql   

原文:sql server 阻塞查詢

 在生產環境下，有時公司客服反映網頁半天打不到，除了在瀏覽器按F12的Network響應來排查，確定web伺服器無故障後。就需要檢查資料庫是否有出現阻塞

當時資料庫的生產環境中主表資料量超過2000w,子表資料量超過1億，且更新和新增頻繁。再加上做了同步鏡像，很消耗資源。

這時就要建立一個會話，大概需要瞭解以下幾點：

1.當前活動會話量有多少？

2.會話已耗用時間？

3.會話之間有沒有阻塞？

4.阻塞時間 ?

查詢阻塞的方法有很多。有sql 2000 的sp_lock, 有sql 2005及以上的dmv

一. 阻塞查詢 sp_lock

      執行 exec sp_lock  下面列下關鍵字段

      spid 是指進程ID，這個過濾掉了系統進程，只展示了使用者進程spid>50。

      dbid 指當前執行個體下的哪個資料庫 , 使用DB_NAME() 函數來標識資料庫

      type 請求鎖住的模式

      mode 鎖的請求狀態

                     GRANT：已擷取鎖。

                     CNVRT：鎖正在從另一種模式進行轉換，但是轉換被另一個持有鎖（模式相衝突）的進程阻塞。
                     WAIT：鎖被另一個持有鎖（模式相衝突）的進程阻塞。

     總結：當mode 不為GRANT狀態時， 需要瞭解當前鎖的模式，以及通過進程ID尋找當前sql 語句 

                例如當前進程ID是416，且mode狀態為WAIT 時，查看方式 DBCC INPUTBUFFER(416)

               用sp_lock查詢顯示的資訊量很少，也很難看出誰被誰阻塞。所以當資料庫版本為2005及以上時不建議使用。

 二.阻塞查詢  dm_tran_locks         

 1 SELECT  2 t1.resource_type, 3 t1.resource_database_id, 4 t1.resource_associated_entity_id, 5 t1.request_mode, 6 t1.request_session_id, 7 t2.blocking_session_id 8 FROM sys.dm_tran_locks as t1 9 INNER JOIN sys.dm_os_waiting_tasks as t210 ON t1.lock_owner_address = t2.resource_address;

     上面查詢只顯示有阻塞的會話， 關注blocking_session_id 也就是被阻塞的會話ID，同樣使用DBCC INPUTBUFFER來查詢sql語句

三.阻塞查詢 sys.sysprocesses

 1 SELECT  2 spid, 3 kpid, 4 blocked, 5 waittime AS ‘waitms‘,  6 lastwaittype,  7 DB_NAME(dbid)AS DB,   8 waitresource,  9 open_tran,10 hostname,[program_name],11 hostprocess,loginame,12 [status]13 FROM sys.sysprocesses WITH(NOLOCK) 14 WHERE    kpid>0  AND  [status]<>‘sleeping‘  AND spid>50

  sys.sysprocesses  能顯示會話進程有多少， 等待時間， open_tran有多少事務， 阻塞會話是多少. 整體內容更為詳細。
  關鍵字段說明:

       spid 會話ID（進程ID），SQL內部對一個串連的編號，一般來講小於50

　　kipid 線程ID
　　blocked: 阻塞的進程ID， 值大於0表示阻塞, 值為本身進程ID表示io操作
　　waittime:當前等待時間（以毫秒為單位）。
　　open_tran: 進程的開啟事務數
　　hostname:建立串連的用戶端工作站的名稱
　　program_name 應用程式的名稱。
　　hostprocess 工作站進程 識別碼。
　　loginame 登入名稱。
　　[status]
　　　　running = 會話正在運行一個或多個批
　　　　background = 會話正在運行一個背景工作，例如死結檢測
　　　　rollback = 會話具有正在處理的交易回復
　　　　pending = 會話正在等待背景工作執行緒變為可用
　　　　runnable = 會話中的任務在等待,由scheduler來啟動並執行可執行隊列中。(重要)
　　　　spinloop = 會話中的任務正在等待調節鎖變為可用。
　　　　suspended = 會話正在等待事件（如 I/O）完成。(重要)
　　　　sleeping = 串連空閑

              wait resource 格式為 fileid:pagenumber:rid 如(5:1:8235440)

              kpid=0, waittime=0 空閑串連

              kpid>0, waittime=0 運行狀態
              kpid>0, waittime>0 需要等待某個資源，才能繼續執行,一般會是suspended（等待io）
              kpid=0, waittime=0 但它還是阻塞的源頭，查看open_tran>0 事務沒有及時提交。

              如果blocked>0，但waittime時間很短，說明阻塞時間不長，不嚴重
              如果status 上有好幾個runnable狀態任務，需要認真對待。 cpu負荷過重沒有及時處理使用者的並發請求

             

 

   

      

 

sql server 阻塞查詢