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

  • Статья

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

Введение

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

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

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

  • Для текущих инструкций проверка 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) или Azure Data Studio, выполните его с помощью команды 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: привязанный к ЦП (средство выполнения)

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

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

Затраченное время (мс) Время ЦП (мс) Чтение (логическое)
3200 3000 300000
1080 1000 20

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

Примечание.

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

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

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

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

Затраченное время (мс) Время ЦП (мс) Чтение (логическое)
2000 300 28000
10080 700 80000

Параллельные запросы — средство выполнения или официант

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

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

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

Затраченное время (мс) Время ЦП (мс) Чтение (логическое)
1200 8100 850000
3080 12300 1500000

Общее визуальное представление методологии

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

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

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

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