SQL Monitor是一個(gè)SQL Server監(jiān)控工具。它可以監(jiān)控SQL Servers的健康狀況和活動(dòng)，并通過(guò)電子郵件為您發(fā)送監(jiān)測(cè)結(jié)果和建議。

點(diǎn)擊下載SQL Monitor免費(fèi)版

SQL Monitor不僅自動(dòng)收集您需要的所有磁盤和數(shù)據(jù)庫(kù)增長(zhǎng)跟蹤數(shù)據(jù)，而且還分析這些數(shù)據(jù)的趨勢(shì)以準(zhǔn)確預(yù)測(cè)何時(shí)磁盤卷會(huì)耗盡可用空間，或數(shù)據(jù)庫(kù)文件何時(shí)需要增長(zhǎng)。本教程一共分為三個(gè)部分，這是最后一部分內(nèi)容——監(jiān)視數(shù)據(jù)庫(kù)文件增長(zhǎng)。（上篇查看點(diǎn)擊這里，中篇查看點(diǎn)擊這里）

監(jiān)視數(shù)據(jù)庫(kù)文件增長(zhǎng)

如果正確完成了初始數(shù)據(jù)和日志大小調(diào)整，并且如上所述，您正在仔細(xì)監(jiān)視文件的使用情況，那么使用文件自動(dòng)增長(zhǎng)功能，可以預(yù)期物理文件大小會(huì)增加，而不是臨時(shí)增加。

但是，眾所周知，為新數(shù)據(jù)庫(kù)預(yù)測(cè)數(shù)據(jù)存儲(chǔ)要求非常困難，甚至由于各種原因，即使是已建立的數(shù)據(jù)庫(kù)有時(shí)也可能會(huì)意外地自動(dòng)增長(zhǎng)。意外的數(shù)據(jù)導(dǎo)入，導(dǎo)致事務(wù)無(wú)休止地打開(kāi)（防止日志截?cái)啵┑能浖e(cuò)誤，維護(hù)操作等等，都可能導(dǎo)致意外的快速增長(zhǎng)。當(dāng)數(shù)據(jù)庫(kù)爆炸性增長(zhǎng)時(shí)，它將導(dǎo)致文件自動(dòng)增長(zhǎng)事件，這可能會(huì)消耗大量CPU資源。如果發(fā)生在繁忙時(shí)段，則可能導(dǎo)致應(yīng)用程序進(jìn)程阻塞和中斷。

依靠自動(dòng)增長(zhǎng)不是一個(gè)好的文件大小管理策略。應(yīng)該啟用它，但僅將其用于提供“安全網(wǎng)”，以適應(yīng)文件突然增長(zhǎng)和意外增長(zhǎng)的情況（假設(shè)它們還確保容納數(shù)據(jù)和日志文件的磁盤卷具有足夠的空間來(lái)容納意外增長(zhǎng)）。

換句話說(shuō)，發(fā)生了自動(dòng)增長(zhǎng)，您需要了解它并調(diào)查原因。

臨時(shí)跟蹤數(shù)據(jù)庫(kù)增長(zhǎng)

跟蹤數(shù)據(jù)庫(kù)增長(zhǎng)的一種經(jīng)典方法是使用msdb.dbo.backupset，從每個(gè)數(shù)據(jù)庫(kù)的夜間完整備份中捕獲數(shù)據(jù)庫(kù)增長(zhǎng)，并在Excel中隨時(shí)間跟蹤它。另外，sp_databases系統(tǒng)存儲(chǔ)過(guò)程將為您提供實(shí)例上所有數(shù)據(jù)庫(kù)的總大小（以KB為單位），或者您可以使用各種系統(tǒng)表和視圖。

目錄視圖的size列sys.database_files以8 KB頁(yè)的形式提供每個(gè)數(shù)據(jù)庫(kù)文件的大小，因此以GB為單位計(jì)算數(shù)據(jù)庫(kù)的總大小很簡(jiǎn)單：

SELECT SUM(size) * 8.0 / 1024.0 / 1024.0 AS SizeInGB
FROM sys.database_files;

