Поделиться через

Устранение различий в производительности запросов между приложением базы данных и SSMS

  • Статья

При выполнении запроса в приложении базы данных он выполняется медленнее, чем тот же запрос в приложении, например SQL Server Management Studio (SSMS), Azure Data Studio или SQLCMD.

Эта проблема может возникать по следующим причинам:

  • Запросы используют разные параметры или переменные.

  • Запросы отправляются на сервер по разным сетям или есть разница в том, как приложения обрабатывают данные.

  • Параметры SET в приложении базы данных и SSMS отличаются.

Чтобы устранить эту проблему, сделайте следующее:

Шаг 1. Проверка отправки запросов с теми же параметрами или переменными

Чтобы сравнить эти запросы и убедиться, что они идентичны во всех отношениях, выполните следующие действия:

  1. Откройте SSMS и подключите его к ядро СУБД, которую вы используете.

  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, которое можно использовать для просмотра и анализа.

    • В обозреватель объектов разверните узел "Сеансы расширенных событий>управления>">EventSessionName, щелкните правой кнопкой мыши 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

  • Используйте XEvent из шага 1 , чтобы проверить длительность или истекшее время запроса (класс SQL:StmtCompletedсобытий, SQL:BatchCompletedили RPC:Completed).

В некоторых случаях разница между запросами может быть вызвана одним приложением, работающим в другой сети или самом приложении. При сравнении выполнения на сервере вы сравниваете время выполнения запросов на сервере.

Шаг 3. Проверка параметров SET для каждого подключения

Существуют параметры SET, влияющие на план запросов, что означает, что они могут изменить выбор плана запроса. Таким образом, если приложение базы данных использует различные параметры набора из SSMS, каждый параметр набора может получить другой план запроса. Например, ARITHABORT, NUMERIC_ROUNDABORT, ROWCOUNT, FORCEPLAN и ANSI_NULLS. Наиболее распространенная разница между приложениями SSMS и .NET — параметр SET ARITHABORT . По умолчанию параметр имеет значение ON в SSMS, но для большинства приложений базы данных задано значение OFF. В зависимости от потребностей приложения задайте ARITHABORT одинаковому параметру в SSMS и приложении для допустимого сравнения между двумя.

Предупреждение

Параметр ARITHABORT для SQL Server Management Studio по умолчанию имеет значение ON. Клиентские приложения, в которых для параметра ARITHABORT установлено значение OFF, могут получать разные планы запроса, что осложняет диагностику плохо выполняемых запросов. То есть тот же запрос может выполняться быстро в Management Studio, но медленно в приложении. При диагностике запросов с помощью Management Studio всегда сопоставляйте параметр ARITHABORT клиента.

Список всех параметров, влияющих на план, см. в разделе "Настройка параметров".

Чтобы убедиться, что параметры SET в SSMS и приложении одинаковы для выполнения допустимого сравнения, выполните следующие действия.

  1. Используйте собранные данные на шаге 1.

  2. Сравните параметры набора, проверив события login и existing_connection, в частности options_text , столбцы параметров.