SQL Server tempdb 初始大小对数据库运行性能的影响

zhuyiwen

SQL Server 版本：2008 R2.

tempdb 在 SQL Server 实例安装时，其初始大小为 8MB，增长设置为 10%。那么也就是说，当其大小超过 8MB 时会自动增长为 8.8MB 以满足 tempdb 的需求。

那么，有没有必要更改 tempdb 的初始大小？tempdb 的大小对整个 SQL Server 实例运行有无很大的影响呢？


答案是有。

最近，监视某医院的 SQL Server 数据库的运行，发现 tempdb.mdf 文件的实时大小达到了 513MB。通过跟踪发现一个奇怪的现象，SELECT GETDATE()、SET NO_BROWSETABLE ON 语句的执行时间(Duration)偶尔达到 2000 以上，正常的话应该是 0。遍查度娘，未找到一个解释，非常郁闷。

于是，更改 tempdb 的初始大小为 1000MB，其后一周之内的跟踪，再没有发现上述奇怪的现象。

现象解析：

......

SQL语句：

1. USE master
   
2. GO
   
3. 
   
4. ALTER DATABASE tempdb
   
5. MODIFY FILE
   
6.    (NAME = tempdev, SIZE = 1000MB)
   
7. GO

复制代码

tmtony

期待更新！

zhuyiwen

tempdb系统数据库和用户数据库非常相似。主要的区别是，SQL服务器关闭后，tempdb中的数据不会持久保持。

每一次SQL Server重新启动时，tempdb会从model数据库复制。它从model数据库中继承了某些数据库配置选项，如ALLOW_SNAPSHOT_ISOLATION。

tempdb中只有一个数据文件组和一个日志文件组。您可以配置文件的大小。当启用自动增长（这是默认的），文件将增长到磁盘卷已满为止。当服务器重启时，tempdb文件大小会重置为配置的大小（默认为8 MB）。自动增长是tempdb数据库临时（不同于其他类型的数据库）空间。SQL服务器重新启动时，重置tempdb数据库。

用户可以明确的在tempdb数据库中创建和使用数据表。事务可以用于修改表中的数据，事务可以回滚。然而，没有必要重做它们，因为tempdb的内容不会在重新启动SQL Server中保持。由于事务日志不需要刷新，事务提交的速度比用户数据库更快。在用户数据库，事务具有ACID属性，即原子性、并发性、隔离和持久性。而在tempdb数据库中，事务将失去持久性属性。

SQL Server在tempdb数据库中存储内部对象，如一个查询的中间结果。tempdb数据库中大部分内部运作不生成日志记录，因为不需要回滚。因此，这些操作速度更快。

tempdb数据库不允许自动收缩。数据库的文件收缩和收缩的能力是有限的。这是因为许多SQL Server存储在tempdb中隐藏的对象，不能通过收缩操作移动。

zhuyiwen

tempdb 数据库的空间使用

以下类型的对象可以占用tempdb数据库的空间:

• 内部对象
• 版本存储
• 用户对象
  

内部对象:

• 存储排序的中间结果。
• 存储哈希联接和哈希聚合的中间结果。
• 存储XML变量或其它大对象（LOB）数据类型的变量。LOB数据类型包括所有的大对象类型：text、image、ntext、varchar(max)、varbinary(max)，以及其它。
• 用于需要池的查询存储中间结果。
• 用于键集游标存储的键。
• 用于静态游标存储的查询结果。
• 用于Broker服务存储传送的消息。
• 用于INSTEAD OF触发器存储中间处理数据。