Отключение очистки «фантомных» записей ради прироста производительности

Автор: Paul Randal, Turning off the ghost cleanup task for a performance gain

Я уже пару раз писал о фантомных (ghost) записях и задаче их очистки (по сути, это единственные разъяснения, о которых мне известно), но сегодня один из MVP коллег спрашивал меня о настройке для отключения этой задачи — не смог её найти для своего клиента.

Мои прежние записи на эту тему:

• Подробно об очистке фантомных строк
• Ghost Cleanup возвращается

Там объяснено, что такое фантомные записи и как работает процесс их очистки.

На больших системах задача очистки может не поспевать за остальной нагрузкой — без шансов «догнать». Это однопоточная задача: представьте 16‑ядерный сервер с массой операций удаления, и один поток, который на одном CPU каждые 5 секунд пытается убрать фантомные записи, образовавшиеся от удалений, выполненных всеми прочими CPU. Очевидно, что очистка будет отставать.

Проблема здесь в том, что задача очистки всё равно будет «всплывать» каждые 5 секунд (каждые 10 секунд в 2008) и начинать удалять фантомы, потенциально ухудшая производительность: удерживая страницы в буферном пуле, порождая журнальные записи и выполняя физические операции ввода‑вывода. Это также один из фоновых процессов, который способен инициировать IO на, казалось бы, полностью бездействующей системе.

Есть способ отключить задачу очистки — флагом трассировки 661, как описано в KB 920093. Но будьте крайне осторожны! Если вы её отключите, место, занятое удалёнными записями, НЕ будет освобождено для повторного использования SQL Server, пока вы явно не сделаете что‑то, чтобы его вернуть, например перестройку индекса.

Иногда предлагают «заставить» очистку пройтись по всему, выполнив скан таблицы или индекса (таким образом поставив все удалённые записи в очередь для очистки). Это альтернативный путь, но он всё равно использует ту же фоновую задачу, а на очень загруженной системе с очень большим числом удалений (внимание, обобщение! :-)) зачастую куда эффективнее убрать «удалённые‑но‑ещё‑не‑возвращённые» записи с помощью reorganize или rebuild индекса.

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

Логирование блокировок и быстрое восстановление

Автор: Paul Randal, Lock logging and fast recovery

Одна из тем, о которых я люблю рассказывать, — журнал транзакций и механизмы журналирования/восстановления. Я читал доклад на эту тему и на PASS, и на SQL Connections, и в обоих случаях обещал опубликовать серию статей о глубинных внутренних аспектах операций журналирования. Перед вами первая из них. Другие мои статьи, посвящённые некоторым деталям журналирования:

• How do checkpoints work and what gets logged (там также объясняется, как стартует восстановление после сбоя: используя загрузочную страницу и самую свежую журнальную запись контрольной точки)
• Finding out who dropped a table using the transaction log
• How expensive are page splits in terms of transaction log?
• Поговорим ещё о циклической природе журнала транзакций
• Ghost Cleanup возвращается
• Подробно об очистке фантомных строк

А теперь — к делу.

В SQL Server в редакции Enterprise существует возможность, называемая быстрое восстановление (fast recovery). Она позволяет базе данных стать доступной для работы сразу после завершения первой фазы восстановления (REDO), ещё до окончания второй (обычно более долгой) фазы (UNDO). Если не понимаете, о чём речь, загляните в мою статью в TechNet Magazine — Ведение журнала и восстановление в SQL Server. Но как SQL Server добивается этого?

Что делает контрольная точка для tempdb?

Автор: Paul Randal, What does checkpoint do for tempdb?

В предыдущей статье я подробно разобрал, как работают контрольные точки и что именно при этом происходит (см. «Как работают контрольные точки и что записывается в журнал транзакций»). А ещё ранее я писал, почему в буферном пуле на загруженной системе может казаться, что там непропорционально много «грязных» страниц tempdb; сейчас хочу чуть подробнее прояснить, почему так и как контрольные точки работают для tempdb. Чтобы посмотреть содержимое буферного пула, см. «Что находится в буферном пуле?».

Контрольная точка для tempdb выполняется только тогда, когда файл журнала tempdb заполнен на 70% — это делается, чтобы по возможности не допускать роста журнала tempdb (заметьте, что долго идущая транзакция по‑прежнему может, по сути, «заложить» журнал и не давать его очистить, как и в пользовательской базе).

Accelerated Database Recovery для tempdb в SQL Server 2025

Автор: Andrew Pruski, Accelerated Database Recovery for tempdb in SQL Server 2025

Ускоренное восстановление базы данных (Accelerated Database Recovery, ADR) появилось в SQL Server 2019 и обеспечивает быстрое восстановление, мгновенный откат транзакций и агрессивное усечение журнала. Полный обзор того, как ADR этого добивается, приведён в документации.

Теперь в SQL Server 2025 можно включить ADR для tempdb! Быстрое восстановление к tempdb не применимо, но вот мгновенный откат транзакций и агрессивное усечение журнала — на все 100%. Посмотрим, что происходит после включения ADR для tempdb в SQL Server 2025.

Как работают контрольные точки и что записывается в журнал транзакций

Автор: Paul Randal, How do checkpoints work and what gets logged

Давно собирался написать про это; к тому же недавно наткнулся в сети на несколько не вполне точных объяснений работы контрольных точек, поэтому хочу коротко описать, как они устроены с точки зрения журнальных записей.

SQL Server и большие страницы: подробное объяснение

Автор: Bob Ward, SQL Server and Large Pages Explained

