監視效能並進行疑難排解

  • 10 分鐘

監視和疑難排解是提供一致效能的重要元素。 除了可使用與 SQL Server 相同的工具和功能來監視效能並進行疑難排解之外，Azure SQL 還具備其他功能。 這包括動態管理檢視 (DMV)、擴充事件和 Azure 監視器等功能。 另請務必了解如何在 Azure SQL 的各種效能案例中使用這些工具和功能。 這些案例包括高 CPU 使用率或等候資源。

監視效能的工具和功能

Azure SQL 可提供 Azure 生態系統中的監視和疑難排解功能，以及 SQL Server 隨附的熟悉工具。 下列各節會簡短地描述這些優點。

Azure 監視器

Azure 監視器屬於 Azure 生態系統，且整合後的 Azure SQL 可支援 Azure 計量、警示和記錄。 您可在 Azure 入口網站中將 Azure 監視器的資料視覺化，且應用程式可透過 Azure 事件中樞或 API 來存取此資料。 Azure 監視器與 Windows 效能監視器極為類似，無須使用 SQL Server 工具即可協助存取 Azure SQL 資源使用計量。

動態管理檢視 (DMV)

Azure SQL 提供與 SQL Server 幾乎相同的 DMV 基礎結構，但仍有一些差異。 DMV 對效能監視十分重要，因為您可使用標準 T-SQL 查詢來檢視關鍵的 SQL Server 效能資料。 例如，您可檢視使用中的查詢、資源使用量、查詢計劃和資源等候類型等資訊。 您會在本單元稍後的部分深入了解 Azure SQL 的 DMV 詳細資料。

擴充事件

Azure SQL 提供與 SQL Server 幾乎相同的擴充事件基礎結構，但仍有一些差異。 擴充事件可供追蹤在 SQL Server 中所執行支援 Azure SQL 的關鍵事件。 針對效能部分，擴充事件可讓您追蹤個別查詢的執行。 您會在本單元稍後的部分深入了解 Azure SQL 擴充事件的更多詳細資料。

輕量查詢分析

輕量分析是針對需要擷取實際執行計劃以進行即時要求和高價值查詢之疑難排解案例的進階方法。 由於輕量分析額外負荷很低，任何尚未繫結 CPU 的伺服器都可以持續執行輕量分析，並允許資料庫專業人員隨時查看任何正在執行的作業：例如，使用 SQL Server Management Studio (SSMS) 中的活動監視器，或直接查詢 sys.dm_exec_query_profiles 或 sys.dm_exec_query_statistics_xml。

您可使用輕量查詢分析來檢查使用中查詢的查詢計劃和執行狀態。 這是一項重要功能，可針對執行中陳述式的查詢效能進行偵錯。 比起使用擴充事件之類的工具來追蹤查詢效能，這項功能可縮短解決效能問題的時間。 您可透過 DMV 來存取輕量查詢分析，且其在 Azure SQL 中預設為啟用，就和 SQL Server 2019 及更新版本一樣。

查詢計劃偵錯功能

在某些情況下，您可能會需要個別 T-SQL 陳述式查詢效能的其他詳細資料。 T-SQL SET 陳述式 (例如 SHOWPLAN 和 STATISTICS) 可提供這些詳細資料，且和 SQL Server 一樣可在 Azure SQL 中獲得完全支援。

查詢存放區

查詢存放區可針對儲存在使用者資料庫中的查詢提供效能執行歷程記錄。 根據預設，Azure SQL 會啟用查詢存放區，其用以提供自動計劃修正和自動調整等功能。 Azure SQL 可提供存放區的 SQL Server Management Studio (SSMS) 報告。 您可使用這些報告來尋找耗用最多資源的查詢，包括查詢計劃差異和查看資源等候案例的熱門等候類型。

效能視覺效果

針對 Azure SQL Database，您可在 Azure 入口網站中，透過視覺效果來查看經過整合的查詢存放區效能資訊。 如此一來，您就可以看到一些與使用如 SSMS 等用戶端工具時所看到的相同查詢存放區資訊。 使用 Azure 入口網站中的 [效能概觀] 和 [查詢效能深入解析] 選項。

