數據庫碎片整理和自動增長設置

我們確實為我們的 SQL Server 2008 r2 express 制定了一些維護計劃。如果任何表的頁數超過 50 並且平均碎片超過 20，我們每個月都會對數據庫進行碎片整理。

如果數據庫日誌大小>2 MB，則恢復模式設為簡單，並縮小，恢復模式設置回 FULL。如果 Page_count>50 且 avg_fragmentation_in_percent > 30，則索引為 REBUILD。

如果 Page_count>50 且 avg_fragmentation_in_percent > 5 且 <30，則索引為 REORGANIZE。

這就是我們目前正在做的事情。但是我們發現自動增長事件是資源密集型的，不應該重複發生。現在對於所有數據庫，mdf 文件的自動增長設置為 MB，ldf 文件設置為 10%，這是創建新數據庫時的預設值。我們計劃根據數據庫每天變大的程度來增加數據庫的自動增長值。但是我想知道數據庫有多少自動增長事件是理想的。我應該設置 autogroth 以便它每天、每週或每月只發生一次等。所以請幫我為我的數據庫設置自動增長值。如果我每月對數據庫進行碎片整理，還有另一個問題，那麼它將被縮小。因此，在此之後，對於我確實收縮自動增長的所有數據庫，當新數據寫入時會發生一次。所以會有很多自動增長事件。那麼會不會有問題呢？請告訴我一個解決方案。

如果任何表的頁數更多

$$ than $$50$$ pages $$

那仍然非常小。對於少於1000 頁的任何索引，我通常不會考慮碎片。

和 avg_fragmentation_in_percent > 30 則索引為 REBUILD

這是重建的一個很好的門檻值，但我也會考慮使用 5% 到 30% 範圍內的碎片來重組索引。

如果數據庫日誌大小>2 MB，則將恢復模式設為簡單，並對其進行收縮，並將恢復模式設置回 FULL

你為什麼做這個？如果日誌文件大於 2 MB，為什麼會出現問題？這是非常小的。更不用說，當您切換到SIMPLE、縮小，然後返回FULL恢復時，您正在破壞日誌鏈。您可能會因時間點恢復而陷入嚴重麻煩。 適當調整日誌文件的大小，並僅在緊急情況下允許自動增長。 收縮數據庫文件只應在極端情況下進行，絕不應作為日常維護計劃的一部分。

但是我們發現自動增長事件是資源激勵，不應該重複發生

正確的。這就是尺寸發揮作用的地方。監控並在使用一定百分比的空間時收到警報。這樣，您可以為維護視窗計劃手動文件增長操作，以最大限度地減少對最終使用者的影響。

10% 用於 ldf 文件，這是創建新數據庫時的預設值

即使百分比文件增長是預設的，我還是建議不要這樣做。您不希望有可變數量的文件增長。如果您必須依靠自動增長，那麼您想知道文件將增長多少。更不用說，隨著日誌文件的增長，您將擁有可變數量的虛擬日誌文件 (VLF) 以及額外的空間。您想巧妙地計劃這一點，這可以通過固定數量的文件增長來完成。

我應該設置 autogroth 以便它每天、每週或每月只發生一次等

為固定的空間量（不是百分比）設置自動增長，只有你可以確定什麼是好的數量。太小，您將頻繁地自動增長，對最終使用者的影響最小，但經常發生。自動增長設置得太高，那麼你的增長頻率就會降低，但當它發生時，影響將持續更長的時間。

我的推薦？監控數據庫文件中使用的空間。當它達到某個百分比（例如，已使用 80% 的空間）時，您應該收到警報。然後在維護視窗期間安排手動增長。

托馬斯·斯金格的觀點是你回答所需要的。但我會更深入地了解您如何確定自動增長設置？

但我想知道數據庫有多少自動增長事件是理想的。我應該設置 autogroth 以便它每天、每週或每月只發生一次等等。所以請幫我為我的數據庫設置自動增長值。還有另一個問題。如果我每月對數據庫進行碎片整理，那麼它將被縮小。因此，在此之後，對於我確實收縮自動增長的所有數據庫，當新數據寫入時會發生一次。所以會有很多自動增長事件。那麼會不會有問題呢？

沒有數學公式可以計算您的自動增長設置，尤其是當您不做數據庫基線時。

現在，正如@ThomasStringer 指出的那樣，您不應該讓數據庫自動增長以 % 為單位，而是將其設置為 MB，您可以使用預設的 Trace找出伺服器實例上發生的自動增長事件。

--below code will help you in finding autogrowth events on your server instance.
IF OBJECT_ID('tempdb..#autogrowthTotal') IS NOT NULL
   DROP TABLE #autogrowthTotal;

IF OBJECT_ID('tempdb..#autogrowthTotal_Final') IS NOT NULL
   DROP TABLE #autogrowthTotal_Final;

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1
   AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.', @filename);
SET @ec = CHARINDEX('_', @filename) + 1;
SET @efn = REVERSE(SUBSTRING(@filename, 1, @bc));
SET @bfn = REVERSE(SUBSTRING(@filename, @ec, LEN(@filename)));
-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT ftg.StartTime
   ,te.NAME AS EventName
   ,DB_NAME(ftg.databaseid) AS DatabaseName
   ,ftg.[FileName] AS LogicalFileName
   ,(ftg.IntegerData * 8) / 1024.0 AS GrowthMB
   ,(ftg.duration / 1000) AS DurMS
   ,mf.physical_name AS PhysicalFileName
INTO #autogrowthTotal
FROM::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
INNER JOIN sys.master_files mf ON (mf.database_id = ftg.databaseid)
   AND (mf.NAME = ftg.[FileName])
WHERE (
       ftg.EventClass = 92 -- Data File Auto-grow
       OR ftg.EventClass = 93
       ) -- Log File Auto-grow
ORDER BY ftg.StartTime

SELECT count(1) AS NoOfTimesEventFired
   ,CONVERT(VARCHAR(10), StartTime, 120) AS StartTime
   ,EventName
   ,DatabaseName
   ,[LogicalFileName]
   ,PhysicalFileName
   ,SUM(GrowthMB) AS TotalGrowthMB
   ,SUM(DurMS) AS TotalDurationMS
INTO #autogrowthTotal_Final
FROM #autogrowthTotal
GROUP BY CONVERT(VARCHAR(10), StartTime, 120)
   ,EventName
   ,DatabaseName
   ,[LogicalFileName]
   ,PhysicalFileName
--having count(1) > 5 or SUM(DurMS)/1000 > 60 -- change this for finetuning....
ORDER BY CONVERT(VARCHAR(10), StartTime, 120)

SELECT *
FROM #autogrowthTotal_Final

下面將是輸出

注意：我在圖像中突出顯示了每列的含義以及您應該查找的內容。

基本上，您必須在一段時間內監控您的自動增長事件，例如在高活動期間或整個業務週期中，然後對其進行平均將為您提供一些您可以為自動增長設置選擇的確切值。

現在，對於日誌文件，您還必須考慮索引維護，CHECKDB執行等因素。因此，調整日誌文件的大小以支持數據庫中發生的數據更改量，並經常進行日誌備份，以便快速重用空間在日誌文件中。

此外，值得一提的是，您還應該啟用即時文件初始化。僅適用於數據文件！

請參閱數據文件大小管理的重要性，尤其是 Paul Randal 的數據文件增長和數據文件收縮。

注意：不要縮小數據庫，除非您對數據進行了大規模清除，並且您確信數據庫不會再次增長到那麼大。它會導致碎片化，數據庫意味著增長！

引用自：https://dba.stackexchange.com/questions/46680