1. оновлення статистик
2. Очищення процедурного кешу
3. Дефрагментація індексів
4. Реіндексація таблиць бази даних

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

Якщо в працюючій системі спостерігаються будь-які симптоми проблем з продуктивністю, слід перевірити, що в системі правильно налаштовані і регулярно виконуються всі рекомендовані регламентні операції на рівні СУБД.

Виконання регламентних процедур повинно бути автоматизовано. Для автоматизації цих операцій рекомендується використовувати вбудоване кошти MS SQL Server: Maintenance Plan. Існують також інші способи автоматизації виконання цих процедур. У цій статті для кожної регламентної процедури дан приклад її налаштування за допомогою Maintenance Plan для MS SQL Server 2005.

Для MS SQL Server рекомендується виконувати наступні регламентні операції:

Оновлення статістікОчістка процедурного КЕШаДефрагментація індексовРеіндексація таблиць бази даних

Рекомендується регулярно контролювати своєчасність і правильність виконання даних регламентних процедур.

оновлення статистик

MS SQL Server будує план запиту на підставі статистичної інформації про розподіл значень в індексах і таблицях. Статистична інформація збирається на підставі частини (зразка) даних і автоматично оновлюється при зміні цих даних. Іноді цього виявляється недостатньо для того, що MS SQL Server стабільно будував найбільш оптимальний план виконання всіх запитів.

У цьому випадку можливо прояв проблем з продуктивністю запитів. При цьому в планах запитів спостерігаються характерні ознаки неоптимальною роботи (неоптимальні операції).

Для того, щоб гарантувати максимально правильну роботу оптимізатора MS SQL Server рекомендується регулярно оновлювати статистики бази даних MS SQL.

Для поновлення статистик по всіх таблиць бази даних необхідно виконати наступний SQL запит:

Оновлення статистик не призводить до блокування таблиць, і не буде заважати роботі інших користувачів. Статистика може оновлюватися настільки часто, наскільки це необхідно. Слід враховувати, що навантаження на сервер СУБД під час поновлення статистик зросте, що може негативно позначитися на загальній продуктивності системи.

Оптимальна частота оновлення статистик залежить від величини і характеру навантаження на систему і визначається експериментальним шляхом. Рекомендується оновлювати статистики не рідше одного разу в день.

Наведений вище запит оновлює статистики для всіх таблиць бази даних. В реально працюючій системі різні таблиці вимагають різної частоти поновлення статистик. Шляхом аналізу планів запиту можна встановити, які таблиці більше за інших мають потребу в частому оновленні статистик, і налаштувати дві (або більше) різних регламентних процедури: для часто оновлюваних таблиць і для всіх інших таблиць. Такий підхід дозволить істотно знизити час поновлення статистик і вплив процесу оновлення статистики на роботу системи в цілому.

Автоматичне оновлення статистик (MS SQL 2005)

Запустіть MS SQL Server Management Studio та підключіться до сервера СУБД. Відкрийте папку Management і створіть новий план обслуговування:

Створіть субплан (Add Sublan) і назвіть його «Оновлення статистик». Додайте в нього завдання Update Statistics Task з панелі завдань:

Налаштуйте розклад поновлення статистик. Рекомендується оновлювати статистики не рідше одного разу в день. При необхідності частота оновлення статистик може бути збільшена.

Налаштуйте параметри завдання. Для цього слід два рази клікнути на завдання в правому нижньому кутку вікна. У формі, що з'явилася вкажіть ім'я базу даних (або кілька баз даних) для яких буде виконуватися оновлення статистик. Крім цього ви можете вказати для яких таблиць оновлювати статистики (якщо точно невідомо, які таблиці потрібно вказати, то встановлюйте значення All).

Оновлення статистик необхідно проводити з включеною опцією Full Scan.

Збережіть створений план. При настанні зазначеного в розкладі терміну оновлення статистик буде запущено автоматично.

Очищення процедурного кешу

Оптимізатор MS SQL Server кешируєт плани запитів для їх повторного виконання. Це робиться для того, щоб економити час, що витрачається на компіляцію запиту в тому випадку, якщо такий самий запит вже виконувався і його план відомий.

