在生产环境下,有时公司客服反映网页半天打不到,除了在浏览器按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负荷过重没有及时处理用户的并发请求
到此这篇关于sql server查询语句阻塞优化性能的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持<计算机技术网(www.ctvol.com)!!>。
需要了解更多数据库技术:sql server查询语句阻塞优化性能,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/1086897.html