如何将 tempdb 分布在多个文件上? [英] How to spread tempdb over multiple files?

问题描述

这个博客 http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx 指出将 TempDB 传播到至少与 CORE 或 CPU 一样多的相同大小的文件中."

This blog http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx states that it is a good idea to "Spread TempDB across at least as many equal sized files as there are COREs or CPUs."

所以我的基本问题是我如何实际配置我的 SQL 服务器来做到这一点.所以我右键单击tempdb，选择属性，文件，然后为每个CPU添加一个文件?它如何知道它应该将 tempdb 分布在这些文件中?有我应该设置的标志吗?

So my basic question is how do i actually configure my SQL server to do that. So I right click the tempdb, choose properties, files and then add a file for each CPU? How does it know that it should spread the tempdb across these files? is there a flag I should set?

我是否误解了这篇文章?

Have I misunderstood the article?

推荐答案

只要您可以将额外的 TempDB 文件分布在不同的硬盘上，这个技巧就最好了.否则，创建不同临时表的不同线程将争夺同一个物理磁盘.

This tip is best as long as you can spread the additional TempDB files across different hard disks. Otherwise, the different threads which create different temp tables will be in contention for the same physical disk.

您确实可以按照您所说的去做，并且工作将自动分布在 TempDB 数据文件中.也可以这样编写脚本:

You can indeed do exactly what you say to do and the work will be automatically spread across the TempDB data files. This can also be scripted as such:

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = 'X:\tempdb3.mdf', SIZE = 256);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev4, FILENAME = 'Y:\tempdb4.mdf', SIZE = 256);
GO

为您提供三个额外的文件(即 4 个 CPU 内核和 4 个物理磁盘).

to get you three additional files (i.e. 4 CPU cores and 4 physical disks).

这篇关于如何将 tempdb 分布在多个文件上?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！