Существует огромное количество лучших практик по настройке производительности SQL Server — я мог бы легко написать целую книгу на эту тему, особенно учитывая множество различных настроек базы данных, настроек сервера, практик написания кода, типов ожидания и так далее, которые могут влиять на производительность. Для этой статьи я решил немного отступить от списка конкретных деталей и дать несколько общих рекомендаций о том, как подходить к настройке производительности, чтобы максимизировать усилия и минимизировать отвлекающие факторы.
1) Не предполагайте, что симптом является первопричиной
Многие администраторы баз данных и разработчики склонны к тому, что я называю «рефлекторным устранением проблем с производительностью» (knee-jerk performance troubleshooting), когда проводится минимальный анализ и исследование, и делается предположение, что самый распространённый симптом плохой производительности должен быть первопричиной. Когда это происходит и предпринимаются усилия для устранения предполагаемой первопричины, это может привести к потере времени и разочарованию, когда меры по смягчению не помогают ситуации.
Мой любимый пример этой проблемы, и я уверен, что вы все с ней сталкивались, — когда средняя задержка диска (average disk latency) высока. Классическая рефлекторная реакция — должно быть, проблема в подсистеме ввода-вывода, поэтому компания тратит деньги на более качественную подсистему ввода-вывода, и проблема уходит на некоторое время, а затем возвращается снова, потому что проблема не в самом оборудовании, а в том, что происходит внутри SQL Server.
В подобном случае обычно лучше сделать мысленный шаг назад и спросить, почему SQL Server перегружает подсистему ввода-вывода или, точнее, почему SQL Server выполняет так много физических чтений. Причин может быть множество, например (но не ограничиваясь):
- Неэффективный план запроса, выполняющий большое параллельное сканирование таблицы вместо использования некластерного индекса из-за отсутствующего индекса, неявного преобразования или устаревшей статистики.
- Нехватка памяти в буферном пуле (то есть недостаточно места для хранения обычного «рабочего набора» страниц базы данных) со стороны операционной системы.
Всегда стоит провести некоторое исследование, вместо того чтобы делать поспешные выводы о первопричине.
2) Определите масштаб проблемы
Крайне важно выяснить масштаб проблемы, так как это определяет, как вы будете её исследовать, какие метрики собирать и какие скрипты и инструменты использовать. Например, запрос на исследование хранимой процедуры XYZ, которая выполняется в два раза дольше обычного, сильно отличается от запроса на настройку всех долго выполняющихся хранимых процедур.
Метрики хранимых процедур можно получить, выполнив запрос в Management Studio и отметив длительность, время CPU и статистику ввода-вывода. Эту информацию также можно получить из кэша планов, и вы также можете использовать кэш планов, когда вам нужно найти самые долго выполняющиеся хранимые процедуры. Следующий запрос, адаптированный из популярного набора скриптов DMV здесь, выводит 25 самых медленных процедур по средней длительности:
SELECT
TOP (25) [p].[name] AS [SP Name],
[eps].[min_elapsed_time],
[eps].[total_elapsed_time] / [eps].[execution_count] AS [avg_elapsed_time],
[eps].[max_elapsed_time],
[eps].[last_elapsed_time],
[eps].[total_elapsed_time],
[eps].[execution_count],
ISNULL ([eps].[execution_count] /
DATEDIFF (MINUTE, [eps].[cached_time], GETDATE ()), 0) AS [Executions/Minute],
FORMAT ([eps].[last_execution_time],
'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time],
FORMAT ([eps].[cached_time],
'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,[qp].[query_plan] AS [Query Plan] -- Раскомментируйте, если нужен план запроса
FROM sys.procedures AS [p] WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS [eps] WITH (NOLOCK)
ON [p].[object_id] = [eps].[object_id]
CROSS APPLY sys.dm_exec_query_plan ([eps]. [plan_handle]) AS [qp]
WHERE
[eps].[database_id] = DB_ID ()
AND DATEDIFF (MINUTE, [eps].[cached_time], GETDATE()) > 0
ORDER BY [avg_elapsed_time] DESC
OPTION (RECOMPILE);Существуют также такие инструменты, как функциональность Top SQL в SolarWinds SQL Sentry, которые могут помочь выявить запросы с наибольшим влиянием и использованием ресурсов.
3) Определите цель успеха
Как только вы определили масштаб проблемы, следующим шагом будет определение цели усилий по настройке производительности, чтобы вы знали, когда достигли успеха и можете перейти к другой задаче. Не позволяйте цели быть чем-то неопределённым и открытым, например «хранимая процедура XYZ должна работать быстрее». Цель должна быть чётко определена, например «хранимая процедура XYZ должна работать с той же скоростью, что и раньше, то есть составлять 50% от текущего времени выполнения».
Иногда исследование будет более сложным, если масштаб шире, требующим сбора метрик и информации в течение некоторого времени, прежде чем можно будет начать анализ и смягчение. Например, один из первых консалтинговых клиентов, с которым я работал, поставил передо мной довольно открытую цель, которую можно перефразировать как «в tempdb раз в неделю заканчивается место, и нам нужно, чтобы этого не происходило», без какого-либо понимания причины. Исследование включало настройку двух заданий SQL Agent: одно выполнялось каждые 10 секунд для поиска больших использований tempdb и записи информации в таблицу, а другое — раз в час, чтобы отправлять мне по электронной почте любые результаты за предыдущий час. Общий код, который я написал для поиска «пожирателей» места в tempdb, приведён ниже:
-- InternalMB/Pages: рабочие таблицы (worktables: курсоры, spool), рабочие файлы (workfiles: хеш-соединения), сортировки
-- UserMB/Pages: всё остальное
--
SELECT
GETDATE () AS [Date],
[tsu].[session_id] AS [SessionID],
[tsu].[exec_context_id] AS [ExecContextID], -- любое значение больше 0 означает параллелизм
([tsu].[user_objects_alloc_page_count] -
[tsu].[user_objects_dealloc_page_count]) AS [UserPages],
ROUND (CONVERT (FLOAT, ([tsu].[user_objects_alloc_page_count] -
[tsu].[user_objects_dealloc_page_count]) * 8) / 1024.0, 2) AS [UserMB],
([tsu].[internal_objects_alloc_page_count] -
[tsu].[internal_objects_dealloc_page_count]) AS [InternalPages],
ROUND (CONVERT (FLOAT, ([tsu].[internal_objects_alloc_page_count] -
[tsu].[internal_objects_dealloc_page_count]) * 8) / 1024.0, 2) AS [InternalMB],
[er].[plan_handle] AS [Plan],
[est].[text] AS [Text]
FROM sys.dm_db_task_space_usage [tsu]
JOIN sys.dm_exec_requests [er]
ON [er].[session_id] = [tsu].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
/*
WHERE
-- При необходимости отфильтруйте по лимиту размера
-- Например, 16384 — это 128 МБ в страницах по 8 КБ
(([user_objects_alloc_page_count] - [user_objects_dealloc_page_count]) +
([internal_objects_alloc_page_count] - [internal_objects_dealloc_page_count])) >= 16384
*/
ORDER BY
(([user_objects_alloc_page_count] - [user_objects_dealloc_page_count]) +
([internal_objects_alloc_page_count] - [internal_objects_dealloc_page_count])) DESC;4) Поймите ограничения
Прежде чем предлагать или вносить изменения, важно знать, есть ли какие-то вещи, которые вы просто не можете сделать. Вот несколько примеров:
- Если приложение написано сторонним поставщиком (vendor), вы не сможете вносить изменения в код для улучшения производительности.
- Если приложение написано сторонним поставщиком, вы, возможно, даже не сможете добавлять или изменять индексы без нарушения соглашения о поддержке с поставщиком.
- Вы, возможно, не сможете изменить такую настройку, как MAXDOP или обработку параметров (parameter sniffing) для всего сервера, что может означать использование параметра ALTER DATABASE SCOPED CONFIGURATION для одной базы данных.
- Даже если вы можете изменить код, может существовать длительный процесс тестирования, который препятствует немедленному внедрению изменения, поэтому вам может потребоваться искать альтернативные (возможно, краткосрочные) решения для быстрого исправления проблемы.
5) Изменяйте только одно за раз
Одна из самых запутанных вещей при настройке производительности — это внесение нескольких изменений одновременно, так как тогда вы не будете знать, какое изменение повлияло на результат или не нейтрализовали ли несколько изменений друг друга. Всегда изменяйте только одно за раз и ведите учёт того, что вы изменили и какой эффект это имело, если таковой был. Кроме того, если изменение не даёт никакого эффекта, откатите его, чтобы оно не стало помехой, если рабочая нагрузка изменится позже.
6) Не тестируйте в производственной среде
Одна из худших вещей, которые можно сделать при настройке производительности, — это вносить изменения непосредственно в производственной среде, так как это может привести к серьёзным последствиям для рабочей нагрузки и бизнеса, если изменение создаст огромный негативный эффект. Это означает, что вам нужна отдельная среда тестирования/QA, которую можно использовать для оценки изменений в условиях производственной рабочей нагрузки или максимально приближённых к ним. И это плавно подводит нас к следующему пункту…
7) Понимайте, как тестовая среда соотносится с производственной
Если ваша тестовая система не соответствует производственной, вы можете не увидеть такого же изменения производительности в продакшене, как в тесте. Классические примеры этого включают:
- Производственная система с определённым количеством процессоров (например, четыре 8-ядерных процессора) и тестовая система с меньшей мощностью для экономии (например, четыре 4-ядерных процессора).
- Похожая ситуация: тестовая система имеет гораздо меньше памяти, чем производственная, или другую конфигурацию NUMA, или менее производительную подсистему хранения.
- Тестовая система имеет только подмножество производственных данных для тестирования.
- Тестовая система не способна симулировать производственную рабочую нагрузку.
Все эти факторы могут привести к тому, что тестовая система будет строить другие планы запросов или рабочая нагрузка в тесте будет иметь совершенно иные характеристики, чем в продакшене. Это означает, что вы будете настраивать производительность для другой рабочей нагрузки и среды, и эффективность изменений может не перенестись на производственную среду.
8) Понимайте последствия изменения
После того как вы определили необходимое изменение, вам нужно подумать о том, какое более широкое влияние, если таковое будет, это изменение окажет. Например, если вам нужно изменить MAXDOP или порог стоимости для параллелизма (cost threshold for parallelism), это приведёт к очистке кэша планов, и вы можете столкнуться с риском перекомпиляции запросов, чувствительных к параметрам, с неоптимальными планами.
Другие изменения могут быть более тонкими, например, выгрузка частей запросов на доступную для чтения вторичную реплику в группе доступности. Это может привести к проблемам фрагментации индексов в основной базе данных, что само по себе может стать проблемой производительности (как я описывал в этой статье на SQLPerformance.com).
Вы же не хотите решить одну проблему производительности и в итоге получить неожиданную другую проблему, которую затем придётся решать.
9) Создайте план отката
Очень важно иметь полный журнал того, что было изменено, и возможность откатить изменения, если что-то пойдёт не так. Это означает сохранение оригинальных копий всего кода и схемы и, в идеале, наличие скрипта, который можно запустить для быстрого отката изменений.
Если это будет трудно сделать и на самом деле потребует восстановления базы данных из резервных копий, стоит рассмотреть возможность создания снимка базы данных (database snapshot) и сохранения его на несколько дней. Снимок базы данных автоматически сохраняет копию всех изменённых страниц файлов данных с момента его создания и позволяет фактически вернуть базу данных в это состояние с помощью однострочной команды T-SQL (внутренне SQL Server делает это, возвращая страницы до изменений обратно в реальную базу данных — это называется «reverting the database to the database snapshot» - восстановлением базы данных до снимка базы данных).
10) Удалите диагностические элементы из производственной среды
После завершения исследования и достижения цели настройки производительности убедитесь, что вы удалили все диагностические инструменты, которые внедрили для помощи в исследовании, так как они сами по себе могут вызывать проблемы с производительностью, если останутся включёнными. Особенно это касается сеансов Extended Events, которые могут стать «тихими убийцами», потребляя много ресурсов CPU без каких-либо других признаков того, что проблема именно в них.
Вы можете увидеть, какие сеансы Extended Events выполняются, с помощью следующего кода:
SELECT
[ses].[name] AS [Session Name],
CASE
WHEN [xs].[address] IS NOT NULL THEN 'Running'
ELSE 'Stopped'
END AS [State],
[xs].[create_time] AS [Start Time]
FROM sys.server_event_sessions AS [ses]
LEFT OUTER JOIN sys.dm_xe_sessions AS [xs]
ON [ses].[name] = [xs].[name]
ORDER BY [State], [Start Time];А если вы используете SQL Server 2025 и Extended Events, есть новая опция MAX_DURATION, которую вы можете использовать, чтобы гарантировать, что диагностический сеанс прекратит работу через определённое время.
Резюме
Вы всегда должны применять пошаговый подход к настройке производительности, а не бросаться и бездумно менять всё в производственной среде, и я надеюсь, что эта статья предоставил вам простую структуру, которую вы можете применить на практике. Существует множество кода, чтобы помочь вам в различных исследованиях, а также бесплатные инструменты, такие как Plan Explorer — я не могу не порекомендовать его! Удачной настройки!
Комментариев нет:
Отправить комментарий