До сих пор широко распространено заблуждение, что при корректной работе в моделях восстановления FULL или BULK_LOGGED полные или дифференциальные резервные копии могут усекать журнал. Нет. Это НИКОГДА не происходит. Это одна из причин, по которой я посвящаю целый доклад этой теме на конференции PASS в этом году — поведение журнала транзакций, по моему скромному мнению, является одной из самых неправильно понимаемых частей SQL Server.
На этой неделе на форумах и в Twitter было оживлённо: люди сталкиваются с множеством интересных проблем. Я заметил, что существует много заблуждений относительно запуска восстановления (repair), поэтому, чтобы завершить пятницу, я пройдусь по их списку для вас. Вот эти заблуждения, по поводу некоторых из которых мне приходилось спорить с людьми несколько раз и в конце концов прибегать к фразе «Слушайте, я писал код восстановления, мне жаль, но вы не правы», что я ненавижу делать:
Это короткое продолжение моей статьи «Заблуждения вокруг размера null bitmap».
Битовая карта NULL всегда присутствует в записи данных (т.е. в записях в куче или на конечном уровне кластерного индекса), за исключением случаев, когда в SQL Server 2008 и новее все столбцы определены как SPARSE, но она необязательна в записях индексов, если все столбцы в записях индексов не допускают значения NULL. Заблуждение связано с тем, что происходит при добавлении нового столбца в таблицу. Распространённое заблуждение заключается в том, что если у вас есть 8 столбцов в таблице (и, следовательно, 8 битов в битовой карте NULL), и вы добавляете девятый столбец, то SQL Server должен обновить каждую запись, чтобы все битовые карты NULL содержали 9 битов. (Такое же заблуждение применяется к добавлению 17-го, 25-го, 33-го и т.д. столбцов).
Эта короткая статья вызвана вопросом, поступившим через Twitter, (перефразированный) вопрос звучит так: «Могут ли данные FILESTREAM храниться удалённо?». Это сбивало с толку многих, и ни документация BOL по FILESTREAM, ни мой технический документ по FILESTREAM (см. здесь) прямо не отвечают на этот вопрос.
Этот вопрос возникал уже несколько раз, совсем недавно — сегодня утром в письме: последующие запуски DBCC CHECKDB показывают разное количество повреждений, а иногда и вовсе их не находят — что происходит? Ещё более странно: задание обслуживания запускает DBCC CHECKDB, который показывает ошибки, но затем утром — никаких ошибок согласованности. Как?
Я обнаружил (и помог исправить) довольно много мифов и заблуждений об операциях перестроения индексов. Их достаточно, чтобы стоило написать об этом статью в блоге (да и здесь, в Орландо, слишком жарко, чтобы идти сидеть у бассейна, так что мы оба сидим здесь и пишем в блогах)…
У меня состоялось обсуждение по электронной почте с одним специалистом, который размышлял о резервных копиях журнала и минимально логируемых операциях. В частности, он был озадачен тем, как работает резервное копирование журнала, если после завершения операции контрольная точка не происходила. Ведь грязные страницы данных всё ещё остаются в памяти, верно?
Оказывается, это не так.
Мне пришёл вопрос по электронной почте: один специалист заметил, что во время выполнения просмотра кучи с использованием NOLOCK на всю продолжительность операции на куче удерживалась блокировка BULK_OPERATION. Вопрос заключался в том, зачем нужна эта блокировка, ведь просмотр с NOLOCK, казалось бы, не должно читать проблемные страницы?
Ответ заключается в том, что эта дополнительная блокировка нужна именно потому, что сканирование с NOLOCK может прочитать проблемную страницу, если в тот же момент над кучей выполняется операция массовой загрузки (bulk operation).
Мне написал бывший студент по поводу странного поведения столбца sample_ms в представлении sys.dm_io_virtual_file_stats. Предполагается, что он должен содержать количество миллисекунд, прошедших с момента запуска экземпляра SQL Server. У него есть экземпляр SQL Server 2016, работающий с августа 2019 года, и там отображается следующее:
ms_ticks из sys.dm_os_sys_info: 51915112684 (что соответствует 28 августа 2019 года)sample_ms из sys.dm_io_virtual_file_stats: 375504432 (что составляет около 4,5 дней)Очевидно, что внутренняя переменная в SQL Server несколько раз переполнилась или сбросилась, вероятно, при достижении 2^31 или 2^32, хотя начиная с SQL Server 2016 тип данных sample_ms должен быть bigint.
На прошлой неделе, преподавая курс IEPTO2, я объяснял, почему иногда поток невозможно завершить с помощью команды KILL, и подумал, что это отличная тема для статьи.
Сегодня я отвечал на вопрос под тегом #sqlhelp в Twitter и упомянул об использовании значения object ID 99, потому что SQL Server никогда не присвоит таблице идентификатор объекта 99. И я подумал, что это станет хорошей темой для небольшой записи в блоге.
На прошлой неделе я несколько раз обсуждал объём журнала транзакций, который генерируется при выполнении DROP TABLE для очень большой таблицы. В обоих случаях люди беспокоились об объёме журнала, и о том, не замедлит ли это работу.
Надеюсь, все вы знаете, что мифом является утверждение, будто операции DROP TABLE и TRUNCATE TABLE не журналируются. Если вы этого не знали, прочитайте мою запись в блоге на sqlperformance.com, где объясняется механизм отложенного удаления (deferred drop). Обе операции полностью журналируются и будут генерировать довольно много записей журнала транзакций.
Основная часть генерируемого журнала возникает из-за необходимости фиксировать в журнале освобождение экстентов и страниц внутри них. Для каждого экстента должен быть сброшен бит на соответствующей странице GAM и странице IAM, а все 8 страниц в экстенте должны быть помечены как освобождённые на соответствующей странице PFS (путем выключения бита 0x40 в каждом байте PFS). Таким образом, получается три записи журнала на каждый распределённый экстент.
Я быстро подготовил тестовый пример, чтобы показать вам записи журнала:
SELECT
[Current LSN],
[Operation],
[Context],
[Log Record Length],
[Description]
FROM fn_dblog (null, null);Current LSN Operation Context Length Description
---------------------- -------------- -------- ------- ------------------------------------------------------
.
.
000001eb:00000010:016a LOP_SET_BITS LCX_IAM 72
000001eb:00000010:016b LOP_MODIFY_ROW LCX_PFS 88 Deallocated 0001:000026f0;Deallocated 0001:000026f1;Deallocated 0001:000026f2;Deallocated 0001:000026f3;Deallocated 0001:000026f4;Deallocated 0001:000026f5;Deallocated 0001:000026f6;Deallocated 0001:000026f7
000001eb:00000010:016c LOP_SET_BITS LCX_GAM 72 Deallocated 1 extent(s) starting at page 0001:000026f0
.
.И этот набор из трёх записей журнала повторяется для каждого освобождаемого экстента.
Одно из обсуждений касалось удаления таблицы размером 20 ТБ.
20 ТБ = 20 * 1024 * 1024 *1024 * 1024 = 21 990 232 555 520 байт.
Один экстент — это 65 536 байт, поэтому 20 ТБ — это 21 990 232 555 520 / 65 536 = 335 544 320 экстентов. При 72 + 88 + 72 = 232 записываемых байта на экстент это означает, что удаление таблицы размером 20 ТБ приведёт к генерации как минимум 335 544 320 * 232 = 77 846 282 240 байт = 72,5 ГБ журнала транзакций, или примерно 0,35% от размера таблицы. Периодически генерируется небольшое количество других записей журнала, поэтому для надёжности я бы сказал, что это 0,35–0,4%.
Всё это не генерируется как одна транзакция (весь смысл отложенного удаления заключается в выполнении операции в фоновом режиме множеством небольших транзакций) и не генерируется мгновенно (поскольку фоновая задача отложенного удаления выполняется в одном потоке), поэтому эти записи журнала будут перемешаны со всем остальным, что происходит в базе данных, и отправлены на синхронную реплику AG так же, как и другие транзакции. Так что, хотя и генерируется дополнительный объём журнала, я не ожидаю, что это вызовет серьёзные проблемы.
И, конечно же, если эта таблица секционирована, вы можете выполнять операцию меньшими шагами, многократно переключая секции и удаляя их по одной.
Суть в том: выполнение DROP или TRUNCATE для таблицы требует генерации в журнале примерно 0,35–0,4% от размера таблицы.