疑難排解資源管理員

如果沒有任何分類 UDF 可用於分類，則所有工作階段將會自動移至預設的工作負載群組。建立分類 UDF 之後，您必須確認它已向資源管理員註冊，而且記憶體中組態已更新。

建立、註冊和啟用分類 UDF 是三個步驟的程序：

• 第一步，您必須建立函數。

  CREATE FUNCTION function_name() RETURNS <something> 
  WITH SCHEMABINDING
  

• 第二步，您必須向資源管理員註冊此函數。

  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=schema_name.function_name)
  

• 第三步，您必須更新資源管理員的記憶體中組態。

  ALTER RESOURCE GOVERNOR RECONFIGURE
  

疑難排解分類時，您應該進行的第一件事就是確認您所建立的函數已向資源管理員註冊，而且組態已更新。您可以針對資源管理員目前使用的分類 UDF，使用下列查詢來取得結構描述名稱 (schema_name) 和分類函數名稱 (function_name)。

USE master
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

當您變更了分類 UDF，但是資源管理員仍然使用先前的函數邏輯來分類工作階段時，您就可以使用上述方法來進行疑難排解。這種行為表示您所做的變更沒有套用至記憶體中組態。

您可能會建立不產生預期結果或需要大量資源而且非常複雜的分類函數。如果您已完成基本的疑難排解，就必須確認函數邏輯正確無誤。編碼錯誤產生無限迴圈或失控查詢是最嚴重的案例狀況。

此時，您可以使用專用管理員連接 (DAC) 來疑難排解撰寫不夠周全的分類函數，因為 DAC 不受分類限制而且可以在資源管理員執行和分類內送工作階段時使用。如需詳細資訊，請參閱＜使用專用管理員連接＞。

您可以透過查詢下列項目，取得分類函數的相關資訊：

• sys.dm_exec_query_stats (包含陳述式資訊，但不包含實際函數)

• sys.dm_exec_sql_text (與從 sys.dm_exec_query_stats 中取得的 sql_handle 搭配使用)

• PreConnect:Starting 事件類別 (提供分類函數的識別碼和名稱)

在此狀況中，使用者報告效能降低，而且您懷疑要求計數已節流。

您必須進行的第一件事就是確認使用者目前所在的群組是否明確設定了要求計數節流。您可以檢查使用者的群組成員資格來查看 GROUP_MAX_REQUESTS 設定是否已啟用，藉以達成此目的。如果 GROUP_MAX_REQUESTS 未啟用，就表示沒有明確的要求計數節流，而且您應該採取下列步驟來進一步調查。

• 查詢 sys.dm_os_waiting_tasks，以便了解是否有任何要求正在等候 RESMGR_THROTTLED 等候類型。這種等候類型的存在就表示要求計數節流。

• 啟動效能監視器，然後使用 Queued requests 和 Active requests 計數器來收集資料。非零的 Queued request 計數表示要求節流。

• 查看 Active requests 值是否符合 GROUP_MAX_REQUESTS 設定。如果 Active requests 值高於 GROUP_MAX_REQUESTS 設定，表示此群組可能具有無法節流的要求 (例如，開啟的交易)。

• 如果 Queued requests 為零，請檢查共用相同資源集區之所有工作負載群組的 Active requests，因為此集區可能已經因要求過多而超過負載。

如果您設有由資源管理員事件產生所驅動的原則，就可以使用達到最大 CPU 限制時所產生的事件。

在此狀況中，您想要判斷針對偵測使用過多 CPU 之查詢所設定的最大 CPU 限制 (REQUEST_MAX_CPU_TIME_SEC) 是否過低。

下列動作將協助您驗證 CPU 限制設定。

• 啟動 SQL 追蹤工作階段，然後收集 CPU Threshold Exceeded 事件。當使用者要求達到最大 CPU 使用量限制時，伺服器就會自動產生 SQL 追蹤事件。如果您的設定過低，就會產生大量事件。

• 啟動效能監視器，然後使用 Max request cpu time (ms) 計數器來收集資料。您可以使用這個計數器的值當做指南，以便針對工作負載群組設定適當的限制。

在此狀況中，您懷疑 CPU 頻寬已節流，因為 CPU usage % 效能計數器等於或接近資源管理員的 MAX_CPU_PERCENT 設定。下列查詢會針對 SQL Server 執行個體的所有工作負載群組和資源集區傳回 CPU usage % 值。

