Почему ALTER COLUMN - NOT NULL вызывает массовый рост файла журнала?

У меня есть таблица с 64-миллиметровыми строками, содержащая 4,3 ГБ на диске для своих данных.

Каждая строка содержит около 30 байт целочисленных столбцов плюс столбец NVARCHAR(255) для текста.

Я добавил столбец NULLABLE с типом данных Datetimeoffset(0).

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

Как только не было записей NULL, я запустил эту команду, чтобы сделать мое новое поле обязательным:

ALTER TABLE tblCheckResult 
ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL

Результатом стал ОГРОМНЫЙ рост размера журнала транзакций - от 6 ГБ до более 36 ГБ до тех пор, пока он не выйдет из космоса!

Кто-нибудь знает, что делает SQL Server 2008 R2 для этой простой команды, чтобы добиться такого огромного роста?

55 голосов | спросил PapillonUK 29 42012vEurope/Moscow11bEurope/MoscowThu, 29 Nov 2012 20:50:41 +0400 2012, 20:50:41

5 ответов


47

Когда вы меняете столбец на NOT NULL, SQL Server должен касаться каждой одной страницы, даже если нет значений NULL. В зависимости от вашего коэффициента заполнения это может привести к большому разрыву страниц. Разумеется, каждая страница, которая затрагивается, должна быть зарегистрирована, и я подозреваю, что из-за разрывов могут потребоваться два изменения для многих страниц. Так как все сделано за один проход, журнал должен учитывать все изменения, так что, если вы нажмете отменить, он точно знает, что отменить.


Пример. Простая таблица:

DROP TABLE dbo.floob;
GO

CREATE TABLE dbo.floob
(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, 
  bar INT NULL
);

INSERT dbo.floob(bar) SELECT NULL UNION ALL SELECT 4 UNION ALL SELECT NULL;

ALTER TABLE dbo.floob ADD CONSTRAINT df DEFAULT(0) FOR bar

Теперь давайте посмотрим на детали страницы. Сначала нам нужно выяснить, на какой странице и с DB_ID мы имеем дело. В моем случае я создал базу данных под названием foo, а DB_ID - 5.

DBCC TRACEON(3604, -1);
DBCC IND('foo', 'dbo.floob', 1);
SELECT DB_ID();

Результат показал, что меня интересовала страница 159 (единственная строка в DBCC IND) с PageType = 1).

Теперь давайте посмотрим некоторые детали страницы, когда мы переходим к сценарию OP.

DBCC PAGE(5, 1, 159, 3);

введите описание изображения здесь>> </p>

<pre><code>UPDATE dbo.floob SET bar = 0 WHERE bar IS NULL;
СТРАНИЦА DBCC (5, 1, 159, 3);
</code></pre>

<p> <img src = команды разработчиков хранилища , я думаю , Я верю, что SQL Server 2012 обрабатывает некоторые из этих сценариев намного лучше, FWIW - но мне еще предстоит провести исчерпывающее тестирование.

ответил Aaron Bertrand 29 42012vEurope/Moscow11bEurope/MoscowThu, 29 Nov 2012 21:17:39 +0400 2012, 21:17:39
29

При выполнении команды

ALTER COLUMN ... NOT NULL

Кажется, что это реализовано как операция добавления столбца, обновления, удаления столбца.

  • Новая строка вставляется в sys.sysrscols для представления нового столбца. Установлен бит status для 128, указывающий, что столбец не позволяет NULL s
  • В каждой строке таблицы выполняется обновление, устанавливающее новое значение columnn в значение старого значения colum. Если строки «до» и «после» строки точно совпадают, это не приводит к записи какой-либо вещи в журнал транзакций, иначе будет зарегистрировано обновление.
  • Исходный столбец помечен как отброшенный (это изменение только метаданных в sys.sysrscols. rscolid обновлено до большого целого и status бит 2, установленный на указанное значение)
  • Запись в sys.sysrscols для нового столбца изменена, чтобы присвоить ей rscolid старого столбца.

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

Таким образом, объяснение того, почему вы получаете много записей, зависит от того, почему именно версии «до» и «после» строки не совпадают.

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

datetimeoffset(0) - фиксированная длина, но для столбцов фиксированной длины, хранящихся в формате FixedVar, старый и новый столбцы, похоже, имеют одинаковый слот в фиксированная длина данных строки, и поскольку оба они имеют одинаковую длину и значение , строки «до» и «после» строки одинаковы . Это можно увидеть в ответе @ Aaron. Обе версии строки до и после ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL; являются

