八、IO优化（7）减少IO竞争

一、自动增长与自动收缩1.自动增长 默认情况下，数据库文件的大小可以根据需要自动增大。这可以使文件的大小增大到磁盘变满为止。（1）不允许自动增长 如果生产

一、自动增长与自动收缩

1. 自动增长

　　默认情况下，数据库文件的大小可以根据需要自动增大。这可以使文件的大小增大到磁盘变满为止。

（1）不允许自动增长

　　如果生产环境不允许自动增长操作过程中可能出现的应用程序超时，则应为预期的工作负荷预分配空间。

　　如果不允许自动增长，而数据库空间已满，则SQL Server会报错“database full”并拒绝写入新数据。

（2）允许自动增长

　　一般建议数据库应设置为自动增长，在出现意外情况时此设置将用于增加数据库文件的磁盘空间。也就是说，自动增长是一道保险，在“database full”的时候自动为数据库增长可用空间。

2. 自动增长的增量设置

　　将文件增量设置为合理的大小以避免数据库文件的增量过小。如果文件的增量与写入数据库的数据量相比过小，则数据库可能需要不断扩大。这将影响性能。

　　建议为数据库文件设置 FILEGROWTH 增量时遵循以下通用原则。

（1）当数据库文件大小为 0 至 100 MB 时，增量为 10 MB。

（2）当数据库文件大小为 100 至 200 MB 时，增量为 20 MB 。

（3）当数据库文件大小超过 200 MB 时，增量为 10% 。此增量可能必须基于数据库的文件所在的 I/O 子系统的速度调整此百分比。

3. 初始化

　　为了避免潜在的闩锁超时，我们建议将自动增长操作限制在大约两分钟之内。例如，，如果 I/O 子系统以每秒 50 MB 的速度初始化文件，则无论数据库文件的大小如何，FILEGROWTH 增量都应设置为最大值 6 GB。

　　可以通过即时文件初始化功能提升性能 (v=sql.105).aspx

4. 自动收缩

　　SQL Server 不会持续测试达到为自动收缩配置的阈值的数据库。相反，它会寻找可用的数据库并找出第一个配置为自动收缩的数据库。它将检查该数据库，并在需要时收缩该数据库。然后，它会等待几分钟，再检查下一个配置为自动收缩的数据库。

　　换句话说，SQL Server 不会同时检查所有数据库，也不会同时收缩所有数据库。它将以循环方式处理各个数据库，以使负载在时间上错开。因此，从数据库达到阈值到实际完成收缩可能需要几个小时，具体取决于特定 SQL Server 实例上配置为自动收缩的数据库数量。

5. 建议

　　数据库的自动增长会导致以下问题：

（1）可控的增长

　　数据库发生自动增长，通常是写入了大量数据，而这种情形又往往会发生在业务高峰时段。因此，自动增长往往会在业务高峰期拖累数据库的性能。如果该增长量很大，或者有其他因素导致时间延长，则您在其中打开事务的查询可能因超时错误而失败。

　　对于受管理的生产系统，您必须将自动增长仅视为偶然的意外增长。请勿使用自动增长管理每天的数据和日志增长。

　　建议定期（每周或每月）在非高峰期手动增加数据库的空间。至于需要增加多少空间才合适，应当监测数据库的大小，估算其增长的趋势。

（2）监视可用的磁盘空间

在使用自动增长设置时，增长后的数据库大小不能超出为其定义文件的驱动器上的可用磁盘空间。因此，如果您依赖自动增长功能来决定您的数据库的大小，仍必须另外检查可用的硬盘空间。因此，应当监测磁盘空间的变化，提前预测磁盘空间的使用情况。

（3）禁用自动收缩

　　一般不建议自动收缩。

　　如果您同时使用自动增长和自动收缩选项，则可能会带来不必要的开销。请确保触发增长和收缩操作的阈值不会造成频繁的大小调整。例如，您可能会运行这样一个事务，它导致事务日志在提交时增长 100 MB。在自动收缩启动后的一段时间内，事务日志收缩 100 MB。然后，您又运行相同的事务，并导致事务日志再次增长 100 MB。在该示例中，您造成了不必要的开销，并且可能会产生日志文件碎片，两者都可能对性能造成负面影响。

二、文件分布

　　在SQL Server中，主要会有以下内容发生磁盘I/O竞争。建议将这些文件分别放在不同的物理磁盘中。

（1）用户数据库的数据文件（mdf和ndf文件）

（2）用户数据库的事务日志文件（ldf文件）

（3）tempdb数据库的数据文件和日志文件

（4）备份时产生的bak和trn文件。

（5）Windows系统的事件、分页文件。

三、磁盘子系统

1. 阵列卡

　　通常磁盘使用以下几种RAID（Redundant Array of Independent Disk，独立冗余磁盘阵列）。

（1）RAID 0

　　即Data Stripping（数据分条技术）。整个逻辑盘的数据是被分条（stripped）分布在多个物理磁盘上，可以并行读/写，提供最快的速度，但没有冗余能力。要求至少两个磁盘。

　　通过RAID 0可以获得更大的单个逻辑盘的容量，且通过对多个磁盘的同时读取获得更高的存取速度。RAID 0首先考虑的是磁盘的速度和容量，忽略了安全，只要其中一个磁盘出了问题，那么整个阵列的数据都会不保了。

（2）RAID 1

　　即镜像（Mirror）方式，也就是数据的冗余。在整个镜像过程中，只有一半的磁盘容量是有效的（另一半磁盘容量用来存放同样的数据）。同RAID 0相比，RAID 1首先考虑的是安全性，容量减半、速度不变。

（3）RAID 5

　　RAID 5的工作方式是将各个磁盘生成的数据校验切成块，分别存放到组成阵列的各个磁盘中去，这样就缓解了校验数据存放时所产生的瓶颈问题，但是分割数据及控制存放都要付出速度上的代价。意味着RAID 5在数据写入时会变慢。

（4）RAID 10

　　为了达到既高速又安全，出现了RAID 10（或者叫RAID 0+1），可以把RAID 10简单地理解成由多个磁盘组成的RAID 0阵列再进行镜像。这种搭配，使得读取和写入的速度都非常快。但是容量减半。

2. 建议

（1）tempdb

　　tempdb 数据库性能要求非常高，但对数据安全性要求低。建议将 tempdb 数据库放置在快速 I/O 子系统中。如果有许多直接连接的磁盘，请使用RAID 0 。

（2）数据文件（mdf和ndf文件）

　　这类文件即要求性能，又要求安全性。建议RAID 10。如果硬件预算非常紧张，RAID 5也凑合吧。

（3）事务日志文件（ldf文件）

　　事务日志文件的特性是连续的顺序写入，因此对性能要求不高，仅要求安全性。建议RAID 1。

本文结语：

　　尽量避开各种磁盘I/O的竞争，避免不必要的性能开销。

本文出自 “我们一起追过的MSSQL” 博客，转载请与作者联系！