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

Статья
01/19/2024

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

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

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

Внимание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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)

Захватить три или четыре моментальных снимка, разделенных на одну минуту, чтобы предоставить достаточно данных для анализа. В частности, можно сравнить row_count числа для каждого оператора с течением времени и увидеть, что показывает значительное увеличение количества строк (миллион или более).
В новом окне запроса в SSMS зафиксируйте предполагаемый план запроса для запроса проблемы, выполнив следующие команды:
```
SET SHOWPLAN_XML ON
GO
<problem query here>
GO
SET SHOWPLAN_XML OFF
```
Используя идентификатор узла с наибольшим числом строк, определяемым запросом на шаге 3, найдите тот же узел в предполагаемом плане запроса. Этот шаг поможет понять, какой оператор в плане является основной причиной длительного времени выполнения.
Остановите XEvent, выполнив следующую команду:
```
ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP
```

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

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

Чтобы включить упрощенную инфраструктуру в этих версиях 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

Запустите затронутый никогда некончающий запрос из приложения.
Используйте команду, аналогичную следующей, чтобы определить 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
```
Выполните следующую команду три или четыре раза в течение одной минуты, чтобы проверить план запроса и фактическую статистику в плане. Обязательно сохраните план запроса каждый раз, чтобы сравнить их и установить, какой оператор запроса потребляет большую часть времени ЦП. В частности, можно сравнить число строк (фактическое число строк) для каждого оператора с течением времени и увидеть, какой из операторов показывает значительное увеличение количества строк (миллион или более). Замените <session_id> целочисленным значением, найденным на предыдущем шаге 3.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
Остановите XEvent при запуске одного или отключите флаг трассировки:
```
ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = STOP
-- or
DBCC TRACEOFF (7412, -1)
```

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

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

Запустите затронутый никогда некончающий запрос из приложения.
Используйте команду, аналогичную следующей, чтобы определить 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
```
Выполните следующую команду три или четыре раза, чтобы проверить план запроса и фактическую статистику в плане. Обязательно сохраните план запроса каждый раз, чтобы сравнить их и установить, какой оператор запроса потребляет большую часть времени ЦП. Замените <session_id> целочисленным значением, найденным на предыдущем шаге 3.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
В частности, выберите 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) и более поздних сборок и версий.

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

Откройте ранее сохраненный файл плана выполнения запроса (.sqlplan).
Щелкните правой кнопкой мыши пустую область плана выполнения и выберите "Сравнить showplan".
Выберите второй файл плана запроса, который вы хотите сравнить.
Найдите толстые стрелки, указывающие большое количество строк, поступающих между операторами. Затем выберите оператор до или после стрелки и сравните количество фактических строк между двумя планами.
Сравните второй и третий планы, чтобы узнать, происходит ли самый большой поток строк в одних и том же операторах.

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

Разрешение

Убедитесь, что статистика обновляется для таблиц, используемых в запросе.
Найдите отсутствующие рекомендации по индексу в плане запроса и примените все.
Переопределите запрос с целью упростить его:
- Используйте более выборочные WHERE предикаты, чтобы уменьшить объем обработанных данных заранее.
- Разорвать его друг от друга.
- Выберите некоторые части в временные таблицы и присоедините их позже.
- Удалите TOP, EXISTSи FAST (T-SQL) в запросах, которые выполняются в течение очень длительного времени из-за цели строки оптимизатора. Кроме того, можно использовать подсказку DISABLE_OPTIMIZER_ROWGOAL . Дополнительные сведения см. в разделе "Голы строк" "Ушел изгой".
- Избегайте использования общих выражений таблиц (CTEs) в таких случаях, как они объединяют операторы в один большой запрос.
Попробуйте использовать подсказки запросов для создания лучшего плана:
- HASH JOIN или MERGE JOIN подсказка
- Подсказка FORCE ORDER
- Подсказка FORCESEEK
- RECOMPILE
- ИСПОЛЬЗОВАТЬ PLAN N'<xml_plan>' , если у вас есть быстрый план запроса, который можно принудительно применить
Используйте хранилище запросов (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.

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

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

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

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