您可以通過(guò)查詢默認(rèn)跟蹤收集的數(shù)據(jù)來(lái)找出哪個(gè)數(shù)據(jù)庫(kù)最近經(jīng)歷了自動(dòng)增長(zhǎng)（或自動(dòng)收縮）事件。但是，默認(rèn)跟蹤文件確實(shí)會(huì)滾動(dòng)，因此歷史數(shù)據(jù)將被覆蓋，并且自上次滾動(dòng)以來(lái)，您只會(huì)看到最近的自動(dòng)增長(zhǎng)事件。

在SQL Monitor中監(jiān)視數(shù)據(jù)庫(kù)增長(zhǎng)

該總數(shù)據(jù)庫(kù)文件大小在SQL監(jiān)視器自訂指標(biāo)收集和分析數(shù)據(jù)sys.database_files（使用以前的查詢），隨著時(shí)間的推移。如果此度量標(biāo)準(zhǔn)檢測(cè)到整個(gè)數(shù)據(jù)庫(kù)大小發(fā)生了變化，則數(shù)據(jù)庫(kù)可能正在以意外的速度增長(zhǎng)，因此DBA需要了解原因。數(shù)據(jù)庫(kù)增長(zhǎng)是由數(shù)據(jù)增長(zhǎng)還是日志文件增長(zhǎng)引起的？

SQL Monitor提供了數(shù)據(jù)庫(kù)自動(dòng)增長(zhǎng)自定義指標(biāo)，該指標(biāo)將按計(jì)劃從默認(rèn)跟蹤中收集數(shù)據(jù)。如果指標(biāo)數(shù)據(jù)的收集周期比跟蹤文件滾動(dòng)的周期短，那么您將不會(huì)錯(cuò)過(guò)任何增長(zhǎng)事件，并且可以為此指標(biāo)設(shè)置警報(bào)，因此您會(huì)立即意識(shí)到發(fā)生了異常的自動(dòng)增長(zhǎng)事件。

監(jiān)控?cái)?shù)據(jù)文件的增長(zhǎng)

當(dāng)數(shù)據(jù)文件增長(zhǎng)時(shí)，它們可以使用即時(shí)文件初始化，從而允許SQL Server分配更多的磁盤空間，而無(wú)需在可以將任何數(shù)據(jù)寫(xiě)入到其中之前對(duì)所有這些空間進(jìn)行零初始化。這使得每個(gè)增長(zhǎng)事件都相對(duì)高效，但是如果文件需要頻繁擴(kuò)展，它仍然會(huì)導(dǎo)致阻塞問(wèn)題，因?yàn)槠渌麛?shù)據(jù)庫(kù)處理必須暫停，直到增長(zhǎng)事件完成為止。隨著時(shí)間的流逝，它也可能導(dǎo)致物理文件碎片化。

前面描述的數(shù)據(jù)庫(kù)文件使用率和警報(bào)為您提供了監(jiān)視數(shù)據(jù)文件增長(zhǎng)，以及文件內(nèi)空間使用所需的診斷數(shù)據(jù)。但是，您也可以使用數(shù)據(jù)庫(kù)文件大小自定義指標(biāo)（該指標(biāo)從sys.dm_os_performance_counters動(dòng)態(tài)管理視圖（DMV）收集值）來(lái)跟蹤數(shù)據(jù)文件大小的變化。

監(jiān)視日志文件增長(zhǎng)

SQL Server寫(xiě)日志是為了添加、刪除或修改數(shù)據(jù)的每個(gè)事務(wù)，以及響應(yīng)數(shù)據(jù)庫(kù)維護(hù)操作（例如索引重建或重組，統(tǒng)計(jì)信息更新等），將日志寫(xiě)入日志。即使是最勤奮的DBA有時(shí)也可能會(huì)因數(shù)據(jù)庫(kù)日志文件意外地快速增長(zhǎng)而陷入困境，因此我們將更詳細(xì)地考慮日志增長(zhǎng)。

與數(shù)據(jù)文件相比，事務(wù)日志文件無(wú)法利用即時(shí)文件初始化的優(yōu)勢(shì)，因此，每個(gè)日志增長(zhǎng)事件在時(shí)間和資源上都相對(duì)昂貴。發(fā)生這種情況時(shí)，其他任何事務(wù)都將無(wú)法使用事務(wù)日志，并且數(shù)據(jù)庫(kù)將是“只讀的”，直到增長(zhǎng)事件完成為止。