Можлива ситуація, при якій MS SQL Server, орієнтуючись на застарілу статистичну інформацію, побудує неоптимальний план запиту. Цей план буде збережений в процедурному кеші і використаний при повторному виклику такого ж запиту. Якщо Ви оновили статистику, але не очистили процедурний кеш, то SQL Server може вибрати старий (неоптимальний) план запиту з кешу замість того, щоб побудувати новий (більш оптимальний) план.

Таким чином, рекомендується завжди після поновлення статистик очищати вміст процедурного кешу.

Для очищення процедурного кешу MS SQL Server необхідно виконати наступний SQL запит:

Цей запит слід виконувати безпосередньо після поновлення статистики. Відповідно, частота його виконання повинна збігатися з частотою оновлення статистики.

Налаштування очищення процедурного кешу

для (MS SQL 2005)

Оскільки процедурний КЕШ необхідно очищати при кожному оновленні статистики, дану операцію рекомендується додати в уже створений субплан «Оновлення статистик». Для цього слід відкрити субплан і додати в його схему завдання Execute T-SQL Statement Task. Потім слід з'єднати завдання Update Statistics Task стрілочкою з новим завданням.

У тексті створеної завдання Execute T-SQL Statement Task слід вказати запит «DBCC FREEPROCCACHE»:

Дефрагментація індексів

При інтенсивній роботі з таблицями бази даних виникає ефект фрагментації індексів, який може привести до зниження ефективності роботи запитів.

Рекомендується регулярне виконання дефрагментації індексів. Для дефрагментації всіх індексів усіх таблиць бази даних необхідно використовувати наступний SQL запит (попередньо підставивши ім'я бази):

Дефрагментація індексів не блокує таблиці, і не буде заважати роботі інших користувачів, однак створює додаткове навантаження на SQL Server. Оптимальна частота виконання даної регламентної процедури має обиратися згідно з навантаженням на систему і ефектом, одержуваних від дефрагментації. Рекомендується виконувати дефрагментацію індексів не рідше одного разу в день.

Можливо запуск програми дефрагментації для однієї або декількох таблиць, а не для всіх таблиць бази даних.

Налаштування дефрагментації індексів (MS SQL 2005)

В раніше створеному плані обслуговування створіть новий субплан з ім'ям «Дефрагментація індексів». Додайте в нього завдання Reorganize Index Task:

Задайте розклад виконання для завдання дефрагментації індексів. Рекомендується виконувати завдання не рідше одного разу на тиждень, а при високій мінливості даних в базі ще частіше - до одного разу на день.

Налаштуйте завдання, вказавши базу даних (або кілька баз даних) і вибравши необхідні таблиці. Якщо ви не знаєте, які таблиці слід зазначити, то встановлюйте значення All.

Реіндексація таблиць включає повне перестроювання індексів таблиць бази даних, що призводить до суттєвої оптимізації їх роботи. Рекомендується виконувати регулярну переіндексацію таблиць бази даних. Для реіндексація всіх таблиць бази даних необхідно виконати наступний SQL запит:

Реіндексація таблиць блокує їх на весь час своєї роботи, що може істотно позначитися на роботі користувачів. У зв'язку з цим реіндексацію рекомендується виконувати під час мінімального завантаження системи.

Після виконання реіндексація немає необхідності робити дефрагментацію індексів.

В раніше створеному плані обслуговування створіть новий субплан з ім'ям «Дефрагментація індексів». Додайте в нього завдання Rebuild Index Task:

Задайте розклад виконання для завдання реіндексірованія таблиць. Рекомендується виконувати завдання під час мінімального навантаження на систему, не рідше одного разу в тиждень.

Налаштуйте завдання, вказавши базу даних (або кілька баз даних) і вибравши необхідні таблиці. Якщо ви не знаєте, які таблиці слід зазначити, то встановлюйте значення All.

Реіндексація таблиць бази даних

Необхідно здійснювати регулярний контроль виконання регламентних процедур на рівні СУБД. Нижче наведено приклад контролю виконання плану обслуговування для MS SQL Server 2005.

Відкрийте створений вами план обслуговування і виберіть з контекстного меню пункт «View History»:

Відкриється вікно з протоколом виконання всіх заданих регламентних процедур.

Успішно виконані завдання і завдання, виконані з помилками, будуть позначені відповідними іконками. Для завдань, виконаних з помилками, доступна докладна інформація про помилку.

Джерело: Регламентні операції MS SQL Server. оптимізація роботи