По кілька разів на тиждень у мене проcят ради, як ефективно обслуговувати робочу базу даних. Часом питання лунають із боку адміністраторів баз даних, які застосовують нові рішення і шукають допомоги в підгонці практики обслуговування під характеристики нових баз даних. Найчастіше питання лунають із боку людей, які не є професійними адміністраторами баз даних, але, по тій або іншій причині, володіють базою даних або несуть за неї відповідальність. Я вважаю за краще називати їх "мимовільними адміністраторами баз даних". Завдання цієї стати полягає в наданні підручника з найкращим варіантам обслуговування баз даних SQL Server.
Як і у випадку з більшістю завдань і процедур в світі інформаційних технологій, що годиться на будь-який випадок ефективного вирішення обслуговування баз даних не існує, але є кілька ключових областей, які майже завжди вимагають уваги. Моя особиста перша п'ятірка цих областей, перерахована без певного порядку важливості:
- Управління файлами даних і журналів
- фрагментація індексу
- Статистика
- виявлення пошкоджень
- Резервні копії
У необслуговуваної (або погано обслуговується) бази даних можуть виникнути проблеми в одній або декількох з цих областей, що з часом може призвести до поганої роботи додатків або навіть простоїв і втрати даних.
Управління файлами даних і журналів
Перша область, яку я завжди рекомендую перевірити при прийнятті управління базою даних - це параметри, пов'язані з управлінням файлами даних і журналу (транзакцій). Точніше говірці, слід переконатися, що:
- Файли даних і журналу відокремлені один від одного, а також ізольовані від всього іншого
- Автоматичне збільшення налаштований належним чином
- Налаштована автоматична ініціалізація файлів
- Автоматичне стиснення відключено, і стиск не є частиною будь-якого плану обслуговування
Коли файли даних і журналу (які, в ідеалі, взагалі повинні бути на різних томах) знаходяться в одному томі з будь-яким іншим додатком, який створює або розширює файли, виникає можливість для фрагментації файлів. У файлах даних надлишкова фрагментація файлів може давати невеликий внесок в погану роботу запитів (особливо тих, що переглядають дуже великі обсяг даних). У файлах журналів вона може завдати набагато серйозніший удар по продуктивності, особливо якщо автоматичне розширення встановлено лише на дуже невелике збільшення розміру файлу кожного разу, як в ньому виникає потреба.
Що стосується розмірів файлів даних і журналу, найкраще створювати їх з відповідним початковим розміром. Для файлів даних при виборі початкового розміру повинна прийматися до уваги можливість додавання до бази додаткових даних в найближчій перспективі. Наприклад, якщо початковий розмір даних дорівнює 50 ГБ, але відомо, що протягом наступних шести місяців буде додано ще 50 ГБ даних, має сенс відразу створити файл даних розміром в 100 ГБ замість того, щоб вирощувати його до цього розміру протягом кількох місяців.
З файлами журналів справа, на жаль, дещо складніша - необхідно враховувати такі фактори, як розмір транзакції (довгострокові транзакції не можуть бути видалені з журналу до свого завершення) і частоту резервного копіювання журналу (оскільки саме при ньому видаляються неактивні частини журналу). Додаткові відомості наведені в "8 Steps to Better Transaction Log Throughput (" 8 кроків до поліпшення пропускної спроможності журналу транзакцій ")", популярної записи в блозі на SQLskills.com, написаної моєю дружиною, Кімберлі Тріпп (Kimberly Tripp).
Після того, як розміри файлів встановлені, за ними слід спостерігати в різні моменти, і їх слід заздалегідь збільшувати вручну у відповідний час доби. Автоматичне збільшення слід залишити в якості заходи на всякий випадок, щоб файли могли зростати в міру необхідності в разі будь-якого незвичайної події. Причина, по якій управління файлами не слід цілком залишати автоматичного збільшення, полягає в тому, що автоматичне збільшення невеликими шматками веде до фрагментації файлів і що автоматичне збільшення може займати багато часу, гальмуючи роботу програми в несподівані моменти.
Розмір автоматичного збільшення слід встановити на певне значення, а не на відсоток, щоб обмежувати час і місце, необхідні для виконання автоматичного збільшення, якщо воно відбувається. Наприклад, в разі 100-гігабайтного файлу даних бажано зафіксувати розмір автоматичного збільшення як 5 ГБ, а не, скажімо, 10%. Це означає, що він завжди буде збільшуватися на 5 ГБ незалежно від поточного розміру файлу, а не на обсяг, що збільшується після кожного збільшення файлу (10 ГБ, 11 ГБ, 12 ГБ і так далі).
Автоматичне стиснення особливо шкідливо, оскільки воно запускається кожні 30 хвилин в фоновому режимі і намагається стискати бази даних, для яких виставлено параметр автоматичного стиснення. Цей процес не цілком передбачуваний в тому, що він стискає лише бази даних з більш ніж 25% вільного місця. Автоматичне стиснення використовує масу ресурсів і викликає знижувальну продуктивність фрагментацію, так що воно небажано при будь-яких обставинах. Його завжди слід відключати за допомогою:
План регулярного облуговування, що включає видачу базі даних команди на стиск вручну, майже настільки ж поганий. Якщо виявиться, що база даних постійно зростає після того, як план обслуговування стискає її, то це викликано тим, що бази даних дане простір потрібно для роботи.
фрагментація індексів
Крім фрагментації на рівні файлової системи і всередині файлу журналу, також можлива фрагментація всередині файлів даних, в структурах, що зберігають дані таблиць і індексів. Всередині файлу даних може статися два базових типу фрагментації:
Мал. 1. Структура сторінки бази даних
Найчастіше внутрішня фрагментація викликається змінами даних, такими як вставки, оновлення та видалення, що може залишити на сторінці порожні місця. Неправильне розпорядження коефіцієнтом заповнення також може сприяти фрагментації, подробиці приведені в технічній документації. Залежно від схеми таблиці / індексу і характеристик програми це порожнє місце може виявитися непридатним для повторного використання після його появи, що може привести до постійного зростання невикористаного місця в базі даних.
Розглянемо, для прикладу, таблицю з 100 мільйонів рядків, де середня запис має розмір 400 байтів. Згодом шаблон зміни даних програми призведе до появи в середньому 2800 байтів вільного простору на сторінку. Спільний простір, необхідне таблицею, становить 59 ГБ, це виводиться шляхом наступного розрахунку: 8096-2800 / 400 = 13 записів на 8-кілобайтні сторінку, потім ділимо 100 мільйонів на 13, щоб отримати число сторінок. Якби простір не пропадало, то на одній сторінці можна було б умістити 20 записів, що зменшує загальний потрібний простір до 38 ГБ. Величезна економія!
Неиспользуемое місце на сторінках даних / індексу може, таким чином, привести до зберігання того ж обсягу даних на більшій кількості сторінок. Це не тільки призводить до більшого витраті місця на диску, але і означає, що запит треба провести більше операцій введення / виводу, щоб прочитати той же обсяг даних. І всі ці додаткові сторінки займають більше місця в кеші даних, займаючи тим самим пам'ять сервера.
На рис. 2 показані свіжостворені сторінки індексу зі 100-відсотковим коефіцієнтом заповнення - сторінки повні, і фізичний порядок сторінок збігається з логічним порядком. На Рис. 3 показана фрагментація, яка може відбуватися після випадкових вставок / оновлень / вилучень.
Мал. 2. новоствореного сторінки індексу без фрагментації, сторінки повні на 100%
Фрагментацію часом можна запобігти, змінивши схему таблиці / індексу, але, як я згадав вище, це може бути дуже складним або неможливим. Якщо запобігання нереально, існують способи усунення фрагментації після її виникнення - зокрема, шляхом відновлення або реорганізації індексу.
Деякі користувачі просто вирішують відновлювати або перебудовувати все індекси щоночі або щотижня (використовуючи, наприклад, варіант з планом обслуговування) замість того, щоб з'ясовувати, які індекси фрагментовані і яке переваги дасть усунення фрагментації. Хоча це може бути хорошим рішенням для мимовільного адміністратора бази даних, який просто хоче застосувати якесь рішення з мінімальними зусиллями, це може виявитися дуже поганим вибором для більших баз даних і систем, де ресурси в дефіциті.
Який би метод не використовувався, настійно рекомендується регулярно шукати і усувати фрагментацію.
Оброблювач запитів є частиною SQL Server, яка вирішує, як слід виконувати запит - а саме, які таблиці і індекси використовувати і які операції виконувати на них для отримання результатів; це називається планом запитів. У число найбільш важливих вхідних даних цього процесу прийняття рішень входить статистика, що описує розподіл значень даних для стовпців усередині таблиці або індексу. Очевидно, щоб бути корисною для обробника запитів, статистика повинна бути точною і свіжої, інакше можуть бути обрані непродуктивні плани запитів.
Статистика створюється шляхом зчитування даних таблиці / індексу і визначення розподілу даних для відповідних стовпців. Статистика може бути побудована шляхом перевірки всіх значень даних для певного стовпця (повної перевірки), але її також можна побудувати на основі зазначеного користувачем відсотка даних (перевірки прикладів). Якщо розподіл значень в стовпці є відносно рівномірним, то перевірки прикладів може бути досить, і це робить створення і оновлення статистики швидшим, ніж при повній перевірці.
Мал. 4. Зміна параметрів бази даних через SQL Server Management Studio
Якщо потрібно оновлювати статистику як частина плану регулярного обслуговування, то потрібно пам'ятати про одну хитрість. І UPDATE STATISTICS, і sp_updatestats за замовчуванням використовують раніше зазначений рівень збору даних (якщо зазначений якийсь) - і він може бути нижче, ніж повна перевірка. Відновлення індексу автоматично оновлюють статистику за допомогою повної перевірки. У разі поновлення статистики вручну після відновлення індексу можна отримати ще менш точну статистику! Це може статися, якщо перевірка прикладів з поновлення вручну перепише повну перевірку, створену відновленням індексу. З іншого боку, при реорганізації індексу статистика взагалі не оновлюється.
Знову-таки, багато хто має план обслуговування, відновлювальний всю статистику в будь-який момент до або після відновлення всіх індексів - і самі того не знаючи, вони можуть виявитися з менш точною статистикою. Якщо вибрано просте відновлення всіх індексів час від часу, то воно подбає і про статистику. У разі вибору більш складний шлях з усуненням фрагментації, це варто робити і для обслуговування статистики. Я пропоную наступне:
- Проаналізуйте індекси і визначте, на якому індексі слід працювати і як провести дефрагментацію.
- Оновлення статистику для всіх індексів, які були відновлені.
- Оновлення статистику для всіх неіндексованих стовпців.
виявлення пошкоджень
Я розповів про обслуговування, пов'язаному з продуктивністю. Тепер пора перейти на розповідь про виявлення пошкоджень та пом'якшення наслідків.
Вкрай малоймовірно, щоб у базі даних містилася зовсім марна інформація, яка нікого не хвилює - так як же можна переконатися, що дані залишаються неушкодженими і відновлюваними в разі аварійної ситуації? Подробиці зведення воєдино повної стратегії аварійного відновлення і високої доступності виходять за межі теми цієї статті, але для початку можна зробити кілька простих речей.
Переважна більшість пошкоджень викликаються "обладнанням". Чому воно в лапках? Ну, обладнання тут - це насправді умовне позначення для "щось в підсистемі введення-виведення, під SQL Server". Підсистема вводу / виводу складається з таких елементів, як операційна система, драйвери файлової системи, драйвери пристроїв, контролери RAID, кабелі, мережа і самі диски. Маса місць, де можуть виникнути (і виникають) неполадки.
Однією з найбільш поширених проблем є збій харчування в момент, коли диск веде запис на сторінку бази даних. Якщо диск не зможе завершити запис, перш ніж у нього скінчиться електрику (або якщо операції записи кешуються і резервного джерела живлення не вистачить для очищення кешу диска), результатом може стати незавершений образ сторінки на диску. Це може статися, оскільки 8-кілобайтні сторінка бази даних на ділі складається з 16 суміжних 512-байтних секторів диска. Неповна запис могла записати деякі з секторів з нової сторінки, але залишити деякі з секторів з образу попередньої сторінки. Така ситуація називається розірваної сторінкою. Як можна виявити, коли це трапляється?
У SQL Server є механізм для виявлення такої ситуації. У нього входять збереження декількох бітів з кожного сектора сторінки і запис певного шаблону на їх місці (це відбувається безпосередньо перед записом сторінки на диск). Якщо під час передачі сторінки шаблон змінився, то SQL Server знає, що сторінка "порвана", і видає помилку.
Мал. 5. Установка попередження для всіх помилок серйозності 24
Якщо ця команда щось видає, DBCC знайшов пошкодження в базі даних. Тоді питання перетворюється в: "Що робити, якщо DBCC CHECKDB знаходить пошкодження?". Тут-то на сцені і з'являються резервні копії.
При виникненні пошкодження або іншого збою найбільш ефективним способів відновлення є відновлення бази даних з резервних копій. Звичайно, це передбачає наявність таких копій і те, що самі вони не пошкоджені. Занадто часто користувачі хочуть знати, як змусити серйозно пошкоджену базу даних працювати знову, коли у них немає резервної копії. Проста відповідь полягає в тому, що це неможливо, у всякому разі без втрати даних, здатної поламати все бізнес-логіку і реляционную цілісність даних.
По-друге, тримайте резервні копії по кілька днів на випадок, якщо одна з них буде пошкоджена - стара резервна копія краще, ніж ніякої. Також слід перевіряти цілісність своїх резервних копій, використовуючи команду RESTORE WITH VERIFYONLY (знову ж, см. Електронну документацію). Якщо при створенні резервної копії був використаний параметр WITH CHECKSUM, при використанні команди перевірки буде перевірено, чи вірна ще контрольна сума резервної копії, а також контрольні суми сторінок всередині неї.
Як можна побачити, забезпечення працездатності та доступності бази даних вимагає виконання кількох обов'язкових завдань. Ось мій останній контрольний список для мимовільних адміністраторів баз даних, яким дісталося управління базами:
Я привів у статті команди T-SQL, але багато чого можна зробити і з Management Studio. Сподіваюся, що я дав вам деякі корисні вказівки щодо ефективного обслуговування баз даних.