如何判斷MSSQL數(shù)據(jù)庫磁盤出現(xiàn)了瓶頸

具體問題具體分析，舉例來說明為什么磁盤IO成瓶頸數(shù)據(jù)庫的性能急速下降了。

站在用戶的角度思考問題，與客戶深入溝通，找到銀州網(wǎng)站設(shè)計與銀州網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設(shè)計與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：成都網(wǎng)站設(shè)計、成都網(wǎng)站建設(shè)、外貿(mào)網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣、域名申請、虛擬空間、企業(yè)郵箱。業(yè)務(wù)覆蓋銀州地區(qū)。

為什么當(dāng)磁盤IO成瓶頸之后, 數(shù)據(jù)庫的性能不是達(dá)到飽和的平衡狀態(tài)，而是急劇下降。為什么數(shù)據(jù)庫的性能有非常明顯的分界點，原因是什么？

相信大部分做數(shù)據(jù)庫運維的朋友，都遇到這種情況。 數(shù)據(jù)庫在前一天性能表現(xiàn)的相當(dāng)穩(wěn)定，數(shù)據(jù)庫的響應(yīng)時間也很正常，但就在今天，在業(yè)務(wù)人員反饋業(yè)務(wù)流量沒有任何上升的情況下，數(shù)據(jù)庫的變得不穩(wěn)定了，有時候一個最簡單的insert操作， 需要幾十秒，但99%的insert卻又可以在幾毫秒完成，這又是為什么了？

dba此時心中有無限的疑惑，到底是什么原因呢? 磁盤IO性能變差了？還是業(yè)務(wù)運維人員反饋的流量壓根就不對？ 還是數(shù)據(jù)庫內(nèi)部出問題？昨天不是還好好的嗎？

當(dāng)數(shù)據(jù)庫出現(xiàn)響應(yīng)時間不穩(wěn)定的時候，我們在操作系統(tǒng)上會看到磁盤的利用率會比較高，如果觀察仔細(xì)一點，還可以看到，存在一些讀的IO. 數(shù)據(jù)庫服務(wù)器如果存在大量的寫IO,性能一般都是正常跟穩(wěn)定的，但只要存在少量的讀IO,則性能開始出現(xiàn)抖動，存在大量的讀IO時（排除配備非常高速磁盤的機器），對于在線交易的數(shù)據(jù)庫系統(tǒng)來說，大概性能就雪崩了。為什么操作系統(tǒng)上看到的磁盤讀IO跟寫IO所帶來的性能差距這么大呢？

如果親之前沒有注意到上述的現(xiàn)象，親對上述的結(jié)論也是懷疑。但請看下面的分解。

在寫這個文章之前，作者閱讀了大量跟的IO相關(guān)的代碼，如異步IO線程的相關(guān)的，innodb_buffer池相關(guān)的，以及跟讀數(shù)據(jù)塊最相關(guān)的核心函數(shù)buf_page_get_gen函數(shù)以及其調(diào)用的相關(guān)子函數(shù)。為了將文章寫得通俗點，看起來不那么累，因此不再一行一行的將代碼解析寫出來。

咱們先來提問題。?buf_page_get_gen函數(shù)的作用是從Buffer bool里面讀數(shù)據(jù)頁，可能存在以下幾種情況。

提問. 數(shù)據(jù)頁不在buffer bool 里面該怎么辦？

回答：去讀文件，將文件中的數(shù)據(jù)頁加載到buffer pool里面。下面是函數(shù)buffer_read_page的函數(shù)，作用是將物理數(shù)據(jù)頁加載到buffer pool, 圖片中顯示

buffer_read_page函數(shù)棧的頂層是pread64(),調(diào)用了操作系統(tǒng)的讀函數(shù)。

buf_read_page的代碼

如果去讀文件，則需要等待物理讀IO的完成，如果此時IO沒有及時響應(yīng)，則存在堵塞。這是一個同步讀的操作，如果不完成該線程無法繼續(xù)后續(xù)的步驟。因為需要的數(shù)據(jù)頁不再buffer 中，無法直接使用該數(shù)據(jù)頁，必須等待操作系統(tǒng)完成IO .

再接著上面的回答提問：

