SQL Server tempdb优化提示新服务器？

I am planning a fresh installation of SQL Server 2005 on a new machine, which I have to order. I know that tempdb tuning is very important to the overall performance of the SQL Server instance.

我计划在新机器上全新安装SQL Server 2005，我必须订购。我知道tempdb调优对SQL Server实例的整体性能非常重要。

I've read that it's best practice to create as many tempdb files as you have CPU's (or cores?). Is that correct? Are there any other recommendations, e.g. for harddisk/RAID setup configuration I should pay attention to?

我已经读过，最好的做法是创建尽可能多的tempdb文件，就像拥有CPU（或核心？）一样。那是对的吗？是否有其他建议，例如对于硬盘/ RAID设置配置我应该注意什么？

Thanks!

谢谢！

5 个解决方案

#1

If I had to pick one person that I trust for advice on configuring a server, it would be Denny Cherry. He wrote an article explaining this.

如果我必须选择一个我信任的人来配置服务器，那就是Denny Cherry。他写了一篇文章解释这一点。

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

#2

Here is what I researched myself from a variety of sources.

以下是我从各种来源研究自己的内容。

In order to optimize tempdb performance pay attention to physical disk configuration, file configuration, as well as some settings within the database.

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

Physical disk configuration

物理磁盘配置

tempdb should reside on its own dedicated physical disks. This allows it to split I/O transactions from the remainder of volumes on the SQL Server.

tempdb应驻留在自己的专用物理磁盘上。这允许它从SQL Server上的其余卷拆分I / O事务。

To move tempdb to a new disk drive, use ALTER DATABASE. It is the key T-SQL command to perform this operation. Microsoft offers a good example in SQL Server 2005 Books Online. The article name is ALTER DATABASE (Transact-SQL) and the specific section is 'G. Moving tempdb to a new location.'

要将tempdb移动到新磁盘驱动器，请使用ALTER DATABASE。这是执行此操作的关键T-SQL命令。 Microsoft在SQL Server 2005联机丛书中提供了一个很好的示例。文章名称是ALTER DATABASE（Transact-SQL），具体部分是'G。将tempdb移动到新位置。'

The tempdb is a very high-write database. So, a RAID 5 array isn't the proper place for it. You should put the tempdb on either a RAID 0 or RAID 10 array as they're optimized for high-write applications. If you can afford additional RAID 0 or RAID 10 arrays for each physical database file for the tempdb, you'll get increased performance.

tempdb是一个非常高写的数据库。因此，RAID 5阵列不适合它。您应该将tempdb放在RAID 0或RAID 10阵列上，因为它们已针对高写入应用程序进行了优化。如果您可以为tempdb的每个物理数据库文件提供额外的RAID 0或RAID 10阵列，则可以提高性能。

Database files

数据库文件

You should have one physical file per CPU core in the server. So, if you have a dual-chip, dual-core server, you should have four physical database files for the tempdb database. When adding more database files, it's important to configure the files at the same initial size and with the same growth settings. That way, SQL Server will write the data across the files as evenly as possible.

服务器中每个CPU核心应该有一个物理文件。因此，如果您有双芯片双核服务器，则应该为tempdb数据库提供四个物理数据库文件。添加更多数据库文件时，以相同的初始大小和相同的增长设置配置文件非常重要。这样，SQL Server将尽可能均匀地跨文件写入数据。

Database file size

数据库文件大小

The size of the tempdb database can affect the performance of a system. For example, if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with autogrowing tempdb to the size required to support the workload every time you restart the instance of SQL Server. You can avoid this overhead by increasing the sizes of the tempdb data and log file.

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

Determining the appropriate size for tempdb in a production environment depends on many factors including the existing workload and the SQL Server features that are used. Microsoft recommends that you analyze the existing workload by performing the following tasks in a SQL Server test environment:

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

