新服务器的SQL Server tempdb优化提示?

如果我必须选择一个我信任的人来提供配置服务器的建议,那就是丹尼·切丽。他写了一篇文章解释这一点。

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1307255,00.html

以下是我从各种渠道对自己进行的研究。

为了优化tempdb性能,请注意物理磁盘配置、文件配置以及数据库中的一些设置。

物理磁盘配置

tempdb应位于其上 拥有专用物理磁盘 . 这允许它将I/O事务与SQL Server上其余卷分离。

要将tempdb移动到新的磁盘驱动器,请使用 ALTER DATABASE . 它是执行此操作的T-SQL键命令。Microsoft在SQL Server 2005联机丛书中提供了一个很好的示例。项目名称是alter database(Transact-SQL),特定部分是 “g.将tempdb移动到新位置。”

tempdb是一个非常高的写数据库。因此,一个RAID 5阵列不是它的合适位置。你应该把临时数据库 在RAID 0或RAID 10阵列上 因为它们针对高写应用程序进行了优化。如果您能够为tempdb的每个物理数据库文件提供额外的raid 0或raid 10阵列,您将获得更高的性能。

数据库文件

你应该拥有 服务器中每个CPU核心一个物理文件 . 因此,如果您有一个双芯片双核服务器,那么您应该有四个用于tempdb数据库的物理数据库文件。添加更多数据库文件时,在 相同的初始大小和相同的增长设置 . 这样,SQL Server将尽可能均匀地跨文件写入数据。

数据库文件大小

tempdb数据库的大小会影响系统的性能。例如,如果为tempdb定义的大小太小,则系统处理负载的一部分可能是 在每次重新启动SQL Server实例时,使用自动增长的tempdb,使其达到支持工作负载所需的大小 . 您可以通过增加tempdb数据和日志文件的大小来避免这种开销。

在生产环境中确定tempdb的适当大小取决于许多因素,包括现有工作负载和所使用的SQL Server功能。Microsoft建议您通过在SQL Server中执行以下任务来分析现有工作负荷 测试 环境:

1. 为tempdb设置autogrow (在测试环境中!) .
2. 执行单个查询或工作负荷跟踪文件并监视tempdb空间使用情况。
3. 执行索引维护操作,例如重建索引和监视tempdb空间。
4. 使用前面步骤中的空间使用值来预测总的工作负载使用情况;为计划的并发活动调整此值,然后设置tempdb的大小。 因此。

tempdb的最小尺寸建议如下:

   Envir. Size  DB Size (MB)  Log Size (MB)
   -----------  ------------  -------------
   Small                1024            256
   Medium               5120           1024
   Large               10024           2048

数据库设置

您可以通过以下方式进一步提高tempdb性能 禁用自动更新状态 ,这将节省您的tempdb一些工作。您也可以设置 将“自动创建统计信息”选项设置为“假” .

免责声明:应小心更改设置。根据放置在tempdb上的负载类型,更改设置可能会对系统性能产生不利影响。

要获得最佳tempdb性能,请遵循中提供的指南和建议 Optimizing tempdb Performance .

如何监控tempdb的使用?

运行 tempdb中磁盘空间不足 可以 造成重大干扰 在SQL Server生产环境中,可以阻止正在运行的应用程序完成操作。

你可以使用 sys.dm_db_file_space_usage 动态管理视图,用于监视tempdb文件中这些功能使用的磁盘空间。此外,要在会话或任务级别监视tempdb中的页面分配或释放活动,可以使用 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage 动态管理视图。

这些视图可用于标识使用大量tempdb磁盘空间的大型查询、临时表或表变量。还有几个计数器可用于监视tempdb中的可用空间以及使用tempdb的资源。

链接:

• 微软技术: Working with tempdb in SQL Server 2005
• 搜索sqlserver.com: SQL Server tempdb best practices increase performance
• MSDN文库: 优化tempdb性能
• 搜索sqlserver.com: Configure SQL Server 2005 TempDB for performance
• MSDN文库: Capacity Planning for tempdb
• MSDN文库: Troubleshooting Insufficient Disk Space in tempdb

根据我读到的,最好将tempdb放在它自己的物理磁盘(或阵列)上。为了获得最大的速度,您可以在一个RAID0阵列上安装。但是,如果其中一个磁盘出现故障,DB服务器将不工作。如果您需要更多的冗余,请将其放在RAID 10阵列上。真正的关键似乎是将它与其他数据库分离,因为tempdb中可能有很多活动。

您真正走了多远取决于服务器上的其他数据库,以及它们是否对临时表做了大量的工作,等等。

基于CPU或核心的数量,我没有听说过多个文件。

另请参阅此文章 SQL Server Transaction Log RAID 其中包含关于服务器布局的讨论,涉及到tempdb的一些问题。

我建议创建1/2到1/4*个内核之间的数据文件。 你应该把这些文件放在你拥有的最快的磁盘上

有关更多信息,请查看以下内容: SQL Server tempdb configuration