在 Azure SQL 受控執行個體中管理資料庫的檔案空間

發行項
07/02/2024

本文涵蓋如何在 Azure SQL 受控執行個體中監視和管理資料庫中的檔案。我們會檢閱如何監視資料庫檔案大小、壓縮交易記錄、放大交易記錄檔，以及控制交易記錄檔的成長。

本文適用於 Azure SQL 受控執行個體。儘管非常類似，如需在 SQL Server 中管理交易記錄檔大小的資訊，請參閱管理交易記錄檔的大小。

了解資料庫的儲存體空間類型

要管理資料庫的檔案空間，務必要了解下列儲存體空間數量。

資料庫數量	定義	註解
已使用的資料空間	用來儲存資料庫資料的空間量。	一般而言，使用的空間會在插入 (刪除) 時增加 (減少)。在某些情況下，根據作業與任何分割中涉及的資料量和模式而定，使用的空間並不會隨插入或刪除而變更。例如，從每個資料頁刪除一個資料列，不見得會減少使用的空間。
已配置的資料空間	可用以儲存資料庫資料的格式化檔案空間量。	配置的空間量會自動成長，但絕不會在刪除後減少。此行為可確保未來能更快地插入，因為不需要重新格式化空間。
已配置但未使用的資料空間	已配置的資料空間量與已使用的資料空間之間的差異。	此數量代表可藉由壓縮資料檔案而回收的可用空間量上限。
資料大小上限	可用來儲存資料庫資料的空間量上限。	配置的資料空間量不得成長超過資料大小上限。

下圖說明資料庫的不同儲存體空間類型之間的關聯性。

示範資料庫數量資料表中差異資料庫空間概念的大小的圖表。

查詢單一資料庫來取得檔案空間資訊

使用 sys.database_files 上的下列查詢，以傳回已配置的資料庫檔案空間量與已配置但未使用的空間量。查詢結果以 MB 為單位。

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

監視記錄空間的使用

使用 sys.dm_db_log_space_usage 來監視記錄空間的使用。這個 DMV 會傳回目前使用之記錄空間量的相關資訊，並指出交易記錄需要截斷的時機。

如需目前的記錄檔大小、大小上限及檔案的自動成長選項等詳細資訊，也可以在 sys.database_files 中使用該記錄檔的 size、max_size 和 growth 資料行。

Azure Resource Manager 計量型 API 中顯示的儲存空間計量僅測量已使用的資料頁面大小。如需範例，請參閱 PowerShell get-metrics。

壓縮記錄檔大小

若要藉由移除未使用之空間來減少實體記錄檔的實體大小，必須壓縮記錄檔。當交易記錄檔包含未使用的空間時，壓縮才會產生差異。如果記錄檔已滿 (可能是因為開啟的交易)，請調查阻止交易記錄截斷的原因。

警告

壓縮作業不應視為一般維修作業。因定期商務作業成長的資料和記錄檔，不需要壓縮作業。壓縮命令會在資料庫執行時影響其效能，如果可能，應該在低使用量期間執行。如果一般應用程式工作負載會讓檔案重新成長到相同的配置大小，即不建議您壓縮資料檔案。

請留意壓縮資料庫檔案可能對效能造成負面的影響，請參閱壓縮後的索引維護。在少數情況下，壓縮作業可能會受到自動化資料庫備份的影響。如有必要，請重試壓縮作業。

壓縮交易記錄檔之前，請記住可能會延遲記錄截斷的因素。如果壓縮記錄檔之後再次需要儲存空間，交易記錄檔將再次成長，並且會因此在記錄檔成長作業期間導入效能額外負荷。如需詳細資訊，請參閱建議。

只有當資料庫已上線，而且至少有一個虛擬記錄檔 (VLF) 可用時，您才能壓縮記錄檔。在某些情況下，壓縮記錄可能要等到下一個記錄截斷之後才能進行。

如長時間執行的交易之類的因素，使 VLF 保持作用中一段很長的時間，可能限制記錄檔壓縮，甚至完全阻止記錄檔壓縮。如需資訊，請參閱可能會延遲記錄截斷的因素。

壓縮記錄檔會移除一或多個不保留任何邏輯記錄的 VLF (即「非使用中 VLF」)。壓縮交易記錄檔時，會從記錄檔的結尾移除非使用中的 VLF，將記錄縮減至大約目標大小。

如需有關壓縮操作的詳細資訊，請檢閱下列內容：

壓縮記錄檔 (但不壓縮資料庫檔案)

監視記錄檔壓縮事件

Log File Auto Shrink Event Class。

監視記錄空間

sys.dm_db_log_space_usage (Transact-SQL)
sys.database_files (Transact-SQL) (請參閱記錄檔的 size、max_size 和 growth 資料行。)

壓縮後的索引維修

完成資料檔案的壓縮作業後，索引可能會變得分散。這會降低特定工作負載 (例如使用大型掃描的查詢) 的效能最佳化有效性。如果壓縮作業完成後發生效能降低的情形，請考慮執行索引維修來重建索引。請記得，索引重建需要資料庫具有可用空間，並可能導致已配置的空間增加，抵銷壓縮的效果。

