Типы фрагментаций
Когда запись удаляется, в файле БД высвобождается место. Когда вставляется новая запись, это может привести к расщеплению страниц, что приводит к появлению пустого пространства на страницах данных. Когда данный обновляются, это может привести к изменению размера записи и к возникновению двух ранее упоминавшихся случаев. Все это приводит к фрагментации. В SQL Server рассматриваются два типа фрагментации: внутренняя и внешняя.
В идеале страницы должны быть подлинкованы слева направо в порядке хранения данных. Вследствие расщепления страниц этот порядок может быть нарушен. Это приводит как к неполному заполнению страниц, так и к увеличению операций I/O вследствие непоследовательного положения цепочек страниц на диске – это вызывает дополнительные перемещения головок с цилиндра на цилиндр диска. А это одна из наиболее медленных дисковых операций.
Команда DBCC SHOWCONTIG помогает определить как внутреннюю так и внешнюю фрагментацию.
Выполним команду
DBCC SHOWCONTIG scanning 'Orders' table
Table: 'Orders' (21575115); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 20
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 4.0
- Scan Density [Best Count:Actual Count].......: 60.00% [3:5]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 40.00%
- Avg. Bytes Free per Page.....................: 146.5
- Avg. Page Density (full).....................: 98.19%
Команда DBCC SHOWCONTIG работает на leaf level поэтому дает ответ только о положении страниц.
Расшифруем результат:
Pages Scanned указывает количество страниц в таблице. В нашем примере их 20.
Extents Scanned показывает количество экстентов занимаемых таблицей. Это сразу указывает на фрагментированность данных – для сохранения 20 страниц хватает 3х экстентов.
Extent Switches говорит о количестве раз переключения с экстента на экстент при последовательном чтении данных. В идеальной ситуации это число равно Extents Scanned – 1
Avg. Pages per Extent говорит о среднем количестве страниц на экстент при перемещении по цепочке страниц. Это значение должно быть как можно ближе к 8
Scan Density представляет собой значение для внешней фрагментации. Этот результат получается от соотношения идеальной смены экстентов к фактической. Вполне очевидно, это что должно быть близко к 100%
Logical Scan Fragmentation дает процент страниц не в логическом порядке. Если страницы находятся в строгой последовательности слева направо, то данный параметр будет иметь значение 0
Extent Scan Fragmentation дает процент экстентов не в логическом порядке. Имеет то же логическое значение что и Logical Scan Fragmentation
Avg. Bytes Free per Page – должно быть как можно ближе к 0 если fill factor 100. Иное значение требует незначительных расчетов. Если fill factor 80, это обеспечивает примерно 1600 свободых байтов на страницу.
Avg. Page Density должно быть как можно ближе к 100%. Avg. Bytes Free per Page и Avg. Page Density дают хорошее представление о внутренней фрагментации.
В нашем примере мы имеем Avg. Page Density 98.19%, что означает что нет внутренней фрагментации (длина записей не всегда совпадает с размером страницы). С другой стороны Scan Density 60% и Extent Scan Fragmentation 40% говорит о внешней фрагментации. Если мы дефрагментируем таблицу а выполним эту команду еще раз, мы получим следующий результат:
DBCC SHOWCONTIG scanning 'Orders' table...
Table: 'Orders' (21575115); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 20
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 6.7
- Scan Density [Best Count:Actual Count].......: 100.00% [3:5]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 146.5
- Avg. Page Density (full).....................: 98.19%
Вставка может приводить к фрагментации на leaf level. Кластерные индексы особенно чувствительны к вставке данных.
Проблема в том что при расщеплении страниц, новая страница будет расположена в первом попавшемся свободном месте базы данных. При многочисленных вставках база данных рискует стать очень фрагментируемой.
При обновлении SQL Server всегда старается оставить запись на старом месте и избежать ее переноса на новое место. Но это невозможно при увеличении длины записи. В случае переноса записи SQL Server использует указатели на новое место. Это позволяет не перестраивать индексы.
Стремление не обновлять индексы наносит негативный удар по производительности поскольку приводит к фрагментированию базы данных. Данный алгоритм вполне оправдан для OLTP БД.
Как мы видели вставки приводят к внутренней и внешней фрагментации, а обновления к внешней. Удаления записи приводит к внутренней фрагментации – делает “дырки” на страницах. При удалении записи, SQL Server не удаляет физически эти записи, а помечает их как удаленные. При вставке или обновлении записей, свободное место от удаленных записей может быть переиспользовано. В противном случае каждые пол часа запускается процесс, который утилизирует удаленные записи.
Удаление записей приводит в внутренней фрагментации и переиспользование места на диске приводит к дополнительным операциям I/O. И не забывайте что страницы на диске копируются в память – нерациональное использование дисковой памяти приводит к переиспользованью оперативной памяти.
Для дефрагментации в SQL Server существует три пути:
DBCC DBREINDEX может использована для перестройки индексов и возможно для изменения fillfactor. Если вы хотите перестроить все индексы для таблицы, необходимо выдать команду c пустым вторым параметром
CREATE INDEX WITH DROP_EXISTING просто пересоздаст существующий индекс.
- Внутренняя подразумевает пустоты внутри страницы
- Внешняя – непоследовательность связей страниц
В идеале страницы должны быть подлинкованы слева направо в порядке хранения данных. Вследствие расщепления страниц этот порядок может быть нарушен. Это приводит как к неполному заполнению страниц, так и к увеличению операций I/O вследствие непоследовательного положения цепочек страниц на диске – это вызывает дополнительные перемещения головок с цилиндра на цилиндр диска. А это одна из наиболее медленных дисковых операций.
Команда DBCC SHOWCONTIG помогает определить как внутреннюю так и внешнюю фрагментацию.
Выполним команду
DBCC SHOWCONTIG('Orders')
Изучим результат:DBCC SHOWCONTIG scanning 'Orders' table
Table: 'Orders' (21575115); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 20
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 4.0
- Scan Density [Best Count:Actual Count].......: 60.00% [3:5]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 40.00%
- Avg. Bytes Free per Page.....................: 146.5
- Avg. Page Density (full).....................: 98.19%
Команда DBCC SHOWCONTIG работает на leaf level поэтому дает ответ только о положении страниц.
Расшифруем результат:
Pages Scanned указывает количество страниц в таблице. В нашем примере их 20.
Extents Scanned показывает количество экстентов занимаемых таблицей. Это сразу указывает на фрагментированность данных – для сохранения 20 страниц хватает 3х экстентов.
Extent Switches говорит о количестве раз переключения с экстента на экстент при последовательном чтении данных. В идеальной ситуации это число равно Extents Scanned – 1
Avg. Pages per Extent говорит о среднем количестве страниц на экстент при перемещении по цепочке страниц. Это значение должно быть как можно ближе к 8
Scan Density представляет собой значение для внешней фрагментации. Этот результат получается от соотношения идеальной смены экстентов к фактической. Вполне очевидно, это что должно быть близко к 100%
Logical Scan Fragmentation дает процент страниц не в логическом порядке. Если страницы находятся в строгой последовательности слева направо, то данный параметр будет иметь значение 0
Extent Scan Fragmentation дает процент экстентов не в логическом порядке. Имеет то же логическое значение что и Logical Scan Fragmentation
Avg. Bytes Free per Page – должно быть как можно ближе к 0 если fill factor 100. Иное значение требует незначительных расчетов. Если fill factor 80, это обеспечивает примерно 1600 свободых байтов на страницу.
Avg. Page Density должно быть как можно ближе к 100%. Avg. Bytes Free per Page и Avg. Page Density дают хорошее представление о внутренней фрагментации.
В нашем примере мы имеем Avg. Page Density 98.19%, что означает что нет внутренней фрагментации (длина записей не всегда совпадает с размером страницы). С другой стороны Scan Density 60% и Extent Scan Fragmentation 40% говорит о внешней фрагментации. Если мы дефрагментируем таблицу а выполним эту команду еще раз, мы получим следующий результат:
DBCC SHOWCONTIG scanning 'Orders' table...
Table: 'Orders' (21575115); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 20
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 6.7
- Scan Density [Best Count:Actual Count].......: 100.00% [3:5]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 146.5
- Avg. Page Density (full).....................: 98.19%
Вставка может приводить к фрагментации на leaf level. Кластерные индексы особенно чувствительны к вставке данных.
Проблема в том что при расщеплении страниц, новая страница будет расположена в первом попавшемся свободном месте базы данных. При многочисленных вставках база данных рискует стать очень фрагментируемой.
При обновлении SQL Server всегда старается оставить запись на старом месте и избежать ее переноса на новое место. Но это невозможно при увеличении длины записи. В случае переноса записи SQL Server использует указатели на новое место. Это позволяет не перестраивать индексы.
Стремление не обновлять индексы наносит негативный удар по производительности поскольку приводит к фрагментированию базы данных. Данный алгоритм вполне оправдан для OLTP БД.
Как мы видели вставки приводят к внутренней и внешней фрагментации, а обновления к внешней. Удаления записи приводит к внутренней фрагментации – делает “дырки” на страницах. При удалении записи, SQL Server не удаляет физически эти записи, а помечает их как удаленные. При вставке или обновлении записей, свободное место от удаленных записей может быть переиспользовано. В противном случае каждые пол часа запускается процесс, который утилизирует удаленные записи.
Удаление записей приводит в внутренней фрагментации и переиспользование места на диске приводит к дополнительным операциям I/O. И не забывайте что страницы на диске копируются в память – нерациональное использование дисковой памяти приводит к переиспользованью оперативной памяти.
Для дефрагментации в SQL Server существует три пути:
- DBCC INDEXDEFRAG
- DBCC DBREINDEX
- CREATE INDEX WITH DROP_EXISTING
DBCC DBREINDEX может использована для перестройки индексов и возможно для изменения fillfactor. Если вы хотите перестроить все индексы для таблицы, необходимо выдать команду c пустым вторым параметром
CREATE INDEX WITH DROP_EXISTING просто пересоздаст существующий индекс.
Прочли: 5686 | Добавлено: 21 апреля 2011 | На главную