Устранение неполадок с запросами в SQL Server, выполнение которых не завершается

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

Что такое никогда некончающийся запрос?

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

Внимание

Если запрос остается, чтобы завершить его выполнение, он в конечном итоге завершится. Это может занять всего несколько секунд, или может занять несколько дней.

Термин никогда не заканчивается, чтобы описать восприятие запроса, не завершающегося в действительности, запрос в конечном итоге завершится.

Определение никогда некончаемого запроса

Чтобы определить, выполняется ли запрос или зависает на узких местах, выполните следующие действия.

1. Выполните приведенный ниже запрос:

   DECLARE @cntr int = 0
   
   WHILE (@cntr < 3)
   BEGIN
       SELECT TOP 10 s.session_id,
                       r.status,
                       r.wait_time,
                       r.wait_type,
                       r.wait_resource,
                       r.cpu_time,
                       r.logical_reads,
                       r.reads,
                       r.writes,
                       r.total_elapsed_time / (1000 * 60) 'Elaps M',
                       SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
                       ((CASE r.statement_end_offset
                           WHEN -1 THEN DATALENGTH(st.TEXT)
                           ELSE r.statement_end_offset
                       END - r.statement_start_offset) / 2) + 1) AS statement_text,
                       COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
                       + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
                       r.command,
                       s.login_name,
                       s.host_name,
                       s.program_name,
                       s.last_request_end_time,
                       s.login_time,
                       r.open_transaction_count,
                       atrn.name as transaction_name,
                       atrn.transaction_id,
                       atrn.transaction_state
           FROM sys.dm_exec_sessions AS s
           JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id 
                   CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
           LEFT JOIN (sys.dm_tran_session_transactions AS stran 
                JOIN sys.dm_tran_active_transactions AS atrn
                   ON stran.transaction_id = atrn.transaction_id)
           ON stran.session_id =s.session_id
           WHERE r.session_id != @@SPID
           ORDER BY r.cpu_time DESC
   
       SET @cntr = @cntr + 1
   WAITFOR DELAY '00:00:05'
   END
   

2. Проверьте пример выходных данных.

   • Действия по устранению неполадок, описанные в этой статье, особенно применимы, если вы заметите выходные данные, аналогичные следующему, где ЦП увеличивается пропорционально с истекающим временем, без значительного времени ожидания. Важно отметить, что изменения не logical_reads имеют значения в этом случае, так как некоторые запросы T-SQL, привязанные к ЦП, могут не выполнять логические операции чтения вообще (например, выполнять вычисления или WHILE цикл).

     session_id	статус	cpu_time	logical_reads	wait_time	wait_type
     56	выполняется	70 ГБ	101000	0	NULL
     56	готово к запуску	12040	301000	0	NULL
     56	выполняется	17020	523000	0	NULL

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

     session_id	статус	cpu_time	logical_reads	wait_time	wait_type
     56	suspended (приостановлено)	0	3	8312	LCK_M_U
     56	suspended (приостановлено)	0	3	13318	LCK_M_U
     56	suspended (приостановлено)	0	5	18331	LCK_M_U

   Дополнительные сведения см. в разделе "Диагностика ожиданий" или узких мест.

Длительное время компиляции

В редких случаях вы можете наблюдать, что ЦП постоянно увеличивается с течением времени, но это не зависит от выполнения запроса. Вместо этого она может быть вызвана чрезмерно длинной компиляцией (анализ и компиляция запроса). В этих случаях проверьте выходной столбец transaction_name и найдите значение sqlsource_transform. Это имя транзакции указывает на компиляцию.

Сбор диагностических данных

SQL Server 2008 — SQL Server 2014 (до sp2)

Чтобы собрать диагностические данные с помощью SQL Server Management Studio (SSMS), выполните следующие действия:

1. Захватить XML-код предполагаемого плана выполнения запроса.

2. Просмотрите план запроса, чтобы узнать, есть ли какие-либо очевидные признаки того, откуда может прийти замедление. Типичные примеры использования этой функции:

   • Просмотр таблиц или индексов (просмотр предполагаемых строк).
   • Вложенные циклы, управляемые огромным набором данных внешней таблицы.
   • Вложенные циклы с большой ветвью во внутренней части цикла.
   • Табличные спули.
   • Функции в списке SELECT , которые занимают много времени для обработки каждой строки.

3. Если запрос выполняется быстро в любое время, можно записать фактический план выполнения XML для сравнения.

