排查数据库应用程序和 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 窗口中打开。
- 在对象资源管理器中，展开管理>扩展事件会话>EventSessionName>，右键单击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:BatchCompletedRPC: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 选项

反馈

其他资源