如需索引維修的詳細資訊，請參閱將索引維修最佳化以改善查詢效能並降低資源耗用量。

評估索引頁密度

如果截斷資料檔案未充分減少配置空間，則可以決定壓縮資料庫資料檔案，以從這些檔案回收未使用的空間。但請先判斷資料庫索引的頁面平均密度，這個步驟為選擇性但建議您採用。如果以相同數量的資料而言，頁面密度高時，壓縮會更快完成，因為必須移動的頁面較少。壓縮資料檔案前，如果部分索引的頁面密度較低，請考慮維修這些索引，增加頁面密度。這也會讓壓縮大幅減少配置的儲存空間。

若要判斷資料庫中所有索引的頁面密度，請使用下列查詢。頁面密度會在 avg_page_space_used_in_percent 資料行中報告。

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

壓縮資料檔案前，如果索引包含高頁面計數，但頁面密度低於 60-70%，請考慮重建或重新組織這些索引。

注意

如果是較大的資料庫，判斷頁面密度的查詢可能需要較長時間 (數小時) 才能完成。此外，重建或重新組織大型索引也需要大量的時間和資源使用量。在花費額外時間增加頁面密度，與縮短壓縮時間並達成較高的空間節省之間需各有取捨。

如果有多個包含低頁面密度的索引，您可以在多個資料庫工作階段平行重建這些索引，加速處理程序。但請確定這麼做不會接近資料庫資源限制，並為應用程式工作負載保留足夠的資源空餘空間。在 Azure 入口網站或使用 sys.dm_db_resource_stats 檢視來監視資源耗用量 (CPU、資料 IO、記錄 IO)，並只在這些維度的個別資源使用率大幅低於 100% 時，才啟動額外的平行重建。如果 CPU、資料 IO 或記錄 IO 利用率為 100%，您可以擴大資料庫，使用更多 CPU 核心並增加 IO 輸送量，以支援額外的並行重建，從而更快地完成流程。

索引重建命令範例

以下是使用 ALTER INDEX 陳述式重建索引並增加索引頁面密度的範例命令：

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8, 
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), 
RESUMABLE = ON);

此命令會初始化線上且可繼續的索引重建。如此一來，並行工作負載可在重建執行的同時繼續使用資料表，且您可在重建因任何理由中斷時將其繼續。但比起會封鎖資料表存取的離線重建，此類重建的速度較慢。重建時，如果沒有其他工作負載需要存取資料表，請將 ONLINE 和 RESUMABLE 選項設為 OFF，並移除 WAIT_AT_LOW_PRIORITY 子句。

若要深入了解索引維修，請參閱將索引維修最佳化以改善查詢效能並降低資源耗用量。

壓縮多個資料檔案

如先前所述，使用資料移動壓縮須花費長時間執行。如果資料庫有多個資料檔案，您可以平行壓縮多個資料檔案，加速處理程序。若要這麼做，請開啟多個資料庫工作階段，並以不同的 file_id 值在各工作階段使用 DBCC SHRINKFILE。類似於先前重建索引，在開始每個新的平行壓縮命令前，請確定您有足夠的資源空餘空間 (CPU、資料 IO、記錄 IO)。

下列範例命令會壓縮 file_id 4 的資料檔案，並移動檔案中的頁面，嘗試減少配置大小至 52,000 MB：

DBCC SHRINKFILE (4, 52000);

若要盡量減少檔案的配置空間，在執行陳述式時，請不要指定目標大小：

DBCC SHRINKFILE (4);

如果工作負載與壓縮同時執行，工作負載可能會在壓縮完成並截斷檔案前就開始使用壓縮所釋放出來的儲存空間。這時，壓縮無法將配置空間減少至指定的目標。

您可以細分步驟，先壓縮各檔案來減輕這個問題。這表示在 DBCC SHRINKFILE 命令中，設定略小於檔案目前配置空間的目標。例如，如果 file_id 4 的檔案配置空間為 200,000 MB，而您想要將其壓縮為 100,000 MB，可先將目標設為 170,000 MB：

DBCC SHRINKFILE (4, 170000);

完成後，此命令會截斷檔案，並減少配置大小至 170,000 MB。接著您可以重複此命令，先將目標設為 140,000 MB，然後是 110,000 MB，以此類推，直到檔案壓縮至所需大小。如果命令完成，但未截斷檔案，請再細分步驟，改為每次減少 15,000 MB 而非 30,000 MB。

若要監視所有同時執行的壓縮工作階段的壓縮程序，您可以使用下列查詢：

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

注意

壓縮程序可能非線性，即使壓縮仍在進行中，percent_complete 資料行的值也可能長時間保持基本不變。

完成所有資料檔案的壓縮後，請使用空間使用量查詢，判斷配置儲存大小的減少結果。如果已使用空間與配置空間仍有很大的差異，您可以重建索引。這可能暫時進一步增加配置的空間，但在重建索引後再度壓縮資料檔案，應該會大幅減少配置的空間。

