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

Исходная версия продукта: SQL Server
Исходный номер базы знаний: 243589

Введение

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

Поиск медленных запросов

Чтобы установить, что у вас возникли проблемы с производительностью запросов в экземпляре SQL Server, сначала изучите запросы по времени их выполнения (истекшее время). Проверьте, превышает ли время заданное пороговое значение (в миллисекундах) на основе установленного базового показателя производительности. Например, в среде стресс-тестирования может быть установлено пороговое значение для рабочей нагрузки, не превышающее 300 мс, и это пороговое значение можно использовать. Затем можно определить все запросы, превышающие это пороговое значение, фокусируясь на каждом отдельном запросе и предустановленной базовой продолжительности базовой производительности. В конечном счете бизнес-пользователи заботятся о общей продолжительности запросов к базе данных; Таким образом, основное внимание уделяется длительности выполнения. Другие метрики, такие как время процессора и логические чтения, собираются для помощи в сужении направления исследования.

  • Для текущих инструкций проверьте total_elapsed_time и cpu_time столбцы в sys.dm_exec_requests. Выполните следующий запрос, чтобы получить данные:

    SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
      1, 512)  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

  • Для прошлых выполнений запроса проверьте столбцы last_elapsed_time и last_worker_time в sys.dm_exec_query_stats. Выполните следующий запрос, чтобы получить данные:

    SELECT t.text,
     (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
     (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
     ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / 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/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
     CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE t.text like '<Your Query>%'
-- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC

    Примечание.

    Если avg_wait_time отображается отрицательное значение, это параллельный запрос.

  • Если вы можете выполнить запрос по запросу в SQL Server Management Studio (SSMS), sqlcmd или расширение MSSQL для Visual Studio Code, запустите его с помощью SET STATISTICS TIMEON и SET STATISTICS IOON.

    SET STATISTICS TIME ON
SET STATISTICS IO ON
<YourQuery>
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

    Затем из сообщений вы увидите время ЦП, истекшее время и логические операции чтения, как показано ниже:

      Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 460 ms,  elapsed time = 470 ms.

  • Если вы можете собрать план запроса, проверьте данные из свойств плана выполнения.

    1. Запустите запрос с включенной опцией Фактический план выполнения.

    2. Выберите левый оператор из плана выполнения.

    3. В разделе Свойства раскройте пункт QueryTimeStats.

    4. Проверьте elapsedTime и CpuTime.

      Снимок экрана: окно свойств плана выполнения SQL Server с развернутыми свойствами QueryTimeStats.

Запуск или ожидание: почему запросы медленные?

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

  • ОЖИДАНИЕ. Запросы могут быть медленными, так как они ожидают узких мест в течение длительного времени. Просмотрите подробный список узких мест в типах ожиданий.

  • ВЫПОЛНЕНИЕ. Запросы могут быть медленными, так как они выполняются в течение длительного времени. Другими словами, эти запросы активно используют ресурсы ЦП.

Запрос может выполняться в течение некоторого времени и некоторое время находиться в режиме ожидания в ходе своего времени существования (продолжительность). Тем не менее, ваша задача — определить доминирующую категорию, которая способствует длительному времени выполнения. Таким образом, сначала следует определить категорию, к которой относятся запросы. Это просто: если запрос не запущен, он ожидает. В идеале запрос тратит большую часть времени в состоянии выполнения и очень мало времени на ожидание ресурсов. Кроме того, в лучшем случае запрос выполняется в пределах предопределенного базового уровня или ниже. Сравните истекшее время и время ЦП запроса, чтобы определить тип проблемы.

Тип 1: привязка ЦП (runner)

Если время ЦП близко, равно или выше, чем истекшее время, его можно рассматривать как запрос, привязанный к ЦП. Например, если истекшее время составляет 3000 миллисекунд (мс), а время ЦП равно 2900 мс, это означает, что большая часть истекшего времени тратится на ЦП. Затем можно сказать, что это запрос, привязанный к ЦП.

Примеры выполнения запросов с привязкой к ЦП:

Истекшее время (мс) Время ЦП (мс) Операции чтения (логические)
3200 3000 300 000
1080 1000 20

Логические операции чтения — чтение страниц данных и индексов в кэше — чаще всего являются драйверами использования ЦП в SQL Server. В некоторых случаях использование ЦП происходит из других источников: цикл while (в T-SQL или другом коде, таком как XProcs или объекты SQL CLR). Второй пример в таблице иллюстрирует такой сценарий, когда большинство загрузки ЦП не связано с операциями чтения.

Примечание.

Если время ЦП больше длительности, это означает, что выполняется параллельный запрос; Одновременно используется несколько потоков ЦП. Дополнительные сведения см. в разделе Параллельные запросы — исполнитель или ожидатель.

Тип 2. Ожидание узких мест (официант)

Запрос ожидает на узкое место, если истекшее время значительно превышает время работы ЦП. Истекшее время включает время выполнения запроса на ЦП (время ЦП) и время ожидания освобождения ресурса (время ожидания). Например, если истекшее время составляет 2000 мс, а время ЦП составляет 300 мс, время ожидания составляет 1700 мс (2000 – 300 = 1700). Дополнительные сведения см. в разделе "Типы ожиданий".

Примеры ожидающих запросов:

Истекшее время (мс) Время ЦП (мс) Операции чтения (логические)
2000 300 28000
10 080 700 80 000

Параллельные запросы — исполнитель или ожидатель

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

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

Примеры параллельных запросов:

Истекшее время (мс) Время ЦП (мс) Операции чтения (логические)
1200 8100 850000
3080 12300 1 500 000

Высокоуровневая визуализация методологии

Снимок экрана: высокоуровневое визуальное представление методологии устранения медленных запросов.

Диагностика и разрешение ожидающих запросов

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

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

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

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

Истекшее время (мс) Время ЦП (мс) Время ожидания (мс)
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.

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

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

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

Диагностика и разрешение выполняемых запросов

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

Чтобы определить запросы, отвечающие за высокую активность ЦП, выполните следующую инструкцию:

SELECT TOP 10 s.session_id,
           r.status,
           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
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
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

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

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

Распространенные методы для разрешения долго выполняющихся запросов, связанных с ЦП

  • Last updated on