Правда, про fill-factor в MS SQL Server, MS SQL Server, Бази даних, статті

Kalen Delaney

Дві недокументовані команди допоможуть вирішити загадку fill-factor

Індекси і fill factor

Спочатку, ми повинні зрозуміти основи того, як організовані індекси. SQL Server організовує індекси як Бі-дерево, з одного кореневої сторінкою, яка є відправною точкою для побудови індексу. Коренева сторінка може мати покажчики на дві або більше сторінки наступного рівня індексу, і, кожна з цих сторінок, може мати покажчики на безліч сторінок наступного рівня. Останній рівень індексу – це рівень листя, який повинен містити всі індексні ключові значення в сортованому послідовності. У SQL Server 2000 ця послідовність може бути або зростаючої або в порядку убування. Більш ранні версії підтримували всі індекси в порядку зростання. В кластерному індексі, рівень листя – зберігає дані, так що SQL Server зберігає дані в відсортованому порядку. В не кластерному індексі, рівень листя містить покажчики на дані. Fill factor – значення, яке Ви можете визначити при створенні індексу, щоб повідомити SQL Server, наскільки заповненими Ви хочете мати сторінки індексу. Ви можете визначити fill factor в інструкції CREATE INDEX або коли Ви використовуєте інструкцію CREATE TABLE або ALTER TABLE для визначення первинного ключа або унікального обмеження на таблицю, після чого SQL Server автоматично побудує індекс, і встановить первинні ключі та унікальні обмеження. Звичайно, Ви також можете визначити fill factor, використовуючи Enterprise Manager, при створенні індексу або додаванні обмежень до таблиці. Якщо Ви не визначаєте явно значення fill-factor, SQL Server встановить значення fill factor за замовчуванням. Щоб визначити, яке було прийнято значення fill factor за замовчуванням, Ви можете виконати наступну команду:

Спочатку, потрібно встановити розширений висновок конфігураційних значень:

EXEC sp_configure ‘show advanced options’, 1

GO
RECONFIGURE
GO

Потім, безпосередньо дивимося значення за замовчуванням ‘fill factor’:

EXEC sp_configure ‘fill factor’
GO

На завершення, повертаємо змінене значення тому:

EXEC sp_configure ‘show advanced options’, 0
GO
RECONFIGURE
GO

Якщо Ви не змінювали значення за замовчуванням, Ви зможете побачити в звіті значення fill factor для параметра run_value (значення, що використовується в даний час), встановлене в “0”:

Name………………minimum….maximum….config_value….run_value

—————————————————————————————

fill factor (%)………0…………..100…………0………………0……….

fill factor встановлений в “0” буде означати, що сторінки індексу будуть повністю заповнені, але в верхніх рівнях індексу залишено місце для однієї – двох індексних рядків, які можуть бути додані пізніше. Фактично, Ви явно ніколи не зможете присвоїти fill factor значення “0” при створенні індексу, тому що “0” є неприпустимим значенням для команди CREATE INDEX. Єдиний спосіб отримати fill factor дорівнює “0” полягає в тому, щоб прийняти значення за умовчанням, якщо Ви не змінювали його до цього. Ви можете змінити значення fill factor за замовчуванням, виконавши команду sp_configure і додавши другий параметр, який повинен відповідати бажаного, новому значенню fill factor по замовчунням:

EXEC sp_configure ‘fill factor’, 80
GO
RECONFIGURE
GO

