Восстановление данных: странные сбои SELECT при повреждении

Автор: Paul Randal, Data recovery: investigating weird SELECT failures around corruption

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

В двух словах, проблема заключалась в таком повреждении, что простой запрос SELECT * завершался ошибкой, а запрос SELECT * с предложением ORDER BY работал.

Давайте разбираться!

Создание сценария

Сначала я создам конкретное повреждение. Я создам простую таблицу с кластерным индексом и подберу размер строк так, чтобы на странице помещалась только одна.

CREATE DATABASE [Company];
GO

USE [Company];
GO

CREATE TABLE [test] (
    [c1] INT IDENTITY,
    [c2] UNIQUEIDENTIFIER DEFAULT NEWID (),
    [c3] CHAR (4100) DEFAULT 'a');
GO
CREATE CLUSTERED INDEX [test_cl] ON [test] ([c1], [c2]);
GO

SET NOCOUNT ON;
GO

INSERT INTO [test] DEFAULT VALUES;
GO 10000

Теперь я удалю одну из строк, создав страницу с единственной фантомной записью, которую можно увидеть с помощью DBCC PAGE на первой PFS-странице базы данных.

DELETE FROM [test] WHERE [c1] = 150;
GO

DBCC TRACEON (3604);
DBCC PAGE ([Company], 1, 1, 3);
GO

<вывод сокращён для краткости>
(1:289)      - (1:295)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:296)      - (1:437)      =     ALLOCATED   0_PCT_FULL
(1:438)      -              =     ALLOCATED   0_PCT_FULL Has Ghost
(1:439)      - (1:8087)     =     ALLOCATED   0_PCT_FULL

Итак, страница (1:438) — это та, на которой находилась строка со значением ключа 150. Она всё ещё выделена и связана со структурой кластерного индекса, поэтому я заставлю код методов доступа «увидеть» её, выполнив сканирование, которое её включит, и это поставит страницу в очередь на очистку задачей очистки фантомных записей.

SELECT COUNT (*) FROM [test] WHERE [c1] < 200;
GO

И теперь, если я подожду 10 секунд и снова посмотрю на PFS-страницу, я увижу, что она была очищена и освобождена — она больше не является частью кластерного индекса. (Вы заметите, что PFS-байт по-прежнему говорит, что на странице есть фантомная запись; это потому, что когда страница освобождается, изменяются только биты статуса выделения в PFS.)

DBCC PAGE ([Company], 1, 1, 3);
GO

<вывод сокращён для краткости>
(1:289)      - (1:295)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:296)      - (1:437)      =     ALLOCATED   0_PCT_FULL
(1:438)      -              = NOT ALLOCATED   0_PCT_FULL Has Ghost
(1:439)      - (1:8087)     =     ALLOCATED   0_PCT_FULL

На этом этапе повреждений ещё нет, так что давайте создадим проблемы.

Создание повреждения

Для начала я обнулю страницу (1:438) с помощью DBCC WRITEPAGE:

ALTER DATABASE [Company] SET SINGLE_USER;
GO

DECLARE @offset INT;
SELECT @offset = 0;

WHILE (@offset < 8185)
BEGIN
    DBCC WRITEPAGE (N'Company', 1, 438, @offset, 8, 0x0000000000000000, 1);
    SELECT @offset = @offset + 8;
END;
GO

ALTER DATABASE [Company] SET MULTI_USER;
GO

И здесь повреждения всё ещё нет, потому что страница (1:438) является освобождённой страницей.

Теперь я создам повреждение, принудительно выделив её снова. Для этого мне нужно найти смещение PFS-байта для страницы (1:438) с помощью шестнадцатеричного дампа PFS-страницы и поиска страницы, PFS-биты которой соответствуют выводу PFS для страницы (1:438) выше. На странице установлен только бит «Has Ghost», который равен 0x08.

DBCC PAGE ([Company], 1, 1, 2);
GO

<сокращено>
Memory Dump @0x00000000185EA000