DMV 詳細資料

長期以來，DMV 與 SQL Server 一直是監視效能並對其進行移難排解的推手。 Azure SQL 提供適用於 SQL Server 的一般 DMV，另外一些則是 Azure 特定的 DMV。

Azure SQL 受控執行個體

所有適用於 SQL Server 的 DMV 都可用於 SQL 受控執行個體。 如 sys.dm_exec_requests 和 sys.dm_os_wait_stats 等關鍵 DMV 通常用於檢查查詢效能。

sys.server_resource_stats 系統檢視是 Azure SQL 受控執行個體所特定，且會顯示歷史資源使用狀況。 因為無法直接存取效能監視器之類的作業系統工具，所以這是查看資源使用狀況的價值性工具。

Azure SQL Database

大部分您需要用於效能的常見 DMV (包括 sys.dm_exec_requests 和 sys.dm_os_wait_stats) 都可使用。 請注意，這些 DMV 僅提供資料庫的特定資訊，而非邏輯伺服器所有資料庫的資訊。

sys.dm_db_resource_stats DMV 是 Azure SQL Database 所特定，且可用來檢視資料庫之資源使用狀況的歷程記錄。 使用此 DMV 類似於對受控執行個體使用 sys.server_resource_stats。

sys.elastic_pool_resource_stats DMV 與 sys.dm_db_resource_stats 類似，但您可以使用它來檢視彈性集區資料庫的資源使用狀況。

您需要的 DMV

您需要有下列 DMV，才能解決某些 Azure SQL 的效能狀況：

  • sys.dm_io_virtual_file_stats 很重要，因為您無法直接存取作業系統計量來了解每個檔案的 I/O 效能。
  • sys.dm_os_performance_counters 可供 Azure SQL Database 和 SQL 受控執行個體用於查看 SQL Server 的常見效能計量。 使用此s DMV 來檢視效能監視器一般會提供的 SQL Server 效能計數器資訊。
  • sys.dm_instance_resource_governance 可供檢視受控執行個體的資源限制。 您可以檢視此資訊來了解您預期的資源限制有哪些，而不需使用 Azure 入口網站。
  • sys.dm_user_db_resource_governance 可供依部署選項、服務層和 Azure SQL Database 部署大小用於查看一般的資源限制。 您可以檢視此資訊來了解您預期的資源限制有哪些，而不需使用 Azure 入口網站。

用於取得深入見解的 DMV

這些 DMV 可讓您深入了解 Azure SQL 的資源限制和資源管理。 雖然不適合用於常見案例，但在深入探索複雜的效能問題時可能會有所幫助。 如需這些 DMV 的所有詳細資料，請參閱文件：

  • sys.dm_user_db_resource_governance_internal (僅限 SQL 受控執行個體)
  • sys.dm_resource_governor_resource_pools_history_ex
  • sys.dm_resource_governor_workload_groups_history_ex

擴充事件詳細資料

擴充事件功能是 SQL Server 的追蹤機制。 Azure SQL 的擴充事件是以 SQL Server 引擎為基礎，因此對 Azure SQL 而言是幾乎相同的功能，但仍有一些顯著的差異。 以下各節會討論這些差異。

Azure SQL Database 的擴充事件

如同 SQL Server，您可藉由建立工作階段和使用事件、動作及目標來對 Azure SQL Database 使用擴充事件。 建立擴充事件工作階段時，請記住下列重點：

  • 支援最常使用的事件和動作。
  • 支援檔案、ring_buffer 和計數器目標。
  • 因為無法存取基礎作業系統磁碟，所以支援以 Azure Blob 儲存體作為檔案目標。

您可以使用 SSMS 或 T-SQL 來建立和啟動工作階段。 您可以使用 SSMS 來檢視擴充事件工作階段目標資料或系統函式 sys.fn_xe_file_target_read_file。

注意

您無法使用 SSMS 來檢視 Azure SQL Database 的使用中資料。

請務必了解，在工作階段所引發的任何擴充事件都僅針對資料庫，而不適用於整個邏輯伺服器。

Azure SQL 受控執行個體的擴充事件

