转载

SQL Server 利用游标解决Tempdb究极竞争-DBA-程序员需知

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_tasks WHERE session_id>50  View Code

图1-1

可以看到图中tempdb中系统页 2:1:53中发生典型的Pagelatch竞争.我们用dbcc page来看下页的情况如图2-2

Code

dbcc traceon(3604) go dbcc 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 tempdb checkpoint ---生产环境中慎用  dbcc checkdb(master) –这里采用dbcc checkdb探究worktables select 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

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

图2-4

以上叙述是否改变了你对游标的看法呢?程序员朋友们,当DBA告诉你使用tempdb太多时是否考虑换种方式使用tempdb, DBA朋友们,不要轻易告诉程序员们过度使用tempdb.

结语任何系统的愉快运转都是基于某种状态的平衡.我们需要在复杂环境中的性能瓶颈,资源消耗,相应时间等等因素中找到平衡点.什么样的平衡点? It depends :)

认为有收获的同学请点赞.

正文到此结束

所属分类：编程技术

本文标签： 程序员 ip CEO 朋友们 db 时间 ACE tab 数据删除测试 sql ask 游标压力 SQL Server Select description 解析
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

SQL Server 利用游标解决Tempdb究极竞争-DBA-程序员需知

热门推荐

相关文章

说给你听

本文目录

随机标签

书籍教程

近期评论

网站信息

其他链接

关于本站

问题交流