SQL Server Tempdb技术手册.docx

此文档收集于网络，如有侵权，请联系网站删除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语句的监控下执行并查看执行效果的。1、临时表create table #t1(c1 int primary key identity(1,1),c2 nvarchar(10) default hello t1)create table #t2(c1 int primary key identity(1,1),c2 int,c3 nvarchar(10) default hello t2,c4 int default rand(datepart(ms,getdate()*10000)insert #t1 default valuesset nocount ondeclare i intset i=0while i20begin insert #t1(c2) select c2 from #t1 set i=i+1end -为了要让随机数乱，所以逐条增加.while i2013/01/01结论：直接从实体表中查数据不使用Tempdb。create view ybxx2 asselect a.cjdw,a.gzzxcode,a.ybjth,a.ybrq,a.yblsh,a.dh,a.zbcj,a.zlcj,a.khcode ,a.khmc,b.cjid,b.cdmc,b.id from ybxj_ybxx a,ybxj_cdxx b where a.ybjth=b.ybjth and a.ybrq=b.ybrq and a.lsh=b.lsh结论：查询连接视图时使用Tempdb。4、聚集函数select MAX(ybrq) from ybxj_ybxxgoselect SUM(jldcm) from ybxj_ybxx结论：执行聚集函数并不使用Tempdb。5、distinctselect distinct ybrq from ybxj_ybxxCtrl+L结果如下：结论：Distinct 没有使用Tempdb，此结论与前面资料提到的使用Tempdb情况不一致。6、unionselect apptype,rightsort,rightname from sys_userrightunionselect apptype,rightsort,rightname from sys_groupright结论：数据表太小，没有消耗Tempdbselect ybjth,ybrq,lsh from ybxj_cdxxunionselect ybjth,ybrq,lsh from ybxj_ybxx结论：数据量增大后，使用Tempdb的内部对象空间。select ybjth,ybrq,lsh from ybxj_cdxxunion allselect ybjth,ybrq,lsh from ybxj_ybxx结论：union all两头都使用的是实体表，不需要进行数据重组，所以不使用Tempdb空间。8、内存与Tempdbselect * from sys_appdb order by apptype 数据表大小：0.031Mselect * from sys_oplog order by apptype 数据表大小：152M从前面的测试已经看出order by 消耗Tempdb，这次测试先找一个小于Tempdb初始大小的小表进行测试，观察Tempdb没有明显变化，又找了个大于Tempdb初始大小的表，结果Tempdb仍然没有变化。结论：1）如果数据量超过了系统内存容量，SQL数据库引擎就会在Tempdb中创建工作表格，否则系统优先使用系统内存容量。2）在正式服务器测试后发现：即使在系统内存足够大的情况下，排序操作仍然会使用Tempdb数据库。9、重建索引-删除表中原有索引后测试重建索引-drop index ybxj_ybxx.ybxj_ybxx1create index ybxj_ybxx1 on ybxj_ybxx(yblsh)-初始大小：192.00.00.06848.01152.0-执行中：192.02752.00.04096.01152.0-执行结束：192.00.00.06848.01152.0结论：当数据量大时消耗系统内存和Tempdb内部对象空间，创建结束释放空间。alter index ybxj_ybxx1 on ybxj_ybxx rebuild with (online=on)结论：没有发现使用Tempdb情况。create index ybxj_ybxx1 on ybxj_ybxx(yblsh) with (drop_existing = on)结论：没有发现使用Tempdb情况。10、触发器create table t1(a1 int not null,a2 int default rand(datepart(ms,getdate()*10000 not null)create table t2(a1 int null,a2 int null)create trigger insertt1 on t1 for insert asbegin declare i int set i = 0 while i A.Age),0)+1,FirstName,Age from Person A order by Rank by Age SELECT RANK() OVER(PARTITION BY Gender ORDER BY Age) AS Partition by Gender,FirstName, Age, Gender FROM Person输出为Partition by Gender FirstName Age Gender- - - -1 Doris 6 F2 Mary 11 F2 Sherry 11 F4 Sue 29 F1 Larry 5 M2 George 6 M3 Sam 17 M4 Ted 23 M4 Marty 23 M6 Frank 38 M7 John 40 M可以看到,按性别分组了,每个性别分组里,继续是用了rank( )函数3.DENSE_RANK( )函数SELECT DENSE_RANK() OVER (ORDER BY Age) AS Dense Rank by Age, FirstName, AgeFROM Person输出结果为:Dense Rank by Age FirstName Age- - -1 Larry 52 Doris 62 George 63 Mary 113 Sherry 114 Sam 175 Ted 235 Marty 236 Sue 297 Frank 388 John 40看到了么,和rank函数区别是,顺序始终是连续的,Doris 和George同年,都是排第2位,但之后的mary不象rank函数那样排第4,而是排第3位了4.ntile( )函数SELECT FirstName,Age,NTILE(3) OVER (ORDER BY Age) AS Age GroupsFROM Person输出结果：FirstName Age Age Groups- - -Larry 5 1Doris 6 1George 6 1Mary 11 1Sherry 11 2Sam 17 2Ted 23 2Marty 23 2Sue 29 3Frank 38 3John 40 3这个函数按照ntile(n)中的N,把记录强制分成多少段,11条记录现在分成3段了,lary到mary是第1段,sherry到maty是第2段,sue到john是第3段了。附录五、T-SQL 查询优化之聚合SQL SERVER 两种聚合， STREAM AGGREGATE，HASH AGGREGATE.哈希和排序都需要内存，哈希小表要内存，排序如果内存不足，需要临时数据库。1，STREAM AGGREGATE返回单一数值，没有 GROUP BY，总会使用它；如果有 GROUP BY，排序使用排序操作符或索引。2，HASH AGGREGATE一般应用于比较大的表，它不需要排序，在内存有一个 HASH KEYS BUILD。如果数据已经排序，计划有可能选择 STREAM AGGREGATE。因此，对于没有排序的数据，将会出现两种情况：SORT AND STREAM AGGREGATE，或HASH AGGREGATE AND SORT优化查询器会根据开销大小，决定采用那个。如果查询使用 DISTINCT，会出现三种情形：1，STREAM AGGREGATE，如果数据已经排序；2，HASH AGGREGATE，如果没有排序并且表比较大，或3，DISTINCT SORT。附录六、SQL Server 触发器触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。 什么是触发器 触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server 2005中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句。 DML触发器分为： 1、 after触发器（之后触发） a、 inser