00000000185EA000:   010b0000 00000000 00000000 00000000 00000000  ....................
00000000185EA014:   00000100 63000000 0200fc1f 01000000 01000000  ....c.....ü.........
00000000185EA028:   12010000 fd000000 01000000 00000000 00000000  ....ý...............
00000000185EA03C:   7944876a 01000000 00000000 00000000 00000000  yD‡j................
00000000185EA050:   00000000 00000000 00000000 00000000 00009c1f  ..................œ.
00000000185EA064:   44444444 00004444 60647060 74706070 60607060  DDDD..DD`dp`tp`p``p`
00000000185EA078:   60707060 40404040 40404040 61706070 60606070  `pp`@@@@@@@@ap`p```p
00000000185EA08C:   60706060 60706060 60706060 60606070 40404040  `p```p```p`````p@@@@
00000000185EA0A0:   40404040 40404040 40404030 60706060 70607060  @@@@@@@@@@@0`p``p`p`
00000000185EA0B4:   70706070 70606060 70607060 70607060 70607060  pp`pp```p`p`p`p`p`p`
00000000185EA0C8:   70607060 70607060 70606060 60607060 60706070  p`p`p`p`p`````p``p`p
00000000185EA0DC:   60706070 60706070 60707070 60607060 60706060  `p`p`p`p`ppp``p``p``
00000000185EA0F0:   60706060 70606060 60606060 70607060 60706060  `p``p```````p`p``p``
00000000185EA104:   60606060 60606060 40000000 00000000 60606060  ````````@.......````
00000000185EA118:   60606060 60606060 60606060 60606060 60606060  ````````````````````
00000000185EA12C:   40404040 40404040 40404040 40404040 40404040  @@@@@@@@@@@@@@@@@@@@
00000000185EA140:   40404040 40404040 40404040 40404040 40404040  @@@@@@@@@@@@@@@@@@@@
00000000185EA154:   60606060 64646260 40404040 40404040 40404040  ````ddb`@@@@@@@@@@@@
00000000185EA168:   40404040 40400000 00000000 40400000 00000000  @@@@@@......@@......
00000000185EA17C:   40404040 00000000 70606060 60606060 40404040  @@@@....p```````@@@@
00000000185EA190:   40404040 40404040 40404040 40404040 40404040  @@@@@@@@@@@@@@@@@@@@
00000000185EA1A4:   40404040 40404040 40404040 40404040 40404040  @@@@@@@@@@@@@@@@@@@@
00000000185EA1B8:   40404040 40404040 40404040 40404040 40404040  @@@@@@@@@@@@@@@@@@@@
00000000185EA1CC:   40404040 40404040 40404040 40404040 40404040  @@@@@@@@@@@@@@@@@@@@
00000000185EA1E0:   40404040 40404040 40404040 40404040 40404040  @@@@@@@@@@@@@@@@@@@@
00000000185EA1F4:   40404040 40404040 40404040 40404040 40404040  @@@@@@@@@@@@@@@@@@@@
00000000185EA208:   40404040 40404040 40404040 40404040 40400840  @@@@@@@@@@@@@@@@@@.@
00000000185EA21C:   40404040 40404040 40404040 40404040 40404040  @@@@@@@@@@@@@@@@@@@@
00000000185EA230:   40404040 40404040 40404040 40404040 40404040  @@@@@@@@@@@@@@@@@@@@
<сокращено>

Можете найти байт 0x08? Он находится по смещению 0x21a на странице.

Я могу заставить страницу (1:438) снова стать выделенной, установив этот байт по смещению в PFS-странице в значение 0x40, снова с помощью DBCC WRITEPAGE.

DBCC WRITEPAGE (N'Company', 1, 1, 538, 1, 0x40);
GO

И теперь, если я запущу DBCC CHECKDB, я увижу повреждение:

DBCC CHECKDB (N'Company') WITH NO_INFOMSGS;
GO

Msg 8909, Level 16, State 1, Line 68
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:438) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 8928, Level 16, State 1, Line 68
Object ID 245575913, index ID 1, partition ID 72057594040614912, alloc unit ID 72057594045857792 (type In-row data): Page (1:438) could not be processed.  See other errors for details.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'test' (object ID 245575913).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'Company'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Company).

И последний шаг — сделать базу данных доступной только для чтения:

ALTER DATABASE [Company] SET READ_ONLY;
GO

Изучение повреждения

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

Простой запрос SELECT * не работал, например так:

SELECT * FROM [test];
GO

Запрос начнёт возвращать результаты, а затем завершится ошибкой:

Msg 824, Level 24, State 2, Line 74
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:438; actual 0:0). It occurred during a read of page (1:438) in database ID 10 at offset 0x0000000036c000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Company.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Но если я выполню SELECT * с упорядочиванием, он работает нормально:

SELECT * FROM [test] ORDER BY [c1];
GO

Что происходит?

Объяснение

Объяснение связано с тем, как работают два типа просмотра для операторов SELECT.

Первый просмотр выполняет так называемый просмотр в порядке распределения. Это когда методы доступа решают не использовать структуру индекса для возврата записей. Просмотр в порядке распределения имеет три требования:

1. План запроса должен допускать неупорядоченный просмотр индекса
2. Индекс должен быть больше 64 страниц
3. Данные в индексе должны быть гарантированно неизменными

Просмотр в порядке распределения использует IAM-страницы для загрузки объекта сканирования, а затем быстро проходит по экстентам в порядке их распределения, используя PFS-страницы для определения того, какие страницы в экстентах выделены и должны быть прочитаны и обработаны просмотром.

Второй просмотр выполняет обычный упорядоченный просмотр, который спускается к левому краю листового уровня индекса, а затем следует по связям листовых страниц для прохода по индексу.

Так откуда же возникает повреждение?

Страница, которую я повредил, а затем принудительно выделил снова, не связана с листовым уровнем индекса, поэтому упорядоченный просмотр не пытается её читать. Однако, поскольку она распределена, просмотр в порядке распределения считает её действительной частью экстента, который её содержит, и пытается прочитать, что приводит к ошибке 823.

Ключом к созданию этого сценария является то, что база данных установлена в режим «только чтение», что удовлетворяет третьему требованию для просмотра в порядке распределения. Если вы вернёте базу данных в режим «чтение-запись», а затем запустите первый оператор SELECT, он будет работать идеально, потому что требования для просмотра в порядке распределения больше не выполняются.

Вы можете подробнее прочитать о сканировании в порядке выделения в этой замечательной записи Пола Уайта.

Итог

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