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

Проблема

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

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

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

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

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

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

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

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

Решение

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

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

Примечание

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

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

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

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

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

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

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

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

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

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

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

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

• Уровень I • Дополнительная структура данных
  Вне соответствующей структуры данных, в базе данных или внутри нее.

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

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

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

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

• Влияние фрагментации

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

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

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

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

Может ли это помочь вам?

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

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

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

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

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

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

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

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

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

Эта статья написана Microsoft

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

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

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

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