Дефрагментация индексов

Здравствуйте, возник такой вопрос : есть таблица (~ 3 млн.) по нескольким полям созданы индексы (fillfactor = 80), добавление новых записей стало медленнее, чем в самом начале, когда таблицу создали. Я так понимаю за счет фрагментации индексов. Решил не перестраивать индексы с помощью DBCC DBREINDEX что бы не ставить систему колом, а дефрагментировать с помощью DBCC INDEXDEFRAG. После запроса статистики вижу , что меняется параметр Logical Scan Fragmentation (становится = 0), а Extent Scan Fragmentation так и остается большим.Время добавления в таблицу меньше не стало, хотя в BOL написано : "DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, thus improving index-scanning performance". Не могу понять где не догоняю.
17 ответов

добавление новых записей стало медленнее, чем в самом начале, когда таблицу создали. Я так понимаю за счет фрагментации индексов.Если значения добалялись в середину индекса, то замедление связано не с дефргаментацией а с отсутсвием свободного места на индексных страницах. Что ведет к дополнительным затратам на расщепеление этих страниц.А DBCC INDEXDEFRAG как раз и "also compacts the pages of an index, taking into account the FILLFACTOR specified when the index was created. Any empty pages created as a result of this compaction will be removed."


2Glory1. DBCC SHOCONTIG выдает инф. о свободном месте на страницах, так что не ясно при чем тут расщепление.2. Все также не ясно почему фрагментированы эстенты3. Не могу понять принципиальное разницы (кроме полной блокировки индексов) заявленной в документации между INDEXDEFRAG и DBREINDEX(после работы которого индексы приходят в "иеальное" состояние).


1. DBCC SHOCONTIG выдает инф. о свободном месте на страницах, так что не ясно при чем тут расщепление.И сколько же его там ?2. Все также не ясно почему фрагментированы эстентыПотому что"DBCC INDEXDEFRAG defragments the leaf level of an index so that the physical order of the pages matches the left-to-right logical order of the leaf nodes, thus improving index-scanning performance. "А индекс - это не только leaf level-ы. Да и для дефрагметации экстентов придется "подвинуть" экстенты других объектов.Не могу понять принципиальное разницы В смысле ? не можете понять разницы между перестройкой индекса и перемещением его некоторых страниц ?