如同 SQL Server，您可藉由建立工作階段和使用事件、動作及目標來對 SQL 受控執行個體使用擴充事件，。 建立擴充事件工作階段時，請記住下列重點：

  • 支援所有事件、目標和動作。
  • 因為無法存取基礎作業系統磁碟，所以支援以 Azure Blob 儲存體作為檔案目標。
  • SQL 受控執行個體中會新增某些特定事件，以追蹤執行個體管理和執行的特定事件。

您可以使用 SSMS 或 T-SQL 來建立和啟動工作階段。 您可以使用 SSMS 來檢視擴充事件工作階段目標資料或系統函式 sys.fn_xe_file_target_read_file。 SQL Server 與 Azure SQL 受控執行個體會支援使用 SSMS 來檢視即時資料的功能。

Azure SQL 的效能案例

為決定效能監視和疑難排解工具及功能的套用方式，請務必透過案例查看 Azure SQL 的效能。

常見效能案例

SQL Server 效能疑難排解的常見技術是檢查效能問題是否為執行中 (高 CPU) 或等候中 (等候資源)。 下圖顯示的決策樹可判斷 SQL Server 效能問題是執行或等候問題，以及如何使用效能工具來判斷原因和解決方案。

執行中與等候中的圖表。

讓我們深入探討圖表各方面的詳細資料。

執行與等候

首先，請查看整體資源使用量。 針對標準 SQL Server 部署，您可以使用例如 Windows 中的效能監視器，或 Linux 中的 top。 針對 Azure SQL，您可以使用下列方法：

  • Azure 入口網站/Powershell/警示

    Azure 監視器具有整合的計量，可檢視 Azure SQL 的資源使用量。 您也可以設定警示來尋找資源使用量的條件。

  • sys.dm_db_resource_stats

    針對 Azure SQL Database，您可以查看此 DMV 來了解資料庫部署的 CPU、記憶體和 I/O 資源使用量。 此 DMV 會以每 15 秒的間隔來建立此資料的快照集。

  • sys.server_resource_stats

    此 DMV 的行為如同 sys.dm_db_resource_stats，但用途是查看 SQL 受控執行個體其 CPU、記憶體和 I/O 資源的使用狀況。 此 DMV 也會以每 15 秒的間隔建立快照集。

  • sys.dm_user_db_resource_governance

    針對 Azure SQL Database，此 DMV 會傳回目前的資料庫或彈性集區中的資源治理機制所使用的實際設定與容量設定。

  • sys.dm_instance_resource_governance

    針對 Azure SQL 受控執行個體，此 DMV 會傳回與 sys.dm_user_db_resource_governance 類似的資訊，但僅針對目前的 SQL 受控執行個體。

執行中

如果您判定問題是高 CPU 使用率，這就稱為「執行中」案例。 執行中案例可能牽涉到透過編譯或執行來取用資源的查詢。 請使用下列工具進一步分析：

  • 查詢存放區

    使用 SSMS 中的「最高資源耗用」報告、查詢存放區目錄檢視或 Azure 入口網站中的查詢效能深入解析 (僅適用於 Azure SQL Database)，找出哪些查詢耗用最多 CPU 資源。

  • sys.dm_exec_requests

    在 Azure SQL 中使用此 DMV 來取得作用中查詢的狀態快照集。 尋找狀態為 RUNNABLE 且等候類型為 SOS_SCHEDULER_YIELD 的查詢，以查看 CPU 容量是否足夠。

  • sys.dm_exec_query_stats

    這個 DMV 的使用方式非常類似查詢存放區，其目的在於尋找最熱門的取用查詢資源。 請注意，其只適用於快取的查詢計劃，因為查詢存放區提供效能的持續歷程記錄。 此 DMV 也可以讓您尋找快取查詢的查詢計劃。

  • sys.dm_exec_procedure_stats

    此 DMV 所提供的資訊與 sys.dm_exec_query_stats 很類似，但可在預存程序層級上檢視效能資訊。

    在確定哪個或哪些查詢正在取用最多資源之後，您可能必須檢查工作負載的 CPU 資源是否足夠。 您可使用一些工具來偵錯查詢計劃，例如輕量查詢分析、SET 陳述式、查詢存放區或擴充事件追蹤等。

