Недавно я посетил одну компанию, разработчики которой были очень обеспокоены последствиями добавления столбцов к таблице. Они рассказали, что при добавлении нового столбца наблюдался тайм-аут кода развертывания, а размер базы данных резко увеличивался. В результате единственной операции небольшая таблица достигала размера более 50 Гбайт. В ходе операции развертывания в таблицу журнала развертывания добавлялся один столбец и записывалась одна строка. Поскольку требовалось записать единственную строку, вина за резкое увеличение размера базы данных при добавлении столбца возлагалась на SQL Server.

Меня озадачивало, что при работе с SQL Server 2012 SP2 этого не должно было происходить. Я поясню, что имеется в виду.

Рассмотрим следующее определение таблицы:

USE master;
GO

CREATE DATABASE AlterDBTest;
GO

USE AlterDBTest;
GO

CREATE SCHEMA Membership AUTHORIZATION dbo;
GO

CREATE TABLE Membership.Companies
(
    CompanyID bigint IDENTITY(1,1)
        CONSTRAINT PK_Membership_Companies PRIMARY KEY,
    TradingName nvarchar(50) NOT NULL
        CONSTRAINT UQ_Membership_Companies_Trading_
        Names_Must_Be_Unique
        UNIQUE
);
GO

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

WITH CompanyIDs
AS
(
    SELECT TOP(2000000)
           ROW_NUMBER() OVER (ORDER BY ac1.object_id)
           AS CompanyID
    FROM sys.all_columns AS ac1
    CROSS JOIN sys.all_columns AS ac2
)
INSERT Membership.Companies (TradingName)
SELECT N'Company_' + CAST(cid.CompanyID AS nvarchar(8))
FROM CompanyIDs AS cid;
GO

WITH CompanyIDs
AS
(
    SELECT TOP(2000000)
           ROW_NUMBER() OVER (ORDER BY ac1.object_id)
           AS CompanyID
    FROM sys.all_columns AS ac1
    CROSS JOIN sys.all_columns AS ac2
)
INSERT Membership.Companies (TradingName)
SELECT N'Company_' + CAST(cid.CompanyID AS nvarchar(8))
FROM CompanyIDs AS cid;
GO

До появления SQL Server 2012 добавление к таблице столбца NOT NULL и указание значения DEFAULT приводило к операции, в ходе которой каждая строка данных перезаписывается с добавлением еще одного столбца. Легко убедиться, что такого больше не происходит.

Начнем с поиска страниц, на которых расположены некоторые строки компании:

SELECT TOP(10) sys.fn_PhysLocFormatter(%%physloc%%), *
FROM Membership.Companies;
GO

На моем компьютере были получены результаты, представленные на экране 1. Как мы видим, первая строка находится в файле 1 на странице 291, слот 0. Посмотрим на содержимое страницы 291:

DBCC TRACEON (3604);
GO

DBCC PAGE (0,1,291,3);
GO

 

Результаты добавления столбца
Экран 1. Результаты добавления столбца

В форматированном выводе DBCC PAGE можно найти данные первой строки, отметив содержимое слота 0:

Slot 0 Offset 0x60 Length 37

Record Type = PRIMARY_RECORD        Record Attributes =
NULL_BITMAP VARIABLE_COLUMNS
Record Size = 37
Memory Dump @0x000000002174A060

0000000000000000:   30000c00 01000000 00000000 02000001
00250043  0................%.C
0000000000000014:   006f006d 00700061 006e0079 005f0031
00        .o.m.p.a.n.y._.1.

Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8

CompanyID = 1
Slot 0 Column 2 Offset 0x13 Length 18 Length (physical) 18

TradingName = Company_1

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (1b7fe5b8af93)

Теперь посмотрим, что происходит при добавлении столбца NOT NULL со значением DEFAULT:

ALTER TABLE Membership.Companies
ADD IsFoundationMember smallint NOT NULL
  CONSTRAINT DF_Membership_Companies_IsFoundationMember
  DEFAULT (1);
GO

Затем повторно проверим страницу, содержащую первую строку:

Record Type = PRIMARY_RECORD
Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 37
Memory Dump @0x000000002174A060

0000000000000000:   30000c00 01000000 00000000
02000001 00250043  0................%.C
0000000000000014:   006f006d 00700061 006e0079 005f0031
00        .o.m.p.a.n.y._.1.

Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8

CompanyID = 1

Slot 0 Column 2 Offset 0x13 Length 18 Length (physical) 18

TradingName = Company_1

Slot 0 Column 3 Offset 0x0 Length 2 Length (physical) 0
IsFoundationMember = 1

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (1b7fe5b8af93)

На первый взгляд может показаться, что строка изменилась, так как имеется элемент для столбца IsFoundationMember, но обратите внимание, что ее длина равна нулю. Также заметьте, что общий размер записи по-прежнему составляет 37, а KeyHashValue не изменилось. В сущности, SQL Server предполагает, что столбец присутствует.

Но в конечном итоге подтвердилось, что размер таблицы не изменился, а операция добавления столбца была выполнена почти мгновенно.

Поэтому я твердо решил, что дело не в добавлении столбца. Проблема связана с добавлением единственной строки в таблицу журнала развертывания.

Следующим шагом была проверка настроек файлов базы данных:

SELECT * FROM sys.database_files;
GO

Интересующий нас фрагмент вывода показан на экране 2.

 

Проверка настроек файлов базы данных
Экран 2. Проверка настроек файлов базы данных

Вот здесь и проблема. Когда я впервые увидел это место, мне пришлось вглядеться еще раз, чтобы убедиться, что глаза не обманывают меня. Каким-то образом программисты настроили увеличение файлов базы данных на 131 072 (см. экран 3).

 

Показатель автоувеличения размера базы данных ненормален
Экран 3. Показатель автоувеличения размера базы данных ненормален

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

Я рекомендую проверить параметры автоувеличения размера файла для вашей базы данных. Редко приходится встречаться с такими грубыми ошибками в настройках, но это один из параметров, с которыми чаще всего случаются ошибки в системах SQL Server. Файлы данных (файлы mdf и ndf) должны увеличиваться крупными фрагментами, пока активен режим Instant File Initialization (IFI). Часто требуются меньшие приращения файла журнала, так как файлы журналов перед использованием нужно обнулять. Необходимо определить, сколько вы готовы ждать, пока будут записываться данные, учитывая, что обычно запись происходит во время пользовательской транзакции.

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

Купить номер с этой статьей в PDF