Устранение неполадок с медленно выполняемыми запросами в 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 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.
Если вы можете собрать план запроса, проверка данные из свойств плана выполнения.
Выполните запрос с включением фактического плана выполнения в.
Выберите самый левый оператор в разделе План выполнения.
В разделе Свойства разверните свойство QueryTimeStats .
Установите флажки ElapsedTime и CpuTime.
Выполнение и ожидание: почему запросы выполняются медленно?
Если вы найдете запросы, которые превышают предопределенное пороговое значение, проверьте, почему они могут быть медленными. Причины проблем с производительностью можно сгруппировать в две категории: выполняющаяся или ожидающая:
ОЖИДАНИЕ. Запросы могут выполняться медленно, так как они долго ждут узкого места. См. подробный список узких мест в типах ожиданий.
ВЫПОЛНЕНИЕ. Запросы могут выполняться медленно, так как они выполняются (выполняются) в течение длительного времени. Другими словами, эти запросы активно используют ресурсы ЦП.
Запрос может выполняться в течение некоторого времени и ожидать некоторое время в течение своего времени существования (длительности). Тем не менее, ваше внимание заключается в том, чтобы определить, какая из них является доминирующей категорией, которая вносит свой вклад в его длительное время. Поэтому первая задача заключается в том, чтобы определить, в какую категорию попадают запросы. Все просто: если запрос не выполняется, он ожидает. В идеале запрос тратит большую часть затраченного времени в состоянии выполнения и очень мало времени на ожидание ресурсов. Кроме того, в лучшем случае запрос выполняется в пределах предопределенного базового плана или ниже нее. Сравните затраченное время и время ЦП запроса, чтобы определить тип проблемы.
Тип 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:
- Выполните запрос с включением фактического плана выполнения в.
- Щелкните правой кнопкой мыши левый оператор на вкладке План выполнения .
- Выберите Свойства , а затем — Свойство WaitStats .
- Проверьте Значения WaitTimeMs и WaitType.
Если вы знакомы со сценариями PSSDiag/SQLdiag или SQL LogScout LightPerf/GeneralPerf, рассмотрите возможность использования любого из них для сбора статистики производительности и определения ожидающих запросов на экземпляре SQL Server. Вы можете импортировать собранные файлы данных и анализировать данные о производительности с помощью SQL Nexus.
Ссылки для устранения или сокращения ожиданий
Причины и способы их устранения для каждого типа ожидания различаются. Не существует единого общего метода для разрешения всех типов ожидания. Ниже приведены статьи по устранению неполадок и устранению распространенных проблем с типом ожидания:
- Общие сведения и устранение проблем с блокировкой (LCK_M_*)
- Общие сведения и устранение проблем с блокировкой базы данных Azure SQL
- Устранение неполадок с низкой производительностью SQL Server, вызванной проблемами ввода-вывода (PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
- Разрешение конфликтов по вставке последней страницы PAGELATCH_EX в SQL Server
- Объяснения и решения, предоставляемые памятью (RESOURCE_SEMAPHORE)
- Устранение неполадок с медленными запросами, возникающими в результате ASYNC_NETWORK_IO типа ожидания
- Устранение неполадок с типом ожидания высокого HADR_SYNC_COMMIT в группах доступности Always On
- Как это работает: 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). Дополнительные сведения см. в разделах Row Goals Gone Rogue и Showplan усовершенствования — Оценка цели строкиRowsWithoutRowGoal
- Оценка и устранение проблем с оценкой кратности . Дополнительные сведения см. в статье Снижение производительности запросов после обновления с SQL Server 2012 г. или более поздней версии до 2014 г.
- Определение и разрешение кубов, которые кажутся никогда не полными, см. статью Устранение неполадок с запросами, которые, кажется, никогда не заканчиваются в SQL Server
- Определение и разрешение медленных запросов, на которые влияет время ожидания оптимизатора
- Выявление проблем с высокой производительностью ЦП. Дополнительные сведения см. в статье Устранение неполадок с высокой загрузкой ЦП в SQL Server
- Устранение неполадок с запросом, показывающим значительную разницу в производительности между двумя серверами
- Увеличение вычислительных ресурсов в системе (ЦП)
- Устранение проблем с производительностью UPDATE с помощью узких и широких планов
Рекомендуемые ресурсы
- Обнаруживаемые типы узких мест производительности запросов в SQL Server и Управляемый экземпляр SQL Azure
- Средства для отслеживания и настройки производительности
- Параметры автоматической настройки в SQL Server
- Рекомендации по архитектуре индексирования и проектированию
- Устранение ошибок времени ожидания запроса
- Устранение проблем в SQL Server с высокой загрузкой ЦП
- Снижение производительности запросов после обновления с SQL Server 2012 или более ранней версии до 2014 или более поздней версии
Обратная связь
https://aka.ms/ContentUserFeedback.
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделеОтправить и просмотреть отзыв по