等待

如果問題似乎與高 CPU 資源使用量無關，則可能是牽涉到等候資源的效能問題。 牽涉到等候資源的案例包括：

  • I/O 等候
  • Lock waits
  • 閂鎖等候
  • 緩衝集區限制
  • 記憶體授與
  • 計畫快取收回

若要在等候案例上執行分析，一般會查看下列工具：

  • sys.dm_os_wait_stats

    使用此 DMV 來查看資料庫或執行個體的前幾個等候類型。 這會引導您根據前幾個等候類型來採取後續動作。

  • sys.dm_exec_requests

    使用此 DMV 來尋找使用中查詢的特定等候類型，以查看其正在等候的資源。 這可能是等候其他使用者提供鎖定的標準封鎖案例。

  • sys.dm_os_waiting_tasks

    您可以使用此 DMV 來尋找目前正在執行之特定查詢的特定工作之等候類型，或許就可以了解為何該查詢執行時間比正常情況下長。 sys.dm_os_waiting_tasks 包含 sys.dm_os_wait_stats 隨著時間彙總的即時等候統計資料。

  • 查詢存放區

    查詢存放區會提供報表和目錄檢視，以顯示查詢計劃執行的前幾個等候彙總。 請務必了解，等候 CPU 相當於「正在執行」問題。

提示

擴充事件可用於任何正在執行或等待的案例。 若要這樣做，您必須設定擴充事件工作階段來追蹤查詢。 這種偵錯效能問題的方法更為進階，並且相較於 DMV，它可能會傳回大量的資訊，但同時會帶來較高的效能額外負荷。。

Azure SQL 特有的案例

有一些執行和等待效能案例為 Azure SQL 所特定。 包括記錄管理、背景工作角色限制、業務關鍵服務層所遇到的等候，以及超大規模資料庫部署特定的等候。

記錄控管

Azure SQL 可使用記錄速率控管來交易記錄使用量實施資源限制。 您可能需要此強制作業以確保資源限制，以及符合承諾的 SLA。 記錄控管可能會出現在下列等候類型中：

  • LOG_RATE_GOVERNOR：等候 Azure SQL Database
  • POOL_LOG_RATE_GOVERNOR：等候彈性集區
  • INSTANCE_LOG_GOVERNOR：等候 Azure SQL 受控執行個體
  • HADR_THROTTLE_LOG_RATE*：等候業務關鍵和異地複寫延遲

背景工作角色限制

SQL Server 會使用執行緒的背景工作角色集區，但對背景工作角色的數目上限有限制。 具有大量同時進行之使用者的應用程式可能會接近 Azure SQL Database 和 SQL 受控執行個體強制的背景工作角色限制：

  • Azure SQL Database 會有服務層級和大小方面的限制。 如果超過此限制，新的查詢就會收到錯誤。
  • 目前，SQL 受控執行個體使用max worker threads，因此超過此限制的背景工作角色可能會看到 THREADPOOL 等候。

業務關鍵性 HADR 等候

如果使用業務關鍵服務層，則可能會在非預期的情況下看到下列等候類型：

  • HADR_SYNC_COMMIT
  • HADR_DATABASE_FLOW_CONTROL
  • HADR_THROTTLE_LOG_RATE_SEND_RECV

雖然這些等待可能不一定會讓應用程式變慢，但您可能不想看到這些等候。 其通常是使用 Always On 可用性群組時所特定的。 業務關鍵層使用可用性群組技術來實作業務關鍵服務層的 SLA 和可用性功能，所以會有這些等候類型。 請注意，冗長的等候時間可能表示 I/O 延遲或複本落後等瓶頸。

超大規模資料庫

超大規模資料庫架構可能會產生一些唯一的等候類型，這些類型前面會加上 RBIO (表示可能有記錄控管)。 此外，已增強 DMV、目錄檢視和擴充事件，以顯示頁面伺服器讀數的計量。

在接下來的練習中，您會了解如何使用在本單元中學到的工具和知識來監視及解決 Azure SQL 的效能問題。