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

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

在寬城等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站 網(wǎng)站設(shè)計(jì)制作按需定制網(wǎng)站,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站制作,營銷型網(wǎng)站,成都外貿(mào)網(wǎng)站建設(shè),寬城網(wǎng)站建設(shè)費(fèi)用合理。

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

相信大部分做數(shù)據(jù)庫運(yùn)維的朋友，都遇到這種情況。 數(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ù)運(yùn)維人員反饋的流量壓根就不對？ 還是數(shù)據(jù)庫內(nèi)部出問題？昨天不是還好好的嗎？

當(dāng)數(shù)據(jù)庫出現(xiàn)響應(yīng)時間不穩(wěn)定的時候，我們在操作系統(tǒng)上會看到磁盤的利用率會比較高，如果觀察仔細(xì)一點(diǎn)，還可以看到，存在一些讀的IO. 數(shù)據(jù)庫服務(wù)器如果存在大量的寫IO,性能一般都是正常跟穩(wěn)定的，但只要存在少量的讀IO,則性能開始出現(xiàn)抖動，存在大量的讀IO時（排除配備非常高速磁盤的機(jī)器），對于在線交易的數(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ù)。為了將文章寫得通俗點(diǎn)，看起來不那么累，因此不再一行一行的將代碼解析寫出來。

咱們先來提問題。?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ù)的步驟。因?yàn)樾枰臄?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ù)頁的線程因?yàn)榇疟PIO瓶頸，遲遲沒有將物理數(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ù)，我們知道其實(shí)通過首先自旋加鎖pin的方式，超過設(shè)定的自旋次數(shù)之后，進(jìn)入等待，等待IO完成被喚醒。這樣節(jié)省不停自旋pin時消耗的cpu,但需要付出被喚起時的開銷。

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

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

