在分析不同形式的语句执行对申请锁行为的影响之前,管理员要先了解怎么去监视一个连接当前持有的锁,以及怎么去监视一个语句的执行过程中,SQL Server对锁的申请和释放行为。
检查一个连接当前锁持有的锁
通常可以使用sp_lock这句命令来列出当前SQL Server里所有的连接持有的锁的内容。
我们也可以查询sys.dm_tran_locks这张系统动态管理视图来实现。
SELECT request_session_id, resource_type, resource_associated_entity_id,
request_status, request_mode, resource_description
FROM sys.dm_tran_locks
当然也可以结合其他的动态管理视图,直接查出某个数据库上面的锁是在哪些表格,以及哪些索引上面。
USE AdventureWorks
GO
SELECT request_session_id, resource_type, resource_associated_entity_id,
request_status, request_mode, resource_description, p.object_id,
OBJECT_NAME(p.object_id) AS object_name, p.*
FROM sys.dm_tran_locks LEFT JOIN sys.partitions p
ON sys.dm_tran_locks.resource_associated_entity_id = p.hobt_id
WHERE resource_database_id = DB_ID('AdventureWorks')
ORDER BY request_session_id, resource_type, resource_associated_entity_id
注意:必须在表格所属的那个数据库下运行。否则从object_id得不到正确的object_name。
监视语句执行过程中SQL对锁的申请和释放行为
有很多锁是在语句运行的过程中申请和释放的,语句运行结束之后,这些锁就会消失。如果这些锁申请不到,也会产生阻塞。那么怎么看一个语句执行过程中锁的申请和释放过程呢?
管理员必须借助SQLServer Profiler。在定义一个trace(跟踪)的时候,需要选取下面的Events(事件):Lock:Accquired,Lock:Released。
请注意要选上Show allcolumns(显示所有列),再选择Lock:Accquired和Lock:Released。
通过这种方式,就能看到一个语句在执行过程中锁的完整申请和释放过程。需要提醒的是,SQLServer锁的申请和释放是个非常复杂的行为,同样一句话在不同情况下执行(例如是否需要编译等),申请的锁都会不一样。有时候锁申请的数目可能大大超过你的想象。所以这种跟踪方式只能在测试环境里,针对特定的语句进行。如果在生产环境里这么做,会产生大量的跟踪记录,影响SQLServer的性能,是个危险的行为。
一般来说,用sys.dm_tran_locks或sp_lock的结果就能够做绝大部分的阻塞分析,所以本章也主要使用这种方法。