SQL Server阻塞的檢查_ZenDei技術網路在線

1. 阻塞除了記憶體、CPU、I/O這些系統資源以外，阻塞和死鎖是影響資料庫應用性能的另一大因素。所謂的「阻塞」，是指當一個資料庫會話中的事務，正在鎖定其他會話事務想要讀取或修改的資源，造成這些會話發出的請求進入等待的狀態。SQL Server 預設會讓被阻塞的請求無限期地一直等待，直到原來的事務 ...

1. 阻塞

除了記憶體、CPU、I/O這些系統資源以外，阻塞和死鎖是影響資料庫應用性能的另一大因素。

所謂的「阻塞」，是指當一個資料庫會話中的事務，正在鎖定其他會話事務想要讀取或修改的資源，造成這些會話發出的請求進入等待的狀態。SQL Server 預設會讓被阻塞的請求無限期地一直等待，直到原來的事務釋放相關的鎖，或直到它超時、伺服器關閉、進程被殺死。一般的系統中，偶爾有短時間的阻塞是正常且合理的；但若設計不良的程式，就可能導致長時間的阻塞，這樣就不必要地鎖定了資源，而且阻塞了其他會話欲讀取或更新的需求。遇到這種情況，可能就需要手工排除阻塞的狀態。

2.阻塞和死鎖可能帶來的問題

（1）併發用戶少的時候，一切還都正常。但是隨著併發用戶的增加，性能越來越慢。

（2）應用程式運行很慢，但是SQL Server 這個CPU和磁碟利用率很低。

（3）客戶端經常受到以下錯誤。

Error 1222--Lock request time out period exceeded.

Error 1205--Your transaction(process ID #XX) was deadlocked on resources with another process and has been chosen as the deadlock victim. Return your transaction.

超時錯誤--Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

（4）有些查詢能夠進行，但是有些特定的查詢或修改總是不能返回。

（5）重啟SQL Server就能解決。但是有可能跑一段時間以後又會出現問題。

3.阻塞的檢查

3.1 主要的系統表或函數

Sys.SysProcesses 系統表是一個很重要的系統視圖，主要用來定位與解決Sql Server的阻塞和死鎖。主要欄位1.Spid：Sql Servr 會話ID 2.Blocked：正在阻塞求情的會話 ID。如果此列為 Null，則標識請求未被阻塞 3. Program_name：應用程式的名稱，就是連接字元串中配的 Application Name 4. Hostname：建立鏈接的客戶端工作站的名稱。

sys.dm_exec_requests、sys.dm_exec_sql_text返回指定SPIDer的 SQL 查詢文本。

DBCC INPUTBUFFER 顯示從客戶端發送到 Microsoft SQL Server 實例的最後一個語句。

sp_lock 系統存儲過程，報告有關鎖的信息。

3.2 Check邏輯

對應的存儲為dblockcheck（job為DB_Lockcheck），主要Check邏輯如下：

3.3 保存的數據

所收集的數據保存dblock_information中,主要包含信息如截圖,定期的統計分析可獲得經常被阻塞和引起阻塞SQL語句和Table，這些信息是進行資料庫優化的一個角度。

select top 100* from dblock_information
order by TransDateTime desc

4.代碼實現

4.1 Table的創建腳本

CREATE TABLE [dbo].[dblock_information](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Message] [nvarchar](300) NULL,
    [LockType] [char](1) NULL,
    [SPID1] [int] NULL,
    [SPID2] [int] NULL,
    [EventType] [nvarchar](100) NULL,
    [Parameters] [nvarchar](10) NULL,
    [EventInfo] [nvarchar](3000) NULL,
    [IndividualQuery] [nvarchar](1000) NULL,
    [TransDateTime] [datetime] NULL CONSTRAINT [DF_dblock_information_TransDateTime]  DEFAULT (getdate()),
    [AppName] [varchar](50) NULL,
    [HostName] [varchar](50) NULL,
 CONSTRAINT [PK_dblock_information] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

4.2 存儲的創建腳本

Create procedure [dbo].[dblockcheck] 
     @AlarmThreshold as tinyint=10
 as
set nocount on
--------------------------------------------------------------------------------------------------------
--*Program*: <dblock check for job>
--*Programer*:<>
--*Date*:<>
--*Description*:<Query SQL Locking Process>
--*Unify*:<UA>
--########## Parameter Description Begin ##########

--########## Parameter Description End # ##########

--##########Update Log Begin ###################
--##########Update Log End # ###################
--------------------------------------------------------------------------------------------------------
declare @SQL as varchar(200)
declare @Subject as varchar(200)
declare @Body as nvarchar(max)
declare @SPName as nvarchar(max)
declare @Message as nvarchar(200)
declare @DBname varchar(15)
declare @IP varchar(20)
declare @CNT as int
declare @cnt2 int
declare @IndividualQuery nvarchar(1000)
declare @HostName varchar(50)
declare @AppName varchar(50)
SET @DBname=DB_NAME()

SELECT @IP='XXX.XXX.XXX.XXX'
----不手動定義IP也可通過以下函數來實現
Declare @ServerIP NVARCHAR(30)='', @SERVERNAME NVARCHAR(60)='' 
    SELECT top 1 @SERVERNAME = @@SERVERNAME ,@ServerIP=LOCAL_NET_ADDRESS
    FROM SYS.DM_EXEC_CONNECTIONS where LOCAL_NET_ADDRESS is not null
--------
begin
    declare @spid int,@bl int,
    @intTransactionCountOnEntry int,
    @intRowcount int,
    @intCountProperties int,
    @intCounter int

create table #tmp_lock_who (
    id int identity(1,1),
    spid smallint,
    bl smallint)