select * from sys.dm_os_performance_counters where counter_name = 'cpu usage %'

如需詳細資訊，請參閱＜sys.dm_os_performance_counters (Transact-SQL)＞。

您可以透過在系統上執行下列檢查，判斷 CPU 頻寬是否已節流。

• 檢查整體伺服器 CPU 使用情形。如果 SQL Server 以外的負載目前正在使用中，它可能會影響您正在疑難排解的查詢。

• 檢查資源集區之間 CPU 使用量的分配。某個資源集區可能已節流，因為另一個集區針對 CPU 使用量所設定的最小值比較高。請比較預期 (已計算的 CPU 使用情形) 與實際 CPU 使用情形的計數器。

• 檢查指派給有問題之資源集區的工作負載群組。其他工作負載群組的負載可能會影響共用相同集區的使用者。

• 檢查排程器之間 CPU 使用量的分配。您正在調查的查詢可能會放置在包含長時間執行之查詢的排程器上。在此情況下，查詢似乎已節流，但是實際的問題是排程器之間的負載分配不平均。

• 檢查是否有工作負載由其他工作階段封鎖而非由資源管理員設定所節流的可能情況。

• 檢查目前正在系統上執行查詢的工作階段數目。當並行執行要求的數目增加時，SQL Server 將嘗試確定所有要求至少都會收到一些 CPU 時間來防止 CPU 資源用盡。

在此狀況中，您懷疑授權記憶體的大小導致查詢執行速度很慢。

由於資源管理員會減少授權記憶體來強制執行最大查詢記憶體限制，所以大型查詢可以納入限制中。如果某個查詢取得小於 100% 的記憶體授權，您可能必須轉散暫存資料並將資料寫入磁碟，而這樣做可能會對效能造成明顯的影響。

您必須判斷大型查詢的百分比，以便設定適當的最大查詢大小限制。下列動作將協助您判斷最佳的設定：

• 查詢 sys.dm_exec_query_memory_grants，以便查看記憶體授權的目前狀態。ideal_memory_kb 資料行會根據基數估計值顯示理想的數量。requested_memory_kb 資料行會顯示要求的數量，而這個數量在達到最大查詢限制之後可能已經減少。如果 requested_memory_kb 遠低於 ideal_memory_kb，則查詢最後可能會經常轉散 (假設基數估計值是正確的)。

• 啟動效能監視器，然後使用 Reduced memory grants/sec 計數器來收集資料。這個計數器的值代表在達到最大要求大小限制之後，收到的記憶體授權計數小於理想數量的比率。大型查詢的執行速度可能會比理想數量的執行速度更慢，因為它們必須轉散至磁碟，以便維持在記憶體限制內。

若要減少記憶體授權問題，您可能必須增加集區大小限制及/或最大記憶體大小限制。

在此狀況中，查詢會因記憶體授權逾時錯誤而失敗。

針對工作負載群組和資源集區定義所指定的使用中記憶體授權要求和記憶體限制總數可能會在記憶體授權逾時中扮演某個角色。如果有多個資源群組共用單一資源集區，其他群組中的並行查詢數目可能也會影響記憶體授權逾時。

下列動作將協助您判斷最佳的資源集區設定：

• 查詢 sys.dm_exec_query_memory_grants，以便查看這個群組與集區的記憶體授權和等候中查詢的數目。

• 查詢 sys.dm_exec_query_resource_semaphores，以便查看授與的總記憶體和目標。

如果授權記憶體使用量大於可用的記憶體空間，您就可以考慮增加資源集區大小限制。

查詢因記憶體不足錯誤而失敗。

基本的疑難排解

進階的疑難排解

在此狀況中，您懷疑查詢執行速度很慢的原因是次佳查詢計畫。如果查詢最佳化工具由於資源集區的記憶體限制設定很低而無法取得足夠的記憶體，它可能就會產生次佳查詢計畫。

下列動作將協助您判斷最佳的資源集區設定：

• 取得 Query optimizations/sec 計數器的資料，以便查看工作負載群組是否具有大量查詢編譯。

• 取得 Suboptimal plans/sec 計數器的資料，以便查看查詢最佳化工具是否經常產生次佳計畫。

如果上述其中一個狀況存在，請考慮增加資源集區的記憶體限制。