SQL Server使用游標(biāo)處理Tempdb究極競爭-DBA問題-程序員必知

SQL Server tempdb分配競爭算是DBA老生常談的問題了,幾乎現(xiàn)在所有的DBA都知道多建幾個(gè)文件來解決/緩解問題.但是深層次的的競爭依舊不可避免.這里給大家剖析下游標(biāo)在tempdb中的特點(diǎn)使其在一定場景下替代臨時(shí)表/表變量對象,解決深層次的tempdb競爭問題.

在拋出這個(gè)不可避免的問題之前我們先簡要看下什么是tempdb競爭.

我們拿SQL Server創(chuàng)建一個(gè)臨時(shí)表的過程來描述

1 在系統(tǒng)表中創(chuàng)建表的條目(系統(tǒng)數(shù)據(jù)頁中)

2 分配一個(gè)IAM頁并找到一個(gè)混合區(qū)在PFS頁中標(biāo)記

3 分配一個(gè)數(shù)據(jù)頁(查看SGAM頁,查看PFS頁后并更新,更新IAM頁)

4 表記錄記錄到系統(tǒng)表中

從上述過程可以看出創(chuàng)建一個(gè)簡單臨時(shí)表需要查找,更新一系列的系統(tǒng)表/系統(tǒng)數(shù)據(jù)頁,且當(dāng)使用完刪除臨時(shí)表時(shí)上述操作逆向進(jìn)行.索引相應(yīng)的創(chuàng)建/銷毀一旦大量并發(fā),內(nèi)部競爭也就產(chǎn)生了.雖然tempdb的緩存策略一定程度可以緩解相應(yīng)創(chuàng)建過程的IAM,數(shù)據(jù)頁分配, Sql Server tempdb原理-緩存機(jī)制解析實(shí)踐,但競爭依舊.

可以看到SGAM,PFS等系統(tǒng)頁是表創(chuàng)建過程的必經(jīng)之路,他的分配競爭也就十分明顯了.這也就是為什么采用多個(gè)數(shù)據(jù)文件，讓系統(tǒng)頁(包含系統(tǒng)表)在分散在多個(gè)數(shù)據(jù)文件中的以減輕分配競爭的壓力原因.

到此也許大家都改猜到了終極問題是什么了,就是對系統(tǒng)對象的操作.連SQL Server大牛Paul Randal都為之頭疼的問題.

具體哪些對象呢,我們可以簡單測試捕捉下如圖1-1

使用SQLQUERYSTRESS捕捉

Code

create table #t
(id int,
str1 varchar(10)
)
---SSMS中開啟會(huì)話捕捉
SELECT resource_description,* FROM SYS.dm_os_waiting_tasks
WHERE session_id>50

                                                     圖1-1

可以看到圖中tempdb中系統(tǒng)頁 2:1:53中發(fā)生典型的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

                                              圖2-2

可以看到在系統(tǒng)對象sysallocunits處發(fā)生了競爭,當(dāng)然還有許多其他的系統(tǒng)對象,感興趣的朋友自行捕捉.

大量的針對系統(tǒng)對象表的操作使得tempdb其吞吐難以得到進(jìn)一步的提升,這個(gè)是由系統(tǒng)本身的運(yùn)作方式引發(fā)的,當(dāng)然面對如此巨量的tempdb使用,就沒有別的方式了嗎?這時(shí)我不能給肯定的答案，但可以給大家一個(gè)IT界的流行答案:It depends :)

在介紹游標(biāo)前,先簡單說下面對tempdb競爭中針對系統(tǒng)表競爭的常規(guī)處理方式