當(dāng)?shù)诙捑€程執(zhí)行sql的時候，也需要去訪問相同的數(shù)據(jù)頁，它是等待上面的線程將這個數(shù)據(jù)頁讀入到緩存中，還是自己再發(fā)起一個讀磁盤的然后加載到buffer的請求呢？?? 代碼告訴我們，是前者，等待第一個請求該數(shù)據(jù)頁的線程讀入buffer pool。

試想一下，如果第一個請求該數(shù)據(jù)頁的線程因為磁盤IO瓶頸，遲遲沒有將物理數(shù)據(jù)頁讀入buffer pool, 這個時間區(qū)間拖得越長，則造成等待該數(shù)據(jù)塊的用戶線程就越多。對高并發(fā)的系統(tǒng)來說，將造成大量的等待。 等待數(shù)據(jù)頁讀入的函數(shù)是buf_wait_for_read，下面是該函數(shù)相關(guān)的棧。

通過解析buf_wait_for_read函數(shù)的下層函數(shù)，我們知道其實通過首先自旋加鎖pin的方式，超過設(shè)定的自旋次數(shù)之后，進入等待，等待IO完成被喚醒。這樣節(jié)省不停自旋pin時消耗的cpu,但需要付出被喚起時的開銷。

再繼續(xù)擴展問題： 如果會話線程A 經(jīng)過物理IO將數(shù)據(jù)頁1001讀入buffer之后，他需要修改這個頁，而在會話線程A之后的其他的同樣需要訪問數(shù)據(jù)頁1001的會話線程，即使在數(shù)據(jù)頁1001被入讀buffer pool之后，將仍然處于等待中。因為在數(shù)據(jù)頁上讀取或者更新的時候，同樣需要上鎖，這樣才能保證數(shù)據(jù)頁并發(fā)讀取/更新的一致性。

由此可見，當(dāng)一個高并發(fā)的系統(tǒng)，出現(xiàn)了熱點數(shù)據(jù)頁需要從磁盤上加載到buffer pool中時，造成的延遲，是難以想象的。因此排在等待熱點頁隊列最后的會話線程最后才得到需要的頁，響應(yīng)時間也就越長，這就是造成了一個簡單的sql需要執(zhí)行幾十秒的原因。

再回頭來看上面的問題，mysql數(shù)據(jù)庫出現(xiàn)性能下降時，可以看到操作系統(tǒng)有讀IO。 原因是，在數(shù)據(jù)庫對數(shù)據(jù)頁的更改，是在內(nèi)存中的，然后通過檢查點線程進行異步寫盤，這個異步的寫操作是不堵塞執(zhí)行sql的會話線程的。所以，即使看到操作系統(tǒng)上有大量的寫IO，數(shù)據(jù)庫的性能也是很平穩(wěn)的。但當(dāng)用戶線程需要查找的數(shù)據(jù)頁不在buffer pool中時，則會從磁盤上讀取，在一個熱點數(shù)據(jù)頁不是非常多的情況下，我們設(shè)置足夠大的innodb_buffer_pool的size, 基本可以緩存所有的數(shù)據(jù)頁，因此一般都不會出現(xiàn)缺頁的情況，也就是在操作系統(tǒng)上基本看不到讀的IO。 ?當(dāng)出現(xiàn)讀的IO時，原因時在執(zhí)行buf_read_page_low函數(shù)，從磁盤上讀取數(shù)據(jù)頁到buffer pool, 則數(shù)據(jù)庫的性能則開始下降，當(dāng)出現(xiàn)大量的讀IO，數(shù)據(jù)庫的性能會非常差。

sql server 2005 2709 出錯 是什么意思

鎖升級 禁止升級　

