SQL в запитаннях і відповідях: Обслуговування журналів та індексів, Інші СУБД, Бази даних, статті

Питання:Я займаюся розробкою плану резервного копіювання наших баз даних, який крім іншого передбачає резервне копіювання журналу транзакцій. Це дозволить нам в разі збою відновити систему, втративши мінімум даних. Досліджуючи, з якими проблемами ми можемо зіткнутися, я багато разів читав, що потрібно бути дуже уважним, щоб не розірвати ланцюжок резервних копій журналу. Не могли б ви пояснити, що це за ланцюжок і як її можна розірвати?


Відповідь:Це чудове питання, тому що дуже часто про такі нюанси забувають. Ланцюжком резервних копій журналу (іноді її просто називають ланцюжком журналу) називають безперервний набір резервних копій журналу транзакцій, які охоплюють час з моменту створення самої останньої резервної копії даних (повної або різницевої) до моменту, на який треба відновити сервер. Приблизна послідовність застосування резервних копій в процесі відновлення така:



Більшість ІТ-спеціалістів зберігають більше резервних копій журналу транзакцій на випадок, якщо якась з резервних копій виявиться пошкодженої, і доведеться відновлювати систему, використовуючи не найсвіжішу резервну копію даних. За більш докладною інформацією відсилаю до двох моїм статтями про резервне копіювання і відновлення, опублікованих у минулому році в TechNet Magazine: "Understanding SQL Server Backups"І"Recovering from Disasters Using Backups“.


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


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


Так відбувається, якщо не використовувати параметр WITH COPY_ONLY, який дозволяє створити резервну копію журналу, але при цьому наступна резервна копія буде містити ті ж записи журналу. Докладніше про те, як уникнути переривання ланцюжка резервних копія см. в записі "BACKUP WITH COPY_ONLY "з моєму блозі.


Більш популярний приклад порушення ланцюжка резервних копій – операція, яка не дозволяє створити регулярну резервні копії журналу транзакцій. До таких операцій відносяться:



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


І останнє: абсолютно помилковий той популярний міф, що створення повної або різницевої резервної копії порушує ланцюжок резервних копій – воно взагалі ніяк не впливає на резервне копіювання журналу.


Кластеризуючих індекси


Питання:У багатьох таблицях у нашій базі даних в SQL Server 2008 немає кластеризувати індексу. Я чув, що можуть бути проблеми з продуктивністю при роботі з перенаправлених записами у зв'язку з необхідністю додаткових операцій введення-виведення. Скажіть, як мені перевірити це і що з цим робити?


Відповідь:Купа – це таблиця, в якій немає кластеризувати індексу. Вона за визначенням невпорядкований. Читачів, які не знайомі з перенаправлених записами, я відсилаю до статті "Forwarding and forwarded records, and the back-pointer size"(Перенаправлення і переслані запису і розмір зворотного покажчика) в моєму блозі. Переслані записи в купах можуть викликати додаткові випадкові операції введення-виведення при обробці запитів, що в свою чергу веде до падіння продуктивності.


Найпростіший спосіб перевірити, чи є у вас запити, що обробляють переслані записи, – вивчити показання лічильника Forwarded Records / sec в об'єкта Access Methods. Потім застосуйте функцію динамічного управління dm_db_index_physical_stats в режимі DETAILED до деяких таблиць бази даних – вона поверне число перенаправлених записів у кожній таблиці в стовпці forwarded_record_count. Докладніше можна почитати в інтерактивної довідкової системі.


Самий невдалий спосіб видалення перенаправлених записів – це створити кластеризувати індекс, а потім знову його видалити. Це викличе автоматичне дворазове перестроювання всіх некластерізованних індексів у таблиці, що є величезною і марною тратою ресурсів. Детальніше див. статтю "What happens to non-clustered indexes when the table structure is changed?" "(Що відбувається з некластерізованнимі індексами при зміні структури таблиці?) В моєму блозі.


Найпростіший шлях назавжди позбутися і запобігти створенню перенаправлених записів в купах полягає у створенні кластеризувати індексів. Я не хочу зараз починати "ідеологічну" дискусію на цю тему "Кластеризувати індекс або купа" і пояснювати, чому в більшості випадків потрібно кластеризувати індекси та не використовувати купи. Докладніше див статті по темі "Clustering Key"(Ключ кластеризації) в блозі моєї дружини, Кімберлі Тріпп (Kimberly Tripp). Я просто рекомендую вам використовувати кластеризувати індекси.


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


В SQL Server 2008 є нова інструкція ALTER TABLE … REBUILD, яка дозволяє перебудовувати купи. Вона аналогічна інструкції ALTER INDEX … REBUIL з тією різницею, що остання перебудовує індекси. Розробники з Microsoft додали цю інструкцію, щоб забезпечити можливість стиснення даних, але вона буде служити і нашим завданням. Докладніше можна почитати в інтерактивної довідкової системі.


