使用优化实用工具来优化SQL Server性能

领测软件测试网

　　 
　　在Microsoft SQL Server 2000中提供了一些优化实用工具，使用这些工具可以有效地提高数据库的使用效率。在一般人的眼里，“优化”可能算得上是一种高级技能，但真正做起来也不是很难，您也可以成为这方面的高手，不信您就试一试。

本文从十二个方面来谈如何优化SQL Server 2000，使其获得较高性能。优化数据库可在生产数据库上执行，获得最佳性能收益的三个操作包括：
　　◆ 备份和还原操作。
　　◆ 将数据大容量复制到表中。
　　◆ 执行数据库控制台命令(DBCC)操作。
　　一般情况下，不需要优化这些操作。然而，在性能很关键的情形中，可采用以下一些技巧来优化性能。
　　
　　优化备份和还原性能
　　
　　SQL Server 2000提供几种方法以提高备份及还原操作的速度：
　　◆ 使用多个备份设备使得可以将备份并行写入所有设备。同样，可以将备份并行从多个设备还原。备份设备的速度是备份吞吐量的一个潜在瓶颈，使用多个设备可以按使用的设备数成比例提高吞吐量。
　　◆ 使用数据库备份、差异数据库备份和事务日志备份的组合，可以将故障恢复所需的时间减到最少。差异数据库备份可以减少必须应用于恢复数据库操作的事务日志量。这种方法通常比创建完整数据库备份快。
　　◆ 使用日志记录和最小日志记录大容量的复制操作。
　　
　　优化数据库、差异数据库和文件备份性能
　　
　　创建数据库备份包含两个步骤：
　　（1）将数据从数据库文件复制到备份设备。
　　（2） 将事务日志中用于将数据库前滚到一致状态的那部分复制到相同的备份设备。
　　与创建数据库备份一样，创建差异数据库备份也包括上面两步，但只复制已更改的数据（尽管需要读取所有数据库页以确定数据是否更改）。备份数据库文件只需一步，将数据从数据库文件复制到备份设备。
　　用于存储数据库的数据库文件按磁盘设备排序，并给每个设备指派读取器线程，给每个备份设备指派写入器线程。该读取器线程从数据库文件中读取数据，写入器线程将数据写入备份设备。通过在更多的逻辑驱动器中分布数据库文件可以增加并行读取操作。同样，通过使用更多的备份设备可以增加并行写操作。
　　一般情况下，瓶颈是数据库文件或备份设备。如果读取吞吐总量比备份设备吞吐总量大，则瓶颈在备份设备这一侧。添加更多的备份设备（如果必要还需添加SCSI控制器）可以提高性能。然而，如果备份吞吐总量比读取吞吐总量大，则应在设备上添加更多的数据库文件或者在RAID（独立磁盘冗余阵列）设备内使用更多磁盘，以便增加读取吞吐量。
　　
　　优化事务日志备份性能
　　
　　创建事务日志备份只包含单个步骤，将日志中尚未备份的部分复制到备份设备。虽然可能有多个事务日志文件，但事务日志在逻辑上是某个线程按顺序读取的一个流。
　　给每个备份设备指派读取器/写入器线程。通过添加更多的备份设备可以获得更高的性能。
　　瓶颈可能是包含事务日志文件的磁盘设备或者是备份设备，具体取决于它们的相对速度和使用的备份设备数。添加更多备份设备将提高线性比例，直到达到包含事务日志文件的磁盘设备最大容量，此后如果不通过使用磁盘条带化等方法提高包含事务日志的磁盘设备的速度，将不可能获得更多性能收益。
　　
　　优化还原性能
　　
　　还原数据库备份或差异数据库备份包含四个步骤：
　　（1）创建数据库和事务日志文件，如果二者还没有存在。
　　（2）将数据从备份设备复制到数据库文件。
　　（3）从事务日志文件复制事务日志。
　　（4）前滚事务日志，然后（如果需要）重新启动恢复。
　　应用事务日志备份包含两个步骤：
　　（1）将数据从备份设备复制到事务日志文件。
　　（2）前滚事务日志。
　　还原数据库文件包含两个步骤：
　　（1）创建任何丢失的数据库文件。
　　（2）将数据从备份设备复制到数据库文件。
　　如果数据库和事务日志文件还没有存在，必须先创建它们才能将数据还原到其中。创建数据库和事务日志文件并将文件内容初始化为零。使用单独的工作线程并行创建和初始化文件。按磁盘设备排序数据库和事务日志文件，并给每个磁盘设备指派单独的工作线程。创建和初始化文件需要很大的吞吐量，因此在可用的逻辑驱动器中均匀分布文件能产生最佳性能。
　　通过读取器/写入器线程将数据和事务日志从备份设备复制到数据库和事务日志文件，给每个备份设备指派一个线程。复制性能受备份设备传送数据的能力或数据库和事务日志文件接收数据的能力的限制。因此，复制性能随添加的备份设备数线性地提高，直到达到数据库或事务日志文件接收数据能力的极限。
　　前滚事务日志操作的性能已经固定，除使用更快的计算机外不能进一步优化。
　　
　　优化磁带备份设备性能
　　
　　有四个变量影响磁带备份设备的性能，并使SQL Server备份及还原性能操作得以在大体上随添加更多磁带设备而提高线性比例。
　　◆ 软件数据块大小。
　　◆ 共享小型计算机系统接口(SCSI)总线的磁带设备数。
　　◆ 磁带设备类型。
　　软件数据块大小是由SQL Server为最佳性能计算的，不应更改。
　　许多高速磁带驱动器如果对每个所使用的磁带驱动器有专用SCSI总线，将运行得更好。本机传输速率超过SCSI总线速度的50%的驱动器必须在专用SCSI总线上。
　　注意：永远不要将磁带驱动器与磁盘或CD-ROM驱动器放置在同一个SCSI总线上。对这些设备的错误处理操作互不兼容。
　　
　　优化磁盘备份设备性能
　　
　　磁盘备份设备的原始I/O速度影响磁盘备份设备性能，并使SQL Server备份及还原性能操作得以在大体上随添加多个磁盘设备而线性提高。
　　在对磁盘备份设备使用RAID（独立磁盘冗余阵列）时需认真考虑。例如，RAID5的写入性能低，大致与单个磁盘的速度相同（由于必须维护奇偶信息）。另外，将数据追加到文件的原始速度明显比原始设备写入速度慢。
　　如果将备份设备高度条带化，以使对备份设备的最大写入速度远远超过备份设备将数据追加到文件的速度，则在相同的条带集上放置几个逻辑备份设备会比较合适。换句话说，可以通过在相同的逻辑驱动器上放置几个备份媒体家族来提高备份性能。然而，需要采用经验方法确定这对每个环境是收益还是损失。通常情况下，最好将每个备份设备放置在单独的磁盘设备上。
　　一般在SCSI总线上只有少数几个磁盘可以以最大速度运行，但Ultra-wide和Ultra-2总线可以处理更多磁盘。不过，很可能需要认真配置硬件以获得最佳性能。
　　
　　数据压缩
　　
　　如今的磁带驱动器有内置的硬件数据压缩，可显著提高将数据传送到驱动器的有效传送速率。数据压缩可以提高将数据传送到磁带驱动器的有效传送速率，该速率超过通过禁用硬件压缩所达到的速率。数据库内实数据的可压缩性取决于数据本身和所使用的磁带驱动器。对于大范围的数据库，典型的数据压缩率是从1.2:1到2:1。该压缩率对于在多种业务应用程序中使用的数据是典型的，但有些数据库可能有更高或更低的压缩率。例如，主要包含已压缩图像的数据库将不能再由磁带驱动器进一步压缩。有关数据压缩的更多信息，请参见磁带驱动器的供应商文档。
　　默认情况下SQL Server支持硬件压缩，但可以使用3205跟踪标记禁用硬件压缩。在极少数情况下，禁用硬件压缩可以提高备份性能。例如，如果数据已经完全压缩，禁用硬件压缩可防止磁带驱动器浪费时间试图进一步压缩数据。
　　
　　传送到磁带的数据量
　　
　　创建数据库备份只捕获数据库中包含实际数据的部分，而不备份未使用的空间，其结果将使备份操作的速度更快。
　　虽然可以根据需要将SQL Server 2000数据库配置为自动增长，但可继续保留数据库内的空间以保证该空间可用。保留数据库内的空间对备份吞吐量和备份数据库所需的总时间没有负面影响。
　　
　　优化DBCC性能
　　
　　数据库控制台命令(DBCC)往往大量占用CPU及磁盘，因为DBCC必须读取每个数据页，而这需要从磁盘到内存全都检查一遍（除非数据页已高速缓存到内存中）。当系统上有许多活动（如大量的查询处理）而运行DBCC时，可用内存减少，而且SQL Server 2000被迫将数据页发送到tempdb数据库中进行假脱机处理，DBCC的性能由此削弱。因此，如果使更多内存可用于DBCC处理，将可以高速缓存数据库中的更多内容，从而使DBCC语句执行得更快。
　　tempdb数据库驻留在磁盘上，因此在将数据写入或写出磁盘时，来自I/O操作的瓶颈将削弱性能。对大型数据库（相对于可用内存的大小而言），运行DBCC会导致送到tempdb数据库进行假脱机处理，与系统活动无关。因此，建议将tempdb数据库放置在与用户数据库分开的一个或多个快速磁盘上，如RAID（独立磁盘冗余阵列）。
　　说明：执行DBCC CHECKDB时将对数据库内的每个表自动执行DBCC CHECKTABLE和DBCC CHECKALLOC，因而不必单独运行这两个语句。
　　
　　优化大容量复制性能
　　
　　为尽可能快地大容量复制数据，可使用下列选项指定如何使用bcp实用工具或BULK INSERT语句将数据大容量复制到SQL Server 2000内：
　　◆ 使用有日志记录和无日志记录的大容量复制。
　　◆ 对并行数据装载使用bcp实用工具。
　　◆ 控制锁定行为。
　　◆ 使用批处理。
　　◆ 排序数据文件。
　　说明：如果可能，使用BULK INSERT语句而不是bcp实用工具将数据大容量复制到SQL Server内。BULK INSERT语句比bcp实用工具快。
　　有两个因素决定可以或应该使用哪个选项以提高大容量复制操作性能：
　　（1）表内现有的数据量相对于要复制到表内的数据量。
　　（2）表上索引的数目和类型。
　　另外，这些因素还取决于是从单个客户端还是并行从多个客户端将数据大容量复制到表内。
　　
　　将数据从单个客户端装载到空表内
　　

文章来源于领测软件测试网 https://www.ltesting.net/