再回頭來看上面的問題，mysql數(shù)據(jù)庫出現(xiàn)性能下降時，可以看到操作系統(tǒng)有讀IO。 原因是，在數(shù)據(jù)庫對數(shù)據(jù)頁的更改，是在內(nèi)存中的，然后通過檢查點(diǎn)線程進(jìn)行異步寫盤，這個異步的寫操作是不堵塞執(zhí)行sql的會話線程的。所以，即使看到操作系統(tǒng)上有大量的寫IO，數(shù)據(jù)庫的性能也是很平穩(wěn)的。但當(dāng)用戶線程需要查找的數(shù)據(jù)頁不在buffer pool中時，則會從磁盤上讀取，在一個熱點(diǎn)數(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中的IO瓶頸

我們可能經(jīng)常會遇到SQLServer數(shù)據(jù)庫頻繁關(guān)閉的情況。在分析了內(nèi)存和CPU使用情況后，我們需要繼續(xù)調(diào)查根源是否在I/O。我們應(yīng)該如何識別SQLServer是否有I/O相關(guān)的瓶頸？

解決：

當(dāng)數(shù)據(jù)頁經(jīng)常從緩沖池中移進(jìn)移出的時候，I/O子系統(tǒng)就會成為SQLServer性能問題的關(guān)鍵因素之一。事務(wù)日志和tempdb同樣也會產(chǎn)生重大的I/O壓力。因此，你必須確保你的I/O子系統(tǒng)能按照預(yù)期運(yùn)行。否則你將會成為響應(yīng)時間增長和頻繁超時的受害者。在這篇文章中，將描述如何使用內(nèi)置工具識別I/O相關(guān)瓶頸，并提供一些磁盤配置的方法：

性能計(jì)數(shù)器（Performance Monitor）：

可以使用性能計(jì)數(shù)器來檢查I/O子系統(tǒng)的負(fù)荷。下面的計(jì)數(shù)器可用于檢查磁盤性能：

PhysicalDisk Object:Avg.DiskQueue

Length：計(jì)算從物理磁盤中的平均讀和寫的請求隊(duì)列。過高的值代表磁盤操作處于等待狀態(tài)。當(dāng)這個值在SQLServer峰值時長期超過2，證明需要注意了。如果有多個硬盤，就需要把這些數(shù)值除以2。比如，有4個硬盤，且隊(duì)列為10，那么平均值就是10/4=2.5，雖然也證明需要關(guān)注，但不能使用10這個值。

Avg.Disk Sec/Read和Avg.Disk

Sec/Write：顯示從磁盤讀或者寫入磁盤的平均時間。10ms內(nèi)是很好的表現(xiàn)，20以下還算能接受。高于此值證明存在問題。

Physical Disk:%Disk

Time：在磁盤忙于讀或者寫請求的時候持續(xù)時間的比率。根據(jù)拇指定律，此值應(yīng)該小于50%。

Disk Reads/Sec和Disk

Writes/Sec計(jì)數(shù)器顯示出在磁盤中讀寫操作的速率。這兩個值應(yīng)該小于磁盤能力的85%。當(dāng)超過此值，磁盤的訪問時間將以指數(shù)方式增長。

可以通過以下方式來計(jì)算逐漸增長的負(fù)載的能力。一種方法是使用SQLIO。你應(yīng)該找到吞吐量比較穩(wěn)定，但緩慢增長。

可以使用以下公式來計(jì)算RAID配置：

Raid 0: I/O per disk = (reads + writes) / number

ofdisks

Raid 1: I/O per disk = [reads + (writes*2)] /

2

Raid 5: I/O per disk = [reads + (writes*4)] / number of

disks

Raid

10: I/O per disk = [reads +

(writes*2)] / number of disks

比如：對于RAID 1，如果得到下面的計(jì)數(shù)器：

Disk Reads/sec = 90

Disk

Writes/sec =75

根據(jù)公式：[reads + (writes*2)] / 2 or [90 + (75*2)] /

2 = 120I/Os每個磁盤。

動態(tài)管理視圖（DMVs）：

有很多游泳的DMVs可以用于檢查I/O瓶頸：

當(dāng)一個頁面被用于讀或者寫訪問且頁面在緩沖池中不存在或不可用時，會引發(fā)一個I/O閂鎖等待（I/O

latch），它會在PAGEIOLATCH_EX/PAGEIOLATCH_SH（具體根據(jù)請求類型而定）。這些等待表明一個I/O瓶頸。可以使用sys.dm_os_wait_stats找到閂鎖等待的信息。如果你保存了SQLServer正常運(yùn)行下的waiting_task_counts和wait_time_ms值，并且于此次的值做對比，可以識別出I/O問題：

select *

from sys.dm_os_wait_stats

where wait_type like

'PAGEIOLATCH%'

order by wait_type asc

掛起的I/O請求可以在下面查詢中查到，并且用于識別那個磁盤負(fù)責(zé)的這個瓶頸：

select database_id,

file_id,

io_stall,

io_pending_ms_ticks,

scheduler_address

from sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,

sys.dm_io_pending_io_requests as iopior

where iovfs.file_handle = iopior.io_handle

磁盤碎片（Disk Fragmentation）：

建議你檢查磁盤碎片和配置用于SQLServer實(shí)例的磁盤。在NTFS文件系統(tǒng)中的碎片會產(chǎn)生嚴(yán)重的性能影響。磁盤需要經(jīng)常整理碎片并且指定整理碎片計(jì)劃。研究表明，一些情況下SAN在整理碎片后性能更差。因此，SAN必須根據(jù)實(shí)際情況對待。

NTFS上的索引碎片同樣能引起高I/O好用。但是這和在SANs中的效果是不一樣的。

磁盤配置/最佳實(shí)踐：

常規(guī)情況，你應(yīng)該把日志文件和數(shù)據(jù)文件分開存放以獲得更好的性能。對于重負(fù)載的數(shù)據(jù)文件（包括tempdb）的I/O特性是隨機(jī)讀取。對于日志文件，是順序訪問的，除非事務(wù)需要回滾。

對于內(nèi)置磁盤僅僅可以用于數(shù)據(jù)庫日志文件，因?yàn)樗鼈儗樞騃/O有很好的性能，但是對隨機(jī)I/O性能低下。

數(shù)據(jù)庫的數(shù)據(jù)和日志文件應(yīng)該放在對應(yīng)專用的磁盤中。確保良好的性能。建議日志文件放在兩個內(nèi)置磁盤，并配置為RAID

1。數(shù)據(jù)文件駐留在僅用于給SQLServer訪問的SAN系統(tǒng)中，并只被查詢和報(bào)表控制。特殊訪問應(yīng)該被禁止。

寫緩沖在可能的情況下應(yīng)該被允許，并保證斷電也能使用。