Обслуговування індексів


Питання:Я змінив наші процедури обслуговування індексів, так щоб індекси перебудовувалися в оперативному режимі, але все одно іноді ми стикаємося із блокуванням під час виконання процедур обслуговування. Чому так виходить? Я вважав, що в оперативних операціях індексації блокування не використовуються, тобто їх бути не повинно. Так має бути чи я роблю щось не так?


Відповідь:Це цілком очікуване поведінка. При ініціалізації процедури потрібно блокування загальної таблиці (це дуже швидка операція). Вона негайно переривається. Блокування негайно скасовується, але як і будь-яка інша блокування, вона повинна бути поставлена ​​в чергу і це буде перешкоджати всім новим запитам вносити зміни в таблицю, поки не буде отримана і скасована блокування.


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


В кінці процедури індексації для її завершення потрібно блокування схеми – можете вважати її монопольної блокуванням. Вона також відбувається надзвичайно швидко і негайно скасовується. Це блокування запобігає виконання будь-яких запитів до таблиці (на читання або запис), поки вона не отримана і не скасована.


І на цей раз неможливо отримати блокування, поки SQL Server не завершить всі поточні запити на читання або запис. А це означає, що блокування можливе.


Резюмуючи, варто сказати, що незважаючи на назву операції – оперативна процедура індексації – вона, тим не менш, вимагає двох короткострокових блокувань, які можуть викликати проблеми з блокуваннями. Перевага в порівнянні з традиційними автономними операціями індексації полягає в тому, що в переважній більшості операцій індексації блокування відсутні, так що загальний рівень паралелізму виявляється вищою. Детальніше про індексуванні в оперативному режимі див. "білу книгу" "Online Indexing Operations in SQL Server 2005"(Оперативні процедури індексації в SQL Server 2005).


Скорочення часу обслуговування індексів


Питання:Я отримав "у спадок" кілька систем, в яких на виконання регулярних завдань обслуговування індексів йде багато часу, до того ж вони викликають багато операцій введення-виведення, але я не виконую жодної перебудови індексів, тому що вони не стають фрагментованими. Я хотів би позбутися від цих процедур, тому що вони не дають ніякого виграшу в продуктивності. Підкажіть, яку стратегію мені використовувати?


Відповідь:Це досить популярна проблема. А причина полягає в логіці визначення, які індекси потребують перестроюванні або реорганізації.


Більшість адміністраторів застосовують до всіх індексах в базі даних системну функцію динамічного управління dm_db_index_physical_stats (вона вже згадувалася раніше), після чого вирішують, що робити – перебудовувати, реорганізувати або нічого не робити. При прийнятті рішення вони орієнтуються на значення avg_fragmentation_in_percent, page_count і avg_page_space_used_in_percent, відбираючи їх за допомогою пропозиції WHERE.


Проблема в тому, що відомості про фрагментацію індексів не зберігаються в пам'яті, як інша статистика. Функція повинна вважати і обробити кожен індекс, щоб визначити ступінь його фрагментації. Якщо більшість індексів у базі даних статичні або змінюються дуже повільно (в сенсі фрагментації), вони не будуть піддаватися перестроювання або реорганізації. Перевірка фрагментації при кожному виконанні завдання обслуговування індексів – це, по суті, марна трата часу.


Більшість уявлень динамічного управління підтримують "обробку після предиката", коли обробляються тільки дані, що відповідають умові предиката в реченні WHERE. Однак dm_db_index_physical_stats – Функція, а не подання, і вона не підтримує таку можливість. Це означає, що фільтрувати треба вручну і запитувати у функції обробку тільки тих індексів, які швидше за все фрагментовані і можуть зажадати перестроювання або реорганізації.


Я рекомендую простежити за фрагментацією протягом декількох тижнів. Так ви виявите індекси, які вимагають перевірки на фрагментацію, і вам не доведеться щоразу перевіряти всі індекси. Отримавши список таких індексів, створіть таблицю з ім'ям, назвою індексів і порогом фрагментації, після якого потрібно вживати заходів. Може виявитися, що деякі індекси починають впливати на продуктивність при істотно більш високому рівні фрагментації, ніж інші. Це буде "контрольна таблиця" для управління завданням обслуговування індексів. Процедура повинна по циклу проходити по всіх індексах в таблиці і виконувати системну функцію dm_db_index_physical_stats тільки по відношенню до них.


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


Більш детальну інформацію про різні методи обслуговування ви знайдете в статті "Importance of index maintenance"(Важливість обслуговування індексів) в моєму блозі, а зі статті"Inside sys.dm_db_index_physical_stats"Ви дізнаєтеся про події всередині функції dm_db_index_physical_stats.


Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*