Автор: Paul Randal, DBCC CHECKDB performance and computed-column indexes
[Примечание 2016 г.: Команда разработчиков «исправила» проблему в SQL Server 2016, отключив проверку согласованности этих индексов, если не используется параметр WITH EXTENDED_LOGICAL_CHECKS.]
Ни для кого не секрет, что производительность DBCC CHECKDB имеет некоторые особенности, зависящие от схемы проверяемой базы данных и различных видов повреждений. Недавно я проводил тестирование масштабируемости DBCC CHECKDB для статьи в блоге и обнаружил довольно неприятную проблему производительности, существующую во всех версиях SQL Server, начиная с SQL Server 2005. Это не ошибка, это просто особенность работы.
Проблема возникает, когда существует некластерный индекс, в котором вычисляемый столбец является частью ключа индекса или одним из включённых столбцов (INCLUDE), и влияет на DBCC CHECKDB, DBCC CHECKFILEGROUP и DBCC CHECKTABLE.
В качестве небольшого пояснения приведу выдержку из книги «SQL Server 2012 Internals», где я описываю один из способов проверки согласованности некластерных индексов:
Перекрёстные проверки некластерных индексов проверяют, что:
- Каждая запись в некластерном индексе (фильтрованном или нет) должна указывать на допустимую запись в базовой таблице (то есть в куче или кластерном индексе).
- Каждая запись в базовой таблице должна указывать ровно на одну запись в каждом нефильтрованном некластерном индексе и на одну запись в каждом фильтрованном индексе, где это позволяет фильтр.
Механизм выполнения этих проверок эффективно менялся в каждом выпуске, начиная с SQL Server 7.0, становясь всё более и более эффективным. В SQL Server 2012 для каждой секции каждого некластерного индекса создаются две хеш-таблицы: одна для фактических записей в этой секции некластерного индекса, а другая для записей, которые должны существовать в этой секции некластерного индекса (вычисляемых на основе существующих записей данных в таблице).
При обработке записи некластерного индекса все столбцы в записи хешируются вместе в значение BIGINT. Это включает:
- Физическую или логическую ссылку обратно на базовую таблицу (известную как RID базовой таблицы).
- Все включённые столбцы (включая значения
LOBиFILESTREAM) хешируются вместе в значениеBIGINT.
Полученное значение добавляется к главному хеш-значению для фактических записей для секции некластерного индекса, частью которой является запись.
DBCC CHECKDB знает, какие некластерные индексы существуют для таблицы и каким должен быть полный состав записи некластерного индекса для каждого из них. При обработке записи данных для каждой соответствующей записи некластерного индекса, которая должна существовать для записи данных (с учётом любых предикатов фильтра для фильтрованных некластерных индексов), выполняется следующий алгоритм:
- Создать запись некластерного индекса в памяти (опять же, включая RID базовой таблицы и включённые столбцы).
- Хешировать все столбцы в записи индекса вместе в значение
BIGINT. - Добавить полученное значение к главному хеш-значению «должен существовать» для соответствующей секции некластерного индекса, частью которой является запись индекса.
Предпосылка, на которой работает этот алгоритм, заключается в том, что если повреждений нет, главные хеш-значения для фактических записей и записей «должен существовать» для каждой секции некластерного индекса должны точно совпадать в конце пакетной обработки DBCC CHECKDB.
Когда некластерный индекс использует вычисляемый столбец, значение вычисляемого столбца должно быть вычислено на основе определения столбца. Для этого создаётся внутренний механизм, называемый «вычислителем выражений» (expression evaluator). Вычислитель выражений предоставляется кодом обработчика запросов (Query Processor), и его поведение полностью находится вне контроля DBCC CHECKDB. Недостаток вычислителя выражений заключается в том, что при каждом его использовании поток, использующий его, должен удерживать исключительную краткую блокировку. Это создаёт невероятное узкое место и резко влияет на производительность.
Моя тестовая система — Dell R720 с 64 ГБ памяти и 2 процессорами E5-2670 с 8 физическими ядрами и включённой гиперпоточностью. На ней работает SQL Server 2012 SP1 CU3.
Тестовая база данных — AdventureWorks, которую Джонатан расширил до 500 ГБ. База данных распределена по 8 файлам данных, хранящимся на двух дисках Fusion-io по 320 ГБ, а tempdb и его журнал размещены на двух других дисках Fusion-io по 320 ГБ. Я настроил всё таким образом, чтобы исключить ожидания ввода-вывода из теста.
Первые несколько запусков моего тестирования с неограниченным параллелизмом дали время выполнения DBCC CHECKDB около 340 минут. Это показалось мне невероятно медленным, поэтому я провёл ещё один тест и посмотрел на вывод sys.dm_os_waiting_tasks. Я обнаружил, что половина потоков в любой момент времени ожидала исключительного доступа к краткой блокировке DBCC_OBJECT_METADATA. Добавив некоторую диагностику для захвата ожиданий и кратких блокировок, я получил данные, приведённые ниже:
WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S
--------------- ---------- ----------- --------- ---------- ----------- ---------- --------- ---------
CXPACKET 684482.45 682667.13 1815.32 60294236 34.90 0.0114 0.0113 0.0000
OLEDB 659325.08 659325.08 0.00 207661462 33.62 0.0032 0.0032 0.0000
LATCH_EX 615168.39 605357.28 9811.11 798224634 31.37 0.0008 0.0008 0.0000
LatchClass Wait_S WaitCount Percentage AvgWait_S
------------------------ ---------- ---------- ----------- ----------
DBCC_OBJECT_METADATA 611768.00 764845636 99.16 0.0008Это не имело для меня никакого смысла, потому что я не мог поверить, что существует такое невероятное узкое место — почти 1 мс на каждое ожидание краткой блокировки и 800 миллионов ожиданий кратких блокировок! — неужели мы бы уже не слышали об этом? Я написал своему другу Райану Стоунсайферу (Ryan Stonecipher), который унаследовал от меня код DBCC CHECKDB, и он напомнил мне о поведении вычислителя выражений.
Я вернулся к своей тестовой системе и поискал некластерные индексы, использующие вычисляемые столбцы, с помощью кода ниже:
SELECT
[s].[name],
[o].[name],
[i].[name],
[co].[name],
[ic].*
FROM sys.columns [co]
JOIN sys.index_columns [ic]
ON [ic].[object_id] = [co].[object_id]
AND [ic].[column_id] = [co].[column_id]
JOIN sys.indexes [i]
ON [i].[object_id] = [ic].[object_id]
AND [i].[index_id] = [ic].[index_id]
JOIN sys.objects [o]
ON [i].[object_id] = [o].[object_id]
JOIN sys.schemas [s]
ON [o].[schema_id] = [s].[schema_id]
WHERE [co].[is_computed] = 1;
GO
И я нашёл 6 некластерных индексов с вычисляемыми столбцами на некоторых из самых больших таблиц в базе данных. Затем я отключил эти некластерные индексы и повторно запустил тесты DBCC CHECKDB.
17–18 минут на один запуск.
Вау! Узкое место из-за вычислителя выражений замедляло DBCC CHECKDB примерно в 20 раз.
Детали ожиданий и защёлок для тестовых запусков с отключёнными индексами приведены ниже, что я и ожидал увидеть при выполнении DBCC CHECKDB:
WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S
--------------- ---------- ----------- --------- ---------- ----------- ---------- --------- ---------
CXPACKET 33064.56 29331.74 3732.83 42034533 48.14 0.0008 0.0007 0.0001
OLEDB 28883.78 28883.78 0.00 173940154 42.05 0.0002 0.0002 0.0000
LATCH_EX 5021.20 4605.50 415.70 30340659 7.31 0.0002 0.0002 0.0002
LatchClass Wait_S WaitCount Percentage AvgWait_S
------------------------ ---------- ---------- ----------- ----------
DBCC_CHECK_AGGREGATE 5039.36 30267451 98.82 0.0002Мы ничего не можем сделать с этим узким местом, кроме как отключать некластерные индексы на вычисляемых столбцах во время выполнения DBCC CHECKDB и затем перестраивать их после выполнения, что не является очень приятным решением. Поэтому я захотел написать об этом в блоге, чтобы сделать проблему известной и помочь тем из вас, кто бьётся головой о стену, пытаясь понять причину низкой производительности DBCC CHECKDB.
Комментариев нет:
Отправить комментарий