Помилково розглядати базу даних як якусь еталонну одиницю, оскільки, з плином часу, можуть проявлятися різного роду небажані ситуації - деградація продуктивності, збої в роботі та інше.
Для мінімізації виникнення таких ситуацій створюють плани обслуговування із завдань, які гарантують стабільність і оптимальну продуктивність бази даних.
Серед подібних завдань можна виділити наступні:
Розглянемо по порядку автоматизацію кожної з цих завдань.
Отже, пункт перший ...
Крім фрагментації файлової системи і лог-файлу, відчутний вплив на продуктивність бази даних надає фрагментація всередині файлів даних:
1. Фрагментація всередині окремих сторінок індексу
Після операцій вставки, оновлення та видалення записів неминуче виникають порожні простору на сторінках. Нічого страшного в цьому немає, оскільки дана ситуація цілком нормальна, якби не одне але ...
Дуже важливу роль відіграє довжина рядка. Наприклад, якщо рядок має розмір, який займає більше половини сторінки, вільна половина цієї сторінки не буде використовуватися. В результаті при збільшенні числа рядків буде спостерігатися зростання невикористаного місця в базі даних.
Боротися з даним видом фрагментації стоїть на етапі проектування схеми, т. Е. Вибирати такі типи даних, які б компактно вміщалися на сторінках.
2. Фрагментація всередині структур індексу
Основна причина виникнення цього виду фрагментації - операції розбиття статті. Наприклад, відповідно до структури первинного ключа, новий рядок необхідно вставити на певну сторінку індексу, але цієї на сторінці недостатньо місця, щоб розмістити вставляються дані.
В такому випадку, створюється нова сторінка, на яку переміститися приблизно половина записів зі старої сторінки. Нова сторінка, часто не є фізично суміжній зі старою і, отже, позначається системою як фрагментована.
У будь-якому випадку, фрагментація веде до зростання числа сторінок для зберігання того ж обсягу інформації. Це автоматично призводить до збільшення розміру бази даних і зростання невикористаного місця.
При виконанні запитів, в яких йде звернення до фрагментованість індексам, потрібно більше IO операцій. Крім того, фрагментація накладає додаткові витрати на пам'ять самого сервера, якому доводиться зберігати в кеші зайві сторінки.
Для запобігання фрагментації в арсеналі SQL Server-а передбачені команди по реорганізації і перебудови індексів.
Перебудова індексу має на увазі видалення старого і створення нового екземпляра індексу, в якому дані на сторінках акуратно стиснуті і організовані максимально безперервно, наскільки це взагалі можливо. Важливо відзначити, що операція по перебудові індексу вельми затратна.
Тому, в разі, коли фрагментація незначна, переважно реорганізовувати існуючий індекс. Дана операція вимагає менших системних ресурсів, ніж пересозданіе індексу і полягає в реорганізації leaf-level сторінок. Крім того реорганізація при можливості стискає сторінки індексів.
Ступінь фрагментації того чи іншого індексу можна дізнатися з динамічного системного уявлення sys.dm_db_index_physical_stats:
В даному запиті, останній параметр задає режим, від значення якого можливе швидке, але не зовсім точне визначення рівня фрагментації індексу (режими LIMITED / NULL). Тому рекомендується ставити режими SAMPLED / DETAILED.
Ми знаємо звідки отримати список фрагментованих індексів. Тепер необхідно для кожного з них згенерувати відповідну ALTER INDEX команду. Традиційно для цього використовують курсор:
Щоб прискорити процес перевтілення індексу рекомендується додатково вказувати опції SORT_IN_TEMPDB і ONLINE.
Перша дозволяє перебудовувати індекси в базі tempdb, що буває особливо корисно для великих індексів в разі нестачі пам'яті і іншому випадку - опція ігнорується. Крім того, якщо база tempdb розташована на іншому диску - це істотно скоротить час створення індексу. Друга опція дозволяє перебудувати індекс НЕ блокуючи при цьому запити до об'єкту для якого цей індекс створюється.
Як показала практика, дефрагментірованіе індексів з низьким ступенем фрагментації або з невеликою кількістю сторінок не приносить яких-небудь помітних поліпшень, що сприяють підвищенню продуктивності при роботі з ними.
У доповненні, наведений вище запит можна переписати без застосування курсору:
В результаті обидва запити при виконанні будуть генерувати запити по дефрагментації проблемних індексів:
Власне, на цьому перша частина зі створення плану обслуговування для бази даних виконана. У наступній частині ми займемося написанням запиту для автоматичного оновлення статистики.
Це пов'язано з тим, що можуть бути (і повинні бути) тригери, які спрацьовують при зміні певної таблички. Наприклад є таблиця з довідником - spr, у довідника багато полів. Як можна відстежити, що конкретний користувач що конкретно змінив? Зробити тригер який би в таблицю spr_his писав все зміни.
1) Ну і до чого тут перебудова індексів? Хто вам заважає перебудовувати індекси при наявності тригера, ви ж не думаєте, що він буде при цьому спрацьовувати? ;)
2) Тригери нікому нічого не винні. Ну тільки хіба що у вас 2-звенка. А у мене все зміни даних в базі проходять через шар сервера додатків, звідти і логгірую (окремими запитами), якщо що мені потрібно.
WITH ONLINE = ON. Друга опція дозволяє перебудувати індекс НЕ блокуючи при цьому запити до об'єкту для якого цей індекс створюється. Тут маються на увазі лише SELECT запити.
2) Тут справа смаку. Для мене централізоване зберігання логіки набагато зручніше / практичніше / швидше в обслуговуванні і т.д. і т.п. Просто ті ж «хранімкі» потрібно застосовувати не повсюдно, а лише там де це буде потрібніше / необхідніше / простіше.
Ніякої відсебеньок. Ви вирвали цю фразу з контексту в параграфі про продуктивність. Там якраз сказано, що ресурсів на UPDATE / INSERT / DELETE витрачається набагато більше. Знаєте чому? Тому що всі ці запити ставляться в чергу в буфер і тільки після перестроювання індексу ці зміни вносяться в базу. Тому використання цієї опції може сильно навантажити сервер.
дозвольте поцікавитися: під який проект потрібно так обробляти базу і який її вага на диску?
PS: у себе в конторі максимальний ефект дала установки нормальної дискової полки (багато-багато шпинделів, пара контролерів, кеш з батарейкою, рейд.). Всякі дефрагментації індексів і очищення статистики давали збільшення продуктивності на величину, умещающуюся в рамки похибки. Сервер MSSQL для 1С
Тут швидше важливий не розмір бази, а розмір окремих таблиць. які мають великий розмір і неприємну особливість - в них часто змінюються дані. Саме для таких таблиць - доцільно робити дефрагментацію індексів.
Час вказано в тому часовому поясі, який встановлений на вашому телефоні.