开发者社区> 杰克.陈> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

sql server 阻塞查询

简介: 原文:sql server 阻塞查询  在生产环境下,有时公司客服反映网页半天打不到,除了在浏览器按F12的Network响应来排查,确定web服务器无故障后。就需要检查数据库是否有出现阻塞 当时数据库的生产环境中主表数据量超过2000w,子表数据量超过1亿,且更新和新增频繁。
+关注继续查看
原文: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 t2
10 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 工作站进程 ID 号。
  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负荷过重没有及时处理用户的并发请求

             

 

   

      

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
基于4.5Framework web程序、SQLSERVER数据库打包
原文:基于4.5Framework web程序、SQLSERVER数据库打包   估计很多朋友和我一样,对于C/S程序打包很熟悉,但对于B/S程序打包一头雾水。。。   最近公司要求我们把项目和数据库(SQLSERVER)一起打包,然后安装在CD光盘上,打算拿光盘去客户那边实现一键安装。
1081 0
SQLServer 2005 数据库定阅复制实现双机热备(主要是sharepoint 内容数据库)
原文:SQLServer 2005 数据库定阅复制实现双机热备(主要是sharepoint 内容数据库) 场景   公司最近的sharepoint的数据库服务器老是出问题,并且在一旦出现问题,就导致无法正常工作。
1511 0
sqlserver:同一数据库内负责表结构。
select * into 新表 from 旧表   where 1=2; ## where 表示要携带的数据,随便写个恒不等式就可以只复制表结构不携带数据。
466 0
+关注
杰克.陈
一个安静的程序猿~
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Server 2017
立即下载
PostgresChina2018_董红禹_SQL_Server迁移PG经验分享
立即下载
SQL Server在电子商务中的应用与实践
立即下载