优化 tempdb 性能

tempdb 数据库的大小和物理位置可能会影响系统的性能。例如，如果为 tempdb 定义的大小过小，则每次重新启动 SQL Server 实例时，都可能会占用部分系统处理负荷，以使 tempdb 自动增长到支持工作负荷所需的大小。您可以通过增加 tempdb 数据和日志文件的大小来避免此开销。有关确定 tempdb 所需的适当磁盘空间量的信息，请参阅 tempdb 容量规划。

 tempdb 大小和位置建议

若要获得最佳的 tempdb 性能，我们建议在生产环境中对 tempdb 进行如下配置：

• 将 tempdb 的恢复模式设置为 SIMPLE。此模式自动回收日志空间以保持较小的空间要求。
  有关详细信息，请参阅 ALTER DATABASE (Transact-SQL) 或如何查看或更改数据库的恢复模式 (SQL Server Management Studio)。
  
• 使 tempdb 文件的大小可以根据需要自动增大。这可以使文件的大小增大到磁盘变满为止。
  

  注意：

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

• 将文件增量设置为合理的大小以避免 tempdb 数据库文件的增量过小。如果文件的增量与写入 tempdb 的数据量相比过小，则 tempdb 可能需要不断扩大。这将影响性能。建议为 tempdb 文件设置 FILEGROWTH 增量时遵循以下通用原则。
  

  tempdb 文件大小	FILEGROWTH 增量
  0 至 100 MB	10 MB
  100 至 200 MB	20 MB
  200 MB 或更多	10%*

  * 您可能必须基于 tempdb 文件所在的 I/O 子系统的速度调整此百分比。为了避免潜在的闩锁超时，我们建议将自动增长操作限制在大约两分钟之内。例如，如果 I/O 子系统以每秒 50 MB 的速度初始化文件，则无论 tempdb 文件的大小如何，FILEGROWTH 增量都应设置为最大值 6 GB。如果可能，请使用实例数据库文件初始化来提高自动增长操作的性能。
  
• 通过将文件大小设置为足够容纳环境中典型工作负荷的值来预分配所有 tempdb 文件的空间。这可以避免 tempdb 因扩展得过于频繁而影响性能。tempdb 数据库应设置为自动增长，但是在出现意外情况时此设置将用于增加磁盘空间。
  
• 根据需要创建足够多的文件以使磁盘宽度最大化。使用多个文件可以减少 tempdb 存储争用并获得更大的可伸缩性。但是，请勿创建过多的文件，因为此操作可能降低性能并增加管理开销。作为通用原则，为服务器中的每一个 CPU 创建一个数据文件（用于解释任何关联掩码设置），然后根据需要上下调整文件的数量。请注意，双核心 CPU 将被视为两个 CPU。
  
• 使每个数据文件的大小相同，这样可以优化比例填充的性能。
  
• 将 tempdb 数据库放置在快速 I/O 子系统中。如果有许多直接连接的磁盘，则请使用磁盘条带化。
  
• 将 tempdb 数据库放置在用户数据库使用的磁盘以外的磁盘中。
  

 修改 tempdb 大小和增长参数

您可以使用下列方法之一修改 tempdb 数据或日志文件的大小和文件增长参数。

• ALTER DATABASE 语句
  
• SQL Server Management Studio
  

每 次创建 tempdb 时都要使用文件大小和文件增长参数的值。例如，如果您将 tempdb 数据文件的大小增加到 20 MB 并将文件增量增加到 15%，则新的值将立即生效。如果后续事务活动使 tempdb 的大小增大，则每次重新启动 SQL Server 实例时，数据文件的大小都将返回到 20 MB。

 查看 tempdb 大小和增长参数

您可以使用下列方法之一查看 tempdb 数据或日志文件的大小和文件增长参数：

• SQL Server Management Studio
  
• 运行下面的查询。
  
  SELECT 
      name AS FileName, 
      size*1.0/128 AS FileSizeinMB,
      CASE max_size 
          WHEN 0 THEN 'Autogrowth is off.'
          WHEN -1 THEN 'Autogrowth is on.'
          ELSE 'Log file will grow to a maximum size of 2 TB.'
      END,
      growth AS 'GrowthValue',
      'GrowthIncrement' = 
          CASE
              WHEN growth = 0 THEN 'Size is fixed and will not grow.'
              WHEN growth > 0 AND is_percent_growth = 0 
                  THEN 'Growth value is in 8-KB pages.'
              ELSE 'Growth value is a percentage.'
          END
  FROM tempdb.sys.database_files;
  GO

 检测磁盘 I/O 路径错误

当设置为 CHECKSUM 时，PAGE_VERIFY 选项可发现磁盘 I/O 路径错误引起的损坏的数据库页并在 SQL 错误日志中报告这些错误，例如 MSSQLSERVER_823、MSSQLSERVER_824 或 MSSQLSERVER_825。磁盘 I/O 路径错误可能导致数据库损坏问题。这些错误通常是将该页写入磁盘时发生的电源故障或磁盘硬件故障引起的。有关 I/O 错误的详细信息，请参阅 Microsoft SQL Server I/O Basics, Chapter 2（Microsoft SQL Server I/O 基本信息，第 2 章）。

在 SQL Server 的早期版本中，tempdb 数据库的 PAGE_VERIFY 数据库选项设置为 NONE 且不能修改。在 SQL Server 2008 中，对于新安装的 SQL Server tempdb 数据库的该默认值为 CHECKSUM。如果升级安装 SQL Server，则默认值仍为 NONE。建议将 tempdb 数据库的 PAGE_VERIFY 选项设置为 CHECKSUM。