create table #tmp_lock_information (
    id int identity(1,1),
    Message nvarchar(200),
    LockType char(1),
    SPID1 int,
    SPID2 int,
    EventType nvarchar(100),
    Parameters nvarchar(10),
    EventInfo nvarchar(max),
    IndividualQuery nvarchar(1000),
    AppName varchar(50),
    HostName varchar(50)
    )

IF @@ERROR<>0 RETURN @@ERROR
    insert into #tmp_lock_who(spid,bl) 
    select 0 ,blocked
        from (select * from master..sysprocesses where blocked>0 ) a
        where not exists(select * from (select * from master..sysprocesses where blocked>0 ) b
    where a.blocked=spid)
    union 
    select spid,blocked from master..sysprocesses where blocked>0
    IF @@ERROR<>0 RETURN @@ERROR
    -- 找到臨時表的記錄數
        select @intCountProperties = Count(*),@intCounter = 1
            from #tmp_lock_who
    IF @@ERROR<>0 RETURN @@ERROR
        if @intCountProperties=0
            select N'現在沒有阻塞信息!' as message
            -- 迴圈開始
                while @intCounter <= @intCountProperties
                    begin
                    -- 取第一條記錄
                        select @spid = spid,@bl = bl
                            from #tmp_lock_who where Id = @intCounter
                        begin
                            SELECT @IndividualQuery= SUBSTRING (qr.text,qs.statement_start_offset/2, 
                                     (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qr.text)) * 2 
                                           ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
                            FROM SYS.DM_EXEC_REQUESTS qs OUTER APPLY SYS.DM_EXEC_SQL_TEXT(qs.sql_handle) AS qr
                            WHERE qr.text is not null and qs.session_id=@bl
                            select @HostName=left(HostName,50),@AppName=Left(Program_Name,50) 
                                from master..sysprocesses With(nolock) Where SPID=@bl
                            set @SQL='DBCC INPUTBUFFER ('+cast(@bl as char(20))+')'
                            if @spid =0
                                begin
                                    select @Message=N'引起資料庫阻塞的是: '+ CAST(@bl AS NVARCHAR(100)) + N'進程號,其執行的SQL語法如下'
                                    --set @SQL='DBCC INPUTBUFFER ('+cast(@bl as char(20))+')'
                                    insert into #tmp_lock_information(EventType,Parameters,EventInfo) exec(@sql)
                                    update #tmp_lock_information set LockType='1',SPID1=@bl,SPID2=@bl,IndividualQuery=@IndividualQuery,[Message]=@Message,AppName=@APPName,HostName=@HostName where [Message] is null
                                end
                            else
                                begin
                                    select @Message=N'進程號SPID：'+ CAST(@spid AS NVARCHAR(100))+ N'被' + N'進程號SPID：'+ CAST(@bl AS NVARCHAR(10)) +N'阻塞,其當前進程執行的SQL語法如下'
                                    insert into #tmp_lock_information(EventType,Parameters,EventInfo) exec(@sql)
                                    update #tmp_lock_information set LockType='2', SPID1=@spid,SPID2=@bl,IndividualQuery=@IndividualQuery,[Message]=@Message,AppName=@APPName,HostName=@HostName where [Message] is null
                                end 
                        end
                        -- 迴圈指針下移
                        set @intCounter = @intCounter + 1
                    end
            drop table #tmp_lock_who
            if exists(select 0 from #tmp_lock_information)
            Begin

                    Insert into dblock_information(Message,LockType,SPID1,SPID2,EventType,Parameters,EventInfo,IndividualQuery,AppName,HostName) 
                    Select [Message],LockType,SPID1,SPID2,EventType,Parameters,Substring(EventInfo,1,500),IndividualQuery,AppName,HostName from #tmp_lock_information

            End

            drop table #tmp_lock_information
            return 0
end