Заблуждения вокруг флага трассировки TF1118

Автор: Paul Randal, Misconceptions around TF 1118

Правка 2016: Этот флаг трассировки требуется во всех версиях SQL Server включая SQL Server 2014. На каждом экземпляре SQL Server в мире он должен быть включён. В SQL Server 2016 поведение, включаемое этим флагом, стало значением по умолчанию, поэтому сам TF1118 больше не требуется и не оказывает эффекта.

Вокруг флага трассировки TF1118 существует немало путаницы и заблуждений. Он переключает выделение страниц в tempdb: вместо одиночных распределений для первых 8 страниц они сразу выделяются как экстент (8 страниц). Он применяется, чтобы при высокой нагрузке ослабить конкуренцию за карты распределения (allocation bitmap contention) в tempdb, которая бывает связана, например, с массовым созданием и удалением маленьких временных таблиц.

Есть несколько мест, которые способны вызвать путаницу, на каждом из которых я остановлюсь по порядку. Затем я докажу, что TF1118 по‑прежнему работает во всех версиях SQL Server вплоть до 2014 включительно.

TempDB переполняется? Попробуйте Resource Governor и SQL Server 2025

Автор: Brent Ozar, TempDB Filling Up? Try Resource Governor

TempDB — одна из моих неизменных головных болей.

Любой, любой, кто может выполнять запросы на вашем сервере, способен за считанные секунды устроить отказ в обслуживании, просто заполнив TempDB простейшим запросом:

DROP TABLE IF EXISTS #big_problem;
CREATE TABLE #big_problem
    (filler VARCHAR(8000));
WHILE 1 = 1
    INSERT INTO #big_problem
    SELECT REPLICATE('X', 8000)
    FROM GENERATE_SERIES(1, 100000);

Этот цикл постепенно заполнит TempDB, и когда одна из попыток в итоге завершится ошибкой, это не беда: сессия останется открытой. Она продолжит занимать всё остальное пространство, мешая другим пользователям (и системным задачам) пользоваться используемыми ресурсами.

Вам уж точно не стоит запускать такое в свой последний рабочий день, выходя за дверь: даже если ваш логин отключат, уже запущенные запросы продолжат выполняться, пока не завершатся, или, как в этом случае, пока не нанесут «добивающий удар» по вашему хранилищу, и файлы TempDB не начнут раздуваться, как брюки Sansabelt, пытающиеся справиться с шведским столом «ешь сколько сможешь». И уж точно не стоит гонять это в цикле. (Если всё же решитесь, убедитесь, что в начале скрипта удаляете таблицу, если она существует).

Если вы ещё не на SQL Server 2025, ваша основная линия обороны — заранее задать файлам данных TempDB нужный максимальный размер и отключить авторасширение. Это... слабая защита. Плохо ведущие себя запросы всё равно могут выгрызть TempDB до нуля, создавая проблемы для остальных.

Сборка мусора в FILESTREAM

Автор: Paul Randal, FILESTREAM garbage collection

В предыдущих статьях о FILESTREAM я разбирал структуру каталогов контейнера данных FILESTREAM и то, как сопоставлять каталоги с таблицами и столбцами базы данных. В этой статье я объясню, как и когда работает процесс сборки мусора FILESTREAM, поскольку, похоже, нигде это не задокументировано (даже в статье FILESTREAM Storage in SQL Server 2008, которую я писал для Microsoft — предполагаемая глубина не включала такой низкий уровень деталей). Похоже, существует немало путаницы вокруг того, как выполняются обновления данных FILESTREAM и когда удаляются старые версии файлов FILESTREAM. Я объясню, как всё устроено, а затем покажу на примере.

Новое в SQL Server 2022: Database Ledger

Автор: Deepam Ghosh, Database Ledger in SQL Server 2022

Защита данных от несанкционированного доступа сегодня — одна из главных задач. SQL Server 2022 предлагает новую функцию Database Ledger, усиливающую безопасность данных. Она защищает данные как от злоумышленников, так и от пользователей с повышенными привилегиями — администраторов баз данных, системных и облачных администраторов.

Database Ledger работает как традиционный реестр, фиксируя историю данных. Когда строка обновляется, SQL Server сохраняет её прежнее значение в таблице истории. Эта возможность использует технологию блокчейна, обеспечивая криптографическую целостность данных. SQL Server хеширует каждую транзакцию по SHA-256, формирует корневой хеш и связывает его с хешем предыдущего блока — создавая надёжную цепочку записей. В этой статье мы познакомимся с новой функцией SQL Server на практическом примере.