В на Europe PASS Summit я читал доклад о диагностике памяти. В нём упомянул, что SQL Server будет использовать большие страницы (Large Pages), если включён trace flag 834. На конференции Кристиан Болтон, хорошо известный MVP из Великобритании, заметил, что видел в ERRORLOG сообщения о «large pages», хотя trace flag у него был выключен. Тогда я ответил, что не представляю, как такое возможно. Что ж, Кристиан, вы ничего не выдумали.

Вскоре тема всплыла снова, когда я помогал коллегам из CSS разбираться с тем же вопросом. Самое время углубиться и разобраться, что же там на самом деле происходит.

TempDB: призрак Version Store

Автор: SWYS SQL, TEMPDB: The Ghost of VersionStore

Ближе к 30 октября некоторые серверы баз данных начинают вести себя странно, словно хотят поддержать атмосферу Хэллоуина. На этой неделе один из моих серверов вёл себя именно так. Последние месяцы он работал без проблем, но сегодня его TempDB начала стремительно заполняться. Разумеется, это потребовало расследования — и я обнаружил, что причиной неожиданного роста TempDB стал Version Store. Самое странное было в том, что одна база данных занимала почти всё пространство TempDB в Version Store, хотя в ней не было открытых транзакций. Действительно жуткая история!

Хранилище версий не очищается, если хоть в одной базе есть открытые транзакции

Автор: Brent Ozar, The Version Store Won’t Clear If ANY Database Has Open Transactions

Это особенно болезненно для тех, кто обслуживает несколько баз данных на одном сервере. Достаточно одному приложению вести себя плохо и оставлять транзакции открытыми — и внезапно остальные базы начинают расширять TempDB за пределы доступного места. Причём та транзакция в злосчастном приложении может даже ничего не менять, и раньше сама по себе проблем не вызывала, — но как только она делит TempDB с другими приложениями, начинаются каскадные эффекты.

CHECKDB со всех сторон: как понять, будут ли выполняться проверки чистоты данных?

Автор: Paul Randal, CHECKDB From Every Angle: How to tell if data purity checks will be run?

Недавно возник вопрос: если я обновил базу данных с SQL 2000 или более ранней версии, как понять, будут ли выполняться проверки чистоты данных (data purity), или нет?

Как известно, начиная с SQL Server 2005, DBCC CHECKDB включает проверки «чистоты данных». Они ищут значения столбцов, выходящие за допустимый диапазон для их типов данных. Для баз, созданных в SQL Server 2005 и новее, эти проверки всегда выполняются DBCC CHECKDB и не могут быть отключены. Для баз, созданных в более ранних версиях, всё чуть сложнее.

Переадресующие и переадресованные записи и размер обратного указателя

Автор: Paul Randal, Forwarding and forwarded records, and the back-pointer size

Этот вопрос всплывает время от времени, в последний раз — сегодня утром во время частного курса (сейчас читает Кимберли): каков размер обратного указателя у переадресованной записи? (И да, я давно не писал ничего по‑настоящему «технического»…)

В куче (heap) возможны переадресующие (forwarding) и переадресованные (forwarded) записи. Это случается, когда запись в куче разрастается так, что уже не помещается на текущей странице. В таком случае запись переносится на новую страницу, а в исходном месте остаётся маленькая переадресующая запись. Переадресующая запись указывает на новое местоположение записи, которая называется переадресованной. Это сделано как оптимизация производительности, чтобы не приходилось изменять все некластерные индексы у кучи из‑за нового адреса записи в этой куче.

Насколько дороги расщепление страниц с точки зрения журнала транзакций?

Автор: Paul Randal, How expensive are page splits in terms of transaction log?

Расщепление страниц (page splits) всегда считаются дорогими, но насколько же на самом деле? В этой статье я покажу пример, насколько больше становится журнал транзакций, когда страница в индексе вынужденно расщепляются. Я буду использовать DMV sys.dm_tran_database_transactions, чтобы это продемонстрировать. Список столбцов и краткие пояснения к каждому столбцу можно найти в Books Online — я вспомнил о её существовании благодаря кому‑то в Twitter (увы, не помню, кто это был, и не смог найти упоминание в поиске).

Заблуждения вокруг размера null bitmap

Автор: Paul Randal, Misconceptions around null bitmap size

В этой статье я хочу развенчать распространённый миф о том, что null bitmap содержит биты только для допускающих NULL столбцов. Это не так — там по одному биту на каждый столбец в определении таблицы, при условии, что в таблице есть хотя бы один допускающий NULL столбец. «Неиспользуемые» биты всегда равны 1, то есть означают «NULL».

Заблуждения вокруг флага трассировки 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, как использовать подходящие параметры и как грамотно планировать его выполнение как для ежедневной эксплуатации, так и для готовности к аварийному восстановлению.

Способы объединения значений в символьные строки + что привнёс SQL Server 2025

Автор: Louis Davidson, Concatenating values as character data including in SQL Server 2025

Недавно я искал тему для новой заметки и перечитал «Новые возможности SQL Server 2025 (предварительная версия)». Нашёл вот это:

|| (String concatenation) Concatenate expressions with expression || expression.

Мой интерес, мягко говоря, проснулся. Использование + для сцепления строк всегда имело свои проблемы, и, как бы мне ни нравилась функция CONCAT, она немного тяжеловесна по сравнению с полноценным оператором.

Существует также функция CONCAT_WS, которая добавляет разделитель в результат, но я не буду о ней говорить в этой статье.

Я пойду к новой возможности «обходными путями», но если хотите пропустить, просто переходите к разделу, где она разобрана.