[转载]SQL SERVER 查询性能优化——分析事务与锁（一） - DotNet菜园 - 博客园

一、使用工具观察与分析数据库中锁信息

      对于并发系统、对于有大量读写数据库操作的业务系统等，当多人同时访问数据库时，最复杂的情况之一就是大量的事务与资源纠缠在一起，相互被锁而彼此等待， 也称为死锁。当数据库中出现很多很多锁时，系统瞬间就无法提供正常服务。此时观察系统资源的使用情况，会发现CPU使用率不高，内存占用量也不高，还有很多未使用的内存，网络带宽也充足，硬盘也不繁忙，通过数据库管理工具查询的话，SQL SERVER中的数据也正常无误，但是使用系统的用户访问此数据库时却要需要等很多久很久，更多的就出现连接超时，数据库无响应。

      此现象类似高速公路上有大量的车辆进入，而在收费口却只开了一个、二个。造成收费口的车辆拥堵，而后方却又有大量的车辆涌入，这时所有的车辆都会慢下来，驾驶员彼此再减速刹车互相等待，最后导致后方的车子会被迫停下来，形成高速公路停车场。（就如第一次节假日高速公路免费通行那天，即2012年国庆高速公路大堵车的情形，高速公路成停车场）

      如果数据库中在设计时，设计不当，设计有过热数据表时，也就是所有应用程序的访问行为都跟某张数据表有关，甚至集中访问某些字段，这就像前述例子中的所有 车辆都在同一地点（高速公路收费口）驶上高速公路。这时耗时与快速的两种访问行为混合在一起，就像大货车与小轿车一起争车道，很容易发生彼此锁定，形成死 锁而动弹不得的情况。

      此类性能延迟现象，尤其在数据使用一段较长时间之后容易发生，当新系统刚上线时，或SQL语句写的不好，如Select * from SaleInfo 且没有where条件，初期因数据量不大，可以瞬间取回结果而不影响别人。但当系统使用一段时间之后，数据库中的数量增加，使用人数增多，使用方式改变。不好的SQL语句就出现运行迟缓，这时数据库开始积聚等待处理的需求，也就是彼此干扰而造成锁定，交互作用后，可能瞬间瘫痪整个系统。当你观察当前数据库的锁定时，可能会发现存在几千个锁定，很难理出头绪。

       本文首先来讨论可以使用哪些工具来观察与分析SQL SERVER中的锁信息。当然，所有的性能调校都需要先了解情况，最好是可重复发生的情景，例如在每天什么时间，多少用户上线后，执行了什么样的功能后，等等。先通盘了解情况，然后评估SQL SERVER的问题，再进一步深入观察SQL SERVER。

第一步，使用SQL SERVER提供图形界面工具观察哪些进程被加锁。

 1 ) SQL SERVER 2005可以通过Microsoft SQL Server Management Studio的“管理–》活动监视器”—》“右键”—》“进程信息”，这里呈现连接、锁定的各种细节。如下图1。

 

                                                                             图1

2) SQL SERVER 2005 的Microsoft SQL Server Management Studio提供图形界面工具中的信息不会自动更新。刷新“活动监视器”界面中的内容，需要手动使用鼠标点击上方工具栏上的“刷新”按钮。如下图2，是未点击“刷新”按钮之前的显示的内容，图3，是点击“刷新”按钮之后的，显示的内容。

　　　　　　　　图2

　　　　　　　　图3

2） SQL SERVER 2005中可以通过下图中的设置，设置成自动更新的时间周期。如下图4。

 

 

                               图4

4） SQL SERVER 2005的Microsoft SQL Server Management Studio中的“活动监视器—》进程信息”提供相当多的信息，其中“等待类型”与“阻塞者”可呈现进程彼此的关系及进程的状态信息。

如果要查询“锁”的细节信息，则可以点击右键窗口中的“活动监视器—》按进程分类的锁”及“活动监视器—》按对象分类的锁”两个标记进行观察。

活动监视器—》按进程分类的锁

如下图5。

 

                                               图5

活动监视器—》按对象分类的锁

如下图6。

 

                                                图6

 

接下来说一下SQL SERVER 2008中的活动监视器的使用：

1) SQL SERVER 2008的活动监视器就不在管理中了。一、点击工具栏上的图标。二、使用右键点击数据库服务器，如下图7。然后都会出现如图8的图形界面工具。

 

 

                图7

 

                       图8

 

2） SQL SERVER 2008中默认就是自动更新的，自动更新时间间隔为10秒。可以通过下图中的设置，设置成自动更新的时间周期。如下图9。右键单击“概述”，选择“刷新间隔”，然后选择活动监视器获取新的实例信息所用的间隔。

 

 

          图9

 

3） SQL SERVER 2008的Microsoft SQL Server Management Studio中的“活动监视器–》进程”只提供了一些基本的信息，其中“等待类型”与“阻塞者”可呈现进程彼此的关系及进程的状态信息。所提供的锁定进程的信息没有SQL SERVER 2005中所提供的详细。

                                      图10