当前位置：文江博客话题详情

新服务器的 SQL Server tempdb 优化技巧？

发布于 2024-07-10 08:13:24 字数 244 浏览 9 评论 0 原文

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

我读到，最佳实践是创建与 CPU（或核心？）一样多的 tempdb 文件。那是对的吗？还有其他建议吗，例如我应该注意的硬盘/RAID 设置配置？

谢谢！

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦途 2024-07-17 08:13:24

这是我自己从各种来源进行研究的内容。

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

物理磁盘配置

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

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

tempdb 是一个写入量非常大的数据库。因此，RAID 5 阵列不适合放置它。您应该将 tempdb 放在 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 测试环境中执行以下任务来分析现有工作负载：

为 tempdb 设置自动增长（在测试环境中！）。
执行单个查询或工作负载跟踪文件并监视 tempdb 空间使用情况。
执行索引维护操作，例如重建索引和监视tempdb空间。
使用前面步骤中的空间使用值来预测您的总工作负载使用情况；根据预计的并发活动调整此值，然后设置 tempdb 的大小
因此。

tempdb 的最小大小建议如下：

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

数据库设置

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

免责声明：更改设置时应小心。根据您对 tempdb 施加的负载类型，更改设置可能会对系统性能产生不利影响。

要实现最佳 tempdb 性能，请遵循优化 tempdb 性能。

如何监控 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 的资源。

链接：

Microsoft Technet：在 SQL 中使用 tempdb Server 2005
SearchSQLServer.com：SQL Server tempdb 最佳实践可提高性能
MSDN 库：优化 tempdb 性能
SearchSQLServer.com：< a href="http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1180698,00.html" rel="noreferrer">配置 SQL Server 2005 TempDB 以提高性能
MSDN 库：tempdb 容量规划
MSDN 库：对 tempdb 中磁盘空间不足进行故障排除

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.

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.

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.'

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.

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.

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.

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:

Set autogrow on for tempdb (in a test environment!).
Execute individual queries or workload trace files and monitor tempdb space use.
Execute index maintenance operations, such as rebuilding indexes and monitor tempdb space.
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.

Minimum size recommendations for tempdb are as follows:

   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.

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.

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

How to monitor tempdb usage?

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.

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.

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.

Links:

Microsoft Technet: Working with tempdb in SQL Server 2005
SearchSQLServer.com: SQL Server tempdb best practices increase performance
MSDN library: Optimizing tempdb Performance
SearchSQLServer.com: Configure SQL Server 2005 TempDB for performance
MSDN library: Capacity Planning for tempdb
MSDN library: Troubleshooting Insufficient Disk Space in tempdb

回复收藏 0 原文

温馨耳语 2024-07-17 08:13:24

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

你能走多远实际上取决于服务器上的其他数据库以及它们是否对临时表等进行了大量工作。

我还没有听说过有关基于 CPU 或核心数量的多个文件的任何信息。