1.dbcc showcontig (t06_04) WITH ALL_INDEXESDBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 0, database ID: 5TABLE level scan performed.- Pages Scanned................................: 35353- Extents Scanned..............................: 4422- Extent Switches..............................: 4421- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.95% [4420:4422]- Extent Scan Fragmentation ...................: 0.05%- Avg. Bytes Free per Page.....................: 359.8- Avg. Page Density (full).....................: 95.55%DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 2, database ID: 5LEAF level scan performed.- Pages Scanned................................: 12455- Extents Scanned..............................: 1566- Extent Switches..............................: 1893- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 82.21% [1557:1894]- Logical Scan Fragmentation ..................: 4.83%- Extent Scan Fragmentation ...................: 9.77%- Avg. Bytes Free per Page.....................: 876.6- Avg. Page Density (full).....................: 89.17%DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 3, database ID: 5LEAF level scan performed.- Pages Scanned................................: 16550- Extents Scanned..............................: 2083- Extent Switches..............................: 2494- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 82.93% [2069:2495]- Logical Scan Fragmentation ..................: 1.07%- Extent Scan Fragmentation ...................: 1.01%- Avg. Bytes Free per Page.....................: 851.9- Avg. Page Density (full).....................: 89.47%DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 4, database ID: 5LEAF level scan performed.- Pages Scanned................................: 11488- Extents Scanned..............................: 1445- Extent Switches..............................: 2621- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 54.77% [1436:2622]- Logical Scan Fragmentation ..................: 4.67%- Extent Scan Fragmentation ...................: 2.21%- Avg. Bytes Free per Page.....................: 1247.3- Avg. Page Density (full).....................: 84.59%DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 5, database ID: 5LEAF level scan performed.- Pages Scanned................................: 10767- Extents Scanned..............................: 1352- Extent Switches..............................: 1445- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 93.08% [1346:1446]- Logical Scan Fragmentation ..................: 0.33%- Extent Scan Fragmentation ...................: 0.67%- Avg. Bytes Free per Page.....................: 788.7- Avg. Page Density (full).....................: 90.26%DBCC execution completed. If DBCC printed error messages, contact your system administrator.DBCC INDEXDEFRAG (mtr,t06_04,index1)DBCC INDEXDEFRAG (mtr,t06_04,index2)DBCC INDEXDEFRAG (mtr,t06_04,index3)DBCC INDEXDEFRAG (mtr,t06_04,index4)DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 0, database ID: 5TABLE level scan performed.- Pages Scanned................................: 35353- Extents Scanned..............................: 4422- Extent Switches..............................: 4421- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.95% [4420:4422]- Extent Scan Fragmentation ...................: 0.05%- Avg. Bytes Free per Page.....................: 359.8- Avg. Page Density (full).....................: 95.55%DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 2, database ID: 5LEAF level scan performed.- Pages Scanned................................: 12417- Extents Scanned..............................: 1562- Extent Switches..............................: 1730- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 89.72% [1553:1731]- Logical Scan Fragmentation ..................: 0.06%- Extent Scan Fragmentation ...................: 9.60%- Avg. Bytes Free per Page.....................: 854.5- Avg. Page Density (full).....................: 89.44%DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 3, database ID: 5LEAF level scan performed.- Pages Scanned................................: 16473- Extents Scanned..............................: 2074- Extent Switches..............................: 2253- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 91.39% [2060:2254]- Logical Scan Fragmentation ..................: 0.05%- Extent Scan Fragmentation ...................: 0.68%- Avg. Bytes Free per Page.....................: 818.1- Avg. Page Density (full).....................: 89.89%DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 4, database ID: 5LEAF level scan performed.- Pages Scanned................................: 11113- Extents Scanned..............................: 1399- Extent Switches..............................: 1788- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 77.70% [1390:1789]- Logical Scan Fragmentation ..................: 0.06%- Extent Scan Fragmentation ...................: 1.93%- Avg. Bytes Free per Page.....................: 1016.2- Avg. Page Density (full).....................: 87.44%DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 5, database ID: 5LEAF level scan performed.- Pages Scanned................................: 10766- Extents Scanned..............................: 1352- Extent Switches..............................: 1407- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 95.60% [1346:1408]- Logical Scan Fragmentation ..................: 0.06%- Extent Scan Fragmentation ...................: 0.67%- Avg. Bytes Free per Page.....................: 788.0- Avg. Page Density (full).....................: 90.26%DBCC execution completed. If DBCC printed error messages, contact your system administrator.-DBCC DBREINDEX (t06_04,'',80)DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 0, database ID: 5TABLE level scan performed.- Pages Scanned................................: 35353- Extents Scanned..............................: 4422- Extent Switches..............................: 4421- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.95% [4420:4422]- Extent Scan Fragmentation ...................: 0.05%- Avg. Bytes Free per Page.....................: 359.8- Avg. Page Density (full).....................: 95.55%DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 2, database ID: 5LEAF level scan performed.- Pages Scanned................................: 13877- Extents Scanned..............................: 1744- Extent Switches..............................: 1743- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.48% [1735:1744]- Logical Scan Fragmentation ..................: 10.71%- Extent Scan Fragmentation ...................: 5.73%- Avg. Bytes Free per Page.....................: 1616.4- Avg. Page Density (full).....................: 80.03%DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 3, database ID: 5LEAF level scan performed.- Pages Scanned................................: 18457- Extents Scanned..............................: 2322- Extent Switches..............................: 2321- Avg. Pages per Extent........................: 7.9- Scan Density [Best Count:Actual Count].......: 99.40% [2308:2322]- Logical Scan Fragmentation ..................: 12.57%- Extent Scan Fragmentation ...................: 1.77%- Avg. Bytes Free per Page.....................: 1600.4- Avg. Page Density (full).....................: 80.23%DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 4, database ID: 5LEAF level scan performed.- Pages Scanned................................: 12125- Extents Scanned..............................: 1523- Extent Switches..............................: 1522- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.54% [1516:1523]- Logical Scan Fragmentation ..................: 12.54%- Extent Scan Fragmentation ...................: 0.26%- Avg. Bytes Free per Page.....................: 1607.1- Avg. Page Density (full).....................: 80.14%DBCC SHOWCONTIG scanning 't06_04' table...Table: 't06_04' (1221579390); index ID: 5, database ID: 5LEAF level scan performed.- Pages Scanned................................: 12126- Extents Scanned..............................: 1523- Extent Switches..............................: 1522- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.54% [1516:1523]- Logical Scan Fragmentation ..................: 7.50%- Extent Scan Fragmentation ...................: 0.20%- Avg. Bytes Free per Page.....................: 1607.7- Avg. Page Density (full).....................: 80.14%DBCC execution completed. If DBCC printed error messages, contact your system administrator.Да и для дефрагметации экстентов придется "подвинуть" экстенты других объектов2. Логично. Я в принципе так предствляю понятие "дефрагментация"В смысле ? не можете понять разницы между перестройкой индекса и перемещением его некоторых страниц ?3. Понять вышесказанную фразу я могу, но не могу понять в чем тогда происходит дефрагментация, если перемещаются только некоторые страницы индекса, а сами данные (индекса) остаются на месте, диск то своими головками тогда и бегает туда-сюда. Насколько я понимаю перестройка индекса - создание упорядоченной структуры = создание нового индекса = дефрагментация. Т.е. результатом всех действий - создание упорядоченной структуры .


