认识Tempdb----配置最佳实践

23ee

一些问题需要改变配置，这里会整合所有的tempdb配置的最佳实践。主要包括：tempdb放置的位置，初始化大小及自动增长，配置多个文件。
Tempdb文件放置（File Placement）
众所周知的最佳实践是把数据、事务日志和tempdb分开放置，该建议的原委在于不同物理存储之间工作负载的类型的分离，例如分离物理磁盘。分离有助于管理，潜在问题更容易隔离。例如，把tempdb分离到它自己的逻辑磁盘，意味着你可以预先对其设置填充磁盘的大小，而不必担心其他文件的空间需求，实现越分离，越容易关联到指定数据库文件的逻辑磁盘性能。你最起码的目标是，一个逻辑磁盘用于数据文件，一个用于事务日志文件，一个用于tempdb数据文件。我更喜欢把tempdb数据文件放在它们自己的磁盘，因此可以设置它们填充磁盘的大小；把tempdb日志文件和用户数据库日志文件放在一起，这个地方应该有足够的可用磁盘空间用于任何日志文件意外的自动增长事件。
本地tempdb用于故障转移群集实例
SQL Server 2012之前，SQL Server的故障转移群集实例需要所有的数据库文件放在群集内的共享磁盘资源上。这是为了确保当发生故障的实例转移到群集的另一个节点时，所有其依赖的磁盘能够随之移动。重启之后，tempdb会重建，过去的数据都不会保留。对于一个群集实例，故障转移的过程涉及SQL Server的重启，所以，tempdb中没有东西需要移到其他节点，为何tempdb应该在一个共享磁盘没有技术上的理由。在SQL Server 2008 R2，你可以强制把tempdb放到本地磁盘，但是不支持；SQL Server 2012完全支持并很容易实施。你所需要做的就是使用ALTER DATABASE，如下：

• USE master ;  
• GO  
• ALTER DATABASE tempdb  
• MODIFY FILE (NAME = tempdev, FILENAME = 'D:\tempdbdata\tempdb.mdf') ;  
• GO  
• ALTER DATABASE tempdb  
• MODIFY FILE (NAME = templog, FILENAME = 'E:\tempdblogs\templog.ldf') ;  
• GO  
  

执行之后，你会看到如下信息：
Local directory 'D:\tempdbdata\tempdb.mdf'is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server service has read/write permission on it. The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
Local directory 'E:\tempdblogs\templog.ldf' is used for tempdb in a clustered server. This directory must exist on each cluster node and SQL Server service has read/write permission on it. The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
如此而已。你需要记住的是，所有群集节点上都要有相同的可用的路径，服务账号需要有读写权限，以便故障转移之后tempdb能够启动。
为何本地tempdb有利？把tempdb从共享磁盘移到本地磁盘有两方面的原因，且两个原因都与性能有关。第一个原因是成本效益，超快的固态存储能够在承受繁重tempdb使用的服务器上实现显著的性能提升。第二个原因是，I/O请求脱离共享磁盘可以改善共享存储的性能。
Tempdb初始化大小和自动增长（Autogrowth）
SQL Server的默认安装会创建一个tempdb数据库，数据文件8MB，事务日志文件1MB。对于很多SQL Server安装，这些文件大小不够，但配置了必要时自动增长10%。你可以在属性窗口看到tempdb的初始化大小：

虽然自动增长功能让我们在维护SQL Server安装时不必插手，但是未必是我们期望的，与因为在它们自动增长时，文件不能被使用，这会导致硬盘上文件的碎片，从而导致差劲的性能。下图显示了tempdb的大小，你可以看到，SQL Server实例重启后，tempdb的大小恢复为初始设置的大小。

tempdb应该设置多大？首先，除非是SQL Server Express，设置tempdb比默认的更大；其次，如果tempdb在自己的磁盘上，那么配置它为几乎填充磁盘的大小。这没有性能损失，你也不必再担心自动增长。
自动增长应该设置多大？如果tempdb在自己的磁盘并且配置几乎填充该磁盘，那么你不需要启用自动增长。对于任何数据库，最佳的办法是，为数据库设置合适的大小，以至于它们不需要自动增长，但是你仍然要配置它，以防万一需要。对于自动增长，使用固定增长量通常是一个更好的办法，因为这使得自动增长更加可预测。例如，自动增长10GB事务日志10%，会花费较长时间，并会影响数据库的可用性。Windows Server 2003及后来版本的Instant File Initialization (IFI)功能使数据文件的自动增长更加容易，但对于日志文件无效。如果服务账号是本地管理员或有Manage Volume Maintenance Tasks advanced user权限，SQL Server就会自动使用IFI。要给服务账号必要的权限，你可以运行gpedit.msc使用Local Group Policy Editor，如下图所示：

一旦IFI工作了，你可以把数据文件的自动增长设置为很大的固定量。根据数据库的大小，50MB或500MB都是不错的值，但设置任何大小几乎都是立即创建的，因此避免了停机。如果配置多个数据文件，想允许自动增长，可以考虑启用追踪标记1117，它会强制所有数据文件均匀地增长，因此不会打破文件之间的负载均衡。
然而，对于事务日志文件，你需要更加保守，使用的配置要能够平衡自动增长所花费的时间和额外空间的有用性。例如，以1MB自动增长是快速的，但需要频繁地做，这会变成瓶颈。对于事务日志，至少10MB的自动增长是个好的开始，但是你或许需要更高，来提供足够的空间，避免再次快速地自动增长。最佳选项是首先通过恰当地设置文件大小来避免自动增长。
配置多个Tempdb数据文件
多个数据文件有助于减少tempdb分配竞争问题，另外它可以增加tempdb的I/O吞吐量，特别是当它运行在非常快的存储上时。创建多个数据文件，它们都会在primary文件组，SQL Server使用一个成比例的填充算法（proportional fill algorithm）来确定使用哪个文件，来为每个请求创建对象。如果所有文件的大小完全一样，那么SQL Server会循环使用这些文件，把负载均等地分摊给各个文件。当然，这恰好是你想要的情况。
微软推荐文件和逻辑CPU的数量达到1：1，因为在巨大工作负载测试时，发现有利于性能，即使有成百数千个数据文件。然而，一个更加务实的办法是，文件和CPU 1:1的映射变成1:8，如果还能看到分配竞争或看到推入I/O子系统更难，那么就添加文件。