放大記錄檔

在 Azure SQL 受控執行個體中，藉由放大現有的記錄檔 (如果磁碟空間允許)，新增空間至記錄檔。不支援新增記錄檔至資料庫。除非記錄檔空間不足，且保存記錄檔的磁碟區上磁碟空間也不足，否則一個交易記錄檔便已足夠。

若要加大記錄檔，請使用 ALTER DATABASE 陳述式的 MODIFY FILE 子句，並指定 SIZE 和 MAXSIZE 語法。如需詳細資訊，請參閱 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項。

如需詳細資訊，請參閱建議。

控制交易記錄檔的成長

請使用 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項陳述式來管理交易記錄檔的成長。請注意：

若要變更目前的檔案大小 (單位為 KB、MB、GB 和 TB)，請使用 SIZE 選項。
若要變更成長的增量，請使用 FILEGROWTH 選項。 0 的值表示將自動成長設為關閉，而且不允許任何其他空間。
若要控制記錄檔大小的最大值 (單位為 KB、MB、GB 和 TB) 或是將成長設定為 UNLIMITED，請使用 MAXSIZE 選項。

建議

以下是關於使用交易記錄檔時的一般建議：

交易記錄檔的自動成長 (autogrow) 增量，如 FILEGROWTH 選項所設定，必須要夠大才能保持領先工作負載交易的需求。記錄檔的檔案成長量應夠大，才不用經常進行擴充。若要適當設定交易記錄檔的大小，有一項良好的指標就是監視下列期間所佔用的記錄檔數量：
- 執行完整備份所需的時間，因為直到完成為止才會發生記錄檔備份。
- 最大索引維護作業所需的時間。
- 執行資料庫中最大批次所需的時間。
使用 FILEGROWTH 選項設定資料和記錄檔的 autogrow 時，最好以 size 來設定它，而不是使用 percentage，以便更能控制成長比率，因為百分比是個不斷成長的數量。
- 在 Azure SQL 受控執行個體中，檔案立即初始化可讓交易記錄成長事件受益多達 64 MB。新資料庫的預設自動成長大小增量為 64 MB。大於 64 MB 的交易記錄檔自動成長事件無法受益於檔案立即初始化。
- 最佳做法是不要將交易記錄的 FILEGROWTH 選項值設定為超過 1024 MB。
小型的自動增長增量可能會產生太多小型 VLF，且可能會降低效能。若要判斷指定執行個體中所有資料庫的目前交易記錄大小的最佳 VLF 分佈，以及達到所需大小的必要增長增量，請參閱 SQL Tiger 團隊提供的用於分析和修正 VLF 的指令碼。
大型自動增長增量可能會導致兩個問題：
- 大型自動增長增量可能會導致資料庫在配置新空間時暫停，並可能導致查詢逾時。
- 大型自動增長增量可能會產生太少且大型的 VLF，且亦可能會降低效能。若要判斷指定執行個體中所有資料庫的目前交易記錄大小的最佳 VLF 分佈，以及達到所需大小的必要增長增量，請參閱 SQL Tiger 團隊提供的用於分析和修正 VLF 的指令碼。
如果無法成長得夠快速以滿足查詢的需求，即使已啟用 autogrow，您還是可能收到訊息，指出交易記錄檔已滿。如需變更成長增量的詳細資訊，請參閱 ALTER DATABASE (Transact-SQL) 檔案及檔案群組選項。
可以將記錄檔設定為自動壓縮。不過並不建議如此，且 auto_shrink 資料庫屬性預設會設定為 FALSE。如果 auto_shrink 設定為 TRUE，只有當超過 25% 的空間未使用時，自動壓縮才會減少檔案的大小。
- 此時，檔案會壓縮成只有 25% 的檔案是未使用空間的大小，或檔案的原始大小，以較大者為準。
- 如需變更 auto_shrink 屬性設定的資訊，請參閱檢視或變更資料庫的屬性和 ALTER DATABASE SET 選項 (Transact-SQL)。

共用方式為

在 Azure SQL 受控執行個體中管理資料庫的檔案空間

了解資料庫的儲存體空間類型

查詢單一資料庫來取得檔案空間資訊

監視記錄空間的使用

壓縮記錄檔大小

壓縮後的索引維修

評估索引頁密度

索引重建命令範例

壓縮多個資料檔案

放大記錄檔

控制交易記錄檔的成長

建議

意見反應

其他資源

共用方式為

在 Azure SQL 受控執行個體中管理資料庫的檔案空間

了解資料庫的儲存體空間類型

查詢單一資料庫來取得檔案空間資訊

監視記錄空間的使用

壓縮記錄檔大小

壓縮後的索引維修

評估索引頁密度

索引重建命令範例

壓縮多個資料檔案

放大記錄檔

控制交易記錄檔的成長

建議

相關內容

意見反應

其他資源