Важное изменение алгоритма создания LSN в SQL Server 2014

http://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/

Автор: Paul Randal

Опубликовано: 6 января 2015г.

SQL Server 2014 был выпущен еще в апреле прошлого года, и ходили некоторые слухи об изменениях в алгоритме создания VLF. Они направлены на уменьшение числа VLF, когда журнал увеличивается по команде или автоматически (далее я буду говорить для простоты авто-приращение, поскольку это наиболее распространенный сценарий). Я сделал несколько экспериментов и подумал, что понял изменения указанного алгоритма. Оказывается, я понял не всё. На прошлой неделе в переписке MVP всплыл вопрос, который породил целую дискуссию, и мы вместе пришли к выводу, что алгоритм ведет себя недетерминированно… другими словами, мы не знаем, что он делает. Так что я обратился к моим друзьям в CSS, которые исследовали код (спасибо Bob Ward и Suresh Kandoth!) и объяснили изменения.

Изменение одно и довольно глубокое, оно направлено на предотвращение создания огромного количества VLF при частом авто-приращении. Это здорово, потому что слишком большое количество VLF (это зависит от размера журнала, но несколько тысяч обычно слишком много) может вызвать проблемы с производительностью для резервного копирования, восстановления, очистки журнала, репликации, восстановления после сбоев, откатов транзакций и даже рядовых операций DML.

До 2014 алгоритм определения необходимого числа VLF при создании, увеличении или авто-приращении журнала зависел от его размера и следующих вопросов:

 

• Менее 1 Мб, тут всё довольно сложно, игнорируйте этот вариант.
• До 64 МБ: 4 новых VLF, каждый примерно 1/4 размера прироста
• От 64 МБ до 1 ГБ: 8 новых VLF, каждый примерно 1/8 размер прироста
• Более 1 GB: 16 новых VLF, каждый примерно 1/16 размер прироста

 

Так что, если вы создали свой журнал размером 1 Гб и авто-прирост выполнялся порциями по 512 МБ до 200 Гб, у вас получится: 16 + ((200 – 1) х 2 х 8) = 3200 VLF (16 VLF при первоначальном создании журнала, 200 – 1 = 199 Гб роста по 512 Мб на автоматическое увеличение = 398 операций авто-прироста, каждый из которых создаст по 8 VLF).

Для SQL Server 2014 алгоритм изменился так:

 

• Является ли размер прироста менее 1/8 размера журнала?
• Да: создать 1 новый VLF, равный размеру прироста
• Нет: воспользоваться приведенной ранее формулой

 

Так что для SQL Server 2014, если вы создали свой журнал размером 1 Гб с авто-приростом кусками по 512 МБ до 200 Гб, у вас получится:

 

• 16 VLF при первоначальном создании журнала
• Все новообразования вплоть до размера журнала 4,5 ГБ происходили в соответствии с формулой, и можно было бы наблюдать такие размеры файла: 1, 1.5, 2, 2.5, 3, 3.5, 4 Гбайт, и на каждом приращении добавлялись по 8 VLF = 56 VLF в сумме.
• Все новообразования для размера журнала более 4 ГБ будут создавать только по 1 VLF – процентный рост = (200 – 4) х 2 = 392 VLF
• Всего = 392 + 56 + 16 = 464 VLF

 

464 многим более разумное число VLF чем 3200, и будет создавать гораздо меньше проблем с производительностью.
Меня также спрашивали влияет ли на это уровень совместимости? Нет, уровень совместимости игнорируется внутри механизмов Storage Engine.

Я думаю, что это отличное изменение, и не вижу каких-либо недостатков в нём (кроме того, что оно не было обнародовано, когда SQL Server 2014 был выпущен). В блоге CSS в ближайшее время появится комплексный обзор об этом.

Вы можете подумать, что это может привести к появлению VLF очень большого размера (например, если вы установите размер авто-приращения 4 Гб для журнала размером 100 Гб), и это возможно. Но что с того? Очень большие VLF будут проблемой только тогда, когда они создаются первоначально, а затем вы пытаетесь сжать журнал до минимального размера. Как минимум, вы можете иметь в журнале только два VLF, так что у вас получилось бы два гигантских VLF в начале журнала, а за ними более мелкие, появившиеся после прироста журнала. Это может быть проблемой, которая помешает журналу использовать повторно высвобождающиеся виртуальные журналы без нужды в авто-приращении, но это не идёт ни в какое сравнение с тем, как часто можно столкнуться со слишком большим числом VLF. И этот сценарий нетипичен для нового алгоритма. (К слову, вы можете решить эту проблему путем создания моментального снимка базы данных, а затем восстановить базу данных до состояния, сохраненного в моментальном снимке, что удалит журнал и создаст новый размером 0,5 Мб с двумя крошечными VLF … это баг особенность, которая появилась с 2005 года, но она нарушит ваш цепочку резервного копирования журнала, когда такое восстановление закончится).

Конечно, для улучшения управления VLF нужно сделать в будущем ещё очень многое (например, для решения проблемы, которую я только что описал), но сделанное – гигантский шаг в правильном направлении.