SQL Server 2014 (после пакета обновления 2 (SP2) и SQL Server 2016 (до sp1)

Упрощенная инфраструктура профилирования запросов была представлена в этих версиях SQL Server. Он позволяет записывать фактическую статистику во время выполнения медленного запроса. Эта функция устранения неполадок позволяет проверять операторы запросов в плане запросов во время выполнения и понимать, где большую часть времени тратится в запросе.

Чтобы определить медленные шаги в запросе с помощью инфраструктуры профилирования статистики выполнения упрощенных запросов версии 1, выполните следующие действия.

1. Выполните следующие команды, чтобы включить query_thread_profile XEvent:

   CREATE EVENT SESSION [NodePerfStats] ON SERVER
   ADD EVENT sqlserver.query_thread_profile(
     ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
       sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
       sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
       sqlserver.sql_text))
   ADD TARGET package0.ring_buffer(SET max_memory=(25600))
   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);
   
   ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = START
   

2. Запустите затронутый никогда некончающий запрос из приложения.

3. Выполните следующую команду несколько раз в минуту или около того, чтобы проверить статистику выполнения во время выполнения для операторов плана запроса:

   SELECT CONVERT (varchar(30), getdate(), 126) as runtime,
               qp.session_id,
               convert(nvarchar(48), qp.physical_operator_name) as physical_operator_name,
               qp.row_count,
               qp.estimate_row_count,
               qp.node_id,
               req.cpu_time,
               req.total_elapsed_time,
               substring
               (REPLACE
               (REPLACE
                   (SUBSTRING
                   (SQLText.text
                   , (req.statement_start_offset/2) + 1
                   , (
                       (CASE statement_END_offset
                           WHEN -1
                           THEN DATALENGTH(SQLText.text)  
                           ELSE req.statement_END_offset
                           END
                           - req.statement_start_offset)/2) + 1)
               , CHAR(10), ' '), CHAR(13), ' '), 1, 512)  AS active_statement_text
   FROM sys.dm_exec_query_profiles qp 
   RIGHT OUTER JOIN sys.dm_exec_requests req
       ON qp.session_id = req.session_id
   LEFT OUTER JOIN sys.dm_exec_sessions sess
       on req.session_id = sess.session_id
   LEFT OUTER JOIN sys.dm_exec_connections conn on conn.session_id = req.session_id
   OUTER APPLY sys.dm_exec_sql_text (ISNULL (req.sql_handle, conn.most_recent_sql_handle)) as SQLText
   WHERE req.session_id <> @@SPID 
       AND sess.is_user_process = 1 
   ORDER BY qp.session_id asc, row_count desc 
   --this is to prevent massive grants
   OPTION (max_grant_percent = 3, MAXDOP 1)
   

4. Захватить три или четыре моментальных снимка, разделенных на одну минуту, чтобы предоставить достаточно данных для анализа. В частности, можно сравнить row_count числа для каждого оператора с течением времени и увидеть, что показывает значительное увеличение количества строк (миллион или более).

5. В новом окне запроса в SSMS зафиксируйте предполагаемый план запроса для запроса проблемы, выполнив следующие команды:

   SET SHOWPLAN_XML ON
   GO
   <problem query here>
   GO
   SET SHOWPLAN_XML OFF
   

6. Используя идентификатор узла с наибольшим числом строк, определяемым запросом на шаге 3, найдите тот же узел в предполагаемом плане запроса. Этот шаг поможет понять, какой оператор в плане является основной причиной длительного времени выполнения.

7. Остановите XEvent, выполнив следующую команду:

   ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP
   

SQL Server 2016 (после пакета обновления 1 (SP1) и SQL Server 2017

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

Чтобы определить медленные шаги в запросе, выполните следующие действия.

1. Чтобы включить упрощенную инфраструктуру в этих версиях SQL Server, используйте один из следующих методов:

   • Включите флаг трассировки 7412, выполнив следующую команду:

     DBCC TRACEON (7412, -1)
     

   • Кроме того, включите query_thread_profile XEvent, выполнив следующие команды:

     CREATE EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER
     ADD EVENT sqlserver.query_plan_profile(
      ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
        sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
        sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
        sqlserver.sql_text))
     ADD TARGET package0.ring_buffer(SET max_memory=(25600))
     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);
     
     ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = START
     

2. Запустите затронутый никогда некончающий запрос из приложения.

