Уменьшить фрагментацию таблицы SQL Server без добавления/удаления кластерного индекса?

Ответ 1

Проблема

Позвольте получить некоторую ясность, потому что это общая проблема, серьезная проблема для каждой компании, использующей SQL Server.

Эта проблема, и необходимость CREATE CLUSTERED INDEX, неправильно понята.

Согласился, что наличие постоянного кластерного индекса лучше, чем отсутствие его. Но дело не в этом, и в любом случае это приведет к длительной дискуссии, поэтому отложите это и сосредоточьтесь на заданном вопросе.

Дело в том, что у вас есть существенная фрагментация в Куче. Вы продолжаете называть это "таблицей", но на физическом хранилище данных или уровне DataStructure такого не происходит. Таблица представляет собой логическое понятие, а не физическое. Это набор физических DataStructures. Коллекция является одной из двух возможностей:

• Heap
  плюс все некластеризованные индексы
  плюс цепочки текста/изображения

• или Кластеризованный индекс
  (исключает кучу и один некластеризованный индекс)
  плюс все некластеризованные индексы
  плюс цепочки текста/изображения.

Кучи сильно фрагментированы; тем больше фрагментированных (случайных) Вставка/Удаление/Обновления есть, тем больше фрагментация.

Невозможно очистить кучу, как есть. MS не предоставляет средства (другие производители делают).

Решение

Однако мы знаем, что Create Clustered Index полностью перезаписывает и перезаписывает кучу. Таким образом, метод (а не трюк) заключается в создании кластерного индекса только с целью дефрагментации кучи и последующего его удаления. Вам нужно свободное пространство в db table_size x 1.25.

Пока вы на нем, во что бы то ни стало, используйте FILLFACTOR, чтобы уменьшить будущую фрагментацию. Затем куча займет больше выделенного пространства, что позволит в будущем вставлять, удалять и расширять строки из-за обновлений.

Примечание

• Обратите внимание, что есть три Уровня фрагментации; это касается только уровня III, фрагментации внутри кучи, вызванного Недостатком кластерного индекса

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

Ответ на комментарий

SqlRyan:
Хотя это не дает мне волшебного решения моей проблемы, довольно ясно, что моя проблема связана с ограничением SQL Server и добавлением кластерного индекса является единственным способом "дефрагментации" кучи.

Не совсем. Я бы не назвал это "ограничением".

• Метод, который я дал для устранения фрагментации в куче, заключается в создании кластерного индекса, а затем его удалении. То есть. временно, единственной целью которого является правильная фрагментация.

• Внедрение кластеризованного индекса в таблице (на постоянной основе) является гораздо лучшим решением, поскольку оно уменьшает общую фрагментацию (DataStructure все еще может получить фрагментированный, см. подробную информацию в ссылках ниже), что намного меньше, чем фрагментация что происходит в куче.

  • Каждая таблица в реляционной базе данных (за исключением таблиц "pipe" или "queue" ) должна иметь кластерный индекс, чтобы воспользоваться преимуществами различных преимуществ.

  • Кластерный индекс должен быть в столбцах, которые распределяют данные (избегая конфликтов INSERT), никогда не индексируются на монотонно возрастающем столбце, таком как Record ID ¹ что гарантирует INSERT Hot Пятно на последней странице.

^{1. Идентификаторы записей в каждом файле делают вашу "базу данных" нереляционной системой записи записей, используя SQL просто для удобства. Такие файлы не имеют базы данных Integrity, Power или Speed.}

Эндрю Хилл:
вы могли бы прокомментировать дальше: "Обратите внимание, что есть три Уровня Фрагментации, это касается только Уровня III" - каковы два других уровня фрагментации?

В MS SQL и Sybase ASE есть три уровня фрагментации и на каждом уровне несколько разных типов. Имейте в виду, что при работе с фрагментацией мы должны сосредоточиться на DataStructures, а не на таблицах (таблица представляет собой набор DataStructures, как объяснялось выше). Уровни:

• Уровень я • Экстра-DataStructure
  Вне соответствующей DataStructure, через или внутри базы данных.

• Уровень II • DataStructure
  В соответствующей структуре DataStructure, выше Страницы (по всем страницам)
  Это уровень, наиболее часто используемый администраторами баз данных.

• Уровень III • Страница
  В рамках соответствующей Структуры данных в пределах страниц

Эти ссылки обеспечивают полную детализацию фрагментации. Они специфичны для Sybase ASE, однако на структурном уровне информация относится к MS SQL.

• Определение фрагментации

• Эффект фрагментации

• Тип фрагментации

Обратите внимание, что метод, который я дал, - это уровень II, он исправляет фрагментацию уровня II и III.

Ответ 2

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

Кластерный индекс удаляет фрагментацию путем сортировки по ключу кластера, но вы говорите, что этот ключ не будет использоваться для будущего использования. Это вызывает вопрос: зачем дефрагментировать этот ключ вообще?

