排查数据库应用程序和 SSMS 之间的查询性能差异问题

项目
04/18/2023

在数据库应用程序中执行查询时，它的运行速度比应用程序中的相同查询慢，例如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 窗口中打开。
- 在“对象资源管理器”中，展开“管理>扩展事件>会话>”“事件”“事件”“名称”，右键单击“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 选项

反馈

其他资源