SQL Server Tempdb技术手册汇总.docxVIP

SQL Server Tempdb技术手册一、基础介绍Tempdb数据库类似于操作系统的分页文件。它用于存储用户创建的临时对象、数据库引擎需要的临时对象和行版本信息。Tempdb数据库是在每次启动SQL Server时创建的，当SQL Server停止运行时，该数据库将被重新创建为其原始大小，所以不必对其进行数据备份。SQL在对Tempdb数据库中的对象做数据修改时，只写入最少的信息到日志文件中，结合Tempdb数据库每次重建的特性，SQL不支持对该数据库的还原及日志还原操作。二、特别之处1、每次重启都会删除,然后从model系统数据库中复制一份，并继承某些model数据库的属性，然而有些属性并不能继承，比如“恢复模式”属性，无论model数据库是什么恢复模式，Tempdb只能是“简单恢复模式”，以便始终自动回收 Tempdb 日志空间，且我们尝试修改其恢复模式时，也会抛出错误如下，提示你不能修改。备注：数据库恢复模式参见附录一。2、SQL Server 2005以上版本默认Tempdb初始大小为8MB只能有一个文件组放置数据库文件，不能创建和使用新的文件组。也就是说Tempdb只能使用默认的PRIMARY文件组，但可以为Tempdb创建多个数据文件和日志文件。3、用户可以在Tempdb数据库创建和使用表，也可以使用事务和回滚事务。在用户数据库中，事务具有ACID属性，但是在Tempdb的事务中，并不具有。备注：SQL Server事务的ACID属性详见附录二。4、SQL Server使用Tempdb存储一些内部对象，比如查询的中间结果集。大部分的这些内部操作不会生成日志记录，因为它们不需要回滚，所以这些操作是非常快的。5、在Tempdb上也存在一些有别于用户数据库的限制。例如上面提到的修改恢复模型、日志收缩、DBCC check等。三、空间使用SQL中有三种类型的数据会存放在Tempdb数据库中，分别是内部对象、版本存储信息、用户对象信息，关于这三种类型的具体介绍，参加附录三。1、内部对象：1）排序(order by 、group by、row_number、rank、dense_rank、ntile等)。详见附录四SQL Server2005四种排序函数。2）hash join(inner join、left outer join、right outer join、full outer join等)。3）hash aggregate(dinstinct等)：哈希聚合，可以通过执行SQL语句的执行计划(Ctrl+L)查看是否使用了聚合操作。关于聚合方面的知识，参加附录五。4）Instead of触发器：目前我们使用的较少。关于instead of触发器和after/for 触发器介绍，详见附录六。5）cursor结果：游标。6）DBCC check：数据库检查语句。7）Service Broker：数据库邮件、事件通知、查询通知等。8）大型的数据，如：XML、text、image、varchar(max)等。9）大型查询或中间查询结果，如：UNION，嵌套子查询等。2、版本存放区:1）事务的snapshot隔离级别或者read committed snapshot。2）多数据结果集multiple active result sets。3）在线生成索引。4）after 触发器。3、自定义对象:1）Global：全局变量、全局临时表。2）local temporary table：临时表。3）数据表变量。四、容量监控可以使用下面的SQL语句查看Tempdb的动态使用情况。SELECTSUM (user_object_reserved_page_count)*8.0/1024 as [用户对象(mb)],SUM (internal_object_reserved_page_count)*8.0/1024 as [内部对象(mb)],SUM (version_store_reserved_page_count)*8.0/1024 as [记录版本空间(mb)],SUM (unallocated_extent_page_count)*8.0/1024 as [可用空间(mb)],SUM (mixed_extent_page_count)*8.0/1024 as [mixedextent(mb)]FROM sys.dm_db_file_space_usage说明：上述语句是从系统数据表中查找SQL系统记录的对应各对象所占数据页，所以：数据页*8（kb）/1024（kb/mb）=MB有些网络资料直接使用数据页数除以128(128=1024/8)，容易让人困惑。五、应用测试说明：以下测试均是在上述容量监控T-SQL语句的监控下执行并查看