Было бы разумно создать этот кластерный ключ и сохранить его, поскольку вам явно нужны/нужны данные, отсортированные таким образом. Вы говорите, что изменения данных будут нести ответственность за перенос данных, которые не могут быть перенесены; подумали ли вы о создании индекса с более низким значением FILLFACTOR, чем значение по умолчанию? В зависимости от моделей изменения данных вы можете выиграть от 80%. Затем у вас есть 20% "неиспользуемого" места на странице, но преимущество нижней страницы разбивается при изменении значений кластеризованных ключей.

Помогло ли вам это?

Ответ 3

Возможно, вы можете сжать кучу, запустив DBCC SHRINKFILE с NOTRUNCATE.

Основываясь на комментариях, я вижу, что вы не тестировали с помощью кластерного индекса.

Чтобы представить это в перспективе, у нас есть база данных с 10 миллионами новых строк в день с кластеризованными индексами во всех таблицах. Удаленные "пробелы" будут удалены через запланированный ALTER INDEX (а также форвардные указатели/разбиения страниц).

Ваша таблица 12 ГБ может быть 2 ГБ после индексации: она имеет только 12 ГБ, но также массово фрагментирована.

Ответ 4

Я понимаю вашу боль в том, что она ограничена дизайном устаревшего дизайна.

У вас есть возможность восстановить резервную копию таблицы на другом сервере и создать кластеризованный индекс? Очень вероятно, что кластеризованный индекс, созданный в наборе узких уникальных столбцов или столбца идентификации, уменьшит размер общей таблицы (данных и индекса).

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

Еще один недостаток наличия кучи - это дополнительный IO, связанный с любыми строк.

Я нашел статью ниже эффективной, когда меня спросили, существует ли какой-либо ДОКАЗАТЕЛЬ, что нам нужен скопированный индекс постоянно в таблице

Эта статья от Microsoft

Ответ 5

Проблема, о которой никто не говорит, - ФРАГМЕНТАЦИЯ ФАЙЛОВ ДАННЫХ ИЛИ ЛОГОТИПОВЫХ УСТРОЙСТВ НА ЖЕСТКОЙ ПРИВОДЕ! Все говорят о фрагментации индексов и о том, как избежать/ограничить эту фрагментацию.

FYI: Когда вы создаете базу данных, вы указываете INITIAL размер .MDF вместе с тем, насколько он будет расти, когда он должен расти. Вы делаете то же самое с файлом .LDF. НЕ ГАРАНТИРОВАНО, КОГДА ЭТИ ДВЕ ФАЙЛЫ РОСТА, ЧТО ПРОСТРАНСТВО ДИСКА, РАСПРОСТРАНЕННОЕ ДЛЯ ДОСТАТОЧНОГО ПРОСТРАНСТВА ДИСКА НЕОБХОДИМО, БУДЕТ ФИЗИЧЕСКИ КОНТРОЛЬНО С СУЩЕСТВУЮЩИМ ПРОСТРАНСТВЕННЫМ ПРОСТРАНСТВОМ!

Каждый раз, когда один из этих двух файлов устройств должен расширяться, существует возможность фрагментации дискового пространства на жестком диске. Это означает, что голова на жестком диске должна работать более интенсивно (и занимать больше времени) для перехода от одной части жесткого диска в другой раздел для доступа к необходимым данным в базе данных. Это аналогично покупке небольшого участка земли и построению дома, который просто подходит на этой земле. Когда вам нужно расширить дом, у вас больше нет земли, если вы не купите пустую партию по соседству - кроме того, что, если кто-то еще тем временем уже купил эту землю и построил на ней дом? Тогда вы НЕ МОЖЕТЕ расширить свой дом. Единственная возможность - купить еще один участок земли в "окрестностях" и построить на нем еще один дом. Проблема в том, что вы и двое ваших детей будете жить в Доме А, а ваша жена и третий ребенок будут жить в Доме B. Это будет болью (пока вы все еще женаты).

Решение этой проблемы заключается в том, чтобы "купить гораздо больший участок земли, забрать существующий дом (т.е. базу данных), переместить его на более крупный участок земли, а затем расширить дом". Ну, как вы это делаете с базой данных? Сделайте полную резервную копию, отбросьте базу данных (если у вас нет свободного места на диске для хранения как старой фрагментированной базы данных - на всякий случай - так и новой базы данных), создайте новую базу данных с большим количеством исходного дискового пространства ( не гарантируйте, что операционная система будет гарантировать, что пространство, которое вы запрашиваете, будет смежным), а затем восстановить базу данных в новое созданное пространство базы данных. Да, это больно, но я не знаю ни одного "автоматического дефрагментатора диска", который будет работать с файлами базы данных SQL.