Поговорим ещё о циклической природе журнала транзакций

Автор: Paul Randal, Inside the Storage Engine: More on the circular nature of the log

Периодически (ладно, честно говоря, очень часто) мой внутренний список «надо бы об этом написать» разрастается до таких размеров, что меня охватывает почти непреодолимое желание. К счастью, эти порывы заканчиваются тем, что я публикую сразу несколько записей — иначе кто знает, в какие неприятности я мог бы вляпаться? :-)

Для начала сегодня — довольно глубокие внутренности о том, как работает журнал транзакций в одной конкретной ситуации. Этот вопрос всплывал несколько раз на последних курсах по внутреннему устройству и обслуживанию, которые я вёл, в модуле про журнал транзакций, так что я решил написать статью, подтверждающую, что мой ответ верен. Проще всего сформулировать вопрос с картинкой, позаимствованной из моей статьи в журнале TechNet Magazine (Ведение журнала и восстановление в SQL Server).

Ghost Cleanup возвращается

Автор: Paul Randal, Ghost cleanup redux

Ещё в первых статьях блога я написал подробное описание «фантомных» записей (ghost records) и процесса их очистки — см. статью «Подробно об очистке фантомных строк». На занятии возник вопрос, который стоит разобрать отдельно: возникают ли «фантомные» записи в кучах (heap)? Ответ — нет, не при обычной работе.

DBCC CHECKDB: проверка целостности крупных баз данных SQL Server

Автор: SQLYARD, DBCC CHECKDB: Practical Integrity Checking for Large SQL Server Databases

DBCC CHECKDB — одна из важнейших команд в наборе инструментов администратора SQL Server. Она проверяет логическую и физическую целостность всех объектов базы данных, помогая выявлять повреждения на ранней стадии — до того, как они превратятся в кошмар восстановления.

В современных версиях, таких как SQL Server 2022, проверки «чистоты данных» (data purity) по умолчанию встроены в движок, CHECKDB может выполняться параллельно, использовать снимки (снапшоты) и даже запускаться на репликах. Тем не менее запустить эту команду на крупных базах данных в рамках коротких «окон» обслуживания бывает непросто.

В этом материале разобрано, когда и как эффективно запускать CHECKDB, как использовать подходящие параметры и как грамотно планировать его выполнение как для ежедневной эксплуатации, так и для готовности к аварийному восстановлению.

Исследуем fn_dblog(): недокументированная функция SQL Server для чтения журнала транзакций

Автор: SQLYARD, Exploring fn_dblog(): The Undocumented SQL Server Function That Reads the Transaction Log

SQL Server ведёт детальную запись каждого изменения в журнале транзакций. Обычно вы взаимодействуете с ним косвенно — через резервные копии, репликацию или восстановление. Но существует скрытая, недокументированная функция fn_dblog(), которая позволяет напрямую запрашивать активный журнал транзакций.

Эта функция может помочь вам:

• Расследовать удаления и изменения данных.
• Отслеживать активность DDL и DML.
• Устранять проблемы вроде случайной потери данных.
• Анализировать поведение репликации и восстановления.

Поскольку функция недокументирована, Microsoft её официально не поддерживает и она может измениться без предупреждения. Тем не менее, для администраторов БД и специалистов по расследованию инцидентов это мощный инструмент.

Структура каталога FILESTREAM

Автор: Paul Randal, FILESTREAM directory structure

После того как я написал FILESTREAM Whitepaper для Microsoft, мне стали часто задавать вопросы о структуре контейнера данных FILESTREAM. Контейнер данных FILESTREAM — это технический термин для структуры каталогов NTFS, где хранятся все данные FILESTREAM.

Заблуждения вокруг мгновенной инициализации файлов

Автор: Paul Randal, Misconceptions around instant file initialization

Каждый раз, когда я веду курс, меня удивляет, сколько администраторов баз данных не знают о мгновенной инициализации файлов (instant file initialization). Не желая повторять публикации, уже написанные другими, кратко: эта возможность позволяет создавать или увеличивать файл «мгновенно», без заполнения нового пространства в файле нулями. Это особенно полезно в сценариях аварийного восстановления, когда перед восстановлением базы данные файлы могут понадобиться впервые — с включённой мгновенной инициализацией исключается лишняя задержка. Подробности см. в записи Кимберли «Instant Initialization – What, Why and How?».