0x10000c00 01000000 00000000 020000

Это не зарегистрировано.

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

Некоторые возможные причины того, почему это может произойти в столбце с фиксированной длиной, - это

  • Если столбец был первоначально объявлен как SPARSE, то новый столбец будет храниться в другой части строки из оригинала, чтобы изображения до и после строки были разными.
  • Если вы используете какие-либо параметры сжатия, то до и после версии строки будут отличаться, поскольку количество столбцов в массиве компакт-дисков увеличивается.
  • В базах данных с включенной опцией изоляции моментальных снимков информация о версиях в каждой строке обновляется (@SQL Kiwi указывает, что это также может происходить в базах данных без использования SI описано здесь ).
  • Может быть какая-то предыдущая операция ALTER TABLE, которая была реализована как изменение только метаданных и еще не применяется к строке. Например, если был добавлен новый столбец с нулевой переменной длины, это первоначально применяется как изменение только метаданных, и оно фактически записывается только в строки, когда они будут обновляться (запись, которая на самом деле происходит в этом последнем экземпляре, - это просто обновления раздел count столбца и NULL_BITMAP в столбце NULL varchar в конце строки не занимает какое-либо пространство)
ответил Martin Smith 30 52012vEurope/Moscow11bEurope/MoscowFri, 30 Nov 2012 17:01:02 +0400 2012, 17:01:02
4

Я столкнулся с той же проблемой в отношении таблицы, содержащей 200 000 000 строк. Сначала я добавил столбец nullable, затем обновил все строки и, наконец, изменил столбец на NOT NULL с помощью инструкции ALTER TABLE ALTER COLUMN. Это привело к тому, что две огромные транзакции взорвали лог-файл невероятно (рост на 170 ГБ).

Самый быстрый способ, который я нашел, это следующее:

  1. Добавьте столбец, используя значение по умолчанию

    ALTER TABLE table1 ADD column1 INT NOT NULL DEFAULT (1)
    
  2. Снимите ограничение по умолчанию с помощью динамического SQL, поскольку ограничение не было указано ранее:

    DECLARE 
        @constraint_name SYSNAME,
        @stmt NVARCHAR(510);
    
    SELECT @CONSTRAINT_NAME = DC.NAME
    FROM SYS.DEFAULT_CONSTRAINTS DC
    INNER JOIN SYS.COLUMNS C
        ON DC.PARENT_OBJECT_ID = C.OBJECT_ID
        AND DC.PARENT_COLUMN_ID = C.COLUMN_ID
    WHERE
        PARENT_OBJECT_ID = OBJECT_ID('table1')
        AND C.NAME = 'column1';
    

Время выполнения сократилось с> От 30 минут до 10 минут, включая репликацию изменений через транзакционную репликацию. Я запускаю установку SQL Server 2008 (SP2).

ответил Fritz 15 +04002013-10-15T13:26:24+04:00312013bEurope/MoscowTue, 15 Oct 2013 13:26:24 +0400 2013, 13:26:24
1

Я проверил следующий тест:

create table tblCheckResult(
        ColID   int identity
    ,   dtoDateTime Datetimeoffset(0) null
    )

 go

insert into tblCheckResult (dtoDateTime)
select getdate()
go 10000

checkpoint 

ALTER TABLE tblCheckResult 
ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL

select * from fn_dblog(null,null)

Я считаю, что это связано с зарезервированным пространством, которое хранится в журнале, если вы откажетесь от транзакции. Посмотрите функцию fn_dblog в столбце «Резервный резерв» для строки LOP_BEGIN_XACT и посмотрите, сколько места она пытается зарезервировать.

ответил Keith Tate 29 42012vEurope/Moscow11bEurope/MoscowThu, 29 Nov 2012 21:17:31 +0400 2012, 21:17:31
-2

Поведение для этого отличается в SQL Server 2012. См. http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/

Количество записей журнала, созданных для SQL Server 2008 R2 и ниже выпусков, будет значительно выше, чем количество записей журнала для SQL Server 2012.

ответил TroubleshootingSQL 5 WedEurope/Moscow2012-12-05T21:18:24+04:00Europe/Moscow12bEurope/MoscowWed, 05 Dec 2012 21:18:24 +0400 2012, 21:18:24

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132