Sql Server tempdb原理-缓存机制解析实践

Tempdb就像Sqlserver的临时仓库,各式各样的对象,数据在里面进行频繁计算,操作.大量的操作使得tempdb可能面临很大压力,tempdb中缓存的设计就是为了缓解这些压力.这次就为大家介绍下tempdb的缓存机制.

在介绍缓存机制前,先简单了解一下TempDB对象

一般我们把tempdb对象分为两种类型用户对象和内部对象.用户对象指通过显式T-sql来创造的对象(如临时表),内部对象指通过隐式T-sql创建的对象(Worktables)

注:在引入版本控制后,也可以此单独分类(DMV sys.dm_db_file_space_usage中单独列出)

用户对象

临时表

表变量(包含表值函数返回值及表值参数)

临时存储过程

用户自定义对象

用户在线(Online)创建索引空间

内部对象

Sorts(排序溢出)

Worktables(checkdb,游标,Merge joins,假脱机,并行查询交换溢出,LOB对象等)

Workfiles(hash join 溢出)

Version store(版本行控制)

查看对象使用情况

当tempdb数据文件很大或者有异常时,我们可以查看相应的使用情况.如何查看,沄剑的Blog中如何查看某个查询用了多少TempDB空间有详细脚本.

Tempdb缓存机制

Tempdb中的众多对象缓存机制不尽相同,其中一些操作(如Sort)采用内部的机制,对用户是不可控的,这里我们主要介绍常用对象临时表/表变量(也是经常引发问题)的缓存机制.

临时表缓存机制(#t)

只有使用存储过程,触发器,Functions才能缓存

以plan cache的形式缓存一个IAM页和一个DatePage页

禁止Create后使用DDL操作

禁止命名约束

可以看出临时表的缓存是以PRoc执行计划缓存的形式实现的.所以batch,动态sql是无法缓存的.值得注意的是既然是执行计划缓存,我们就不能在proc中加WITH RECOMPILE关键字.

注意:缓存对象时局部临时表,不包括全局临时表.

我们通过一个简单的实例来分析下缓存实现.

首先我们来看下一般batch的操作(非缓存)执行两次,观察日志情况如图1-1

batch create code

use tempdbgocheckpointgocreate table #t(id int)insert into #t select 1drop table #tselect Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null)