快速的日志增長(zhǎng)可能是由于大規(guī)模數(shù)據(jù)或數(shù)據(jù)庫(kù)修改（例如，由于索引重建，長(zhǎng)時(shí)間運(yùn)行的數(shù)據(jù)清除或歸檔過(guò)程）或未提交的事務(wù)（防止日志中的空間重用）而導(dǎo)致的。

圖8顯示了SQL Monitor中的分析圖。我在圖表上僅繪制了兩個(gè)指標(biāo)，一個(gè)測(cè)試數(shù)據(jù)庫(kù)（MyTestDB）的日志文件總大小，以及該實(shí)例的機(jī)器處理器時(shí)間。它顯示了一段爆炸性的事務(wù)日志增長(zhǎng)時(shí)期。

圖8

圖9顯示了爆炸性日志增長(zhǎng)期間的服務(wù)器指標(biāo)圖，以及該期間運(yùn)行的最昂貴的查詢。

圖9

在這種情況下，（人為）原因是對(duì)包含幾百萬(wàn)行的Persons表的一系列更新。但是，在歸檔數(shù)據(jù)時(shí)，大規(guī)模數(shù)據(jù)清除可能會(huì)產(chǎn)生類似的影響。SQL Server必須將更改記錄到數(shù)據(jù)的每一行，并且由于存在約束而加劇了這種情況，并且觸發(fā)器加劇了問(wèn)題。

例如，如果您要執(zhí)行數(shù)據(jù)清除，并且表通過(guò)FOREIGN KEY設(shè)計(jì)為的約束來(lái)引用目標(biāo)表CASCADE ON DELETE，則SQL Server還將記錄通過(guò)級(jí)聯(lián)約束刪除的行的詳細(xì)信息。如果表上有DELETE觸發(fā)器，則為了審核數(shù)據(jù)更改，SQL Server還將記錄觸發(fā)器執(zhí)行期間執(zhí)行的操作。所有這些都會(huì)導(dǎo)致爆炸性的原木增長(zhǎng)，有時(shí)甚至?xí)茐淖钚燎诘脑鲩L(zhǎng)預(yù)測(cè)計(jì)算。

在大規(guī)模數(shù)據(jù)更改期間控制日志增長(zhǎng)

良好的做法是小批量運(yùn)行大規(guī)模數(shù)據(jù)操作，如果在下一個(gè)CHEKPOINT運(yùn)行（如果數(shù)據(jù)庫(kù)使用SIMPLE恢復(fù)模型），或者在下一個(gè)日志備份（如果運(yùn)行），則可以在事務(wù)日志之間進(jìn)行截?cái)唷?數(shù)據(jù)庫(kù)使用FULL或BULK_LOGGED恢復(fù)模型。

在本示例中，MyTestDB數(shù)據(jù)庫(kù)在FULL恢復(fù)模型下運(yùn)行，因此僅在發(fā)生日志備份之后，才能截?cái)嗍聞?wù)日志并重用現(xiàn)有空間。在這種情況下，數(shù)據(jù)庫(kù)的大小增長(zhǎng)到超過(guò)20 GB（從最初的1 GB以下），完全是由于日志文件的增長(zhǎng)（先前的數(shù)據(jù)文件使用情況警報(bào)未觸發(fā)，因?yàn)閿?shù)據(jù)文件中未使用空間）。

SQL Monitor提供了一個(gè)自定義的“大事務(wù)日志文件”度量標(biāo)準(zhǔn)，可在具有大日志（超過(guò)10 GB，但可配置）的數(shù)據(jù)庫(kù)數(shù)量增加時(shí)發(fā)出警報(bào)。

如果由于某種原因阻止了日志空間的重用，則需要查詢sys.databases中的log_reuse_wait_desc列，以找出原因。 這通常是由于事務(wù)運(yùn)行時(shí)間長(zhǎng)或缺少日志備份（SQL Monitor也應(yīng)該提醒您！），但是還有其他可能的原因。

