Вы когда-нибудь задумывались, есть ли призраки, скрывающиеся в вашей базе данных SQL Server? Хорошо, ответ – да! Но не волнуйтесь, эти призраки не пришли, чтобы преследовать вас. На самом деле, это увлекательная концепция в SQL Server, которая может помочь улучшить производительность и оптимизировать операции удаления.
Итак, что же такое эти призраки? Простыми словами, призрак – это удаленная запись в индексе таблицы. Когда запись удаляется, она не удаляется физически с страниц. Вместо этого она помечается как удаленная или “призрачная”. Эта оптимизация позволяет операциям удаления завершаться быстрее и обеспечивает более быстрый откат операций удаления.
Но почему SQL Server использует такой подход? Основная причина – производительность. Помечая записи как призрачные, SQL Server может просто “переключить флаг”, указывающий, что запись удалена, вместо того чтобы повторно вставлять удаленные записи. Это значительно ускоряет операции удаления и процессы отката.
Теперь вы можете задаться вопросом, создает ли оператор KILL призрачные записи. Ответ – нет. Оператор KILL – это просто команда с тематикой Хэллоуина и не имеет ничего общего с призрачными записями в базе данных.
Охота на призраков
Теперь, когда мы понимаем назначение призрачных записей, давайте поговорим о том, как мы можем проверить их существование. Как охотник на призраков, нам нужны правильные инструменты, чтобы поймать эти призрачные сущности.
Один из инструментов, которыми мы можем воспользоваться, – это база данных с названием “Sandbox”. Вот пример того, как мы можем настроить базу данных Sandbox:
USE master;
GO
IF DB_ID('Sandbox') IS NULL
BEGIN
EXECUTE ('CREATE DATABASE Sandbox');
ALTER DATABASE [Sandbox] SET RECOVERY SIMPLE WITH NO_WAIT;
ALTER DATABASE [Sandbox] MODIFY FILE ( NAME = N'Sandbox', SIZE = 6144000KB , FILEGROWTH = 262144KB );
ALTER DATABASE [Sandbox] MODIFY FILE ( NAME = N'Sandbox_log', SIZE = 131072KB , FILEGROWTH = 131072KB );
END
GO
USE Sandbox;
GO
-- Создание таблицы и вставка данных
После настройки базы данных Sandbox мы можем создать таблицу с названием “Halloween.Ghosts” и заполнить ее некоторыми данными. Эта таблица будет служить нам ловушкой для пойманных призраков:
USE Sandbox;
GO
IF SCHEMA_ID('Halloween') IS NULL EXECUTE ('CREATE SCHEMA Halloween');
GO
IF OBJECT_ID('Halloween.Ghosts','U') IS NOT NULL
BEGIN
DROP TABLE Halloween.Ghosts
END
GO
-- Создание таблицы Halloween.Ghosts и вставка данных
После установки ловушки мы можем попытаться поймать несколько призраков, удалив записи. Чтобы увидеть призрачные записи, нам нужно включить следующий флаг трассировки: TF 661. Это можно сделать с помощью следующего оператора:
DBCC TRACEON (661, -1);
GO
-- Удаление записей для создания призрачных записей
После удаления некоторых записей мы можем проверить статистику индекса, чтобы узнать, поймали ли мы каких-либо призраков:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DB_NAME(database_id) AS DBName, schema_name(so.schema_id) + '.' + so.name as ObjName, record_count, index_type_desc, ghost_record_count, version_ghost_record_count
FROM sys.dm_db_index_physical_stats(db_id('sandbox'), OBJECT_ID('Halloween.Ghosts'), NULL, NULL , 'DETAILED') ps
INNER JOIN sys.objects so
ON ps.object_id = so.object_id
WHERE index_level = 0;
Если запрос возвращает ненулевое значение в столбце “ghost_record_count”, поздравляю! Вы успешно поймали несколько призраков в вашей базе данных.
Но как мы можем увидеть эти призраки более подробно? Один из способов – использовать неопубликованную функцию “fn_dblog”, чтобы получить информацию о страницах с призрачными записями:
SELECT Operation, Context, [Transaction ID], [Page ID] AS [File:PageIDHex], AllocUnitId, [Slot ID], PartitionId, CONVERT(INT, CONVERT(VARBINARY,'0x' + RIGHT([Page ID], 8),1)) AS PageID
FROM fn_dblog(null,null)
WHERE CONTEXT = 'LCX_MARK_AS_GHOST';
Этот запрос покажет вам, какие страницы в базе данных содержат призрачные записи. Затем вы можете использовать неопубликованную процедуру “DBCC PAGE”, чтобы изучить содержимое этих страниц и поймать тех призраков:
DBCC PAGE('Sandbox',1,1522936,3) WITH TABLERESULTS;
Следуя этим шагам, вы можете раскрыть скрытых призраков в вашей базе данных SQL Server и получить более глубокое понимание их работы.
Заключение
Открытие призраков в вашей базе данных SQL Server может быть захватывающим приключением. Эти призрачные записи – нечто, чего стоит бояться, а скорее увлекательная оптимизационная техника, которая улучшает производительность. Понимая, как работают призрачные записи, и используя правильные инструменты, вы можете поймать и изучить эти призрачные сущности в вашей базе данных.
Итак, отправляйтесь и исследуйте склепы вашей базы данных, и не забудьте поделиться своими открытиями с другими энтузиастами SQL Server. Счастливой охоты на призраков!