SQL Server使用游標(biāo)處理Tempdb究極競(jìng)爭(zhēng)-DBA問題-程序員必知
SQL Server tempdb分配競(jìng)爭(zhēng)算是DBA老生常談的問題了,幾乎現(xiàn)在所有的DBA都知道多建幾個(gè)文件來解決/緩解問題.但是深層次的的競(jìng)爭(zhēng)依舊不可避免.這里給大家剖析下游標(biāo)在tempdb中的特點(diǎn)使其在一定場(chǎng)景下替代臨時(shí)表/表變量對(duì)象,解決深層次的tempdb競(jìng)爭(zhēng)問題.
在拋出這個(gè)不可避免的問題之前我們先簡(jiǎn)要看下什么是tempdb競(jìng)爭(zhēng).
我們拿SQL Server創(chuàng)建一個(gè)臨時(shí)表的過程來描述
1 在系統(tǒng)表中創(chuàng)建表的條目(系統(tǒng)數(shù)據(jù)頁(yè)中)
2 分配一個(gè)IAM頁(yè)并找到一個(gè)混合區(qū)在PFS頁(yè)中標(biāo)記
3 分配一個(gè)數(shù)據(jù)頁(yè)(查看SGAM頁(yè),查看PFS頁(yè)后并更新,更新IAM頁(yè))
4 表記錄記錄到系統(tǒng)表中
從上述過程可以看出創(chuàng)建一個(gè)簡(jiǎn)單臨時(shí)表需要查找,更新一系列的系統(tǒng)表/系統(tǒng)數(shù)據(jù)頁(yè),且當(dāng)使用完刪除臨時(shí)表時(shí)上述操作逆向進(jìn)行.索引相應(yīng)的創(chuàng)建/銷毀一旦大量并發(fā),內(nèi)部競(jìng)爭(zhēng)也就產(chǎn)生了.雖然tempdb的緩存策略一定程度可以緩解相應(yīng)創(chuàng)建過程的IAM,數(shù)據(jù)頁(yè)分配, Sql Server tempdb原理-緩存機(jī)制解析實(shí)踐,但競(jìng)爭(zhēng)依舊.
可以看到SGAM,PFS等系統(tǒng)頁(yè)是表創(chuàng)建過程的必經(jīng)之路,他的分配競(jìng)爭(zhēng)也就十分明顯了.這也就是為什么采用多個(gè)數(shù)據(jù)文件,讓系統(tǒng)頁(yè)(包含系統(tǒng)表)在分散在多個(gè)數(shù)據(jù)文件中的以減輕分配競(jìng)爭(zhēng)的壓力原因.
到此也許大家都改猜到了終極問題是什么了,就是對(duì)系統(tǒng)對(duì)象的操作.連SQL Server大牛Paul Randal都為之頭疼的問題.
具體哪些對(duì)象呢,我們可以簡(jiǎn)單測(cè)試捕捉下如圖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)頁(yè) 2:1:53中發(fā)生典型的Pagelatch競(jìng)爭(zhēng).我們用dbcc page來看下頁(yè)的情況如圖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)對(duì)象sysallocunits處發(fā)生了競(jìng)爭(zhēng),當(dāng)然還有許多其他的系統(tǒng)對(duì)象,感興趣的朋友自行捕捉.
大量的針對(duì)系統(tǒng)對(duì)象表的操作使得tempdb其吞吐難以得到進(jìn)一步的提升,這個(gè)是由系統(tǒng)本身的運(yùn)作方式引發(fā)的,當(dāng)然面對(duì)如此巨量的tempdb使用,就沒有別的方式了嗎?這時(shí)我不能給肯定的答案,但可以給大家一個(gè)IT界的流行答案:It depends :)
在介紹游標(biāo)前,先簡(jiǎn)單說下面對(duì)tempdb競(jìng)爭(zhēng)中針對(duì)系統(tǒng)表競(jìng)爭(zhēng)的常規(guī)處理方式
1 減小針對(duì)系統(tǒng)對(duì)象的事務(wù)大小(如select * into #的使用)
2 減小tempdb的使用頻次(看似廢話,但實(shí)際中的確可能用不到這么多)
3 臨時(shí)對(duì)象中少使用約束造成額外的系統(tǒng)對(duì)象負(fù)擔(dān).
好了接下來該說游標(biāo)了,貌似八竿子打不著的事兒,實(shí)際上的確如此,我們只是利用游標(biāo)的特性在極其特殊的場(chǎng)景下來解決相應(yīng)問題.
也許你已經(jīng)猜到了,游標(biāo)是使用tempdb的,歸類到worktables中,使用worktables的對(duì)象如游標(biāo),dbcc checkdb,merge join,exchange spill等等.worktables是tempdb中一種普遍而又特殊的使用方式,他只在SQL Server內(nèi)部中應(yīng)用,給它定義為”temporary rowsets”,他的object id是負(fù)的,且無(wú)需系統(tǒng)表的記錄!
我們來簡(jiǎn)單驗(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分析此頁(yè) 可以看到這個(gè)是個(gè)IAM頁(yè)如圖2-2
code
dbcc traceon(3604) dbcc page(2,4,104,3)
圖2-2
我們進(jìn)而分析IAM分配的數(shù)據(jù)頁(yè),發(fā)現(xiàn)他就是一個(gè)簡(jiǎn)單的數(shù)據(jù)頁(yè),不屬于任何系統(tǒng)對(duì)象如圖2-3
Code
dbcc traceon(3604) dbcc page(2,5,104,3)
圖2-3
OK,至此聯(lián)想起游標(biāo)同樣適用worktables,我們可能聯(lián)想到了一些游標(biāo)適用的場(chǎng)景居然還可以幫助tempdb緩解競(jìng)爭(zhēng).至于何種場(chǎng)景?It depends,大家自己去聯(lián)想吧,但tempdb遇到相應(yīng)競(jìng)爭(zhē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
以上敘述是否改變了你對(duì)游標(biāo)的看法呢?程序員朋友們,當(dāng)DBA告訴你使用tempdb太多時(shí)是否考慮換種方式使用tempdb, DBA朋友們,不要輕易告訴程序員們過度使用tempdb.
結(jié)語(yǔ) 任何系統(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ù)庫(kù) ' ' 附近有語(yǔ)法錯(cuò)誤
昨天做項(xiàng)目時(shí)候,遇到標(biāo)題的問題,代碼跟蹤把sql 語(yǔ)句 復(fù)制出來在數(shù)據(jù)庫(kù)執(zhí)行不了,然后重新寫個(gè)一模一樣的,然后在 賦值到代碼中,還是同樣的錯(cuò)誤,就是不知道哪里出現(xiàn)了錯(cuò)誤,最后 把 sql 語(yǔ)句寫成最簡(jiǎn)單的 select * from tab 還是同樣的錯(cuò)誤。
然后 ,然后就不會(huì)了。
最后在這個(gè)語(yǔ)句寫同樣的語(yǔ)句,最后發(fā)現(xiàn)問題了,新寫的sql 語(yǔ)句的 select 變 顏色了,而之前的賦值出來的 select 和 字段 表名的顏色一樣,證明系統(tǒng) 不承認(rèn)它是關(guān)鍵字,把這個(gè)select 刪掉在 這個(gè)位置上重新寫,還是同樣的錯(cuò)誤,最后發(fā)現(xiàn)原來在 這個(gè)select 前面有個(gè)全角的 空格,全角空格真的是用肉眼看不出來啊,恍然大悟,才知道 ' ' 附近有語(yǔ)法錯(cuò)誤 ,意思是 空格 有語(yǔ)法錯(cuò)誤,證明不是 sql server 支持的 空格格式。
這個(gè)問題百度了,也沒解決,希望 可以幫到其他人,又不是特別難的東西,但是找到問題還是很浪費(fèi)時(shí)間。
- Sql Server臨時(shí)表和游標(biāo)的使用小結(jié)
- SQL Server查看未釋放游標(biāo)的方法
- MS SQL Server游標(biāo)(CURSOR)的學(xué)習(xí)使用
- sqlserver游標(biāo)使用步驟示例(創(chuàng)建游標(biāo) 關(guān)閉游標(biāo))
- SQLServer用t-sql命令批量刪除數(shù)據(jù)庫(kù)中指定表(游標(biāo)循環(huán)刪除)
- SQL Server 游標(biāo)語(yǔ)句 聲明/打開/循環(huán)實(shí)例
- SQL Server游標(biāo)的使用/關(guān)閉/釋放/優(yōu)化小結(jié)
- SQL Server遍歷表中記錄的2種方法(使用表變量和游標(biāo))
- sqlserver中觸發(fā)器+游標(biāo)操作實(shí)現(xiàn)
- SQL Server游標(biāo)的介紹與使用
相關(guān)文章
格式導(dǎo)致的Excel導(dǎo)入sql出現(xiàn)異常的解決方法
因?yàn)镋xcel導(dǎo)入到sql的事兒,今天折騰了大半天。2009-03-03數(shù)據(jù)庫(kù)SQL語(yǔ)句優(yōu)化總結(jié)(收藏)
網(wǎng)上關(guān)于SQL優(yōu)化的教程很多,但是比較雜亂。近日有空整理了一下,寫出來跟大家分享一下,感興趣的朋友參考下2017-03-03SQLSERVER記錄登錄用戶的登錄時(shí)間(自寫腳本)
下面是本人寫的一個(gè)腳本,我的實(shí)現(xiàn)原理是使用觸發(fā)器,觸發(fā)器是登錄觸發(fā)器,范圍是整個(gè)服務(wù)器范圍,如果有人登錄過,就使用 bcp命令把登錄信息記錄日志文件,感興趣的朋友可以了解下,或許本文的知識(shí)點(diǎn)對(duì)你有所幫助2013-02-02sqlserver復(fù)制數(shù)據(jù)庫(kù)的方法步驟(圖文)
這篇文章主要介紹了sqlserver復(fù)制數(shù)據(jù)庫(kù)的方法步驟(圖文),文中通過圖文示例介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-04-04SQL Server數(shù)字開頭的數(shù)據(jù)庫(kù)表名的解決方法
這篇文章主要介紹了SQL Server數(shù)字開頭的數(shù)據(jù)庫(kù)表名的解決方法,需要的朋友可以參考下2015-11-11Zabbix監(jiān)控SQL Server服務(wù)狀態(tài)的方法詳解
這篇文章主要給大家介紹了關(guān)于Zabbix監(jiān)控SQL Server服務(wù)狀態(tài)的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-10-10SQL Server2008數(shù)據(jù)庫(kù)導(dǎo)入導(dǎo)出兼容性處理方案
SQL Server 的高版本數(shù)據(jù)庫(kù)恢復(fù)到低版本則可能會(huì)有兼容性問題,下面為大家介紹的是如何解決此類問題2014-05-05MSSQL 基本語(yǔ)法及實(shí)例操作語(yǔ)句
MSSQL 基本語(yǔ)法及實(shí)例操作語(yǔ)句,需要的朋友可以參考下2012-06-06