在生产环境下，有时公司客服反映网页半天打不到，除了在浏览器按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 

SELECT 
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;

上面查询只显示有阻塞的会话， 关注blocking_session_id 也就是被阻塞的会话ID，同样使用DBCC INPUTBUFFER来查询sql语句

三.阻塞查询 sys.sysprocesses

SELECT 
 spid,
 kpid,
 blocked,
 waittime AS 'waitms', 
 lastwaittype, 
 DB_NAME(dbid)AS DB,  
 waitresource, 
 open_tran,
 hostname,[program_name],
 hostprocess,loginame,
 [status]
 FROM sys.sysprocesses WITH(NOLOCK) 
 WHERE    kpid>0  AND  [status]<>'sleeping'  AND spid>50
 AND spid<>@@SPID

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负荷过重没有及时处理用户的并发请求

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：mmqy2019@163.com进行举报，并提供相关证据，查实之后，将立刻删除涉嫌侵权内容。

猜你喜欢

• sql存储过程有何好处？怎样创建和使用？

  这篇文章给大家分享的是有关sql存储过程的内容，对于存储过程的使用在SQL中是比较重要的，为帮助大家学习和理解存储过程，下文介绍了存储过程的定义、好处、语法、参数和使用等等，感兴趣的朋友可以看看。

• SQL中RAND函数有什么作用，怎样使用的

  SQL 有一个 RAND 函数，用于产生 0 至 1 之间的随机数，有不少朋友对于RAND 函数的使用比较感兴趣，下面小编给大家整理和分享了相关知识和资料，易于大家学习和理解，有需要的朋友可以借鉴参考，下面我们一起来了解一下吧。

• SQL Serve基础编程及常见操作是什么

  这篇文章主要为大家介绍了SQL Server 数据库基础编程，具有一定的参考价值，感兴趣的小伙伴们可以参考一下，希望能够给你带来帮助

• SQL Server中字符串拆分的SQL语句是什么

  这篇文章主要介绍了SQL Server实现将特定字符串拆分并进行插入操作的方法,涉及SQL Server的循环、遍历、判定及插入等相关操作技巧,需要的朋友可以参考下

• SQL alter table命令用于做什么，怎样使用

  SQL ALTER TABLE 命令用于添加、删除或者更改现有数据表中的列。你还可以用 ALTER TABLE 命令来添加或者删除现有数据表上的约束。有不少朋友对于比较感兴趣，下面小编给大家整理和分享了相关知识和资料，易于大家学习和理解，有需要的朋友可以借鉴参考，下面我们一起来了解一下吧。