Дефрагментація файлів SQL Server за допомогою утиліти DISKEEPER
Бази даних SQL Server, згодом піддаються внутрішньої фрагментації. Це відбувається, коли записи зі сторінок бази даних видаляються, і місце, яке вони займали, вивільняється. Зрештою, це місце багаторазово використовується, внаслідок чого сторінки даних стають фізично фрагментованими, що може призвести до збільшення операцій вводу/виводу, особливо у разі сканування таблиці, при якому читається багато сторінок даних одна за одною.
У SQL Server є кілька шляхів подолання внутрішньої фрагментації. Один із цих методів полягає в тому, щоб використовувати команду DBCC REINDEX для перебудови кластеризованих та некластеризованих індексів. Після перебудови індексів, сторінки даних стають логічно безперервними, і дискове введення/виведення мінімізовано. На жаль, внутрішня фрагментація – це лише частина проблеми фрагментації. Виконання DBCC REINDEX не позначається на зовнішній фрагментації.
Зовнішня фрагментація – це фрагментація фізичних файлів на дисках вашого сервера, яка може викликати таку ж велику кількість непотрібних операцій вводу/виводу, як і внутрішня, якщо не більше. Непотрібні операції введення-виводу призводять до зниження продуктивності SQL Server.
Бази даних SQL Server є великими файлами бази даних та журналів, для яких під час створення резервується певний розмір. Якщо під час створення цих файлів на диску є безперервний, не зайнятий і достатній за розміром відрізок, вони фрагментовані. Але якщо доступне вільне місце не є безперервним, то спочатку база даних і журнали будуть фрагментовані. Навіть якщо спочатку база даних та журнали не фрагментовані, після їх створення вони майже напевно станутьфрагментованими, оскільки база даних постійно зростає. Наприклад, якщо Ви встановлюєте початковий розмір бази даних 100 МБ, а файл журналу 10 МБ, і встановили такі параметри автоматичного приросту файлів: до 5Гб файл даних і до 100 МБ файл журналу, зовнішня фрагментація може бути великою. Щоразу, коли файли даних або журналів автоматично збільшуються, виникає загроза зовнішньої фрагментації.
Є два типи зовнішньої фрагментації, з якою можуть впоратися утиліти, подібні до Diskeeper: файлова фрагментація та фрагментація вільного простору. Файлова фрагментація стосується файлу на диску комп'ютера, коли фізично файл лежить не одним шматком, а поділений на кілька фрагментів, які розкидані по всьому диску; у той час як фрагментація вільного простору означає, що порожнє місце на диску не лежить одним великим шматком, а також роздроблене безліч частин. Файлова фрагментація призводить до проблем із доступом до даних файлу, збереженого на диску комп'ютера, у той час як фрагментація вільного простору призводить до проблем при створенні нових файлів даних або під час зростання старих. Робота утиліти Diskeeper призводить до дефрагментації файлів даних і журналів, і таким чином файл фізично займає безперервний простір у пам'яті, замість того, щоб бути розбитим на частини. Крім того, утиліта Diskeeper дефрагментує вільний простір за рахунок чого зростання файлів даних або журналів викликає лише невелику фрагментацію або не викликає фрагментації зовсім. Але такий стан речей не триває вічно. У результаті фрагментація знову стає проблемою, і файли даних та журналів необхідно знову дефрагментувати. В ідеалі дефрагментація має виконуватися регулярно.
Тепер ще дещо, про що Ви ймовірно ранішене замислювалися. Чи знаєте ви який ефект має фізична фрагментація файлу на SQL Server при перестворенні індексу? Іншими словами, якщо ви не виконуєте фізичну дефрагментацію, але усуваєте внутрішню фрагментацію, чи це стане перешкодою для переіндексації? Так, це цілком може стати на заваді. Оскільки фізично файли фрагментовані, і SQL Server знадобиться набагато більше часу, щоб відновити індекси у фрагментованих файлах, ніж у безперервних файлах. Таким чином, перш ніж виконувати внутрішню дефрагментацію, бажано спочатку було б виконати фізичну дефрагментацію. Це дозволить зменшити час перестворення індексу, а також зменшить кількість операцій введення/виводу на сервері протягом процесу перестворення індексу.
Окрім того, що фізична фрагментація може мати негативний вплив на продуктивність при роботі з файлами даних та журналами SQL Server, необхідно пам'ятати що є й інші файли, до яких SQL Server має доступ, це файли SQL Server, що виконуються, і файли повнотекстових індексів, якщо такі використовуються . Таким чином, бажано дефрагментувати не лише файли даних та журналів, але й усі файли, розташовані на сервері, де запущено SQL Server.
Усіми переміщеннями файлів під час роботи утиліти Diskeeper під час дефрагментації безпосередньо управляє операційна система. Фактично, код операційної системи, що виконує цю функцію, який спочатку був написаний Executive Software, розподіляє за пріоритетами безпеки, що може бути дефрагментовано, а що ні. Файли SQL Server (наприклад, LDF і MDF) абсолютно безпечно дефрагментувати. Якщо в той момент, коли Diskeeper надсилає запит операційній системі (за допомогою програмного інтерфейсу – API) на переміщення файлів, то йомутрапляться файли, які можуть бути безпечно переміщені, він через них просто перескакує без повідомлень про помилку чи інших сигналів.
Як дізнатися, що файли вашого SQL Server фізичні фрагментовані? На щастя, це просто. Однією з функціональних можливостей Diskeeper є аналіз фрагментації, за допомогою якого можна подивитися фрагментацію файлів, що відносяться до SQL Server. Як і дефрагментація, цю процедуру можна виконувати під час роботи SQL Server.
Вам може здатися, що буде важко вибрати певний розклад дефрагментації, оскільки різні бази даних можуть відрізнятися за рівнем фрагментації. На цей випадок в утиліту Diskeeper вбудований динамічний планувальник, який називається Smart Scheduling, і який визначає та автоматизує відповідні завдання з дефрагментації. Також передбачена можливість обмеження часу роботи та ресурсів для завдань дефрагментації, що запускаються під управлінням Smart Scheduling.
Таким чином, нескладно зробити висновок, що утиліти дефрагментації, подібні до Diskeeper, можуть допомогти знизити зовнішню фрагментацію файлів на дисках, у той час як такі засоби SQL Server, як оператор DBCC REINDEX, можуть допомогти у боротьбі з внутрішньою фрагментацією сторінок файлів SQL Server. Вони можуть працювати разом, гарантуючи оптимальну продуктивність SQL Server.
Якщо є сумніви, просто встановіть Diskeeper і запустіть його функцію аналізу, це дозволить Вам дізнатися, скільки частин розбиті ваші файли. Я впевнений, що ви будете дуже здивовані отриманими результатами. Я бачив звіти з сайтів, у яких файли бази даних були розбиті більш ніж на 287 000 частин.