Устранение неполадок с запросами, которые, кажется, никогда не заканчиваются в 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	status	cpu_time	logical_reads	wait_type
56	Запущена	7038	101000	NULL
56	готово к запуску	12040	301000	NULL
56	Запущена	17020	523000	NULL

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

session_id status 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

session_id	status	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, найдите тот же узел в предполагаемом плане запроса. Этот шаг поможет понять, какой оператор в плане является main причиной длительного выполнения.
Остановите 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).
Щелкните правой кнопкой мыши пустую область плана выполнения и выберите Сравнить план выполнения.
Выберите второй файл плана запроса, который требуется сравнить.
Найдите толстые стрелки, указывающие на большое количество строк, передаваемых между операторами. Затем выберите оператор до или после стрелки и сравните количество фактических строк в двух планах.
Сравните второй и третий планы, чтобы узнать, происходит ли наибольший поток строк в одних и том же операторах.

Пример:

Разрешение

Убедитесь, что статистика обновлена для таблиц, используемых в запросе.
Найдите отсутствующие рекомендации по индексу в плане запроса и примените ее.
Перепишите запрос, чтобы упростить его:
- Используйте более селективные WHERE предикаты, чтобы сократить объем обрабатываемых данных.
- Разорвать его на части.
- Выберите некоторые части во временные таблицы и соедините их позже.
- Удалите TOP, EXISTSи FAST (T-SQL) в запросах, которые выполняются в течение очень длительного времени из-за цели строки оптимизатора. Кроме того, можно использовать подсказкуDISABLE_OPTIMIZER_ROWGOAL. Дополнительные сведения см. в разделе Row Goals Gone Rogue.
- Избегайте использования общих табличных выражений (CTE) в таких случаях, когда они объединяют инструкции в один большой запрос.
Попробуйте использовать указания запросов , чтобы создать лучший план:
- 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.

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

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

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

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