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 tempdb go checkpoint go create table #t ( id int ) insert into #t select 1 drop table #t select Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null)
图1-1
实际上我们可以看出第一次和第二次执行的日志记录情况是相同的.
再来看下proc方式
proc code
use tempdb go checkpoint go create proc p_tstcache as create table #t ( id int ) insert into #t select 1 exec p_tstcache------第一次执行后观察日志记录如图1-2 select Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null) checkpoint go exec p_tstcache------第二次执行后观察日志记录信息如图1-3 select Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null)
图1-2
图1-3
可以看到当存储过程第二次执行时使用了缓存,日志记录数明显减少.使用完成后继续缓存。
缓存的益处
我们通过一个简单的压力测试来看下缓存的效果.
我们使用sqlquerystress开100的threads分别执行1000次看下batch,proc,proc中create后ddl的效果.(感兴趣的朋友可以观察相应的计数器 temp tables creation rate) 图1-4
注意:预先设定好tempdb数据日志文件大小,避免因为文件增长带来的测试偏差.
Code 1 batch
create table #t (id int)
Drop table #t
Code2 proc
Create proc p_tstcache As create table #t (id int)
Code 3 proc ddl after create script
create proc p_tstcache_ddl as create table #t (id int) Create index ix_id on #t(id) ----ddl after create
图1-4
可以看到因为缓存机制,在一些应用频繁创建临时表的实例中我们可以通过proc中完成临时表的构建从而缓解竞争.但应注意proc 临时表cache的限制.
关于表变量.
表变量的缓存机制与临时表相同(注: 表值参数不支持缓存)
表变量是不能创建索引的,但可以有个默认约束
表变量没有统计信息
表变量不支持事务
关于Proc中显式drop临时表.
微软声称proc中显式drop临时表并不受create后DDL的影响,但在现实生产环境中的情况,显式drop还是有一定影响的.况且proc执行完成后字自动缓存处理,没必要显式drop.
关于临时表/表变量缓存应用
通过上面的实例我们可以看到,如果要利用proc缓存cache是有不少限制的.现实生产环境中我们有可能使用到临时表(表变量)需要创建索引以提高查询效率.这时就需要我们来权衡.实际上高并发查询中使用数据量较大的临时表此时我们可以在创建临时表的脚本中一并完成索引的创建.但高并发的大临时表下的压力会是创建过程吗?
结语:有时候DBA的工作的确是手艺活,需要不停的打磨权衡.在业务稳定的情形下如果我们无法调整硬件环境,就需要我们打造业务所需的合理平衡.
