針對資料庫應用程式與 SSMS 之間的查詢效能差異進行疑難排解

發行項
12/14/2024

當您在資料庫應用程式中執行查詢時，其執行速度會比 SQL Server Management Studio （SSMS）、Azure Data Studio 或 SQLCMD 等應用程式中的相同查詢慢。

下列原因都有可能導致發生此問題：

查詢會使用不同的參數或變數。
查詢會透過不同的網路提交至伺服器，或應用程式處理數據的方式有所差異。
資料庫應用程式和 SSMS 中的 SET 選項不同。

若要對此問題進行疑難排解，請執行下列步驟：

步驟 1：使用相同的參數或變數確認查詢已提交

若要比較這些查詢，並確定它們以各種方式相同，請遵循下列步驟：

開啟您的 SSMS，並將它連線到您使用的資料庫引擎。

執行下列命令來建立擴充事件會話：

CREATE EVENT SESSION <EventSessionName> ON SERVER
ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)),
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(sqlserver.client_app_name)),
ADD EVENT sqlserver.rpc_completed,
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name)),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name)),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name))
ADD TARGET package0.event_file(SET filename=N'<FilePath>')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 
SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

注意

將預留位置 <EventSessionName> 和 <FilePath> 取代為您想要建立的預留位置。

執行下列命令以啟動工作階段 EventSessionName：

ALTER EVENT SESSION <EventSessionName> ON SERVER
STATE = START

執行您的查詢以重現問題。
使用下列其中一種方法來分析收集的資料：
- 開啟 Windows 檔案總管，尋找目標 .xel 檔案，然後按兩下它。檔案將會在另一個 SSMS 視窗中開啟，您可以用來檢視和分析。
- 在 [物件總管] 中，展開 [管理>擴充事件會話>事件>][essionName]，以滑鼠右鍵按兩下 [package0.event_file]，然後選取 [檢視目標數據...]。
- 尋找 .xel 檔案的位置，並使用函式sys.fn_xe_file_target_read_file讀取此檔案。
藉由檢查下列事件來比較 Field 語句：
- sp_statement_completed
- sql_batch_completed
- sql_statement_completed
- rpc_completed

如需相同查詢的詳細資訊，請參閱下列範例：

如果預存程式或函式有不同的參數值，查詢時間可能會不同：
- SpUserProc @p1 = 100
- SpUserProc @p1 = 270
下列查詢不同。第一個查詢使用直方圖的平均密度進行基數估計，而第二個查詢則使用直方圖步驟進行基數估計：
- ```
declare @variable1 = 123
select * from table where c1 = @variable1
```
- ```
select * from table where c1 = 123
```

基於上述相同原因，比較預存程式的執行與對等特定查詢的執行（使用局部變數）可能不同。必須比較相同的語句。

步驟 2：測量伺服器上的運行時間

如需查詢持續時間的精確比較，您可以排除網路等待時間或應用程式特定的數據處理時間。使用下列其中一種方法，只測量 SQL Server 上的運行時間：

使用 SET STATISTICS TIME 執行查詢：

SET STATISTICS TIME ON
<YourQuery>
SET STATISTICS TIME OFF

使用步驟 1 中的 XEvent 來檢查查詢的持續時間或經過時間（事件類別SQL:StmtCompleted、 SQL:BatchCompleted或 RPC:Completed）。

在某些情況下，查詢之間的時間差異可能是由在不同網路或應用程式本身中執行的一個應用程式所造成。當您比較伺服器上的執行時，會比較查詢在伺服器上執行的時間長度。

步驟 3：檢查每個連線的 SET 選項

有一個 SET 選項會影響查詢計劃，這表示它們可以變更查詢計劃的選擇。因此，如果資料庫應用程式使用SSMS的不同集合選項，則每個集合選項都可以取得不同的查詢計畫。例如，ARITHABORT、NUMERIC_ROUNDABORT、ROWCOUNT、FORCEPLAN 和 ANSI_NULLS。 SSMS 和 .NET 應用程式之間最常見的差異是 SET ARITHABORT 選項。根據預設，此選項會在 SSMS 中設定為 ON，但在大部分的資料庫應用程式中設定為 OFF。根據您的應用程式需求，在 SSMS 和應用程式中將 ARITHABORT 設定為相同的設定，以取得兩者之間的有效比較。

警告

SQL Server Management Studio 的預設 ARITHABORT 設定為 ON。將 ARITHABORT 設為 OFF 的用戶端應用程式可能會收到不同的查詢計劃，導致難以針對效能不良的查詢進行疑難排解。也就是說，相同的查詢可能會在Management Studio中快速執行，但在應用程式中執行速度很慢。使用 Management Studio 進行查詢的疑難排解時，一律要符合用戶端 ARITHABORT 設定。

如需所有影響計劃的選項清單，請參閱設定選項。

若要確保 SSMS 和應用程式中的 SET 選項都相同，以便執行有效的比較，請遵循下列步驟：

使用步驟 1 中收集的數據。
藉由檢查事件 login 和 existing_connection來比較設定選項，特別是 options_text 和選項數據行。

共用方式為

針對資料庫應用程式與 SSMS 之間的查詢效能差異進行疑難排解

步驟 1：使用相同的參數或變數確認查詢已提交

步驟 2：測量伺服器上的運行時間

步驟 3：檢查每個連線的 SET 選項

意見反應

其他資源