Да и для дефрагметации экстентов придется "подвинуть" экстенты других объектов2. Логично. Я в принципе так предствляю понятие "дефрагментация"Значит вы себе неправильно представляете дефрагментацию индексов.В смысле ? не можете понять разницы между перестройкой индекса и перемещением его некоторых страниц ?3. Понять вышесказанную фразу я могу, но не могу понять в чем тогда происходит дефрагментация, если перемещаются только некоторые страницы индекса, а сами данные (индекса) остаются на месте, диск то своими головками тогда и бегает туда-сюда. Насколько я понимаю перестройка индекса - создание упорядоченной структуры = создание нового индекса = дефрагментация. Т.е. результатом всех действий - создание упорядоченной структуры .Всем объектам страницы/экстенты выделяются по общим правилам. Из общей кучи свободных страниц/экстентов.


Насколько я понял единственным способом увеличить производительность вствки в таблицу является переодическое перестроение индексов с помощь DBREINDEX. Это так ? (поиском я уже пользовался, но конкретики нет никакой)


Для начала бы неплохо выяснить что именно замедляет добавление данных. Дело может быть не в самом индексе а в их количестве. Хотя и fill factor тоже может напрямую влиять на скорость добавления записи.


А какие мероприятия для этого нужно предпринять, посоветуйте пожалуйста ?


Что тут можно посоветовать - искать компромисс между приемлимым временем добавления записи и количеством индексов.


Все дело в том, что количество индексов не обсуждается : они все нужны.Мне все таки неясна ситуация при добавлении : после перестроения индексов вставка работает более или менее быстро. Проблемы появляются через каждые 100-200 тыс. Неясно вот что - на страницах есть свободные слоты, расщеплять ничего не надо, пиши и пиши себе. Откуда дефрагментация и замедление?


Все дело в том, что количество индексов не обсуждается : они все нужны.Пытаетемь совместить OLTP и OLAP системы ??на страницах есть свободные слоты, расщеплять ничего не надоAvg. Page Density - это средняя заполненность страниц. Причем только листьев.Это вовсе не означает что на той странице индекса, куда добавляется новое значение для новой записи есть свободное место.


А может того, кластерный индекс добавить?А то наличие indid = 0 говорит о том, что таблица без кластерного индекса, а в этом случае, вроде как, дефрагментация индексов ни к чему хорошему не приводит...


2GloryПытаетемь совместить OLTP и OLAP системы ??Действительно, что то я об этом и не подумал.Avg. Page Density - это средняя заполненность страниц. Причем только листьев.Это вовсе не означает что на той странице индекса, куда добавляется новое значение для новой записи есть свободное место.Ну наверное2CrimeanЧестно говоря сомневаюсь , что замена одного из четырех индексов с некластерного на кластерный кардинально повлияет на ситуацию.Буду находить компромис.Кстати не подскажете в некоторых таблицах есть индексы типа _WA_Sys_ ....я их не создавал. Для чего они нужны, нужны ли вообще и как их прибить если они не нужны.Спасибо


_WA_ - статистики, созданы системой аптоматическиНасчет кластерного индекса... Построй кластерный ПК по суррогату (identity) и повтори все эксперименты. Надеюсь, заметишь разницу.


Хорошо - попробую


2 Александр ГладченкоСпасибо за ссылку. Основная масса информации мне известна, но есть несколько практических рекомендаций над которыми можно поработать. Спасибо всем советы.