Фрагментация кучи лечится созданием и удалением кластерного индекса - миф!

Автор: Paul Randal, A SQL Server DBA myth a day: (29/30) fixing heap fragmentation

Миф №29: исправить фрагментацию кучи можно, создав и затем удалив кластерный индекс.

Неееееет!!!

Это одно из худших действий, которые только можно совершить, если не считать сжатия базы данных.

Если вы запускаете sys.dm_db_index_physical_stats (или старый DBCC SHOWCONTIG) на куче (то есть на таблице без кластерного индекса) и видите некоторую фрагментацию, не создавайте и не удаляйте кластерный индекс, стремясь получить «красивую, непрерывную» кучу. Сделайте себе одолжение: создайте хорошо подобранный кластерный индекс и оставьте его. Материалов о выборе ключа кластеризации предостаточно — вам нужен узкий+статичный+уникальный+монотонно возрастающий ключ. У Кимберли есть статья аж 2005 года, которая всё прекрасно суммирует: Ever‑increasing clustering key – the Clustered Index Debate……….again!, а у меня — An example of a nasty cluster key.

Да, в SQL Server 2008+ можно использовать ALTER TABLE … REBUILD, чтобы убрать фрагментацию кучи, но это почти так же плохо, как операция «создать, а затем удалить кластерный индекс».

Почему я так «вскипаю» на эту тему? Потому что каждая запись в некластерном индексе должна ссылаться на соответствующую строку в таблице (будь то куча или таблица с кластерным индексом — одновременно и то и другое иметь нельзя; за объяснением см. материал Кимберли в SQL Server Magazine: What Happens if I Drop a Clustered Index?). Ссылка принимает одну из форм:

• если таблица — куча, то это фактическое физическое расположение записи в таблице (файл данных:номер страницы:номер записи);
• если у таблицы есть кластерный индекс, то это ключ(и) кластеризации.

Ссылка в конце этой статьи объясняет это куда подробнее.

Если вы создаёте кластерный индекс, все связи на записи кучи перестают быть валидными, и значит, все некластерные индексы должны быть автоматически перестроены, чтобы перейти на ссылки по ключу кластеризации. Если вы затем удаляете кластерный индекс, эти ссылки опять становятся недействительными, и все некластерные индексы снова должны быть автоматически перестроены, чтобы перейти на ссылки по физическому расположению записей в куче.

Иными словами: создав, а затем удалив кластерный индекс, вы заставляете все некластерные индексы перестроиться дважды. Неприятно.

Если вам кажется, что можно воспользоваться ALTER TABLE … REBUILD, чтобы исправить фрагментацию кучи — можно, но это тоже приводит к перестройке всех некластерных индексов, поскольку меняются расположения записей в куче.

А что, если вы перестраиваете кластерный индекс? Тут всё зависит от версии и от того, делаете ли вы простую перестройку или меняете определение индекса. Частое заблуждение — будто перенос кластерного индекса или его секционирование меняют ключи кластеризации. Это не так. Полный перечень случаев, когда некластерные индексы нужно перестраивать, приведён здесь: Indexes From Every Angle: What happens to non‑clustered indexes when the table structure is changed?