Share via

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

  • 發行項

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

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

  • 查詢會使用不同的參數或變數。

  • 查詢會透過不同的網路提交至伺服器，或應用程式處理數據的方式有所差異。

  • 資料庫應用程式和 SSMS 中的 SET 選項不同。

若要針對問題進行疑難解答，請遵循下列步驟：

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

若要比較這些查詢，並確定它們在每個方面都相同，請遵循下列步驟：

  1. 開啟您的 SSMS，並將其連線至您使用的 Database Engine。

  2. 執行下列命令以 建立擴充事件會話：

    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> 佔位符取代為您想要建立的佔位符<。<>

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

    ALTER EVENT SESSION <EventSessionName> ON SERVER
STATE = START

  4. 執行您的查詢以重現問題。

  5. 使用下列其中一種方法來分析收集的資料：

    • 開啟 Windows 檔案總管，尋找目標 .xel 檔案，然後按兩下該檔案。 檔案將會在另一個 SSMS 視窗中開啟，供您用來檢視和分析。

    • 在 [物件總管] 中，展開 [管理>擴充事件>會話>事件][事件名稱]，以滑鼠右鍵按兩下 [package0.event_file]，然後選取 [檢視目標數據...]。

    • 尋找 .xel 檔案的位置，並使用函 式sys.fn_xe_file_target_read_file讀取此檔案。

  6. 檢查下列事件來比較 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 的設定選項，則每個 set 選項都可以取得不同的查詢計劃。 例如，ARITHABORT、NUMERIC_ROUNDABORT、ROWCOUNT、FORCEPLAN 和 ANSI_NULLS。 SSMS 和 .NET 應用程式之間最常見的差異是 SET ARITHABORT 選項。 根據預設，此選項在 SSMS 中會設定為 ON，但在大部分的資料庫應用程式中則設定為 OFF。 根據您的應用程式需求，將ARITHABORT設定為SSMS和應用程式中的相同設定，以在兩者之間進行有效的比較。

警告

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

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

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

  1. 使用 步驟 1 中收集的數據。

  2. 藉由檢查 事件login和 ，特別是 options_text 和existing_connection選項數據行來比較設定選項。