Ви повинні зупинити і перезапустити SQL Server для того, щоб зміни вступили в силу. Новий fill factor буде дійсно встановлений, коли Ви побачите нове значення в стовпці run_value.
Якщо Ви створюєте індекс з fill factor = 80, сторінки будуть на 80 відсотків заповнені, і верхні рівні індексу будуть мати достатньо місця, залишеного в них для обліку однієї або двох індексних рядків.
Зверніть увагу, що SQL Server не підтримує оригінальне значення fill-factor, в момент додавання до таблиці нових рядків. fill factor тільки управляє ступенем заповнення ваші сторінки при початковому створенні індексу. SQL Server зберігає первинне значення fill factor в системній таблиці sysindexes, в стовпці OrigFillFactor. Якщо Ви виконуєте команду DBCC DBREINDEX, яка перебудовує один або всі індекси таблиці, визначення fill factor рівним “0” повідомляє SQL Server, що необхідно перебудувати індекси з оригінальним значенням fill factor, і що індекс буде побудований не обов’язково зі значенням за замовчуванням, яке Ви встановили для вашого сервера.
Значення fill-factor може бути важливо в середовищі, де виконується дуже багато вставок. Рівень листя індексу повинен зберігати всі індексні, ключові значення в сортованому послідовності, так, що якщо хтось буде вставляти новий рядок в таблицю, індексне, ключове значення в цьому рядку буде визначати позицію рядка в індексі (або таблиці, якщо індекс кластерний). Наприклад, якщо Ви маєте індекс по імені людини, вставка рядка з ім’ям “Marlin” вимагатиме, щоб SQL Server вставив нову індексний рядок в ту ж саму сторінку з іншими іменами, які починаються на “Ma”, можливо, між Margolin і Martin. Якщо сторінка, яку новий рядок повинна належати, повністю заповнена, SQL Server розіб’є її і зв’яже з новою сторінкою в ланцюжок сторінки. SQL Server перемістить приблизно половину рядків з первісної, заповненої сторінки в нову сторінку. Мало того, що такий поділ сторінок є ресурсемкой операцією, яка може помітно позначитися на ефективності операцій вставки, але внаслідок того, що нова сторінка найімовірніше виявиться фізично не безперервної по відношенню до первісної сторінці, Ви отримаєте в результаті фрагментацію індексу або таблиці.
Створення індексу з низьким значенням fill factor означає, що у вашій таблиці зарезервовано місце для подальшого росту, що запобігає розбиття сторінок, відкладаючи, таким чином, пов’язані з цим проблеми. Однак, якщо ваші сторінки заповнені лише частково, щоб зберігати дані буде потрібно більшу кількість сторінок і індекс може досягти вельми великого розміру. Микрософт визначив значення за замовчуванням для fill-factor рівне “0”, як компроміс між наявністю місця для подальшого зростання і мінімізацією розміру таблиці та її індексів після створення. З fill factor = “0”, рівень листя буде заповнений, але сторінки в верхніх рівнях індексу, які можуть бути піддані розбиття, якщо вони стануть заповненими, мають деякий резерв місця для подальшого зростання.
У версіях SQL Server до 6.5, значення fill-factor = “100” передбачало, що не тільки рівень листя індексу, але і всі рівні були б повністю заповнені. Взагалі, це значення використовується тільки для таблиць, які є read-only (тільки для читання). У SQL Server 6.5, Микрософт додав нову опцію до команди CREATE INDEX. Якщо Ви також включаєте опцію WITH PAD_Index, безвідносно значення fill-factor, Ви накаже серверу встановлювати значення fill-factor всіх рівнях індексу, а не тільки рівнем листя. Так, створення індексу зі значенням fill-factor = “0” і включення опції PAD_Index, буде мати той же самий ефект, як використання значення fill-factor = “100”. Але що буде, якщо fill factor = “100” зі звичайним значенням PAD_Index?
Зверніть увагу, що Ви можете визначити значення fill-factor при визначенні первинного ключа або унікального обмеження на таблицю, тому що SQL Server автоматично будує індекс, щоб підтримати ці обмеження. Однак, Ви не можете визначити опцію PAD_Index одночасно з обмеженням. Ви можете використовувати її тільки в інструкції CREATE INDEX.

Загадка

Починаючись з SQL Server 7.0, автор часто зустрічав таке питання: “Яке реальне відмінність між fill factor = “0” і fill factor = “100”? “. Хоча SQL Server 2000 і 7.0 Books Online (BOL) повідомляють, що fill factor = “100” означає, що всі рівні індексу будуть заповнені, багато людей, очевидно, не думають, що це правда. Для дослідження такого розходження, автор використовував дві недокументовані команди DBCC: DBCC IND і DBCC PAGE. Ці команди не перераховані в BOL, і Микрософт офіційно не підтримує їх, хоча статті Микрософт іноді згадують DBCC PAGE. Щоб використовувати ці недокументовані команди, Ви повинні спочатку встановити прапор трасування 3604, щоб дозволити висновок недокументованих команд DBCC на екран клієнта. DBCC IND, який вимагає три параметри, повідомляє які сторінки належать індексу. Перші два параметри: ім’я бази даних і ім’я таблиці; третій параметр встановлює, які результати Ви хочете бачити в звіті. Значення = “1”, для третього параметра ступеня подробиця виведеної інформації, говорить про те, що будуть повернуті дані про всі рівнях всіх індексів в таблиці. Код в Лістингу № 1 (див. наприкінці цієї статті) створює таблицю в 1000 рядків у базі даних Pubs і генерує послідовно збільшуються цілочисельні значення від 1 до 1000. Автор використовує числові значення, тому що вони дуже просто генеруються. Однак, він зберігає їх як символьні рядки, тому що команда DBCC PAGE відображає символьні рядки більш ясно ніж числові значення, що зручно, якщо використовувати команду DBCC PAGE для відображення повного змісту сторінок.
Після генерації 1000 рядків, сценарій використовує значення за замовчуванням fill factor = 0, щоб створити унікальний, кластерний індекс, потім використовується DBCC IND, щоб згенерувати список усіх сторінок, які належать таблиці та її індексами. DBCC IND повертає 12-стовпців звіту, але автору необхідні були лише чотири з них:

– PagePID – ідентифікатор сторінок, це значення буде використовуватися в другій команді DBCC.
– IndexID – значення рівне “0” встановлюється для сторінок даних, “1” для сторінок в верхніх рівнях кластерного індексу, а більше ніж “1” для не кластерних індексів.
– PageType – значення рівне “1” встановлюється для сторінок даних, “2” для індексних сторінок, а “10” для сторінки Index Allocation Map (IAM).
– IndexLevel – рівень індексу, на якому сторінка створена. Якщо IndexID дорівнює “0”, значить, IndexLevel також дорівнює “0”. Найвище значення IndexLevel у будь-якого IndexID – для кореневих сторінок, причому, тільки одна сторінка буде мати це максимальне значення IndexLevel.