3. Используйте команду, аналогичную следующей, чтобы определить Session_id конечный запрос, выполняющийся:

   SELECT t.text, session_id 
   FROM sys.dm_exec_requests req
   CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
   

4. Выполните следующую команду три или четыре раза в течение одной минуты, чтобы проверить план запроса и фактическую статистику в плане. Обязательно сохраните план запроса каждый раз, чтобы сравнить их и установить, какой оператор запроса потребляет большую часть времени ЦП. В частности, можно сравнить число строк (фактическое число строк) для каждого оператора с течением времени и увидеть, какой из операторов показывает значительное увеличение количества строк (миллион или более). Замените <session_id> целочисленным значением, найденным на предыдущем шаге 3.

   SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
   

5. Остановите XEvent при запуске одного или отключите флаг трассировки:

   ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = STOP
   -- or
   DBCC TRACEOFF (7412, -1)
   

SQL Server 2019 и более поздних версий

Вы можете использовать инфраструктуру профилирования статистики выполнения упрощенных запросов версии 3 для записи планов динамических запросов с фактическими значениями для подсчета строк. Эта инфраструктура профилирования позволяет проверять операторы запросов в плане запроса во время выполнения и понимать, где большую часть времени тратится в запросе. Упрощенная профилирование включена по умолчанию в SQL Server 2019.

Чтобы определить медленные шаги в запросе, выполните следующие действия.

1. Запустите затронутый никогда некончающий запрос из приложения.

2. Используйте команду, аналогичную следующей, чтобы определить Session_id конечный запрос, выполняющийся:

   SELECT t.text, session_id 
   FROM sys.dm_exec_requests req
   CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
   

3. Выполните следующую команду три или четыре раза, чтобы проверить план запроса и фактическую статистику в плане. Обязательно сохраните план запроса каждый раз, чтобы сравнить их и установить, какой оператор запроса потребляет большую часть времени ЦП. Замените <session_id> целочисленным значением, найденным на предыдущем шаге 3.

   SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
   

4. В частности, выберите XML-ссылку в столбце query_plan . Когда графический план запроса откроется в новом окне, щелкните его правой кнопкой мыши и выберите "Сохранить план выполнения как...". Повторите действия, чтобы записать три или четыре моментальных снимка в минуту друг от друга, чтобы дать достаточно данных для анализа. В частности, можно сравнить число строк (фактическое количество строк) для каждого оператора с течением времени и увидеть, какой из операторов показывает значительное увеличение количества строк (миллион или более).

   Примечание.

   Если выходные данные sys.dm_exec_query_statistics_xmlотсутствуют, можно проверить, отключен ли параметр LAST_QUERY_PLAN_STATS базы данных, выполнив следующую команду:

   SELECT name, value, value_for_secondary, is_value_default 
   FROM sys.database_scoped_configurations
   WHERE name = 'LAST_QUERY_PLAN_STATS'
   

   Вы можете включить последнюю статистику плана запросов на уровне базы данных, выполнив команду ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON.

Метод проверки собранных планов

В этом разделе показано, как просмотреть собранные данные. Он будет использовать несколько планов ЗАПРОСОВ XML (с помощью расширения *.sqlplan), собранных в SQL Server 2016 с пакетом обновления 1 (SP1) и более поздних сборок и версий.

Выполните следующие действия, чтобы сравнить планы выполнения:

1. Откройте ранее сохраненный файл плана выполнения запроса (.sqlplan).

2. Щелкните правой кнопкой мыши пустую область плана выполнения и выберите "Сравнить showplan".

3. Выберите второй файл плана запроса, который вы хотите сравнить.

4. Найдите толстые стрелки, указывающие большое количество строк, поступающих между операторами. Затем выберите оператор до или после стрелки и сравните количество фактических строк между двумя планами.

5. Сравните второй и третий планы, чтобы узнать, происходит ли самый большой поток строк в одних и том же операторах.

   Приведем пример:

   

Решение

1. Убедитесь, что статистика обновляется для таблиц, используемых в запросе.

2. Найдите отсутствующие рекомендации по индексу в плане запроса и примените все.

3. Переопределите запрос с целью упростить его:

   • Используйте более выборочные WHERE предикаты, чтобы уменьшить объем обработанных данных заранее.
   • Разорвать его друг от друга.
   • Выберите некоторые части в временные таблицы и присоедините их позже.
   • Удалите TOP, EXISTSи FAST (T-SQL) в запросах, которые выполняются в течение очень длительного времени из-за цели строки оптимизатора. Кроме того, можно использовать подсказку DISABLE_OPTIMIZER_ROWGOAL . Дополнительные сведения см. в разделе "Голы строк" "Ушел изгой".
   • Избегайте использования общих выражений таблиц (CTEs) в таких случаях, как они объединяют операторы в один большой запрос.

