Я обнаружил (и помог исправить) довольно много мифов и заблуждений об операциях перестроения индексов. Их достаточно, чтобы стоило написать об этом статью в блоге (да и здесь, в Орландо, слишком жарко, чтобы идти сидеть у бассейна, так что мы оба сидим здесь и пишем в блогах)…
У меня состоялось обсуждение по электронной почте с одним специалистом, который размышлял о резервных копиях журнала и минимально логируемых операциях. В частности, он был озадачен тем, как работает резервное копирование журнала, если после завершения операции контрольная точка не происходила. Ведь грязные страницы данных всё ещё остаются в памяти, верно?
Оказывается, это не так.
Мне пришёл вопрос по электронной почте: один специалист заметил, что во время выполнения просмотра кучи с использованием 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% от размера таблицы.
На прошлой неделе мне по электронной почте задали вопрос о странных сообщениях в журнале ошибок, похожих на эти:
[DbId:12]. Removing xdes id: 0000:8112c9d0 from aborted xdes map.
[DbId:12]. Removing xdes id: 0000:8112b45e from aborted xdes map.
[DbId:12]. Removing xdes id: 0000:8112b403 from aborted xdes map.Прежде всего, XDES — это структура данных (и класс C++) в механизме хранения (Storage Engine), представляющий транзакцию; это сокращение от «transaction descriptor» (дескриптор транзакции). Идентификатор XDES — это тот же самый идентификатор, который вы видите в столбце Transaction ID в выводе функции fn_dblog для изучения содержимого журнала транзакций (см. примеры здесь).
Эти сообщения безвредны и связаны с внутренней работой функции Ускоренного восстановления баз данных (Accelerated Database Recovery, ADR), которая позволяет мгновенно откатывать транзакции с помощью умного механизма версионности.
На прошлой неделе мне задали вопрос о том, почему существует ограничение в 8060 байт на размер строки и почему страницы файлов данных иногда показывают более 8060 байт свободного места при просмотре с помощью DBCC PAGE.
Прежде чем объяснять, позвольте мне прояснить, что ограничение в 8060 байт касается только той части записи, которая хранится на странице данных или индекса в единице распределения «внутристроковых данных» (in-row data). Запись может иметь множество столбцов LOB, которые хранятся вне строки в единице распределения «данных LOB» (например, столбцы varchar(max) или FILESTREAM), и/или не-LOB столбцов переменной длины, которые были вытеснены из строки для хранения в единице распределения «переполнения строки» (например, столбцы char(1-8000), nchar(1-4000) или sqlvariant). Таким образом, фактический размер строки практически не ограничен.
Я мог бы перефразировать исходный вопрос так: учитывая, что размер страницы составляет 8192 байта, и только 96 байт используется для заголовка страницы, почему размер внутристроковой записи ограничен 8060 байтами? Куда деваются остальные 36 байт доступного пространства?
(«Любопытный случай…» раньше был частью нашей двухнедельной рассылки, но мы решили сделать его обычной записью в блоге, чтобы он мог выходить иногда чаще. Он рассказывает о чём-то интересном, с чем столкнулся один из нас при работе с клиентом, проведении тестов или в случайном вопросе от сообщества.)
В одном списке рассылки, на который я подписан, сегодня кто-то поинтересовался возможностью реализации восстановления таблицы в другую базу данных, в которой уже есть свои данные. Это интересное умственное упражнение, поэтому я написал ответ для списка, а потом подумал, что из него получится хорошая запись в блоге.
Восстановление таблицы в другую базу данных, где уже есть данные, на самом деле гораздо сложнее, чем может показаться.
Ранее мне прислали интересный вопрос о том, почему человек видит множество VLF (виртуальных файлов журнала) в журнале со статусом = 2 (что означает «активный») после очистки (также известной как «усечение») журнала, при том что log_reuse_wait_desc показывает NOTHING.
Я немного покопался и всё, что смог найти, — это старая запись в блоге от 2013 года, которая демонстрирует это поведение и упоминает, что оно происходит при зеркальном отображении и в группах доступности. Я не слышал об этом поведении раньше, но предположил причину и подтвердил её с командой SQL Server.
По крайней мере с тех пор, как я начал работать в команде SQL Server (сразу после выхода версии 7.0) и до недавнего времени, существовало всего два кода статуса VLF (виртуального файла журнала):
Несколько недель назад я узнал о новом коде статуса VLF, который был добавлен ещё в SQL Server 2012, но не был широко известен до недавнего времени (по крайней мере, я никогда с ним не сталкивался в реальных условиях). Я провёл несколько обсуждений с другом из Microsoft (Шоном Галларди, PFE и MCM из Тампа), которому удалось покопаться в коде, чтобы выяснить, когда он используется.
Некоторое время назад я участвовал в переписке по электронной почте, где люди обсуждали некоторое «странное» поведение SQL Server. Проблема возникала в SQL Server 2016 при использовании уровня изоляции по умолчанию — read committed. Сценарий был следующим:
SELECT * из таблицы«Странное» поведение заключается в том, что когда выборка «Пакета 2» завершается после того, как была заблокирована транзакцией «Пакета 1», она возвращает не все 1000 строк (даже несмотря на то, что «Пакет 1» завершился). Более того, в зависимости от того, когда была запущена выборка «Пакета 2» в течение 10 секунд выполнения «Пакета 1», «Пакет 2» возвращает разное количество строк. Такое поведение также сообщалось и в более ранних версиях SQL Server. Это легко воспроизвести в SQL Server 2016/2017 и можно воспроизвести во всех более ранних версиях с одним изменением конфигурации (подробнее чуть позже).
Этот документ помогает диагностировать или решать проблемы, связанные с производительностью репликации.
У SQLskills есть инициатива вести блог на базовые темы, которую мы называем SQL101. Мы все пишем о вещах, которые часто делаются неправильно, технологиях, используемых неверно, или случаях, когда существует множество заблуждений, ведущих к серьёзным проблемам.
Вот вопрос, который мне часто задают (перефразирую):
«У меня есть большая таблица-куча, в которой пространство не освобождается, когда я удаляю большое количество записей, но когда я сжимаю базу данных, размер кучи уменьшается. Можете объяснить?»
Как писала в блоге Кимберли, SQLskills начинает инициативу — ведение блога на базовые темы, которую мы называем SQL101. Мы все будем писать о вещах, которые часто делаются неправильно, о технологиях, используемых неверно, или о случаях, когда существует множество заблуждений, ведущих к серьёзным проблемам.
Это интересное заблуждение, о котором меня спрашивали на прошлой неделе: (перефразируя) «Наверняка операция SELECT не может привести к изменению базы данных, потому что она просто читает данные, а не изменяет их каким-либо образом, верно?».
Что ж, нет. На самом деле существует довольно много побочных эффектов у запросов, которые только читают данные и никогда не выполняют изменения данных (не считая, конечно, SELECT ... INTO). Вот четыре, которые сразу приходят на ум…
Ещё в марте 2025 года, когда Microsoft впервые анонсировала поддержку REGEX в SQL Server 2025 и Azure SQL DB, я провёл быстрое тестирование, и производительность была ужасающей. Она была плохой в трёх разных аспектах:
После комментария Эрланда Соммарског в этом месяце я вернулся и снова запустил тесты с релизной версией SQL Server 2025. Отличные новости! Microsoft исправила 1 из проблем, и... ну, одна из них немного хитрая. Для демонстрации я собираюсь использовать большую базу данных Stack Overflow за апрель 2024 года, чтобы создать наихудший сценарий, затем начать с индекса на небольшой таблице Users и запросить её через regex, как мы делали в статье от марта 2025 года.
На занятии возник интересный вопрос: как хранится значение столбца по умолчанию и что происходит, если некоторые строки уже существуют при добавлении столбца, а затем значение по умолчанию меняется?
В Twitter состоялось краткое обсуждение того, почему страницы PFS (повреждённый заголовок, а не отдельные байты PFS) нельзя восстановить (поводом стал вопрос #sqlhelp о том, почему для них невозможно восстановление отдельной страницы, в отличие от других карт распределения базы данных). Для справки: их также нельзя исправить с помощью автоматического восстановления страниц с зеркала или в группе доступности (AG).
Страницы PFS встречаются каждые 8088 страниц в каждом файле данных и хранят байт информации о себе и о следующих 8087 страницах. Самая важная информация, которую они хранят, — это то, выделена ли страница (используется) или нет. Подробнее о страницах PFS и других картах распределения базы данных можно прочитать в этой записи блога.
Итак, почему их не может восстановить DBCC CHECKDB, когда все остальные карты распределения базы данных могут?
Мне прислали вопрос по электронной почте о причине возникновения ожиданий LOGMGR_RESERVE_APPEND, и в следующем выпуске рассылки Insider я дал краткое объяснение. Это очень необычный тип ожидания, чтобы видеть его в качестве основного на сервере, и, по сути, его вообще редко встречают.