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

mofdan

一、使用工具观察与分析数据库中锁信息
      对于并发系统、对于有大量读写数据库操作的业务系统等，当多人同时访问数据库时，最复杂的情况之一就是大量的事务与资源纠缠在一起，相互被锁而彼此等待，也称为死锁。当数据库中出现很多很多锁时，系统瞬间就无法提供正常服务。此时观察系统资源的使用情况，会发现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