mssql sqlserver提升性能的几大要点


摘要:
数据库性能问题是困扰用户的关注最多的问题。
不了解sqlserver 的人,通常会怀疑sqlserver的处理能力,
sqlserver面世已经20多个年头了,目前已经变成了非常成熟的企业级产品,合理使用sqlserver ,使其处理上百G的数据,将会变的轻轻松松。
下文讲述提升sqlserver性能的几大要点



1. SQL Server访问的数据必须缓存在内存中

无论要查询的数据还是要修改的数据,SQL Server都会运行客户端发送的语句,并在处理数据之前检查访问的数据是否在内存中。
如果它不在内存中,SQL Server将首先将存储数据的页面从磁盘传输到内存,然后进行实际的数据处理。
数据处理完毕后,SQL Server将不会立即丢弃该数据缓存。只要SQL Server不缺少内存,先前访问过的数据页将被缓存在SQL中
服务器进程地址空间。这样,如果下次其他用户想要访问相同的数据记录,SQL
服务器可以立即在内存中处理,而无需转到磁盘。
这种设计可以最大限度地重用内存并提高SQL Server的速度。它也确定SQL Server是一个享有大内存的应用程序。
如果SQL Server没有空闲内存,并且用户需要访问新数据,则SQL将选择以前缓存数据的一部分将其从内存中清除,释放空间以缓存新数据。
SQL Server不会无限期地请求内存。它会根据用户的设置和系统内存量计算自己的最大内存。如果当前内存大小达到此限制,则SQL将不再向Windows应用更多内存。这种机制可以保证SQL Server和Windows以及在同一台机器上运行的其他应用程序的和平共存。
当SQL Server中的内存数量达到最大值时,内存空间会被缓存到各种数据页面,用户必须访问尚未在内存中缓存的新数据。
根据现有数据访问的频率,服务器将从内存中删除最旧和最不频繁访问的数据,腾出空间缓存客户正在访问的新数据。
因此,理想的状态是用户想要访问的数据总是缓存在内存中,而SQL Server从不需要访问磁盘。这也是最好的数据库性能。
此时SQL Server几乎从不需要进行磁盘读取。
如果用户经常访问数据不在内存中,SQL Server将被迫在内存和磁盘之间反转数据,性能将受到严重影响。此时,您将看到SQL磁盘频繁读取。
————————————————
这就是当我们查询大报表时,会把数据库中其它模块全部拖死的原因,因为其它所有模块数据,都不在缓存中。
————————————————

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

SQL Server的一个显着特性是将数据存储在表中,并按聚簇索引中字段的值进行排序。而不是聚集索引是基于聚集索引结构。如果一个表没有聚集索引,那么这些数据将作为一个堆存储而没有任何顺序。
对于相同数量的数据,SQL Server管理具有聚簇索引的表远比没有聚簇索引的表更有效。在大多数情况下,如果您希望大型表具有良好性能,则必须具有合适的聚集索引。如果没有聚簇索引,只会添加非聚簇索引并且无法实现最佳性能。
这是SQL Server的一个非常重要的功能。

3.在默认的事务隔离级别上,对同一记录的读写操作是互斥的

SQL Server实现了ANSI标准的四个隔离级别。在ReadCommitted(默认隔离级别)下,读取操作将应用于S锁定,修改操作将应用于X锁定。 S锁和X锁相互排斥。因此,读取操作和写入操作不能同时在同一记录上执行。
业内其他一些数据库产品默认使用行版本控制来实现事务隔离。如果用户正在修改记录,但没有提交事务,而另一个用户想要读取相同的记录,则会让第二位用户阅读。将先前记录的值更改为第一个人。所以读写操作可以同时进行。
此版本控制隔离级别,并发性肯定高于SQL Server,读写操作之间的阻塞概率较小。但是,它的事务隔离效果和SQL是不同的。
例:对于商品库存查询,假设商品原有100个,用户A出库90个。当出库尚未完成时,用户B将查询库存数量。用版本控制,用户B可以立即得到结果:100个,但这个结果其实很可能已经过时。对于SQL Server,用户B必须等到用户A转账才能找到库存数量,但必须给他一个当前值。
这两个隔离级别实际上反映了两个用户的需求,不能说哪个好,哪个不好。如果用户想要使用SQL,则喜欢版本控制此隔离级别的用户
服务器具有相同的并发程度,您可以选择SQL中的SnapshotIsolation级别(此功能在SQL2005中引入)。
通过这种方式,SQL中的读写操作不是相互排斥的。
上述功能决定了SQL Server的许多行为特征。如果您在SQL Server上运行应用程序没有按照上述特征设计好,则很容易遇到各种性能问题。
在实际应用程序中,
SQL Server性能问题的常见原因如下:

1).索引的设计不够优化,不足以强制语句使用全表扫描的执行计划
没有好的索引帮助,SQL Server可能必须扫描整个表以查找任何记录。
这对数据库很小时几乎没有影响,因为即使全表扫描不太慢,SQL也可以将所有数据缓存在内存中。
随着数据量的增加,内存不适合。全表扫描的负担将变得越来越重,最终会严重影响SQL。

2).服务器的整体性能。
这是SQL Server运行速度较慢的常见原因。

4.用户需要频繁访问的数据量远大于机器的内存容量。

正如我们前面提到的那样,SQL Server将要运行的数据必须缓存在内存中。
如果终端所有用户频繁访问的数据总量远远超过SQL Server的内存量,SQL将不得不在内存和磁盘之间重复页面。
此时SQL性能的下降可能不是一个或两个数量级。
此时我们需要加大内存,来满足sqlserver的查询

5.应用程序的编写

应用程序合理的访问数据库,减少不必要的访问和不必要的返回数据。