影响SQL Server数据库应用性能的几个常见因素 (2012/1/18)

影响SQL Server数据库应用性能的几个常见因素

性能问题是困扰数据库用户的常见问题之一。经常会有人因为遇到性能问题，质疑SQL Server处理大型数据应用的能力。其实，作为一个在市场上经营了二十多年，出了好几代版本的数据库产品，SQL Server作为一个企业级数据库的能力，是毋庸置疑的。在实际应用中，数据量达到几百GB，甚至上TB级，并发连接数超过1、2千个，每秒钟处理的请求数量超过1000多个的SQL Server，现在已经很多了。在国内的一些大客户那里，我们也越来越多地看到这样的SQL Server。

那为什么有些SQL Server能跑得那么强劲，而很多用户的数据库还只有几十GB，就感觉跑不动了呢？在谈性能问题的常见原因之前，我们先谈谈SQL Server的几个和性能有关的重要特性。了解这些特性，对设计一个高效的数据库应用，是非常必要的。

1.      SQL Server要访问的数据，是一定要缓冲在内存里的

不管是要查询的数据，还是要修改的数据，SQL Server在运行客户端发过来的语句，处理这些数据之前，都要检查其访问的数据是否在内存中。如果不在内存中，SQL Server会先把存储数据的页面从磁盘调入内存，然后再做真正的数据处理。

数据处理完毕后，SQL Server不会马上把这段数据缓存丢弃。只要SQL Server不缺内存，先前访问过数据页面就会一直缓存在SQL 
Server进程的地址空间里。这样，如果下次有其他用户要访问同样的数据记录，SQL
Server就可以马上在内存中进行处理，而不需要再到磁盘上去找。

这样的设计，可以最大程度的重用内存，提高SQL Server的处理速度。同时也决定了，SQL Server是一个非常喜欢大内存的应用程序。

2.      如果SQL Server没有空闲的内存，而用户需要访问新的数据，SQL会把以前缓存的数据挑选一部分从内存中清除，腾出空间来缓存新的数据

SQL Server不会无限制地申请内存。它会根据用户的设置，以及系统的内存数量，计算自己的最大内存数。如果当前的内存大小以及达到了这个上限，SQL就不会再向Windows申请更多的内存。这样的机理，可以保证SQL Server和Windows，以及运行在同一台机器上的其他应用程序和平共处。

当SQL Server的内存数已经达到最大值，内存空间已经缓存满各种各样的数据页面，而用户又要访问新的、还没有缓存在内存里的数据时，SQL
Server会根据现有数据访问的频度，把最老的、最不经常被人访问的数据，从内存里清除，从而腾出空间来缓存现在客户要访问的新数据。

所以最理想的状态，是用户要访问的数据永远都缓存在内存里，SQL Server从来都不需要到磁盘上去找。这也是数据库性能最佳的情况。这时候SQL Server几乎从来不需要做磁盘读。

如果经常发生用户要访问的数据不在内存里的情况，SQL Server就会被迫不停地在内存和磁盘之间倒腾数据，性能会受到严重的影响。而这时候，你会看到SQL在经常地作磁盘读的动作。

3.      表格中数据的组织与访问，和聚集索引的选择密切相关

SQL Server的一个显著的特点，是表格里数据的存储，是按照聚集索引所在字段的值排序的。而非聚集索引是建立在聚集索引结构之上的。如果一张表格没有聚集索引，数据是按照堆的方式存储，没有任何顺序。

对于同样的数据量，SQL Server对一个有聚集索引的表格的管理，远远比没有聚集索引的表格要有效。绝大多数情况下，一张大表如果想要有良好的性能，就必须有一个合适的聚集索引。没有聚集索引，只加非聚集索引，也不能够达到优化的性能。

这是SQL Server的一个很重要的特点。

4.      在缺省的事务隔离级别下，同一条记录上的读操作和写操作是互斥的

SQL Server实现的是ANSI 标准的四个隔离级别。在Read Committed这个缺省的隔离级别上，读操作会申请S锁，修改操作会申请X锁，S锁和X锁互斥。所以同一条记录上不能同时进行读操作和写操作。

业界的有些其他数据库产品，缺省使用行版本控制方式实现事务隔离，如果一个用户在修改某一条记录、但是没有提交事务，而另一个用户要读同一条记录，它会让第二个用户读到第一个人修改之前这条记录的值。所以读操作和写操作是可以同时进行的。

这种版本控制的隔离级别，并发度当然比SQL Server的要高，读写操作之间产生阻塞的几率要小。可是它的事务隔离效果和SQL是不同的。例如，对于某个银行账户的查询，假设账户里原来有1万元，用户A开始一个事务，将账户里的1万元转出。在转出这个动作还没有完成时，用户B来查询账户余额。使用版本控制，用户B可以马上得到结果：1万元，但是这个结果其实很有可能已经过时。使用SQL Server，用户B必须等到用户A转帐完成才能查到余额，但是他得到的一定是一个最新的值。

