Устранение неполадок, связанных с медленно выполняемыми запросами в 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) или Azure Data Studio, запустите его с помощью SET STATISTICS TIME ON и 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.

     

Выполнение и ожидание: почему запросы медленно выполняются?

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

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

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

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

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

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

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

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

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

Примечание.

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

Тип 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.

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

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

• Общие сведения и устранение проблем с блокировкой (LCK_M_*)
• Изучение и устранение проблем с блокировкой Базы данных SQL Azure
• Устранение неполадок с медленной производительностью SQL Server, вызванных проблемами ввода-вывода (PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
• Разрешение конфликтов PAGELATCH_EX вставки последней страницы в SQL Server
• Память предоставляет объяснения и решения (RESOURCE_SEMAPHORE)
• Устранение неполадок с медленными запросами, которые приводят к типу ожидания ASYNC_NETWORK_IO
• Устранение неполадок с типом ожидания высокой HADR_SYNC_COMMIT с помощью групп доступности AlwaysOn
• Как это работает: CMEMTHREAD и отладка их
• Создание параллелизма ожидает действия (CXPACKET и CXCONSUMER)
• Ожидание THREADPOOL

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

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

Если время ЦП (рабочей роли) очень близко к общей продолжительности, запрос тратит большую часть времени выполнения. Как правило, если подсистема 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

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

• Проверка плана запроса
• Обновить статистику
• Выявление и применение отсутствующих индексов. Дополнительные инструкции по выявлению отсутствующих индексов см. в разделе "Настройка некластеризованных индексов с отсутствующими предложениями по индексу"
• Изменение или перезапись запросов
• Выявление и разрешение планов, зависящих от параметров
• Определение и устранение проблем с возможностью SARG
• Определите и устраните проблемы с целью цели строк, из-за которой длительные вложенные циклы могут быть вызваны TOP, EXISTS, IN, FAST, SET ROWCOUNT, OPTION (FAST N). Дополнительные сведения см. в разделе "Ушенные цели строки" и улучшения Showplan - Row Goal EstimateRowsWithoutRowGoal
• Оцените и устраните проблемы оценки кратности. Дополнительные сведения см. в статье Об уменьшении производительности запросов после обновления с SQL Server 2012 или более поздней версии до версии 2014 г.
• Определение и устранение запросов, которые, кажется, никогда не завершены, см. в статье "Устранение неполадок", которые, как представляется, никогда не заканчиваются в SQL Server
• Определение и разрешение медленных запросов, затронутых временем ожидания оптимизатора
• Определите проблемы с высокой производительностью ЦП. Дополнительные сведения см. в разделе "Устранение проблем с высоким потреблением ЦП в SQL Server"
• Устранение неполадок с запросом, производительность которого значительно различается между двумя серверами
• Увеличение вычислительных ресурсов в системе (ЦП)
• Устранение неполадок с производительностью UPDATE с узкими и широкими планами

Рекомендуемые ресурсы

• Обнаруженные типы узких мест производительности запросов в SQL Server и Управляемый экземпляр SQL Azure
• Средства контроля и настройки производительности
• Параметры автоматической настройки в SQL Server
• Общие рекомендации по проектированию индексов
• Устранение ошибок времени ожидания запроса
• Устранение проблем с подключением в SQL Server
• Снижение производительности запросов после обновления с SQL Server 2012 или более ранней версии до 2014 или более поздней версии