Tempdb数据库异常增长的优化方法

Tempdb数据库异常增长的优化方法
本期概述
z本文档适用于 K/3所有版本
z本文档针对SQL Server系统中Tempdb数据库的原理和作用，介绍限制Tempdb的数据库增长解决方法，如何规避和处理等。

通过学习本文档可了解如何合理设置Tempdb数据库的增长，避免出现消耗系统大量内存和只增长不释放的情况。

版本信息
z2011年11月28日 V1.0 编写人： 崔志佳
z2011年11月29日 V2.0 修改人： 李合雷
版权信息
z本文件使用须知
著作权人保留本文件的内容的解释权，并且仅将本文件内容提供给阁下个人使用。

对于内容中所含的版权和其他所有权声明，您应予以尊重并在其副本中予以保留。

您不得以任何方式修改、复制、公开展示、公布或分发这些内容或者以其他方式把它们用于任何公开或商业目的。

任何未经授权的使用都可能构成对版权、商标和其他法律权利的侵犯。

如果您不接受或违反上述约定，您使用本文件的授权将自动终止，同时您应立即销毁任何已下载或打印好的本文件内容。

著作权人对本文件内容可用性不附加任何形式的保证，也不保证本文件内容的绝对准确性和绝对完整性。

本文件中介绍的产品、技术、方案和配置等仅供您参考，且它们可能会随时变更，恕不另行通知。

本文件中的内容也可能已经过期，著作权人不承诺更新它们。

如需得到最新的技术信息和服务，您可向当地的金蝶业务联系人和合作伙伴进行咨询。

著作权声明著作权所有 2011金蝶软件（中国）有限公司。

所有权利均予保留。

目 录
1.Tempdb数据库简介 (3)
1.1 什么是Tempdb数据库 (3)
1.2 Tempdb数据库的特点 (3)
2.Tempdb数据库的优化 (3)
2.1 Tempdb数据库应用时的异常情况 (3)
2.2 Tempdb数据库异常增长的优化方法 (4)
2.2.1最简单的操作方法 (4)
2.2.2推荐的优化方法 (4)
1.Tempdb数据库简介
1.1 什么是Tempdb数据库
SQL Server系统的tempdb数据库是一个特殊的数据库，它和其它数据库最大的不同之处在于SQL Server每次重启的时候，它会被重建（注意不是恢复）。

Tempdb是一个临时数据库，它为所有的临时表、临时存储过程及其他临时操作提供存储空间。

Tempdb数据库由整个系统的所有数据库使用，不管用户使用哪个数据库，他们所建立的所有临时表和存储过程都存储在tempdb上。

SQL Server每次启动时，tempdb数据库被重新建立，当用户与SQL Server断开连接时，其临时表和存储过程自动被删除。

1.2 Tempdb数据库的特点
Tempdb数据库的特点：
1、总是使用简单恢复模式，日志不断的被截断，因为不需要备份，它记录的信息只能用来回滚事务，不能用于恢复事务；
2、某些数据库选项无法设置，例如离线（OFFLINE）、只读（READONLY）和校验（CHECKSUM）；
3、无法被删除；
4、无法为其创建快照，因为无法恢复。

2.Tempdb数据库的优化
SQL Server系统中将tempdb数据库作为工作空间使用，大量操作都会在tempdb数据库中完成，有时会出现tempdb消耗系统大量内存和只增长不释放的情况，需要对tempdb数据库进行优化。

2.1 Tempdb数据库应用时的异常情况
在SQL中创建表、排序、检查数据库完整性等应用操作时，可能造成tempdb占用大量的磁盘空间，导致出现程序和系统性能问题。

1、当在存储过程中创建一个临时表CREATE TABLE #Temp_1时，无论当前正在使用哪个数据库，SQL数据库引擎都会将这个表格创建在tempdb数据库中。

2、当对大型的结果集进行排序order by 或group by 或 union 或执行一个嵌套的
select查询时，如果数据库超过了系统内存容量，那么SQL数据库引擎就会在tempdb中创建工作表格。

3、当运行DBCC REINDEX或者向现有的表格中添加集群序列时，SQL数据库引擎同样会使用tempdb。

4、任何对大型的表格操作alter table命令都会在tempdb中占用大量的空间。

在理想状态下，SQL会在完成例如上述所列的指定操作后自动清理，并销毁这目标临时表格，但是很多情况下会产生错误，造成一直占用tempdb空间。

例如，程序创建了一个事务，但是却没能执行或重新运行，那么这些对象将遗留在tempdb中，而且，对大型的数据库运行DBCC CHECK时，它还会消耗掉大量的空间。

所以我们常常看到tempdb增长的很大，甚至占满所在磁盘空间，导致出现磁盘空间不足。

2.2 Tempdb数据库异常增长的优化方法
有很多方法来处理2.1节中所述的情形，有些方法操作简单，但不能彻底解决问题，有些方法推荐使用，但需要结合实际情况综合运用。

如下分别做介绍：
2.2.1最简单的操作方法
为tempdb数据库减肥的最简单方法就是重新启动SQL服务，即需要关闭SQL服务，再启动SQL数据库引擎，但是在运行重要的程序时，不允许重启SQL，这样做可能有难度；
另一个方法就是增加磁盘空间或增加磁盘用于放置tempdb，可进行如下的操作：
USE master
GO
ALTER DATABASE tempdb modify file (name = tempdev, filename ='NewDrive:Pathtempdb.mdf')
GO
ALTER DATABASE tempdb modify file (name = templog, filename ='NewDrive:Pathtemplog.ldf')
GO
上述两种方法操作较简单，但是所受限制较多，分别需要在满足允许频繁重启SQL或有多余的磁盘（或磁盘空间）的情况下才可以使用。

2.2.2推荐的优化方法
1、Tempdb数据库的初始大小设置：
将 tempdb 数据库文件的初始大小设置为合理的大小，以避免当需要更多空间时文件自
动扩展时，扩展得过于频繁，性能会受到影响。

2、设置 Tempdb 数据库按需自动扩展：
启用自动扩展，根据实际情况，设置合理的大小，设置按百分比或按MB增长，不要设置过小，频繁自动扩展将影响性能。

建议为tempdb文件设置文件增长量时遵循以下通用原则：
Tempdb 文件大小文件增长量
小于100 MB 10MB
100 MB至200 MB 20MB
大于200 MB 10%
使用存储过程sp_spaceused可查看数据库使用的空间，一般设置为“按百分比10%增长”、“不限制文件增长”。

3、创建足够多的tempdb数据文件：
使用多个文件可以减少tempdb存储争用并获得最大的可伸缩性。

但是，不可盲目创建过多的文件，导致性能降低并增加管理开销。

作为通用原则，为服务器中的每一个CPU创建一个数据文件；使每个数据文件的大小相同，这样可以优化比例填充的性能。

4、优化tempdb数据文件存放位置：
建议存放位置遵循以下规则：将 tempdb 数据库放在快速 I/O 子系统上以确保好的性能；在多个磁盘上条带化 tempdb 数据库以获得更好的性能；将 tempdb 数据库放在除用户数据库所使用的磁盘之外的磁盘上。

5、定期自动收缩tempdb数据库：
创建SQL维护计划，定期收缩数据库文件和日志文件。