4. Попробуйте использовать подсказки запросов для создания лучшего плана:

   • HASH JOIN или MERGE JOIN подсказка
   • Подсказка FORCE ORDER
   • Подсказка FORCESEEK
   • RECOMPILE
   • ИСПОЛЬЗОВАТЬ PLAN N'<xml_plan>' , если у вас есть быстрый план запроса, который можно принудительно применить

5. Используйте хранилище запросов (QDS), чтобы принудительно применить известный план, если такой план существует, и если версия SQL Server поддерживает хранилище запросов.

Диагностика ожиданий или узких мест

Этот раздел включен здесь в качестве ссылки, если проблема не является длительным запросом на управление ЦП. Его можно использовать для устранения неполадок запросов, которые являются длительными из-за ожиданий.

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

Чтобы вычислить приблизительное время ожидания, вычитайте время ЦП (рабочий период) из истекшего времени запроса. Как правило, время ЦП — это фактическое время выполнения, а оставшаяся часть времени существования запроса ожидается.

Примеры вычисления приблизительной длительности ожидания:

Истекшее время (мс)	Время ЦП (мс)	Время ожидания (мс)
3200	3000	200
7080	1000	6080

Определение узких мест или ожиданий

• Чтобы определить исторические длительные запросы (например, >20% от общего времени ожидания), выполните следующий запрос. Этот запрос использует статистику производительности для кэшированных планов запросов с момента начала SQL Server.

  SELECT t.text,
           qs.total_elapsed_time / qs.execution_count
           AS avg_elapsed_time,
           qs.total_worker_time / qs.execution_count
           AS avg_cpu_time,
           (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count
           AS avg_wait_time,
           qs.total_logical_reads / qs.execution_count
           AS avg_logical_reads,
           qs.total_logical_writes / qs.execution_count
           AS avg_writes,
           qs.total_elapsed_time
           AS cumulative_elapsed_time
  FROM sys.dm_exec_query_stats qs
           CROSS apply sys.Dm_exec_sql_text (sql_handle) t
  WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time
           > 0.2
  ORDER BY qs.total_elapsed_time / qs.execution_count DESC
  

• Чтобы определить выполнение запросов с ожиданием дольше 500 мс, выполните следующий запрос:

  SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms
  FROM sys.dm_exec_requests r 
     JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id 
  WHERE wait_time > 500
  AND is_user_process = 1
  

• Если вы можете собрать план запроса, проверьте значение WaitStats из свойств плана выполнения в SSMS:

  1. Запустите запрос с включением фактического плана выполнения .
  2. Щелкните правой кнопкой мыши левый оператор на вкладке "План выполнения"
  3. Выберите "Свойства ", а затем свойство WaitStats .
  4. Проверьте waitTimeMs и WaitType.

• Если вы знакомы с сценариями PSSDiag/SQLdiag или SQL LogScout LightPerf/GeneralPerf, рассмотрите возможность использования любого из них для сбора статистики производительности и определения ожиданий запросов на экземпляре SQL Server. Вы можете импортировать собранные файлы данных и проанализировать данные производительности с помощью SQL Nexus.

Ссылки на устранение или сокращение ожиданий

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

• Общие сведения и устранение проблем с блокировкой (LCK_M_*)
• Изучение и устранение проблем с блокировкой Базы данных SQL Azure
• Устранение неполадок с медленной производительностью SQL Server, вызванных проблемами ввода-вывода (PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
• Разрешение конфликтов PAGELATCH_EX вставки последней страницы в SQL Server
• Память предоставляет объяснения и решения (RESOURCE_SEMAPHORE)
• Устранение неполадок с медленными запросами, которые приводят к типу ожидания ASYNC_NETWORK_IO
• Устранение неполадок с типом ожидания высокой HADR_SYNC_COMMIT с помощью групп доступности AlwaysOn
• Как это работает: CMEMTHREAD и отладка их
• Создание параллелизма ожидает действия (CXPACKET и CXCONSUMER)
• Ожидание THREADPOOL

Описание многих типов ожиданий и то, что они указывают, см. в таблице в разделе "Типы ожиданий".