【学习】sql server常见分析脚本

环境

sql server 2008 R2 sp3
再一次事务代码执行后出现了阻塞，使用profile（不熟悉）发现了lock timeout，反查代码发现一个异常导致没有commit。

辅助函数

sp_who_lock

来源见下面

CREATE  PROCEDURE [dbo].[sp_who_lock]
AS
    BEGIN

        DECLARE @spid INT ,
            @bl INT ,
            @intTransactionCountOnEntry INT ,
            @intRowcount INT ,
            @intCountProperties INT ,
            @intCounter INT,
            @sql_handle VARBINARY(64)

        DECLARE @tmp_lock_who TABLE 
            (
              id INT IDENTITY(1, 1) ,
              spid SMALLINT ,
              bl SMALLINT,
              sql_handle VARBINARY(64)
            )
 
        IF @@ERROR <> 0
            RETURN @@ERROR
        ;
        WITH tb_blocked AS(
            SELECT spid, blocked, sql_handle FROM master..sysprocesses WHERE blocked > 0
        )
        INSERT  INTO @tmp_lock_who
                ( spid ,
                  bl, sql_handle
                )
        SELECT DISTINCT  blocked,0, p_bl.sql_handle
        FROM    tb_blocked
            CROSS APPLY (SELECT p_bl.sql_handle FROM master..sysprocesses p_bl WHERE p_bl.spid = tb_blocked.blocked) p_bl
        WHERE   NOT EXISTS ( SELECT *
                                FROM   tb_blocked a
                                WHERE  tb_blocked.blocked = a.spid )
        UNION ALL
        SELECT spid, blocked, sql_handle FROM tb_blocked

        IF @@ERROR <> 0
            RETURN @@ERROR
 
-- 找到临时表的记录数
        SELECT  @intCountProperties = COUNT(*),
                @intCounter = 1
        FROM    @tmp_lock_who
 
        IF @@ERROR <> 0
            RETURN @@ERROR
 
        IF @intCountProperties = 0
            SELECT  '现在没有阻塞和死锁信息' AS message
-- 循环开始
        WHILE @intCounter <= @intCountProperties
            BEGIN
-- 取第一条记录
                SELECT  @spid = spid, @bl = bl,    @sql_handle = sql_handle
                FROM    @tmp_lock_who
                WHERE   id = @intCounter
                BEGIN
                    IF @bl = 0
                    BEGIN
                        SELECT '阻塞根源' + CAST(@spid AS VARCHAR(10)) AS [description], text AS [sql_text] FROM sys.dm_exec_sql_text(@sql_handle) AS dest
                    END
                    ELSE
                    BEGIN
                        SELECT CAST(@spid AS VARCHAR(10)) + '被' + CAST(@bl AS VARCHAR(10)) + '阻塞' AS [description], text AS [sql_text] FROM sys.dm_exec_sql_text(@sql_handle) AS dest
                    END
                    DBCC INPUTBUFFER(@spid)
                END
-- 循环指针下移
                SET @intCounter = @intCounter + 1
            END

        RETURN 0
    END
GO

使用：exec xxx.dbo.sp_who_lock;

查看spid和表关系

SELECT
	request_session_id spid,
	OBJECT_NAME(
		resource_associated_entity_id
	) tableName
FROM
	sys.dm_tran_locks
WHERE
	resource_type = 'OBJECT'

修改阻塞信息

exec sp_configure 'show advanced options',1;
reconfigure with override
 
exec sp_configure 'blocked process threshold (s)',4
reconfigure with override

查看进程信息

SELECT * FROM sys.dm_exec_connections;
SELECT * FROM sys.dm_exec_sessions;
SELECT
	spid,
	blocked,
	DB_NAME(sp.dbid) AS DBName,
	program_name,
	waitresource,
	lastwaittype,
	sp.loginame,
	sp.hostname,
	a.[Text] AS [TextData],
	SUBSTRING (
		A. TEXT,
		sp.stmt_start / 2,
		(
			CASE WHEN sp.stmt_end = - 1 THEN DATALENGTH (A. TEXT) ELSE sp.stmt_end END - sp.stmt_start
		) / 2
	) AS [current_cmd] FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A WHERE spid > 50 ORDER BY blocked DESC,
	DB_NAME(sp.dbid) ASC,
	a.[text];

参考

1. 官方sp_lock函数说明；
2. SQLSERVER——查看阻塞信息（sp_who_lock优化无误版）
3. 一个特殊的SQL Server阻塞案例分析
4. 第十六章——处理锁、阻塞和死锁（3）——使用SQLServer Profiler侦测死锁
5. SqlServer 查看死锁的进程