SQL SERVER 查询性能优化——分析事务与锁（三）

上接SQL SERVER 查询性能优化——分析事务与锁（二）

 

接下来看看SP_WHO2这个系统存储过程，如果你查询这个系统存储过程的源代码，就可以发现这个系统存储过程是整理master.sys.sysprocesses系统视图中的内容。在此用sp_who2来说明一下。

第一步，在查询分析器中执行例二，例三代码。(就是上一篇文章SQL SERVER 查询性能优化——分析事务与锁（二）中的示例)--例二

第二步，再打开一个查询分析器界面，在此界面中输入exec sp_who2，如下图，在此界面中你可以很容易的观察到锁与被锁的关联，看到进程“56”被“53”锁住。

Use test

Go

Begin tran

update book set Name='MS SQL 2008'

where bookid=1

---切换到另一个查询界面，执行以下代码

--例三

Use test

Go

select * from Book where bookid=1

go

 

 

 

 

 

你可以通过dbcc inputbuffer(53)来查看进程“53”所执行的查询语句。如下图1、2。

 

Sql 2008中的 wbk_pde_list表

 

 

图1

Book表

 

图2

 

当然，如果你使用SQL SERVER 2005也可以通过Microsoft SQL Server Management Studio中的“活动监视器--》进程信息”直接以鼠标双击某条进程，便可以看到此进程所执行的查询语句。如下图3。

 

 图3

你还可以通过sp_lock系统存储过程来观察进程“53”和“56”的结果。执行如下命令

Exec sp_lock 53

Exec sp_lock 56

然后得到如下图结果：

Book表

 

图4

 

以上语句执行结果，同SQL SERVER 2005中的Microsoft SQL Server Management Studio中的“活动监视器--》按进程分类的锁”有异曲同工之处。

Sql 2005

 

 图5

当然在Sql 2008中就只能执行以下的SQL 语句了。

Exec sp_lock 54

Exec sp_lock 55

 

图6

 

如上，图6中的Type字段如果是PAG，则Resource表示的是该分页在数据库的第几个文件上。以及分页编号。我们可以通过DBCC PAGE来观察该分布。

 如果indId为1，则表示为聚集索引，则dbcc page查询出来的是整个分页的细节，如果IndId大于1，则表示为非聚集索引，则dbcc page查询出来的是索引键值与哈希值。如下图7。

Dbcc traceon(3604)

dbcc page(28,1,10683,3)

 

Book

 

图7

 

结合图5对象ID、说明与图7中的KeyHashValue字段相比较，就可以进一步看出什么样的记录被锁住了。

 也可以结合结合图6中的RESOURCE与图7中的KeyHashValue字段相比较，就可以进一步看出什么样的记录被锁住了。

注：此处的图7不是图6的明细。

select db_name(28) 数据库名称,OBJECT_NAME(117575457) 表名

,(select name from sys.indexes where OBJECT_ID=117575457 and index_ID=54) 索引名称

 

 

 

 

另外可以打开 SQL Profiler观察多人交互情况。

 

 

综上所述，你可以从以下几方面来观察数据库是否因为锁与被锁而造成系统运行出现问题。

1.通过Microsoft SQL Server Management Studio或SP_WHO2系统存储过程来观察数据库中是否有许多进程被锁。

2.观察master.sys.sysprocesses系统视图内，被锁进程中的waittime字段的值是否异常的大。

3.SQL Profiler工具所录制的结果中，有许多attention事件，代表SQL语句执行过久没有响应，前端程序放弃执行。

4.SQL SERVER所在服务器并没有显的很忙碌。例如，CPU，内存，硬盘，网络等硬件资源使用率并不是很高，但系统的效率却不高，或是正相反，上述资源由于某个操作而持续高度使用，但是该操作一直做不完，导致它持有的资源都无法释放。

5.通过Microsoft SQL Server Management Studio、性能监视器、SQL PROFILER等结果，进行交叉分析以相互印证。