八、IO优化（6）优化tempdb性能

一、tempdb简介

1. 概念

　　tempdb是一个系统数据库，可供连接到SQL Server实例的所有用户使用。

　　每次启动 SQL Server 时都会重新创建 tempdb，从而在系统启动时总是保持一个干净的数据库副本。在断开联接时会自动删除临时表和存储过程，并且在系统关闭后没有活动连接。因此 tempdb 中不会有什么内容从一个 SQL Server 会话保存到另一个会话。

　　不允许对 tempdb 进行备份和还原操作。

2. tempdb保存的对象

（1）显式创建的用户对象

　　这些对象由用户显式创建。存在于用户会话的作用域中，也可位于创建对象所用的例程（存储过程、触发器或用户定义函数）的作用域中。

　　包括：表和索引（系统的，或用户定义的）、临时表和索引（全局的，或局部的）、表变量、表值函数中返回的表。

（2）数据库引擎创建的内部对象

　　这些内部对象由数据库引擎根据需要而创建，用于处理SQL Server语句。可以在语句的作用域中创建和删除。每个内部对象至少使用9个页面：1个IAM页，1个连续8页的区。

　　包括：用于游标或假脱机操作以及临时大型对象（LOB）存储的工作表；用于HASH连接或HASH聚合操作的工作表；用于创建或重新生成索引等操作（如果指定了SORT_IN_TEMPDB）的中间排序结果，或者某些GROUP BY、ORDER BY或UNION查询的中间排序结果。

（3）版本存储区

　　版本存储区是数据页的集合，它包含支持使用行版本控制的功能所需的数据行，主要用来支持快照事务隔离级别，以及一些其它提高数据库并发性能的新功能。主要分为2类：公用版本存储区、联机索引生成版本存储区。

　　包括：由使用快照隔离级别或已提交隔离级别（基于行版本控制）的数据库中的数据修改事务生成的行版本；由数据修改事务为实现联机索引操作、多个活动的结果集（MARS）以及AFTER触发器等功能而生成的行版本。

3. tempdb 的性能提高

　　在 SQL Server 中，tempdb 性能以下列方式进行提高：

（1）可能缓存临时表和表变量。缓存允许删除和创建临时对象的操作非常快速地执行，并减少页分配的争用问题。

（2）分配页闩锁协议得到改善。从而减少使用的 UP（更新）闩锁数。

（3）减少了 tempdb 的日志开销。从而降低了 tempdb 日志文件上的磁盘 I/O 带宽占用。

（4）在 tempdb 中分配混合页的算法得到改善。

二、监视tempdb大小

　　tempdb 数据库的大小和物理位置可能会影响系统的性能。tempdb 的大小可以影响系统性能。例如，如果 tempdb 的大小太小，则每次启动 SQL Server 时，系统处理可能忙于数据库的自动增长，而不能支持工作负荷要求。可以通过增加 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 的资源。

http://technet.microsoft.com/zh-cn/library/ms176029(v=sql.105).aspx

　　每次创建 tempdb 时都要使用文件大小和文件增长参数的值。例如，如果您将 tempdb 数据文件的大小增加到 20 MB 并将文件增量增加到 15%，则新的值将立即生效。如果后续事务活动使 tempdb 的大小增大，则每次重新启动 SQL Server 实例时，数据文件的大小都将返回到 20 MB。

三、tempdb的通用原则

1. 文件数量

　　根据需要创建足够多的文件以使磁盘宽度最大化。使用多个文件可以减少 tempdb 存储争用并获得更大的可伸缩性。但是，请勿创建过多的文件，因为此操作可能降低性能并增加管理开销。

　　作为通用原则，为服务器中的每一个 CPU 创建一个数据文件（用于解释任何关联掩码设置），然后根据需要上下调整文件的数量。

2. 恢复模式

　　将 tempdb 的恢复模式设置为 SIMPLE。此模式自动回收日志空间以保持较小的空间要求。

3. 自动增长

　　通过将文件大小设置为足够容纳环境中典型工作负荷的值来预分配所有 tempdb 文件的空间。这可以避免 tempdb 因扩展得过于频繁而影响性能。

本文结语：

　　规划tempdb数据库的大小，根据CPU数量使用多个文件。