你的位置:首页 > 数据库

[数据库]SQL Server 利用游标解决Tempdb究极竞争


SQL Server tempdb分配竞争算是DBA老生常谈的问题了,几乎现在所有的DBA都知道多建几个文件来解决/缓解问题.但是深层次的的竞争依旧不可避免.这里给大家剖析下游标在tempdb中的特点使其在一定场景下替代临时表/表变量对象,解决深层次的tempdb竞争问题.

 

在抛出这个不可避免的问题之前我们先简要看下什么是tempdb竞争.

我们拿SQL Server创建一个临时表的过程来描述

1 在系统表中创建表的条目(系统数据页中)

2 分配一个IAM页并找到一个混合区在PFS页中标记

3 分配一个数据页(查看SGAM页,查看PFS页后并更新,更新IAM页)

4 表记录记录到系统表中

 

从上述过程可以看出创建一个简单临时表需要查找,更新一系列的系统表/系统数据页,且当使用完删除临时表时上述操作逆向进行.索引相应的创建/销毁一旦大量并发,内部竞争也就产生了.虽然tempdb的缓存策略一定程度可以缓解相应创建过程的IAM,数据页分配, Sql Server tempdb原理-缓存机制解析实践,但竞争依旧.

可以看到SGAM,PFS等系统页是表创建过程的必经之路,他的分配竞争也就十分明显了.这也就是为什么采用多个数据文件,让系统页(包含系统表)在分散在多个数据文件中的以减轻分配竞争的压力原因.

 

到此也许大家都改猜到了终极问题是什么了,就是对系统对象的操作.连SQL Server大牛Paul Randal都为之头疼的问题.

具体哪些对象呢,我们可以简单测试捕捉下如图1-1

使用SQLQUERYSTRESS捕捉

Code

create table #t(id int,str1 varchar(10))---SSMS中开启会话捕捉SELECT resource_description,* FROM SYS.dm_os_waiting_tasksWHERE session_id>50

View Code

 

dbcc traceon(3604)godbcc page(2,1,53,1)select OBJECT_NAME(7)----the object_id from dbcc page

View Code

 

                                              图2-2

可以看到在系统对象sysallocunits处发生了竞争,当然还有许多其他的系统对象,感兴趣的朋友自行捕捉.

 

大量的针对系统对象表的操作使得tempdb其吞吐难以得到进一步的提升,这个是由系统本身的运作方式引发的,当然面对如此巨量的tempdb使用,就没有别的方式了吗?这时我不能给肯定的答案,但可以给大家一个IT界的流行答案:It depends :)

在介绍游标前,先简单说下面对tempdb竞争中针对系统表竞争的常规处理方式

1 减小针对系统对象的事务大小(如select * into #的使用)

2 减小tempdb的使用频次(看似废话,但实际中的确可能用不到这么多)

3 临时对象中少使用约束造成额外的系统对象负担.

 

好了接下来该说游标了,貌似八竿子打不着的事儿,实际上的确如此,我们只是利用游标的特性在极其特殊的场景下来解决相应问题.

 

也许你已经猜到了,游标是使用tempdb的,归类到worktables中,使用worktables的对象如游标,dbcc checkdb,merge join,exchange spill等等.worktables是tempdb中一种普遍而又特殊的使用方式,他只在SQL Server内部中应用,给它定义为”temporary rowsets”,他的object id是负的,且无需系统表的记录!

 

我们来简单验证说明下

code

use tempdbcheckpoint ---生产环境中慎用dbcc checkdb(master) –这里采用dbcc checkdb探究worktablesselect Description,* from fn_dblog(null,null)

View Code

得到的tempdb Log如图 2-1

                                                 图2-1

我们用dbcc page分析此页 可以看到这个是个IAM页如图2-2

code

dbcc traceon(3604)dbcc page(2,4,104,3)

 

 

                                                  图2-2

我们进而分析IAM分配的数据页,发现他就是一个简单的数据页,不属于任何系统对象如图2-3

Code

dbcc traceon(3604)dbcc page(2,5,104,3)

 

                                           图2-3

OK,至此联想起游标同样适用worktables,我们可能联想到了一些游标适用的场景居然还可以帮助tempdb缓解竞争.至于何种场景?It depends,大家自己去联想吧,但tempdb遇到相应竞争时我是否可以采用?朋友们自己抉择吧.

最后看图说话如图2-4

Code

--cursordeclare @cur cursor set @cur =cursor For select * from tt--temp tablecreate table #tt (id int)insert into #tt select * from tt

 

 

丽江旅游线路大全丽江旅游报价价格丽江旅游团购优惠深圳去丽江旅游报价跟团去丽江旅游多少钱暑假想在野外露营,请问要野外露营要注意什么? 在野外露营遇到意外怎么办? 暑假出门注意事项有哪些? 暑假学生票什么时候开始可以买? 2015五一两江四湖门票价格?五一到桂林两江四湖门票多少钱? 桂林两江四湖三月三门票多少钱?两江四湖三月三门票价格? 两江四湖三月三有演出吗?桂林两江四湖三月三演出时间? 三月三两江四湖夜游时间?夜游桂林两江四湖几点开始? 桂林阳朔有什么好玩的?阳朔各景点门票价格 三亚鹿回头公园好玩吗?国庆去鹿回头公园怎么样? 桂林各个市县好玩的地方推荐 2015厦门中秋节有什么活动?2015中国厦门中秋旅游嘉年华 长隆欢乐世界跨年有什么活动?广州长隆欢乐世界圣诞优惠? 长隆欢乐世界冬天有什么优惠?广州长隆欢乐世界2015圣诞活动? 长隆欢乐世界免费吃自助吗?广州长隆欢乐世界自助什么餐? 广州长隆野生动物园有专车吗?香江野生动物园在哪里上车? IDT71T75702S75PFG8 Datasheet IDT71T75702S75PFG8 Datasheet IDT71T75702S75PFGI Datasheet IDT71T75702S75PFGI Datasheet IDT71T75702S75PFI Datasheet IDT71T75702S75PFI Datasheet 过年旅游去哪里 过年旅游去哪里 过年旅游去哪里 公司年会表演节目 公司年会表演节目 公司年会表演节目 泡温泉泳衣 泡温泉泳衣 泡温泉泳衣