Забудьте о  SQL Server  фрагментации

Я уверен, что тебя беспокоит фрагментация твоего индекса.

И я готов поспорить, что ты тратишь свое время впустую, упуская суть.

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

Давай сделаем шаг назад и представим, что твоя база данных — это телефонный справочник, организованный по фамилии, имени.

По мере того как люди переезжают в твой город, нам приходится добавлять их в телефонную книгу. Идеально было бы, чтобы каждая страница имела немного свободного пространства, и мы управляем этим с помощью коэффициента заполнения. Когда SQL Server перестраивает индексы, он использует коэффициент заполнения, чтобы решить, сколько свободного места оставить на каждой странице. Если свободного места недостаточно, SQL Server вынужден производить некоторые манипуляции — но он не может просто вставить совершенно новую страницу прямо посередине телефонного справочника. Книга уже переплетена. Нам придется прикреплять дополнительные пустые страницы в конце.

Проблема №1 — Внутренняя фрагментация: У нас есть новая добавленная страница, на которой почти ничего нет.

Проблема №2 — Внешняя фрагментация: Страницы телефонной книги расположены в неправильном порядке.

Как фрагментация ухудшает производительность SQL Server 

Плохая внутренняя фрагментация (наличие большого количества свободного пространства на страницах) означает, что индекс больше, чем необходимо. Вместо того чтобы наша телефонная книга содержала 1000 страниц, заполненных на 100%, у нас может оказаться 1100 страниц, заполненных только на 90%. Это значит, что каждый раз, когда нам нужно сканировать индекс, это займет на 10% больше времени (1100 вместо 1000 страниц). Это также означает, что нам потребуется больше памяти для кеширования такого же объема данных — потому что SQL Server должен кэшировать пустое пространство на каждой странице.

Наша наименьшая единица кеширования — одна страница, а не запись. Плохая внешняя фрагментация (переставленные страницы на диске) означает, что производительность нашего хранилища может снизиться. Если магнитные жесткие диски вынуждены прыгать между разными областями диска, их производительность страдает — значительно. Возьмем, например, обзор корпоративного накопителя Toshiba от StorageReview — он достигает скорости около 200 МБ/сек для больших последовательных чтений, но менее 2 МБ/сек для случайных чтений. Ой-ой.

Временное исправление фрагментации индекса

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

Это проблема, поскольку перестройка и дефрагментация индексов заставляют SQL Server записывать информацию в журнал транзакций. Чем больше мы пишем в журналы, тем дольше занимают резервные копии журналов, тем больше данных мы должны передавать через сетевое соединение для зеркального отображения базы данных или отправки журнала транзакций, и тем дольше длится восстановление.

Мы можем даже нанести ещё больший ущерб. Некоторые администраторы баз данных решают устранить фрагментацию, установив низкий коэффициент заполнения, скажем, 50%. При таком подходе половина каждой страницы останется пустой, поэтому вставки будут выполняться молниеносно быстро. Чтение, однако, станет вдвое медленнее. Чтобы просмотреть всю телефонную книгу, нам пришлось бы прочитать в два раза больше страниц. Настройка коэффициента заполнения — опасный танец, похожий на те, которые я танцевал в старшей школе. (Правдивая история: сломал нос во время слэм-данса.)Мы могли бы принудительно оставлять больше пустого пространства на каждой странице всякий раз, когда выполняем перестроение, тогда как на самом деле этого делать не нужно.

Навсегда устраните фрагментацию индекса

Начните с попытки закэшировать вашу базу данных — или, по крайней мере, те данные, к которым осуществляется частый доступ, — в памяти.Внешняя фрагментация (страницы вне порядка на диске) не столь важна, когда нам изначально не нужно обращаться к дискам. Разница между пропускной способностью физически фрагментированного диска и физически нефрагментированного диска ничтожна по сравнению со скоростью работы памяти и диска. Просто закэшируйте данные и забудьте об этом — 384 ГБ оперативной памяти стоят всего 5–6 тысяч долларов.

Далее изучите, что именно делает ваша система хранения с вашими данными. Если вы используете общую систему хранения, такую как оборудование EMC, NetApp или Dell, которое распределяет диски между несколькими серверами, то весь ваш доступ к диску в любом случае будет случайным. Ваши жесткие диски используются совместно с другими серверами, которые также отправляют запросы к диску одновременно, поэтому диски постоянно перемещаются туда-сюда, чтобы получить данные. Дефрагментация ваших индексов — это просто бессмысленная работа.

Узнайте, чего ждут ваши запросы, и определите, какую проблему вы пытаетесь решить на самом деле. Во время проверок состояния наших серверов SQL Server мы редко говорим: «Ах да, вот в чём проблема — ваши данные фрагментированы». Мы, администраторы баз данных, привыкли дефрагментировать наши базы данных до последней возможности, потому что это одна из немногих проблем, которую мы можем легко решить, и увидеть простые числа, чтобы узнать, стали ли наши индексы менее фрагментированными. Найдите истинную первопричину и затем атакуйте её.

А если выяснится, что корень проблемы действительно кроется в фрагментированных индексах — и это вполне возможно — используйте бесплатные скрипты для обслуживания баз данных от Олы Халленгрена. Они проверяют уровень фрагментации каждого индекса перед началом работы и затем выполняют дефрагментацию или перестроение только тех индексов, которые соответствуют вашим пороговым значениям. Это позволяет минимизировать активность лог-файлов, достигнув при этом желаемого улучшения производительности.