А теперь — к причине появления это статьи: хочу развеять несколько заблуждений, которые продолжаю слышать.

Как Storage Engine находит столбцы переменной длины?

Автор: Paul Randal, Search Engine Q&A #27: How does the storage engine find variable-length columns?

Этот вопрос прозвучал на одном из моих недавних занятий: если массив смещений столбцов переменной длины в записи хранит только смещения, то как механизм хранения находит конкретный столбец переменной длины?

Сомнение возникает потому, что в самой записи нет пометки, какой столбец переменной длины где расположен — как это работает? Ответ состоит из двух частей: это и NULL‑битовая карта (null bitmap) в записи, и метаданные таблицы/индекса, хранящиеся в системных таблицах. Все столбцы переменной длины имеют фиксированную «позицию» внутри переменной части записи в том случае, если их значение не равно NULL. Давайте покажу, что это значит.

Почему буферный пул содержит так много «грязных» страниц tempdb?

Автор: Paul Randal, Why does the buffer pool contain so many dirty tempdb?

Грег задал вопрос по поводу скрипта для анализа содержимого буферного пула (передаю по смыслу): почему на загруженных промышленных системах в буферном пуле оказывается столь высокая доля «грязных» страниц tempdb?

Ответ связан с возможностью восстановления базы tempdb. Одна из причин существования контрольной точки (checkpoint) — ограничить длительность фазы «redo» при восстановлении после сбоя: это этап, когда записи журнала заново «прокручиваются» на находящихся на дисках страницах, для которых модифицированный образ страницы так и не был записан на диск после фиксации транзакций. Для этого в базах данных выполняются автоматические контрольные точки.

Однако tempdb после сбоя не восстанавливается — он заново создаётся. Это означает, что время восстановления tempdb не является проблемой, а значит, нет и необходимости в автоматических контрольных точках. Иными словами, для tempdb срабатывает лишь один триггер автоматической контрольной точки: когда его файл журнала заполняется примерно на 70%.

По этой причине на загруженных системах пользовательские базы будут проходить контрольные точки гораздо чаще, чем tempdb. А на системе, где tempdb тоже используется активно, в буферном пуле в любой момент времени будет заметно больше «грязных» страниц именно из tempdb.

Что находится в буферном пуле?

Автор: Paul Randal, Inside the Storage Engine: What’s in the buffer pool?

Это короткая статья, навеянная вопросом из письма (спасибо, Маркос!), и заодно отличный повод показать DMV, о котором я давно хотел написать. А ещё сейчас в Редмонде ужасная погода, так что на улицу не выйти — блогинг послужит стратегией уклонения от работы этим днём :-).

(В пересказе) вопрос таков: Контрольная точка — это процесс, который записывает все «грязные» страницы на диск и работает в разрезе базы данных. Но если кеш данных может хранить страницу из любой базы, как же контрольная точка понимает, какие страницы проверять на «грязность»? Она просматривает буферный пул в поисках страниц конкретной базы X и обрабатывает только их? Или кеш данных как‑то разделён по базам? Хотелось бы лучше понимать, как это устроено «под капотом».

Контроль состояния распределённой группы доступности: T-SQL и Zabbix

Автор: Pablo Echeverria, Distributed Availability Group Health: T-SQL and Zabbix

После создания распределённой группы доступности по шагам из моей статьи «SQL Server 2022: Распределённая группа доступности без кластера», как проверить, что синхронизация между дата-центрами в порядке?

В SQL Server Management Studio, если щёлкнуть правой кнопкой по распределённой группе доступности, вы заметите, что нет панели мониторинга, подтверждающей корректную синхронизацию данных между дата-центрами:

Даже если проверять панели первичного и вторичного дата-центров по отдельности, вы не увидите, есть ли проблема «между ними».

Повреждения баз данных SQL Server: обнаружение, причины и некоторые подробности о DBCC CHECKDB

Автор: MSFTPawelM, SQL Server Database Corruption: Causes, Detection, and some details behind DBCC CHECKDB