Команда DBCC IND в сценарії лістингу № 1 повертає номери сторінок для чотирьох сторінок з PageType = 2; з них, три мають IndexLevel = 0, а одна має IndexLevel = 1. Крім того, якщо Ви працюєте з базою даних, яка розміщується більш ніж в одному файлі, Вам буде також цікавий стовпець PageFID, який ідентифікує файл, що містить сторінку.

Примітка автора перекладу: Якщо Ви використовуєте для перевірки наведених у статті прикладів коду варіант движка MSDE або у Вас не встановлена ​​база даних pubs, Ви можете окремо встановити її методом прикріплення зі стандартного дистрибутива. Для MS SQL Server 2000 виглядати це буде так: з дистрибутива, каталог … x86 \ DATA, скопіюйте в потрібне Вам розташування файли pubs.mdf і pubs_log.ldf; зніміть у цих файлів атрибут – тільки для читання; в програмі Enterprise Manager, за допомогою опції Databases \ All Tasks \ Attach Database прикріпіть вказані файли, відкоригувавши (Якщо це необхідно) шляхи до їх розташування. Після цього, ви отримаєте повідомлення про успішне прикріплення бази pubs і можете з нею працювати.

Навіть притому, що рівень листя кластерного індексу зберігає безпосередньо дані, результат роботи команди DBCC IND не розглядає сторінки даних як індексні сторінки. Однак, ці будуть саме ті сторінки даних, до яких відноситься значення fill factor, тому що вони відносяться до рівня листя кластерного індексу. Ви можете ідентифікувати ці сторінки даних за такою комбінації значень: PageType = 1, IndexID = 0, IndexLevel = 0.

Розгадка

Далі, для відповіді на наше запитання, виберемо одну зі сторінок індексу і використовуємо її, як параметр в команді DBCC PAGE. У SQL Server 2000 і 7.0 ця команда використовує чотири параметри. Перший – це ім’я бази даних або ідентифікатор бази даних, другий – це номер файла; а третій – це номер сторінки, отриманий зі звіту DBCC IND. Четвертий параметр – це бажаний формат звіту. Наприклад, значення четвертого параметра, рівне “0” встановлює те, що Ви хочете бачити тільки колонтитул. Нижче представлена ​​команда, яку виконував автор статті:

DBCC PAGE (pubs, 1, 285, 0)

Примітка автора перекладу: третій параметр вибирайте з реальних результатів звіту роботи команди: DBCC IND (pubs, ff1, -1), отриманого в вашому варіанті установки бази pubs. Значення “285” може виявитися за рамками вашого розподілу сторінок.

Значення в колонтитулі, яке нам повинно бути цікаво, називається m_slotCnt. Воно повідомляє число рядків на заданій сторінці. Коли автор виконав сценарій в Лістингу № 1 і потім використовував DBCC PAGE, щоб подивитися на одну зі сторінок даних, він побачив, що число рядків на сторінці дорівнювало 20-ти. Коли він аналогічно виконав DBCC PAGE для однієї з індексних сторінок на більш низькому рівні, ніж корінь, він побачив, що число рядків там було одно 19-ти. Це було результат, який автор очікував побачити, тобто сторінки даних були заповнені рядками, що містять 390 байтів даних, плюс приблизно 10 байт зверху, а значить, тільки 20 рядків можливо розмістити на одній сторінці, та сторінки даних максимально заповнені. Рядки індексів мають той же самий розмір, тому що індекс будується на основі стовпця випадкових чисел (390), але замість 20 індексних рядків на сторінці, ми маємо тільки 19, тому що SQL Server зарезервував місце для однієї додаткової індексного рядка.
Використовуючи ці два інструментальних кошти, DBCC IND і DBCC PAGE, автор зміг відповісти на питання про відмінність між fill factor = 0 і fill factor = 100. Виконуючи той же самий сценарій, але, явно встановлюючи factor = 100, автор виявив, що кількість рядків в сторінках даних та індексних сторінках було тим же самим, що й у кластерного індексу з fill factor = 0. Автор вважає, що дав Вам достатньо інформації про те, як використовувати ці інструментальні засоби, щоб Ви могли переконатися в отриманих ним результати самостійно і змогли виконати глибше дослідження. Ви можете також дізнатися, що трапляється, якщо Ви встановите опцію PAD_Index, при fill factor = 100.

LISTING 1: Script to Create Table and Index, Then Generate Page List

USE pubs
GO
CREATE TABLE ff1(col1 char(390) NULL)
GO

SET NOCOUNT ON
GO
DECLARE @i int
SET @i = 1
WHILE @i < 1001 BEGIN
  INSERT INTO ff1 SELECT CONVERT(char(390), @i)
  SET @i = @i + 1
END
GO
CREATE UNIQUE CLUSTERED INDEX indx1 on ff1(col1)
GO
DBCC TRACEON (3604)
GO
DBCC IND(pubs, ff1, -1)
GO

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*