欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server使用游标处理Tempdb究极竞争-DBA问题-程序员必知

程序员文章站 2022-11-20 12:51:03
sql server tempdb分配竞争算是dba老生常谈的问题了,几乎现在所有的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

SQL Server使用游标处理Tempdb究极竞争-DBA问题-程序员必知

                                                     图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

SQL Server使用游标处理Tempdb究极竞争-DBA问题-程序员必知

                                              图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)

得到的tempdb log如图 2-1

SQL Server使用游标处理Tempdb究极竞争-DBA问题-程序员必知

                                                 图2-1

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

code

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

SQL Server使用游标处理Tempdb究极竞争-DBA问题-程序员必知

                                                   图2-2

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

code

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

SQL Server使用游标处理Tempdb究极竞争-DBA问题-程序员必知

                                           图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

 SQL Server使用游标处理Tempdb究极竞争-DBA问题-程序员必知

                                                    图2-4

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

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

ps:sql server 数据库 ' ' 附近有语法错误

昨天做项目时候,遇到标题的问题,代码跟踪把sql 语句 复制出来在数据库执行不了,然后重新写个一模一样的,然后在 赋值到代码中,还是同样的错误,就是不知道哪里出现了错误,最后 把 sql 语句写成最简单的 select * from tab  还是同样的错误。

然后 ,然后就不会了。

最后在这个语句写同样的语句,最后发现问题了,新写的sql 语句的 select 变 颜色了,而之前的赋值出来的  select 和 字段 表名的颜色一样,证明系统 不承认它是关键字,把这个select 删掉在 这个位置上重新写,还是同样的错误,最后发现原来在 这个select 前面有个全角的 空格,全角空格真的是用肉眼看不出来啊,恍然大悟,才知道  '   '    附近有语法错误 ,意思是  空格  有语法错误,证明不是 sql server 支持的 空格格式。

这个问题百度了,也没解决,希望 可以帮到其他人,又不是特别难的东西,但是找到问题还是很浪费时间。