Недавно я посетил одну компанию, разработчики которой были очень обеспокоены последствиями добавления столбцов к таблице. Они рассказали, что при добавлении нового столбца наблюдался тайм-аут кода развертывания, а размер базы данных резко увеличивался. В результате единственной операции небольшая таблица достигала размера более 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
.jpg) |
| Экран 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.
.jpg) |
| Экран 2. Проверка настроек файлов базы данных |
Вот здесь и проблема. Когда я впервые увидел это место, мне пришлось вглядеться еще раз, чтобы убедиться, что глаза не обманывают меня. Каким-то образом программисты настроили увеличение файлов базы данных на 131 072 (см. экран 3).
.jpg) |
| Экран 3. Показатель автоувеличения размера базы данных ненормален |
Аномальное увеличение файла и разбухание базы данных вызвано добавлением единственной дополнительной строки, а не вводом дополнительного столбца в большую таблицу.
Я рекомендую проверить параметры автоувеличения размера файла для вашей базы данных. Редко приходится встречаться с такими грубыми ошибками в настройках, но это один из параметров, с которыми чаще всего случаются ошибки в системах SQL Server. Файлы данных (файлы mdf и ndf) должны увеличиваться крупными фрагментами, пока активен режим Instant File Initialization (IFI). Часто требуются меньшие приращения файла журнала, так как файлы журналов перед использованием нужно обнулять. Необходимо определить, сколько вы готовы ждать, пока будут записываться данные, учитывая, что обычно запись происходит во время пользовательской транзакции.
В целом, я думаю, автоматическое увеличение файлов должно быть включено, но никогда не должно происходить. Его следует избегать с помощью упреждающего управления размером файлов базы данных.