这两种隔离级别其实反映了两种用户需求，不能讲哪种好，哪种不好。喜欢版本控制这种隔离级别的用户，如果想要在SQL
Server里有同样的并发度，可以选择SQL里面的Snapshot Isolation Level（这个功能在SQL 2005里引入）。这样，SQL里的读写操作就不互斥了。

上面的这几个特征，决定了SQL Server的很多行为特点。如果跑在SQL Server上应用程序没有很好地按照上述的特征设计，就容易遇到各种各样的性能问题。

在现实应用中，SQL Server性能问题的常见原因有下面几点。

1.      索引的设计不够优化，从而迫使语句经常使用全表扫描的执行计划

如果没有好的索引帮助，SQL Server查询任何一条记录都有可能不得不把整张表都扫描一遍。这个在数据库比较小的时候影响不大，因为SQL能够把所有数据都缓存在内存里，就算是全表扫描也不会太慢。随着数据量增大，内存会放不下。全表扫描的负担会越来越重，到最后会严重影响SQL
Server的整体性能。

这是一个很常见的SQL Server越跑越慢的原因。

2.      大的表格没有聚集索引，或者聚集索引建立在不合适的数据列上

对于没有聚集索引的表格，如果里面的记录数以万计或者更多，管理和查询都会增加很多开销。一个经验是，对于一个要经常使用的任何大表格，请建立聚集索引。索引所在的数据列，应该是一个或者几个重复性的记录不是很多的数据列。

业界的一些其他数据库可能对聚集索引这个概念不是非常强调，或者使用其他的方法管理表格。所以如果把非SQL
Server的数据库迁移到SQL Server里，一定要重新检查索引结构，按照SQL Server的特点，调整索引设置。否则迁移后在SQL Server上得不到好的性能，是很正常的。

3.      用户需要经常访问的数据量，远大于机器的内存数

前面我们已经说过，SQL 
Server要操作的数据，是必须要缓存在内存里的。如果终端所有用户要经常访问的数据量的总和，远远超出SQL Server所拥有的内存数量，那SQL就会不得不在内存和磁盘间反复换页。这时候的SQL性能下降，可能就不是一两个数量级了。

用户需要经常访问的数据量的大小，通常跟以下几个因素有关。

a.用户发过来的语句定义

如果语句里有良好的条件约束，那数据量就能得到控制。如果条件约束性不强，那数据量就会随着表格里的记录数增长而增长。一个例子是，用户总是查询“今年的某某数据”。那在一年开始的时候，查询肯定很快。到年底时候的数据量可是一月份的12倍。那时候访问的数据量也很可能就是12倍。

b. SQL Server是否能够利用索引，使用Seek的方式找到数据，而不是扫描全表

如果没有好的索引，哪怕用户要访问的数据只是表格里的很小一部分，SQL可能也需要遍历整个表格。在这种情况下，也会出现表格越大，SQL跑得越慢的情况。如果索引设计地比较优化，SQL Server的数据访问量，不应该跟表格的大小有着绝对的关系。

c. 应用程序的性质

数据库应用基本可以分两大类：OLTP类型的，和Data Warehouse类型的。前者会处理大量的小事务，比如病人挂号、超市结帐、仓库进货等。这种应用用户发过来的请求不会很复杂，每次要处理的数据量也比较小，但是要求的响应速度要很快。有可能0.5秒的等待都会带来性能问题。所以对这种应用，其访问的数据应该保证总是缓存在内存里。

Data Warehouse类型的应用主要是做数据分析和整理，经常是为了产生一些报表。这种应用主要以查询为主，肯定会访问大量的数据，会出现数据库越大，访问的数据量越多的情况。但是客户端可以容忍一定时间的等待。对于这种应用，其访问的数据不在内存里关系也不大。

对于OLTP类型的应用，应该尽量保证用户经常要访问的数据能够长时间地缓存在内存里。所以在设计应用逻辑和数据库时，就要有所考虑，控制用户访问的数据量，建立有效的索引，避免全表扫描。在管理上，也要有历史数据归档的机制，控制整个数据库的大小。如果证明用户需要访问的数据量就是比内存大，升级内存也是需要考虑的方案。

对于报表类型的应用，因为访问的数据量会很大，磁盘换页是难以避免的。当然良好的索引设计，对性能也会很有帮助。

要避免的情况，是同一个SQL Server既在运行OLTP类型的应用，又时不时地在运行报表类型的应用。它们两者会产生很大的相互干扰，对OLTP应用的响应速度会产生破坏性的影响，而且很难用调整数据库设计，或者升级硬件的方法轻易解决。

