Устранение различий в производительности запросов между приложением базы данных и 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 . По умолчанию параметр в SSMS имеет значение ON, но в большинстве приложений базы данных — значение OFF. В зависимости от потребностей приложения задайте для ARITHABORT один и тот же параметр в SSMS и приложении для правильного сравнения между ними.

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

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

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

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