Устранение неполадок запросов, которые, как представляется, выполняются бесконечно в SQL Server

В этой статье приводятся рекомендации по устранению неполадок, в которых запрос Microsoft SQL Server занимает чрезмерное время окончания (часы или дни).

Симптомы

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

Внимание

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

Причина

Распространенные причины длительных (никогда не заканчивающихся) запросов:

• Вложенный цикл (NL) присоединяется к очень большим таблицам: Из-за характера соединений NL запрос, который объединяет таблицы с большим количеством строк, может выполняться в течение длительного времени. Дополнительные сведения см. в разделе "Соединения".
  • Одним из примеров соединения NL является использование TOP, FASTили EXISTS. Даже если соединение хэша или слияния может быть быстрее, оптимизатор не может использовать любой оператор из-за цели строки.
  • Еще одним примером соединения NL является использование предиката соединения с неравенством в запросе. Например: SELECT .. FROM tab1 AS a JOIN tab 2 AS b ON a.id > b.id. Оптимизатор не может использовать слияние или хэш-соединения здесь.
• Устаревшие статистические данные: Запросы, которые выбирают план на основе устаревшей статистики, могут быть неоптимальными и занять много времени.
• Бесконечные циклы: Запросы T-SQL, использующие циклы WHILE, могут быть неправильно записаны. Полученный код никогда не покидает цикл и бесконечно выполняется. Эти запросы действительно никогда не заканчиваются. Они работают до тех пор, пока они не будут убиты вручную.
• Сложные запросы с большим количеством соединений и больших таблиц: Запросы, включающие множество присоединенных таблиц, обычно имеют сложные планы запросов, которые могут занять много времени. Этот сценарий распространен в аналитических запросах, которые не фильтруют строки и включают большое количество таблиц.
• Отсутствующие индексы: Запросы могут выполняться значительно быстрее, если соответствующие индексы используются в таблицах. Индексы позволяют выбрать подмножество данных, чтобы обеспечить быстрый доступ.

Solution

Шаг 1. Обнаружение некончаемых запросов

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

1.1 Запуск диагностики

Выполните следующий диагностический запрос в экземпляре SQL Server, где некончающийся запрос активен:

DECLARE @cntr INT = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    CAST(r.cpu_time / (1000 * 60.0) AS DECIMAL(10,2)) AS cpu_time_minutes,
                    CAST(r.total_elapsed_time / (1000 * 60.0) AS DECIMAL(10,2)) AS elapsed_minutes,
                    r.logical_reads,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    r.reads,
                    r.writes,
                    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

1.2 Проверка выходных данных

Существует несколько сценариев, которые могут привести к выполнению запроса в течение длительного времени: длительного выполнения, длительного ожидания и длительной компиляции. Дополнительные сведения о том, почему запрос может выполняться медленно, см. в статье "Выполнение и ожидание": почему запросы медленно выполняются?

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

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

session_id	статус	cpu_time_minutes	elapsed_time_minutes	logical_reads	wait_time_minutes	wait_type
56	выполняется	64.40	23.50	0	0.00	NULL

Запрос выполняется непрерывно, если он имеет:

• Увеличение времени ЦП
• Состояние running или runnable
• Минимальное или нулевое время ожидания
• Нет wait_type

В этой ситуации запрос считывает строки, присоединяется, обрабатывает результаты, вычисляет или форматирует. Эти действия являются всеми действиями, связанными с ЦП.

Примечание.

Изменения в logical_reads этом случае не важны, так как некоторые запросы T-SQL, привязанные к ЦП, такие как выполнение вычислений или WHILE цикл, не могут выполнять логические операции чтения вообще.

Если медленный запрос соответствует этим критериям, сосредоточьтесь на сокращении среды выполнения. Как правило, сокращение времени выполнения включает сокращение количества строк, которые запрос должен обрабатывать в течение всей своей жизни, применяя индексы, переписывая запрос или обновляя статистику. Дополнительные сведения см. в разделе "Разрешение ".

Длительное время ожидания

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

session_id	статус	cpu_time_minutes	elapsed_time_minutes	logical_reads	wait_time_minutes	wait_type
56	suspended (приостановлено)	0.03	4.20	50	4.10	LCK_M_U

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

Сведения об устранении неполадок с запросами, длительными из-за ожиданий, см. в статье "Устранение неполадок с медленными запросами в SQL Server".

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

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

Шаг 2. Сбор журналов диагностики вручную

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

SQL Server 2008 — SQL Server 2014 (более ранний пакет обновления 2 (SP2)

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

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

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

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

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

SQL Server 2014 (более поздних версий с пакетом обновления 2 (SP2) и SQL Server 2016 (более ранних версий с пакетом обновления 1 (SP1)

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

Чтобы определить медленные шаги в nu запроса с помощью инфраструктуры профилирования упрощенного выполнения запросов версии 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. Чтобы включить упрощенную инфраструктуру, используйте один из следующих методов:

   • Включите флаг трассировки 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> целочисленным значением, найденным на предыдущем шаге.

   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 для записи планов динамических запросов, включающих фактические счетчики строк. Эта инфраструктура профилирования позволяет проверять операторы запросов в плане запроса во время выполнения и понимать, где большую часть времени тратится в запросе. Упрощенная профилирование включена по умолчанию в Microsoft 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> целочисленным значением, найденным на предыдущем шаге.

   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.

Использование SQL LogScout для отслеживания никогда некончаемых запросов

Вы можете использовать SQL LogScout для записи журналов во время выполнения никогда не завершающегося запроса. Используйте сценарий никогда не заканчивающегося запроса со следующей командой:

.\SQL_LogScout.ps1 -Scenario "NeverEndingQuery" -ServerName "SQLInstance"

Примечание.

Для этого процесса записи журналов требуется, чтобы длительный запрос потреблял по крайней мере 60 секунд времени ЦП.

SQL LogScout записывает по крайней мере три плана запросов для каждого запроса с высоким потреблением ЦП. Вы можете найти имена файлов, которые похожи servername_datetime_NeverEnding_statistics_QueryPlansXml_Startup_sessionId_#.sqlplan. Эти файлы можно использовать на следующем шаге при проверке планов, чтобы определить причину длительного выполнения запроса.

Шаг 3. Просмотр собранных планов

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

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

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

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

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

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

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

   Рассмотрим пример.

   

Шаг 4. Разрешение

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

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

3. Упрощение запроса:

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

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

   • HASH JOIN или MERGE JOIN подсказка
   • Подсказка FORCE ORDER
   • Подсказка FORCESEEK
   • RECOMPILE
   • USE PLAN N'<xml_plan>' (если у вас есть план быстрого запроса, который можно принудительно применить)

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

Связанный контент

• Устранение неполадок с запросом, показывающим значительную разницу в производительности между двумя серверами