4.      数据库应用是从其他非SQL Server的架构下直接移植过来的

前面已经谈过，SQL Server缺省的事务隔离级别是Read Committed，和其他数据库可能不一样。另外，很多应用是通过调用数据库控件的接口来访问数据库的，而不是直接调用SQL语句或者存储过程。要达到同一个目的，程序里有很多种实现的方法。有些方法可能在其他数据库上性能会不错，但是到SQL
Server上有可能就要换一个做法，或者换一种驱动。再加上不同的数据库系统对指令的写法可能有不同的建议，表格上的索引要求也不一样。所以要在SQL
Server上实现良好的性能，就必须对迁移过来的应用在SQL Server平台上进行优化。没有测试、优化而直接移植过来的应用，常常不能发挥SQL Server的长处，性能欠佳。

 

 

By 微软亚太区全球技术支持中心 数据库技术支持组

对话微软原厂技术专家，手把手教会你 SQL Server性能调优: https://support.microsoft.com/gp/anxin_training2/zh-cn

给SQL Server来个全套体检，有病治病，无病强身：https://support.microsoft.com/gp/anxin_guide6/zh-cn

SQL Server技术支持大全，根治纠结您N久的疑难杂症：https://support.microsoft.com/gp/anxin_sql/zh-cn

Comments

• Anonymous
  February 06, 2012
  页扇区损坏，SQLSERVER将如何处理？如果遇到硬件故障，如：页的某个扇区损坏，SQLSERVER将如何处理呢（在写入数据的时候，或者写入数据没有遇到故障，而过了一段时间又去读取的时候遇到了故障）？是否能够有什么方法模拟这样一个环境出来？谢谢！
• Anonymous
  August 09, 2012
  运行DBCC CHECKDB。 你可以直接用十六进制编辑工具打开SQL SERVER MDF文件，然后更改里边的数据。这样就可以模拟磁盘IO问题了。
• Anonymous
  November 22, 2013
  to wanyongSQLSERVER对页面损坏有一定的校验机制的--1、checksum：--SQL在写入每个页面时，根据页面里的数据算出一个校验值，一同存储到页面里。当下次读取页面的时候，--在根据这次读到的页面数据，算出一个新的校验值。如果写入和读出的数据一模一样，那么两个校验值一--定是相等的。而如果两个校验值不相等，意味着上次SQL写入的数据和这次读出的一定不同，现在读出来--的数据有问题。通过校验值SQL能发现数据页面损坏，这个功能是SQL2005以后引入的--2、torn page--SQL2000引入的残缺页（torn page）保护，是一种对电源故障导致的页损坏进行检测的方法。--页的每个512B扇区末尾会放置一个2位签名（在这之前会把原来的2位复制到页头之后）每次--进行写操作时，这个签名在二进制01和10之间交替。这样始终可以确定是否只有部分扇区写到--磁盘。如果稍后读取页时发现某个位的状态不正确，则说明该页没有被正确写入。--如果页面没有被正确写入，因此检测到问题页面，称为残缺页。相对于checksum，残缺页使用--的资源最少，但是它的算法太简单，无法检测到磁盘硬件故障导致的所有错误--不知道01 还是10 是指明那个扇区被正确写入--3、short transfer--读到的数据长度比预期的少。例如，一个读取要求预期可以读到8KB的数据，可是实际只返回了--4KB。这意味着当前读到的页面有损坏--4、bad page id--在读到页面后，SQL会比较页面开头存储的页面编号和自己请求的目标编号。如果发现自己--想要读取的页面是第200页，而读到的内容里显示它是第100页，SQL就会触发824错误。--这种错误经常是因为I/O子系统没有正确处理SQL的请求，传给SQL一个错误的页面，甚至--是一个空页面--5、restore pending--在SQL2005以后的企业版里，用户可以要求在做还原的时候跳过一些有损坏的页面（continue after error）--延迟的错误。这些跳过的页面被标识成“restore pending”.如果用户想去访问它，也会遇到824错误--6、stale read--由于硬件系统发生漏写的现象，checksum和torn page算法都不能检测到错误。--可以打开SQL启动参数开关 /T818 以后，SQL会在内存维护一张哈希表，记录下自己所有做过的写入--的动作的页面的LSN值。在下次读出页面的时候去比较这两个值是否相等。由于LSN是自动增长的唯一值，--每个发生新修改的页面，LSN的值会比原来的要大。所以如果读到的LSN的内存中存放的不一样，就--说明上次写入的请求没有真正完成。触发824错误--net start MSSQLSERVER /t818--修复方法：DBCC CHECKDB能修复，但是只是逻辑修复，会有部分数据丢失