鎖定粒度是一個查詢或更新所鎖定的最小數(shù)據(jù)，粒度不同數(shù)據(jù)庫的性能和并發(fā)能力是此消彼長的，怎么來理解呢？鎖定的粒度越小并發(fā)的用戶數(shù)越多，這是顯而易的，如果這時發(fā)生一種情況，根據(jù)業(yè)務(wù)規(guī)律要鎖定大量的記錄行來進行更新，在保持并發(fā)用戶的前提下，我們鎖定的記錄的行鎖或鍵鎖就很多，我們知道鎖定不是免費的午餐，是要付出代價的，管理的鎖定多越多系統(tǒng)資源開銷就越大。還記得我們在前面介紹過鎖塊吧，鎖塊是一個64/128(128是64位操作系統(tǒng))字節(jié)的內(nèi)存塊，另外對每一個申請或正持有鎖塊的進程還要準(zhǔn)備一個32/64(64是64位操作系統(tǒng))字節(jié)的內(nèi)存塊來描述這些進程，在這兒我們確定一個前提：不管鎖定粒度的大小，每一個鎖定都占用幾乎同樣的系統(tǒng)開銷。好，比如我們要進行10W行數(shù)據(jù)更新，為了并發(fā)我們都采用行鎖來鎖定，按照鎖塊的定義那么我們就得需要64B * 100000+N*32B= 6400000B +32NB(理論更新我們?nèi)=1相對于6400000可以忽略) 6.4M的RAM來管理這些行鎖，假設(shè)并發(fā)進程(當(dāng)然是不同資源上的)數(shù)量是X，那么當(dāng)前數(shù)據(jù)庫就得要X*6.4M的RAM用于管理鎖定，顯然這種對RAM需求的上升是系統(tǒng)無法忍受，不可能無限制的滿足的這種增長，那么SQLSERVER得用一種辦法來防止系統(tǒng)使用太多的內(nèi)存來追蹤鎖定并且提高鎖定的效率。這個任務(wù)交給了鎖管理器，它負(fù)責(zé)平衡資源的使用(當(dāng)然還負(fù)責(zé)從特定操作的開始到結(jié)束保持連續(xù)、邏輯完整性)，這時管理器就采取鎖定升級這一明智造擇，從行鎖或鍵鎖或頁鎖升級為表級鎖定，比較6.4M和96B，顯然獲取一個表級鎖定比持有許多行或鍵鎖更有意義。

鎖升級的意義是顯而易見，使得鎖定開銷下降并避免系統(tǒng)資源耗盡。在結(jié)構(gòu)引擎里我們提及鎖管理器，系統(tǒng)分配給鎖管理器的內(nèi)存是有限的，鎖的升級保證了鎖定占用內(nèi)存維持一個合理的限度。

鎖升級發(fā)生的時機：

1、 在一個對象上一個查詢或更新持有鎖的數(shù)量超過閥值。SQL2005缺省是5000個鎖(記得SQL6.0只有200個，但是我們要記住SQL6.0只有頁面鎖定哦)。

2、 鎖資源占用的內(nèi)存超過AWE或常規(guī)內(nèi)存的40%，40%是一個約數(shù)。

時機一滿足SQLSERVER就會嘗試鎖升級，當(dāng)然升級不一定會成功，當(dāng)失敗后在同一個對象上的鎖資源再次上升到一定程度時升級會再次發(fā)生，如果升級成功SQLSERVER會釋放對象上先前獲得的行、鍵、分頁鎖定。升級失敗發(fā)生當(dāng)另外一個進程對表有行或頁有排它鎖定時。

鎖升級潛在的危險：

1、 鎖升級的結(jié)果一定是一個完全表級鎖定，也就是不可能出現(xiàn)行鎖升級為頁鎖的，最細(xì)的行級鎖升級的直接結(jié)果一定是表鎖定。

2、 鎖升級可能造成意外的阻塞(這個應(yīng)該是很好理解的)

3、 鎖升級成功后無法降級

禁止升級 我們知道鎖升級是有潛在的危險，并且這種升級的結(jié)果是不可能現(xiàn)降級除非事務(wù)結(jié)束。所以升級不是對所有的應(yīng)用都是一件好事，MS提供了兩個開關(guān)項：1211和1224，我們可以通過設(shè)置跟蹤標(biāo)識來禁止升級。

7、行鎖、頁鎖

7.0之前的版本鎖定的最小粒度就是頁鎖，提醒大家一下那時的頁面最小單位是2K，如果細(xì)心部署一定程度上是可以滿足夠大的呑吐量和可以接受的響應(yīng)時間。然后7.0后把分頁從2KB提升為8KB時(為什么要提升呢？嘿嘿，留一個疑問給大家)，這種頁面鎖定對并發(fā)能力是一種挑戰(zhàn)，也就是鎖定的范圍是7.0之前的4倍，這時并發(fā)及響應(yīng)時間都成一個問題。SQL2005完全實現(xiàn)行級鎖定，顯然這對并發(fā)響應(yīng)是可喜的，可是正如我在鎖升級里給大家算的一筆帳，在有限可利用的鎖定資源前提下，大量行級鎖定的代價還是讓人無法接受的，特別在極限的狀態(tài)下。

