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

У меня есть большая база данных (данные 90 ГБ, индексы 70 ГБ), которая медленно росла в течение прошлого года, и этот рост / изменения вызвали большую внутреннюю фрагментацию не только индексов, но и самих таблиц.

Очень легко разрешить (большое количество) очень фрагментированные индексы - об этом позаботятся REORGANIZE или REBUILD, в зависимости от того, насколько они фрагментированы, - но единственный совет, который я могу найти при очистке фактической фрагментации таблицы, - добавить кластеризованный индекс к столу. Я бы сразу же отбросил его, так как не хочу, чтобы кластеризованный индекс в таблице шел вперед, но есть ли другой способ сделать это без кластерного индекса? Команда "DBCC", которая сделает это?

Спасибо за вашу помощь.

5 ответов

Решение

проблема

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

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

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

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

  • отвал
    плюс все некластеризованные индексы
    плюс текстовые / графические цепочки

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

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

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

Решение

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

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

Заметка

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

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

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

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

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

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

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

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

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

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

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

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

  • Уровень I • Extra-DataStructure
    Вне рассматриваемой DataStructure, через или внутри базы данных.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Другие вопросы по тегам