探索資料庫維護檢查

  • 7 分鐘

查詢最佳化工具會利用索引中的統計資訊，嘗試建立最佳的執行計畫。

在為您處理的 Azure SQL 維護工作中 (例如備份和完整性檢查)，雖然您可以利用自動更新來減輕負擔，並讓您的統計資料保持在最新狀態，但有時還是不夠。

擁有良好的索引和統計資料，可確保任何指定計畫都能以最佳效率執行。 當資料庫中的資料隨著時間變動時，應定期執行索引維護。 您可以根據資料的修改頻率來變更索引維護策略。

重建和重組

當索引頁內的邏輯排序與實體排序不符時，就會發生索引片段。 在例行的資料修改陳述式期間 (例如 UPDATE、DELETE 和 INSERT)，頁面可能會順序紊亂。 片段可能會導致效能問題，因為必須有額外的 I/O 才能找出索引頁內指標所參考的資料。

從索引插入、更新和刪除資料時，索引中的邏輯順序將不再符合索引所組成頁面內部及之間的實體順序。 此外，一段時間後的資料修改，可能會使資料庫中的資料變得很分散或片段。 當資料庫引擎必須讀取額外頁面以找出所需的資料時，片段可能會降低查詢效能。

重新組織索引是一種線上作業，會將索引的分葉層級重組 (叢集化和非叢集化的部分都包含在內)。 此重組流程會實際重新排序分葉層級頁面，以符合由左至右的節點邏輯順序。 在此流程中，系統也會根據已設定的 fillfactor 值壓縮索引頁面。

根據執行的命令或使用的 SQL Server 版本，重建可以線上或離線執行。 離線重建流程會卸除並重新建立索引本身。 如果您可以在線上進行，新索引會以平行方式建立至現有的索引。 一旦新索引建立完成，現有的索引將會卸除，然後重新命名新索引以符合舊索引的名稱。 請記住，線上版會需要額外的空間，因為新索引會平行建立至現有的索引。

索引維護的一般指南如下：

  • > 5% 但 < 30% - 重新組織索引

  • > 30% - 重建索引

這些數值可作為一般建議。 視您的工作負載和資料而定，您可能需要更積極，或是在某些情況下，對於大多執行搜尋特定頁面查詢的資料庫，您或許可以延緩其索引維護。

SQL Server 和 Azure SQL 平台提供 DMV，可讓您偵測物件中的片段。 針對此用途最常用的 DMV 包括 sys.dm_db_index_physical_stats (適用於 B 型樹狀結構索引) 和 sys.dm_db_column_store_row_group_physical_stats (適用於資料行存放區索引)。

另外值得注意的一點是，重建索引會導致更新索引上的統計資料，因此可進一步協助效能。 重新組織索引不會更新統計資料。

Microsoft 在 SQL Server 2017 中引進了可繼續重建索引作業。 可繼續重建索引作業選項提供更大的彈性，讓您控制重建作業在指定的執行個體上可能需要的時間。 SQL Server 2019 推出了可控制關聯性平行處理原則最大程度的功能，進而為資料庫管理員提供更精密的控制。

統計資料

在 Azure SQL 中進行效能微調時，了解統計資料的重要性非常重要。

統計資料會以二進位大型物件 (blob) 的形式儲存在使用者資料庫中。 這些 blob 包含資料表或索引檢視表之一個或多個資料行中資料值分佈的相關統計資料。

統計資料包含資料行內資料值分佈的相關資訊。 查詢最佳化工具會使用資料行和索引統計資料來判斷基數，也就是查詢時預期會傳回的資料列數目。

然後，查詢最佳化工具會使用基數估計值來產生執行計畫。 基數估計值也可協助最佳化工具判斷何種類型的作業 (例如，索引搜尋或掃描) 能用來取出所要求的資料。

若要查看上次更新日期的使用者定義統計資料清單，請執行下列查詢：

SELECT sp.stats_id, 
       name, 
       last_updated, 
       rows, 
       rows_sampled
FROM sys.stats
     CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp
WHERE user_created = 1

建立統計資料

當您將 AUTO_CREATE_STATISTICS 選項設定為 ON 時，查詢最佳化工具預設會在索引資料行上建立統計資料。 查詢最佳化工具也會為查詢述詞中的單一資料行建立統計資料。

這些方法可為大多數查詢提供高品質的查詢計劃。 有時候，您可能需要使用 CREATE STATISTICS 陳述式建立更多統計資料，以改善特定查詢計劃。

建議維持啟用 AUTO_CREATE_STATISTICS 選項，因為這可讓查詢最佳化工具自動為查詢述詞資料行建立統計資料。

每當您遇到下列情況時，請考慮建立統計資料：

  • Database Engine Tuning Advisor 建議您建立統計資料
  • 查詢述詞包含多個尚未存在於相同索引中的資料行
  • 查詢會從資料子集中選取
  • 查詢具有遺失的統計資料

維護工作自動化

Azure SQL 提供原生工具來執行資料庫維護工作，以進行自動化。 視資料庫執行所在的平台而定，可以使用不同的工具。

Azure 虛擬機器上的 SQL Server

您可以存取排程服務，例如 SQL Agent 或 Windows 工作排程器。 這些自動化工具可協助將索引中的片段數量維持在最小值。 使用較大的資料庫時，必須在重建和重組索引之間取得平衡，以確保最佳效能。 SQL Agent 或工作排程器所提供的彈性可讓您執行自訂工作。

Azure SQL Database

由於 Azure SQL Database 的本質，您不會有 SQL Server Agent 或 Windows 工作排程器的存取權。 如果沒有這些服務，就必須使用其他方法來建立索引維護。 您可以透過三種方式來管理 SQL Database 的維護作業：

  • Azure 自動化 Runbook

  • 來自 Azure 虛擬機器中 SQL Server 的 SQL Agent 作業 (遠端呼叫)

  • Azure SQL 彈性作業

Azure SQL 受控執行個體

如同使用 Azure 虛擬機器上的 SQL Server，您可以透過 SQL Server Agent 排程 SQL 受控執行個體上的作業。 使用 SQL Server Agent 可讓您彈性執行經過設計的程式碼，以減少資料庫中索引內的片段。