Повреждение базы данных в SQL Server бывает редко, но почти всегда имеет серьёзные последствия. Когда оно возникает, под угрозой оказываются требования ACID — фундамент транзакционной целостности — и это может привести к простоям, потере данных и операционным рискам. В этой статье рассматриваются:

• распространённые причины повреждений;
• как «под капотом» работает DBCC CHECKDB;
• рекомендации по настройке производительности при запуске CHECKDB;
• типовые сообщения об ошибках и их смысл;
• лучшие практики предотвращения и восстановления.

Повреждение страницы заголовка файла

Автор: Paul Randal, Search Engine Q&A #21: File header pages, and file header corruption

Итак, что такое страница заголовка файла? В каждом файле данных самая первая страница 8КБ (то есть страница 0 в файле) отведена для хранения всех метаданных об этом файле. Как и для загрузочной страницы, вы можете посмотреть её содержимое с помощью DBCC PAGE — команда интерпретирует все поля, — либо воспользоваться командой DBCC FILEHEADER, которая делает это ещё удобнее. Эта команда недокументирована и не поддерживается (точно так же, как DBCC DBINFO для просмотра загрузочной страницы базы), но о ней уже писали и говорили в интернете, так что её существование не секрет.

Малоиспользуемые индексы в группах доступности – Часть 2

Автор: Aaron Bertrand , Managing Underused Indexes in SQL Server Availability Groups – Part 2

В первой части статьи «Малоиспользуемые индексы в группах доступности – Часть 1» я показал, как с помощью динамического SQL собрать статистику использования индексов для заданной таблицы со всех реплик в группе доступности. Знать использование по всем нагрузкам, безусловно, лучше, чем смотреть только на первичную или на одну вторичную. Но что если я хочу принимать ещё более взвешенные решения, добавив к выводу количество строк, размер и столбцы индексов?

Boot Page и их повреждения

Автор: Paul Randal, Search Engine Q&A #20: Boot pages, and boot page corruption

Для начала: что такое загрузочная страница (boot page)? В каждой базе данных есть единственная страница, на которой хранится критически важная информация о самой базе. Это всегда страница 9 в файле 1 (первый файл в файловой группе PRIMARY). Её можно посмотреть с помощью DBCC PAGE — команда интерпретирует все поля. Есть и другая команда, DBCC DBINFO, которая тоже выдаёт всю эту информацию (фактически, код DBCC PAGE вызывает тот же внутренний код дампа). Команда недокументирована и не поддерживается, но широко известна и «задокументирована» во множестве мест в сети — и, поскольку использует тот же код, что и DBCC PAGE, по моему мнению, она столь же безопасна в использовании.

Отключается ли "torn page detection" при включении "page checksums"?

Автор: Paul Randal, Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?

Это действительно интересный вопрос, который возник на курсе Microsoft Certified Architect, который я сейчас веду: если в базе данных включена защита от «оборванных» страниц (torn-page protection), а затем включаются контрольные суммы страниц (page checksums), теряется ли при этом действующая проверка на torn-page?

Вопрос важный, потому что включение контрольных сумм страниц не приводит к мгновенному появлению checksum на всех уже выделенных страницах (контрольная сумма появится лишь после того, как страница будет прочитана в буферный пул, изменена и затем записана на диск). Если бы существующая torn-page‑защита полностью «отбрасывалась» при включении контрольных сумм, страницы оставались бы без защиты до тех пор, пока на них не появятся checksums. Я не помнил точного ответа, поэтому провёл эксперимент!

Недокументированная функция sys.fn_PhysLocCracker

Автор: Paul Randal, SQL Server 2008: New (undocumented) physical row locator function

Один из минусов того, что я больше не в команде SQL в Microsoft, — я не знаю обо всех недокументированных возможностях следующего релиза и, как и все, вынужден их выискивать :-(

Итак, ковыряясь в SSMS в 2008 CTP‑6, я заметил функцию под названием sys.fn_PhysLocCracker, о которой никогда не слышал. Выполнив sp_helptext для неё, получил такой вывод:

— Name: sys.fn_PhysLocCracker
—
— Description:
— Cracks the output of %%physloc%% virtual column
—
— Notes:
——————————————————————————-
create function sys.fn_PhysLocCracker (@physical_locator binary (8))
returns @dumploc_table table
(
 [file_id] int not null,
 [page_id] int not null,
 [slot_id] int not null
)