我們知道鎖定操作是一個密集型操作，一個鎖定不僅要看到內(nèi)存的損耗，還要看到SQLSERVER管理這些鎖定對其本身來也是一種負(fù)荷。雖然SQL內(nèi)部使用閂或自旋鎖來降低這種負(fù)荷，但我們很容易可以想像管理一個分頁鎖定比管理N個行級鎖定(假設(shè)頁面內(nèi)有N行記錄)更輕松、更有效率。

比較行鎖和頁鎖，行鎖降低了并發(fā)沖突但是資源的損耗也是顯然的，頁鎖減少必須存在鎖的數(shù)量及管理這些鎖定的資源損耗但是以并發(fā)能下降為代價的。到底哪個更合適，恐怕不是一句兩句能說完的，因為針對不同應(yīng)用、不同行業(yè)、不同并發(fā)模型、不同隔離兩都各有各的優(yōu)勢。

在SQLSERVER2005可以用sp_indexoption來控制索引的鎖定單位。關(guān)于這個設(shè)置我們可以看看聯(lián)機幫助，但是一定要注意它只針對索引所以對堆表無法控制分頁鎖定。

8、動態(tài)管理鎖定

SQL造擇鎖定類型、粒度是基于行數(shù)、可能掃描的頁面數(shù)、分頁上的行數(shù)，隔離級別、進行的何種操作、可使用的系統(tǒng)資源等因素的影響 ，根據(jù)這些影響因素SQLSERVER選擇一種合適的鎖定模式這個過程稱動態(tài)鎖定策略(我發(fā)現(xiàn)策略在MS很流行)，數(shù)據(jù)庫引擎(還有印象我有引擎結(jié)構(gòu)中介紹的存儲引擎吧)動態(tài)的管理粒度和鎖定模式，控制鎖定與系統(tǒng)資源的最佳成本效率。一個范圍內(nèi)的鎖定所要使用的系統(tǒng)資源肯定小，但是系統(tǒng)的并發(fā)性也就降低，如果選擇小范圍內(nèi)的鎖定，那管理鎖定所使用的系統(tǒng)資源上升，然而并發(fā)性能卻得到了淋漓發(fā)揮。

一般情況我們可使用系統(tǒng)缺省設(shè)置(行級鎖定是系統(tǒng)缺省的)，讓系統(tǒng)決定是否要進行鎖定的升級。這樣一來簡化我們對庫鎖定的管理，系統(tǒng)根據(jù)實際情況平衡負(fù)載。

9、死鎖

首先，我們得清楚死鎖與等待是兩回事。等待是當(dāng)前進程所需要的資源讓另一個進程排它了，只要另外一個進程釋放，當(dāng)時進程就可以繼續(xù)執(zhí)行(當(dāng)然如果另外這個進程已經(jīng)死鎖那會進入無限期等待，但是這種情況一般不會發(fā)生，因為SQLSERVER會干預(yù)死鎖的。另外我們還有一個鎖定超時設(shè)置 ，這方面大家可以看聯(lián)機叢書)。而死鎖是發(fā)生在兩個進程間，在沒有人為干預(yù)兩個鎖定的進程是都無法繼續(xù)工作的一種困境。另外一個顯著的地方就是死鎖一旦發(fā)生，SQLSERVER就會干預(yù)進來，我們所能感知比如接收到1205號錯誤，健壯的應(yīng)用系統(tǒng)會人工干預(yù)1205錯誤，恰當(dāng)?shù)闹匦绿峤慌幚恚?dāng)1205錯誤發(fā)生沒有終止的進程獲得相應(yīng)的資源并處理自己的事務(wù)直至釋放資源，其實這種人為的干預(yù)潛在的又為死鎖提供一個外在環(huán)境。當(dāng)然我們前面寫的一個過程也可以查詢到相應(yīng)的鎖定信息。

