| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
| ||||||||||||||||
|
|
Правда, о fill-factor в MS SQL Server Две недокументированные команды помогут разрешить загадку 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 Затем, непосредственно смотрим значение по умолчанию 'fill factor':
EXEC sp_configure 'fill factor' В завершение, возвращаем изменённое значение назад:
EXEC sp_configure 'show advanced options', 0 Если Вы не изменяли значение по умолчанию, Вы сможете увидеть в отчёте значение fill factor для параметра run_value (значение, используемое в настоящее время), установленное в "0":
Name………………minimum….maximum….config_value….run_value fill factor установленный в "0" будет означать, что страницы индекса будут полностью заполнены, но в верхних уровнях индекса оставлено место для одной - двух индексных строк, которые могут быть добавлены позже. Фактически, Вы явно никогда не сможете присвоить fill factor значение "0" при создании индекса, потому что "0" является недопустимым значением для команды CREATE INDEX. Единственный способ получить fill factor равный "0" состоит в том, чтобы принять значение по умолчанию, если Вы не изменяли его до этого. Вы можете изменить значение fill factor по умолчанию, выполнив команду sp_configure и добавив второй параметр, который должен соответствовать желаемому, новому значению fill factor по умолчанию:
EXEC sp_configure 'fill factor', 80
Вы должны остановить и перезапустить SQL Server для того, чтобы изменения вступили в силу. Новый
fill factor будет действительно установлен, когда Вы увидите новое значение в столбце run_value. Загадка
Начинаясь с 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 для отображения
полного содержания страниц.
- PagePID - идентификатор страниц, это значение будет использоваться во второй команде DBCC. Команда 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 зарезервировал место для одной дополнительной индексной
строки. LISTING 1: Script to Create Table and Index, Then Generate Page List
USE pubs
SET NOCOUNT ON |
|
|
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|