Когда мы попросили две популярные большие языковые модели «составить рейтинг ведущих реляционных СУБД по широте и глубине предоставляемых ими средств мониторинга работы ядра», одна поставила SQL Server на первое место, а другая — на второе. Сотни динамических административных представлений (DMV) и системных представлений каталога, тысячи расширенных событий, XML-представление планов запросов и хранилище запросов (Query Store) позволяют вам или вашему агенту диагностировать и устранять практически любые проблемы производительности.
И всё же иногда обнаруживаются слепые зоны.
Загадочный рост нагрузки на процессор
Представьте себе загруженную базу данных OLTP, которая обычно работает без каких-либо проблем, используя около 30% ресурсов процессора. Но однажды загрузка процессора начинает расти, хотя объём работы не увеличился. При достижении 60% срабатывает тревожное оповещение.
Вы начинаете искать наиболее распространённую причину — ухудшение плана выполнения запроса, — но ничего не находите. Планы запросов не изменились, однако некоторые запросы потребляют всё больше процессорного времени и выполняются всё медленнее.
На этом этапе можно записать трассу XPerf и изучить стеки вызовов, которые потребляют больше всего процессорного времени. В данном случае это эффективный способ обнаружить виновника. Но он совершенно не так прост и быстр, как обычный запрос к DMV. Для записи трассы нужен доступ к операционной системе, необходимо установить Windows Performance Toolkit и загрузить файлы символов. Зачастую всё это просто невозможно.
В рассматриваемом случае виновником оказалось хранилище версий, точнее — версии строк, которые приходилось хранить значительно дольше обычного.
Версии строк нужны только до тех пор, пока какая-либо транзакция может к ним обратиться. После этого фоновый процесс их удаляет. Однако несколько обстоятельств могут задерживать эту очистку. Самая распространённая причина — длительная транзакция, нередко оставленная открытой в бездействующем сеансе, который начал транзакцию, но так и не выполнил фиксацию. Пока очистка заблокирована, ситуация постепенно ухудшается:
Цепочки версий становятся длиннее. Каждое обновление часто изменяемой строки создаёт ещё одну её версию. Запросам, которым требуется старая версия строки, приходится проходить всё больше таких версий в обратном порядке. До сих пор не существовало практического способа обнаружить это без трассы XPerf или отладчика.
Версии перемещаются за пределы строки. В самой строке может храниться только одна версия. Пока очистка заблокирована, последующие обновления этой строки вынуждены сохранять новые версии вне строки. Их запись и чтение обходятся дороже. Для часто обновляемых строк почти все новые версии в итоге оказываются вне строки. До сих пор невозможно было связать замедление выполнения запросов с таким переходом от хранения версий внутри строки к хранению вне её.
В результате загрузка процессора и количество логических чтений постепенно растут во всей рабочей нагрузке, но ни один отдельный запрос или план выполнения не выделяется на общем фоне.
Это важно, поскольку версионирование строк лежит в основе уровней изоляции RCSI и Snapshot Isolation, позволяющих избежать проблем параллельного доступа, связанных с блокировками. Ускоренное восстановление базы данных (Accelerated Database Recovery, ADR) также использует версионирование строк: оно обеспечивает быстрое восстановление, мгновенный откат и позволяет уменьшить размер журнала транзакций.
В Azure SQL Database и RCSI, и ADR включены по умолчанию. Мы рекомендуем использовать такую же конфигурацию для большинства рабочих нагрузок SQL Server. Примечание переводчика: тут хотелось бы сразу увидеть предупреждение о том, насколько с RCSI распухнет база.
До сих пор не хватало простого способа обнаружить момент, когда чтение версий начинает замедлять работу.
Освещаем проблему
В Azure SQL Database мы добавили метрики чтения версий в SET STATISTICS IO, динамические административные представления (DMV), расширенные события и XML-представление планов запросов. Новые показатели охватывают как прежнее хранилище версий в tempdb, так и новое постоянное хранилище версий (Persistent Version Store, PVS) в каждой пользовательской базе данных.
Предусмотрены следующие виды хранилища версий:
- Хранилище версий в tempdb. Версии, создаваемые для баз данных, в которых не используется ADR.
- Версии PVS внутри строки (in-row PVS). Небольшие версии, хранящиеся непосредственно в строке данных.
- Версии PVS вне строки (off-row PVS). Версии, хранящиеся в специальной системной таблице пользовательской базы данных.
- Версии PVS для оперативного перестроения индексов (online index PVS). Версии, которые во время выполнения операции с индексом в оперативном режиме сохраняются в другой системной таблице. Операции с индексом могут нуждаться в этих версиях на протяжении всего своего выполнения. Раздельное хранение позволяет в это время продолжать очистку обычного PVS.
Теперь новые метрики доступны следующим образом.
SET STATISTICS IO. Если при работе с объектом выполнялось чтение версий, после привычных строк статистики ввода-вывода появляется дополнительная строка:
Object '[dbo].[t]'. Tempdb versions read 0, in-row PVS versions read 0, off-row PVS versions read 1000, online index PVS versions read 0.XML плана запроса. В плане, содержащем статистику выполнения, элемент RunTimeCountersPerThread каждого оператора, который читает таблицу или индекс, теперь содержит четыре новых атрибута:
ActualTempdbVersionsReadActualInRowPvsVersionsReadActualOffRowPvsVersionsReadActualOnlineIndexPvsVersionsRead
Эти атрибуты показывают, какой оператор в плане читал версии строк, к какому типу хранилища они относились и сколько версий было прочитано.
Расширенные события. События sql_statement_completed, sp_statement_completed, sql_batch_completed и rpc_completed получили по четыре новых поля:
tempdb_versions_readinrow_pvs_versions_readoffrow_pvs_versions_readonline_index_pvs_versions_read
Статистика запросов и программных модулей. В представлениях sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, sys.dm_exec_trigger_stats и sys.dm_exec_function_stats появились четыре новых столбца:
total_version_readslast_version_readsmin_version_readsmax_version_reads
Эти столбцы содержат соответственно общее, последнее, минимальное и максимальное значения суммы всех четырёх счётчиков. Теперь можно ранжировать запросы и программные модули, планы которых находятся в кэше, по количеству чтений версий и сопоставлять эти показатели с процессорным временем (временем работы), фактическим временем выполнения и количеством логических чтений.
Сеансы и запросы. В представлениях sys.dm_exec_requests и sys.dm_exec_sessions появился новый столбец version_reads, также объединяющий значения всех четырёх счётчиков.
Первое представление позволяет узнать, сколько чтений версий выполнил запрос к текущему моменту. Второе показывает общее количество чтений версий для всех завершённых запросов за время существования сеанса.
Когда очистка версий заблокирована, количество чтений версий растёт вместе с процессорным временем и числом логических чтений во всей рабочей нагрузке. Кроме того, в PVS может наблюдаться переход от хранения версий внутри строк к хранению вне строк.
Это направляет поиск причины проблемы в сторону хранилища версий, а не планов запросов или самой рабочей нагрузки. Далее статья Monitor and troubleshoot accelerated database recovery («Мониторинг и устранение проблем с ускоренным восстановлением базы данных») рассказывает, как найти и устранить причину блокировки очистки.
Подробнее см. в документации раздел Version read metrics («Метрики чтения версий»).
Речь идёт о процессоре или о вводе-выводе?
Возможно, вы зададитесь вопросом: если обход цепочек версий в первую очередь увеличивает нагрузку на процессор, почему эти показатели появились в STATISTICS IO?
Действительно, этот вопрос вызвал немало споров. Может быть, следовало добавить отдельную команду SET STATISTICS — например, STATISTICS VERSION_CHAIN — специально для этих показателей? В итоге мы остановились на STATISTICS IO по трём причинам:
- Мы не хотели заставлять вас запоминать и вводить ещё одну команду.
- Мы хотели, чтобы новые показатели было легко обнаружить.
- Логические чтения в
STATISTICS IOи так отражают работу процессора — доступ к буферному пулу, а не физический ввод-вывод с диска. Чтение версий — показатель того же типа, только он учитывает строки, а не страницы. Поэтому логично было объединить их.
Можно ли вместо этого показывать затраты на ввод-вывод при чтении версий, подсчитывая страницы, прочитанные из хранилища версий? Конечно. Но тогда осталась бы значительная слепая зона — версии, хранящиеся внутри строк.
Их чтение не вызывает дополнительных операций ввода-вывода, поскольку они хранятся непосредственно вместе со строкой данных и попадают в память при чтении страницы данных. Однако обход таких версий и восстановление старых версий по различиям между ними создают заметную нагрузку на процессор.
Эти затраты остались бы невидимыми, если бы мы показывали статистику ввода-вывода только для страниц версий, хранящихся вне строк.
Зачем мы внесли это улучшение?
Помимо очевидной причины — устранения слепой зоны в средствах мониторинга ядра СУБД, — мы хотели убрать неопределённость в оценке накладных расходов на чтение версий.
По своей природе эти затраты не могут быть нулевыми. Однако преимущества RCSI и ADR с точки зрения параллельного доступа и доступности чрезвычайно велики. Тем не менее мы иногда сталкиваемся с тем, что заказчики не решаются включать эти возможности в SQL Server, поскольку накладные расходы сложно отслеживать и количественно оценивать.
Надеемся, это улучшение избавит их от подобных сомнений :-).
Что дальше?
На данном этапе мы ожидаем два вопроса.
А что насчёт Query Store? Почему не добавить чтение версий к остальным показателям фактического выполнения запросов?
Этот вопрос рассматривается. Нужен ли вам новый показатель чтения версий в Query Store? Оставьте комментарий и расскажите, чем он будет полезен.
А что насчёт SQL Server и Azure SQL Managed Instance?
В ближайшее время новые метрики станут доступны в Azure SQL Managed Instance с политикой обновления Always-up-to-date. Что касается практически всех улучшений ядра СУБД, которые сначала появляются в Azure SQL Database, мы планируем включить и это улучшение в следующую версию SQL Server, а также в соответствующую версию Azure SQL Managed Instance, когда эти версии выйдут.
Расскажите в комментариях, что вы думаете об этом улучшении. Есть ли другие слепые зоны, из-за которых становится сложнее понять, что происходит внутри ядра СУБД? Поделитесь проблемами, с которыми сталкиваетесь в повседневной работе.
Пример
Вот простой способ ознакомиться с некоторыми новыми показателями. Подключитесь к базе данных в Azure SQL Database и выполните весь приведённый ниже сценарий одним пакетом команд.
Обратите внимание: графический план запроса в SSMS пока не отображает новые показатели, хотя в XML фактического плана они уже присутствуют. Эта возможность появится в одной из будущих версий SSMS.
SET NOCOUNT ON;
/*
Create a table with 1000 rows.
*/
DROP TABLE IF EXISTS dbo.t;
CREATE TABLE dbo.t (id int PRIMARY KEY, v int NOT NULL);
INSERT INTO dbo.t (id, v)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0
FROM sys.all_columns;
/*
Create versions in Persistent Version Store (PVS).
With Accelerated Database Recovery (ADR), rollback is instant,
but the row versions from an aborted transaction
stay in place until background cleanup happens later.
*/
BEGIN TRAN;
UPDATE dbo.t SET v = v + 1;
ROLLBACK;
/*
Immediately, read the data from the table.
This requires version reads because PVS cleanup hasn't completed yet,
but the SELECT must see the data before the aborted transaction.
Version read statistics are returned after IO statistics.
Showplan XML contains four new attributes under RunTimeCountersPerThread.
*/
SET STATISTICS IO, XML ON;
SELECT COUNT(*) AS cnt FROM dbo.t WHERE v = 0;
SET STATISTICS IO, XML OFF;
/*
Version reads are also present in the current request statistics.
The value is doubled because of a statistics update by the previous SELECT,
which also reads the same row versions.
*/
SELECT version_reads
FROM sys.dm_exec_requests
WHERE session_id = @@SPID;
/*
Aggregate version reads are also available for queries with cached plans,
such as the previous SELECT (cached after auto-parameterization).
*/
SELECT qs.execution_count,
qs.total_version_reads,
qs.last_version_reads,
qs.min_version_reads,
qs.max_version_reads
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE '%SELECT COUNT(*) \[cnt\] FROM \[dbo\].\[t\] WHERE%' ESCAPE '\';
Комментариев нет:
Отправить комментарий