经常会需要分析SQLSERVER的阻塞情况,尤其是某些SQL操作异常缓慢从而怀疑是有人在搞事情的情况下。网上有许多一模一样的帖子,是关于sp_who_lock这个存储过程的,然而,网上流传的这个是略有问题的(被阻塞的SQL输出有误),为此改造了一下实现,顺便优化了一下输出结构:

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

  关于输出的SQL文本,我使用了sys.dm_exec_sql_text与DBCC INPUTBUFFER两种方式,这两种方式是的结果是略有差别的,在SQL批里有多条SQL语句的情形下,前者可以精确定位到当前阻塞/被阻塞是哪一条语句,然而输出的并非原始的SQL文本,而后者则输出的是原始SQL批,但并不能精确定位是哪一条。两者结合方可更快的排查问题。举例如下:

  假设有如下两个链接的SQL语句:

  链接一:  

BEGIN TRAN
	UPDATE dbo.t_UserDataAccess SET ObjectValue = '' WHERE UserID = 1024

  链接二:

BEGIN TRAN
    UPDATE dbo.t_UserDataAccess SET ObjectValue = '' WHERE UserID = 1023
    SELECT * FROM dbo.t_UserDataAccess AS tuda WHERE UserID = 1024

  在链接一和链接二顺序执行的情形下,很显然,链接2的SELECT语句将会被阻塞,这时来看sys.dm_exec_sql_text和DBCC INPUTBUFFER的不同表现:

应该不需要解释了。

 

posted @ 2018-04-18 17:31 细雨黄昏 阅读(7688) 评论(0) 推荐(0) 编辑
摘要: 近几日生产环境总是偶发的出现数据库连接失败的错误,一开始并未引起重视,因为反馈的人很少,而且应用服务器与数据库服务器都处在同一机房的内网环境,相互之间的访问应该是很稳定的。直到早上有几分钟的时间里出现的概率很大(基本上每10次请求就有一次出错),检查数据库状况是良好的,负载并不高,监控了user c 阅读全文
posted @ 2018-04-13 15:14 细雨黄昏 阅读(291) 评论(0) 推荐(0) 编辑
摘要: 概念了解 IIS Server Farms,实际上应该叫“Microsoft Web Farm Framework (WFF)”,依赖于“Web Platform Installer”才能安装,依赖于WebDeploy组件。可参见 Web Farm Framework Setting up a Se 阅读全文
posted @ 2018-04-04 13:15 细雨黄昏 阅读(5163) 评论(0) 推荐(0) 编辑
摘要: IIS的反向代理是通过ARR模块来完成的,ARR模块需要另外安装,而且只能通过Web PlatForm Installer安装。关于安装来源与步骤,帖子已有很多,不做描述。启用“Application Request Routing”功能以及简单设置URL Rewrite的步骤也不做描述,帖子很多, 阅读全文
posted @ 2018-03-30 12:01 细雨黄昏 阅读(759) 评论(0) 推荐(1) 编辑
摘要: .NET Framework中提供了现成的类库可以很方便的实现对windows服务的安装、卸载、启动、停止、获取运行状态等功能。这些类都在System.ServiceProcess命名空间下。 安装window服务 卸载windows服务 启动windows服务 坑 一切都似乎很简单,略坑的是,Se 阅读全文
posted @ 2018-02-26 10:06 细雨黄昏 阅读(1605) 评论(0) 推荐(0) 编辑
摘要: 有个朋友问到用soapui测试wcf服务时如果使用windows身份验证要怎么传输凭据,于是自己试了一下。其实服务端是wcf还是webservice还是webapi都无所谓,关键是windows身份验证的凭据在哪里设置。手头上恰好有一个使用windows身份验证的站点,在浏览器上访问成功时,大约是这 阅读全文
posted @ 2018-01-29 11:02 细雨黄昏 阅读(3216) 评论(0) 推荐(0) 编辑
摘要: 在之前的帖子里有提到过,为了避免IP变更带来的一系列问题,采取了不改变IP的策略。原以为,只要SQLSERVER的群集IP保持与之前单机部署的IP一致,就基本上不会有问题。然而实际永远比预想的更复杂。迁移并投入正式使用之后,确实大体上一直都稳定运行,然而我总是间隔一段时间就发现某几个JOB会连续失败 阅读全文
posted @ 2017-12-22 10:21 细雨黄昏 阅读(1235) 评论(0) 推荐(0) 编辑
摘要: 不少文章有提到怎样使用WCF RIA Service中的验证功能,但一般都是以建立一个SilverLight Business Application项目作为开始,然而SilverLight Business Application的模板并不是那么符合实际需要,默认添加了一堆的类、方法,在这个基础上去调整,实在恶心,故实验了一下在普通的SilverLight项目中使用。现总结如下: 1、建立一个普通的SilverLight项目,选中“Enable WCF RIA Service”,假设项目SL项目名为MyAuthentication,对应WEB项目为MyAuthentication.Web;. 阅读全文
posted @ 2012-04-03 21:49 细雨黄昏 阅读(623) 评论(0) 推荐(0) 编辑
摘要: 本来反射是跟SilverLight是没有必然联系的,之所以把这一篇随笔也冠以“SilverLight系列”的前缀,是因为这一篇里所讲的内容确实是在做SilverLight开发时才决心要做的。相信涉猎过SilverLight的人都会见过类似于如下的XAML代码: <TextBox Text=”{Binding Path=[0].Name}” /> 记得一开始看到"[0].Name”这种表达式时就觉得SilverLight里的绑定真是强大,而且也预感在接下来的SL的开发中,如果能使用类似于GetPropertyValue(“[0].Name”)这种方法的话是能够带来多大的方便 阅读全文
posted @ 2011-08-16 21:00 细雨黄昏 阅读(851) 评论(2) 推荐(2) 编辑
摘要: 其实关于动态装载XAP包,网上已有不少资料,且都附有代码,我当初也是参考网上找到的代码改的,基本原理就是通过WebClient请求XAP文件流,然后分析流,加载程序集,最后通过反射创建实例。细节方面,直接看代码就可以很清楚了: /// <summary> /// XAP包加载器 /// </summary> public class XapLoader { /// <summary> /// XAP包路径 /// </summary> private string _xapUri; public XapLoader(string xapUri) 阅读全文
posted @ 2011-08-15 22:50 细雨黄昏 阅读(1160) 评论(1) 推荐(1) 编辑
点击右上角即可分享
微信分享提示