能安静写代码是一件很幸福的事。

但是出现哪些让人抓狂的问题，比如数据库操作缓慢，快速定位问题成为了重中之中，最近就遇到这个问题，在网上看到一篇文章，可以快速排查阻塞情况，在此分享给大家。

创建检测存储过程

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

验证阶段

分别打开三个查询窗口

窗口一执行如下语句

UPDATE Cloud_Message SET UserId=199999 WHERE id=1

窗口二执行如下语句

SELECT * FROM dbo.Cloud_Message WHERE id=1

窗口三执行新建的存储过程

EXEC sp_who_lock

总结

排查数据库执行缓慢的方法有很多，比如通过各种的检测工具查看数据库会话链接情况，语句执行情况等，这里暂且介绍一种，感兴趣的可以翻阅资料，同时也欢迎交流。

如果觉得文章对你有帮助，请点击关注，后续会有更多干活分享

参考链接：