Set autogrow on for tempdb (in a test environment!).
为tempdb设置自动增长（在测试环境中！）。
Execute individual queries or workload trace files and monitor tempdb space use.
执行单个查询或工作负载跟踪文件并监视tempdb空间使用情况。
Execute index maintenance operations, such as rebuilding indexes and monitor tempdb space.
执行索引维护操作，例如重建索引和监视tempdb空间。
Use the space-use values from the previous steps to predict your total workload usage; adjust this value for projected concurrent activity, and then set the size of tempdb accordingly.
使用前面步骤中的空间使用值来预测总工作负载使用情况;为预计的并发活动调整此值，然后相应地设置tempdb的大小。

Minimum size recommendations for tempdb are as follows:

tempdb的最小大小建议如下：

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

Database settings

数据库设置

You can further increase tempdb performance by disabling the auto update stats, which will save your tempdb some work. You can also set the auto create statistics option to false.

您可以通过禁用自动更新统计信息来进一步提高tempdb性能，这将为您的tempdb节省一些工作。您还可以将auto create statistics选项设置为false。

Disclaimer: Settings should be changed with care. Depending on the kind of load you place on your tempdb, changing settings could adversely impact system performance.

免责声明：应谨慎更改设置。根据您放置在tempdb上的负载类型，更改设置可能会对系统性能产生负面影响。

To achieve optimal tempdb performance, follow the guidelines and recommendations provided in Optimizing tempdb Performance.

要获得最佳tempdb性能，请遵循优化tempdb性能中提供的准则和建议。

How to monitor tempdb usage?

如何监控tempdb的使用情况？

Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prevent applications that are running from completing operations.

tempdb中的磁盘空间不足可能会导致SQL Server生产环境中的严重中断，并可能阻止正在运行的应用程序完成操作。

You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used by these features in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views.

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

These views can be used to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space. There are also several counters that can be used to monitor the free space that is available in tempdb and also the resources that are using tempdb.

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

Links:

链接：

Microsoft Technet: Working with tempdb in SQL Server 2005
Microsoft Technet：在SQL Server 2005中使用tempdb
SearchSQLServer.com: SQL Server tempdb best practices increase performance
SearchSQLServer.com:SQL Server tempdb最佳实践提高了性能
MSDN library: Optimizing tempdb Performance
MSDN库：优化tempdb性能
SearchSQLServer.com: Configure SQL Server 2005 TempDB for performance
SearchSQLServer.com：配置SQL Server 2005 TempDB以提高性能
MSDN library: Capacity Planning for tempdb
MSDN库：tempdb的容量规划
MSDN library: Troubleshooting Insufficient Disk Space in tempdb
MSDN库：对tempdb中的磁盘空间不足进行故障排除

#3

From what I've read, it's best to put tempDB on it's own physical disk (or array). For maximum speed, you could put in on a raid 0 array. However, if one of the disks fail, the DB server will become inoperative. If you want more redundancy, put it on a raid 10 array. The real key seems to be separating it from the other databases because there can be so much activity in TempDB.

从我读过的内容来看，最好将tempDB放在它自己的物理磁盘（或阵列）上。为了获得最大速度，您可以放入raid 0阵列。但是，如果其中一个磁盘发生故障，则DB服务器将无法运行。如果您想要更多冗余，请将其放在raid 10阵列上。真正的关键似乎是将它与其他数据库分开，因为TempDB中可能有这么多活动。

How far you go really depends on the other databases on the server and whether they do a lot of work with Temporary tables, etc.

你走多远取决于服务器上的其他数据库以及它们是否在临时表等方面做了大量工作。

I haven't heard anything about multiple files based on the number of CPUs or cores.

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

#4

See also this posting SQL Server Transaction Log RAID which contains a discussion on server layout that touches on some of the issues around tempdb.

另请参阅此帖子SQL Server事务日志RAID，其中包含有关tempdb周围某些问题的服务器布局的讨论。

#5

i would recommend to create data files between 1/2 and 1/4 * number of cores. you should place these files on the fastest disk you have

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

for more information you can look here: SQL Server tempdb configuration

有关详细信息，请参阅此处：SQL Server tempdb配置

#1