為了盡可能保證對于OLTP系統(tǒng)的I/O瓶頸影響最小化，不應(yīng)該把OLAP和OLTP環(huán)境混合。并且保證你的代碼優(yōu)化及有合適的索引來避免不必要的I/O。

如何識別SQL Server中的CPU瓶頸

怎樣查出SQLServer的性能瓶頸 QLServer性能監(jiān)控 這套性能優(yōu)化的清單將至少準(zhǔn)科學(xué)的幫助你找出你的SQLServer任何明顯的性能問題。說是這樣說，SQLServer的性能調(diào)優(yōu)仍然是很困難的。我試圖用這套清單去找出“容易”的sqlserver性能問題，困難的留待...

怎樣查出sqlserver的性能瓶頸

如果你曾經(jīng)做了很長時間的DBA，那么你會了解到SQLServe的性能調(diào)優(yōu)不是一個精密的科學(xué)。即使是，對于為最佳的性能找到最佳的配置也是很困難的。這是因?yàn)閷τ谡{(diào)優(yōu)來說很少東西是絕對的。例如，一個性能調(diào)優(yōu)可能對某一方面有用，可是卻會影響其他的性能。

我曾經(jīng)做過DBA，在最后7年的日子里，我總結(jié)了一套SQLServer調(diào)優(yōu)的清單。當(dāng)?shù)谝淮芜M(jìn)行SQLServer性能調(diào)優(yōu)的時候，可以用它來作為一個向?qū)?。我?jīng)常被邀請去檢查SQLServer并提供一些性能方面的建議。直到現(xiàn)在，我還沒有真正寫下一個貫穿整個性能調(diào)優(yōu)過程的方案。但是當(dāng)我做了越來越多的性能調(diào)優(yōu)的咨詢工作后，我現(xiàn)在決定花點(diǎn)時間整理出來。你將會發(fā)現(xiàn)它是很有用的，就象我發(fā)現(xiàn)對我的用處一樣.

怎樣查出SQLServer的性能瓶頸

1、查詢SQL中的所有表： Select TABLE_NAME FROM 數(shù)據(jù)庫名稱.INFORMATION_SCHEMA.TABLES Where TABLE_TYPE='BASE TABLE' 執(zhí)行之后，就可以看到數(shù)據(jù)庫中所有屬于自己建的表的名稱 2、查詢SQL中所有表及列： Select dbo.sysobjects.name as Table_name, dbo.syscolumns.name AS Column_name FROM dbo.syscolumns INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id Where (dbo.sysobjects.xtype = 'u') AND (NOT (dbo.sysobjects.name LIKE 'dtproperties')) 3、在Sql查詢分析器，還有一個簡單的查詢方法： EXEC sp_MSforeachtable @command1="sp_spaceused '?'" 執(zhí)行完之后，就可以看到數(shù)據(jù)庫中所有用戶表的信息 4、查詢總存儲過程數(shù):select count(*) 總存儲過程數(shù) from sysobjects where xtype='p' 附：xtype類型D = 默認(rèn)值或 DEFAULT 約束

F = FOREIGN KEY 約束L = 日志FN = 標(biāo)量函數(shù)

IF = 內(nèi)嵌表函數(shù)

P = 存儲過程

PK = PRIMARY KEY 約束（類型是 K）

RF = 復(fù)制篩選存儲過程S = 系統(tǒng)表TF = 表函數(shù)

TR = 觸發(fā)器U = 用戶表UQ = UNIQUE 約束（類型是 K）V = 視圖X = 擴(kuò)展存儲過程 另：在sqlserver中取得某個數(shù)據(jù)庫中所有表名的sql語句 select sysobjects.name from sysobjects.xtype ='U';SELECT name

WHERE (xtype = 'U') 在數(shù)據(jù)庫的sysobjects表里有這個數(shù)據(jù)庫全部表的信息, xtype值為'U'的就是表名 注意：一般通過上述方法獲得全部用戶表示都會有一個dtproperties表，SQLSERVER 默認(rèn)它也是用戶表，想要從用戶表中排出，需要加上限定條件 status0，即：select * from sysobjects where xtype='U' and status0

當(dāng)前題目：sqlserver瓶頸的簡單介紹
網(wǎng)站地址：http://jinyejixie.com/article20/dseddjo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、軟件開發(fā)、營銷型網(wǎng)站建設(shè)、ChatGPT、網(wǎng)站導(dǎo)航、全網(wǎng)營銷推廣

廣告

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