1 減小針對系統(tǒng)對象的事務(wù)大小(如select * into #的使用)

2 減小tempdb的使用頻次(看似廢話,但實(shí)際中的確可能用不到這么多)

3 臨時(shí)對象中少使用約束造成額外的系統(tǒng)對象負(fù)擔(dān).

好了接下來該說游標(biāo)了,貌似八竿子打不著的事兒,實(shí)際上的確如此,我們只是利用游標(biāo)的特性在極其特殊的場景下來解決相應(yīng)問題.

也許你已經(jīng)猜到了,游標(biāo)是使用tempdb的,歸類到worktables中,使用worktables的對象如游標(biāo),dbcc checkdb,merge join,exchange spill等等.worktables是tempdb中一種普遍而又特殊的使用方式,他只在SQL Server內(nèi)部中應(yīng)用,給它定義為”temporary rowsets”,他的object id是負(fù)的,且無需系統(tǒng)表的記錄!

我們來簡單驗(yàn)證說明下

code

use tempdb
checkpoint ---生產(chǎn)環(huán)境中慎用
dbcc checkdb(master) –這里采用dbcc checkdb探究worktables
select Description,* from fn_dblog(null,null)

得到的tempdb Log如圖 2-1

                                                 圖2-1

我們用dbcc page分析此頁 可以看到這個(gè)是個(gè)IAM頁如圖2-2

code

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

                                                   圖2-2

我們進(jìn)而分析IAM分配的數(shù)據(jù)頁,發(fā)現(xiàn)他就是一個(gè)簡單的數(shù)據(jù)頁,不屬于任何系統(tǒng)對象如圖2-3

Code

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

                                           圖2-3

OK,至此聯(lián)想起游標(biāo)同樣適用worktables,我們可能聯(lián)想到了一些游標(biāo)適用的場景居然還可以幫助tempdb緩解競爭.至于何種場景？It depends,大家自己去聯(lián)想吧,但tempdb遇到相應(yīng)競爭時(shí)我是否可以采用?朋友們自己抉擇吧.

最后看圖說話如圖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

以上敘述是否改變了你對游標(biāo)的看法呢?程序員朋友們,當(dāng)DBA告訴你使用tempdb太多時(shí)是否考慮換種方式使用tempdb, DBA朋友們,不要輕易告訴程序員們過度使用tempdb.

結(jié)語 任何系統(tǒng)的愉快運(yùn)轉(zhuǎn)都是基于某種狀態(tài)的平衡.我們需要在復(fù)雜環(huán)境中的性能瓶頸,資源消耗,相應(yīng)時(shí)間等等因素中找到平衡點(diǎn).什么樣的平衡點(diǎn)? It depends :)

ps：sql server 數(shù)據(jù)庫 ' ' 附近有語法錯(cuò)誤

昨天做項(xiàng)目時(shí)候，遇到標(biāo)題的問題，代碼跟蹤把sql 語句 復(fù)制出來在數(shù)據(jù)庫執(zhí)行不了，然后重新寫個(gè)一模一樣的，然后在 賦值到代碼中，還是同樣的錯(cuò)誤，就是不知道哪里出現(xiàn)了錯(cuò)誤，最后 把 sql 語句寫成最簡單的 select * from tab  還是同樣的錯(cuò)誤。

然后 ，然后就不會(huì)了。

最后在這個(gè)語句寫同樣的語句，最后發(fā)現(xiàn)問題了，新寫的sql 語句的 select 變 顏色了，而之前的賦值出來的  select 和 字段 表名的顏色一樣，證明系統(tǒng) 不承認(rèn)它是關(guān)鍵字，把這個(gè)select 刪掉在 這個(gè)位置上重新寫，還是同樣的錯(cuò)誤，最后發(fā)現(xiàn)原來在 這個(gè)select 前面有個(gè)全角的 空格，全角空格真的是用肉眼看不出來啊，恍然大悟，才知道  '   '    附近有語法錯(cuò)誤 ，意思是  空格  有語法錯(cuò)誤，證明不是 sql server 支持的 空格格式。

這個(gè)問題百度了，也沒解決，希望 可以幫到其他人，又不是特別難的東西，但是找到問題還是很浪費(fèi)時(shí)間。