與不受控制的日志增長(zhǎng)相關(guān)的問(wèn)題（尤其是當(dāng)日志文件配置為以小增量增長(zhǎng)時(shí)）會(huì)導(dǎo)致日志碎片，其中日志在內(nèi)部由大量的虛擬日志文件（VLF）組成。在上面的示例中，日志通過(guò)反復(fù)快速地增長(zhǎng)，最終獲得了超過(guò)2000個(gè)VLFS，如運(yùn)行所示DBCC LOGINFO。“內(nèi)部碎片化”日志可能會(huì)降低讀取日志的操作的性能，例如日志備份、復(fù)制和鏡像過(guò)程。它還可能減慢崩潰恢復(fù)的速度，因?yàn)镾QL Server必須在開(kāi)始恢復(fù)數(shù)據(jù)庫(kù)之前打開(kāi)日志并讀取每個(gè)VLF。

設(shè)置FILEGROWTH日志的值時(shí)，您都希望避免因允許日志以小增量增長(zhǎng)而導(dǎo)致的碎片化。但是不要過(guò)度使用它，因?yàn)槿绻麑⒃隽吭O(shè)置得太高，則可能會(huì)在正常的事務(wù)日志增長(zhǎng)事件期間發(fā)生超時(shí)（由于需要對(duì)所有空間進(jìn)行零初始化）。通常建議使用固定的512MB自動(dòng)增長(zhǎng)大小作為指導(dǎo)，但是最好了解您自己系統(tǒng)上的日志增長(zhǎng)特征和行為并相應(yīng)地設(shè)置自動(dòng)增長(zhǎng)大小。

總結(jié)

您可以手動(dòng)設(shè)置警報(bào)，以在磁盤空間不足時(shí)發(fā)出警告，并希望及時(shí)提供更多空間以避免任何數(shù)據(jù)庫(kù)“中斷”。但是，這種反應(yīng)性磁盤空間管理僅能帶您到達(dá)目的地，因?yàn)樗鼰o(wú)法讓您知道這種情況將在多久之前發(fā)生。

勤奮的DBA希望通過(guò)了解數(shù)據(jù)庫(kù)文件中空間的使用速度來(lái)避免磁盤用盡的機(jī)會(huì)。諸如SQL Monitor之類的工具可以非常輕松地捕獲這些數(shù)據(jù)，但是它還可以做很多事情。它分析收集到的數(shù)據(jù)以捕獲數(shù)據(jù)增長(zhǎng)趨勢(shì)，并使用它們來(lái)準(zhǔn)確預(yù)測(cè)何時(shí)您將用完空間。這使您有機(jī)會(huì)進(jìn)行計(jì)劃，而不僅僅是作出反應(yīng)，并最大程度地減少由于文件填滿或觸發(fā)文件自動(dòng)增長(zhǎng)而造成的任何干擾。

監(jiān)視磁盤和數(shù)據(jù)庫(kù)增長(zhǎng)數(shù)據(jù)時(shí)，隨著時(shí)間的推移，您將更好地了解數(shù)據(jù)庫(kù)的增長(zhǎng)特性，并提高磁盤容量調(diào)整和計(jì)劃的準(zhǔn)確性。這樣，文件自動(dòng)增長(zhǎng)事件將是一個(gè)例外，而不是正常情況，因此，您可以跟蹤自動(dòng)增長(zhǎng)事件，并在事件發(fā)生時(shí)得到警告，并調(diào)查導(dǎo)致意外增長(zhǎng)的原因。

本教程內(nèi)容到這里就完結(jié)了，希望對(duì)您有所幫助~您可以點(diǎn)擊下方鏈接查看該教程前面兩部分內(nèi)容，或者下載SQL Monitor試用版體驗(yàn)一下~

相關(guān)內(nèi)容推薦：

使用SQL Monitor避免耗盡磁盤空間（上）：監(jiān)視磁盤上的可用空間

使用SQL Monitor避免耗盡磁盤空間（中）：監(jiān)視數(shù)據(jù)庫(kù)文件中的可用空間

想要購(gòu)買SQL Monitor正版授權(quán)，或了解更多產(chǎn)品信息請(qǐng)點(diǎn)擊