接著，死鎖是無法完全避免的。在一個并發(fā)的多用戶系統(tǒng)，鎖定、線程、內(nèi)存、并行查詢、MARS中死鎖的發(fā)生是正常的、可以預(yù)見的，也是必然的。在我們能力范圍內(nèi)只能盡可能的在應(yīng)用端或服務(wù)器上恰當(dāng)?shù)奶幚硭梨i，使得這種無法完全避免的事件給系統(tǒng)帶來的影響降到最低。也就是我們應(yīng)該明白：死鎖是無法完全避免，但是我可以降低發(fā)生的次數(shù)。

第三，死鎖是一種末日，沒有人為干預(yù)時永遠(yuǎn)退不出這種狀態(tài)。一個并發(fā)的多用戶系統(tǒng)這種競爭資源的可能性是很大的，一有競爭就會有“矛盾”發(fā)生，雙方等待對方釋放自己所需要的資源，必然成了無限期等待，這種等待就是我們所說的死鎖。我們通過上面的介紹知道這時SQLSERVER鎖管理器會干預(yù)這個過程，試想如果沒有SQLSERVER鎖管理器的干預(yù)那么兩個進程一根筯的結(jié)果就是無限期等待，對于應(yīng)用系統(tǒng)來說就是一個末日。SQLSERVER2005更是提供了豐富的鎖有關(guān)元數(shù)據(jù)，可以很方便的偵察出鎖定信息，SQLSERVER鎖管理器干預(yù)的結(jié)果就是根據(jù)犧牲品的優(yōu)先等級及回滾代價，把優(yōu)先級低和代價最小的進程當(dāng)作犧牲品，殺掉這個進程并拋出1205錯誤。

第四，死鎖大體分為三類：cycle死鎖、conversion死鎖、應(yīng)用級死鎖及不明死鎖。

sql server 2014 sp2能不能加載sql server 2016的數(shù)據(jù)庫

需要在sqlserver啟動參數(shù)里添加 Trace Flag 8079 來打開這個功能，這也是SQL2016的新功能

DBCC CHECK* 系列命令可以使用MAXDOP查詢提示：使用局部配置而不是全局配置的sp_configure 值來控制DBCC CHECK* 的并行度

buffer pool可以利用8TB內(nèi)存：通過128TB的虛擬地址空間，SQL Server的buffer pool可以利用到8TB內(nèi)存的buffer pool

SOS_RWLock 自旋鎖增強：SOS_RWLock 是一個同步原語并且在SQL Server的代碼庫里的很多地方都有使用。

求助sqlserver服務(wù)器壓力問題

剛才參考了這個

看了你幾個出現(xiàn)次數(shù)比較多的等待，下面可以參考，另外，癥狀和解決方案-LATCH_XX

這意味著

存在非頁閂鎖

使用sys.dm_os_latch_stats來分析哪一個閂鎖等待時間過長

和其它同時發(fā)生的等待類型結(jié)合查看

比如說CXPACKET和LATCH_EX與ACCESS_METHODs_SCAN_RANGE_GENERATOR往

往意味著存在大量掃描

癥狀和解決方案-LCK_M_XX

解決方案基于最開始被阻塞進程的等待類型

一個查范圍更新或掃描造成的鎖升級

癥狀和解決方案-

SOS_SCHEDULER_YIELD

這意味著

線程用完4毫秒的時間片，主動放棄CPU

存在自旋鎖

不一定是CPU問題（CPU問題往往體現(xiàn)在長Runnable隊列或大量signal

wait）

通過執(zhí)行計劃查看是否存在大量掃描

查看等待類型

避免望文生義

更多分析

注意：該方式?jīng)]有Resource_wait等待類型，因此一些查另外關(guān)于sqltrace的，參考這個

另外你的服務(wù)器硬件配置還有數(shù)據(jù)庫大小是什么樣的？

建議你查詢一下執(zhí)行次數(shù)最多的sql和最耗費IO的sql，看看執(zhí)行計劃是不是缺少索引之類的

網(wǎng)站欄目：sqlserver自旋鎖,sqlserver行鎖
轉(zhuǎn)載注明：http://chinadenli.net/article8/dseipip.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、網(wǎng)站策劃、面包屑導(dǎo)航、企業(yè)網(wǎng)站制作、靜態(tài)網(wǎng)站、全網(wǎng)營銷推廣

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)