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

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


Відповідь: Я радий, що ви обізнані про побічні ефекти процедури стиснення бази даних. Так, іноді це просто неминуче.


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


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


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


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


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


Незважаючи на те, що процес стиснення може бути повільними, починаючи з SQL Server 2005 в режимі динамічного адміністративного подання sys.dm_exec_requests у стовпці percent_complete надається інформація про хід виконання стиснення. Хід стиснення можна відстежувати за лічильником Shrink Data Movement Bytes / sec об'єкта Databases в Системному моніторі.


Включення автоматичного збільшення розміру файлів


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


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


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


Вся складність полягає у визначенні "правильної" настройки автоматичного збільшення. У SQL Server 2005 і пізніших версіях за умовчанням встановлюються автоматичне збільшення файлів журналу транзакцій на 10%, а файлів даних – на 1Мб. Однак визначення автоматичного збільшення у відносних цифрах означає, що при збільшенні файлів зростає і рівень автоматичного збільшення. Це також означає, що необхідний час також може збільшитися, якщо не дозволена миттєва ініціалізація файлу. Таким чином обидва типи файлів повинні мати абсолютне значення автоматичного збільшення, щоб поведінка цієї функції було передбачувано.


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


Для файлів даних 1 Мб автоматичного збільшення до смішного мало, але правильне значення визначити важко. Все залежить від того, що потрібно від функції автоматичного збільшення – надзвичайні тимчасові заходи або заміна ручного управління розміром файлу даних. Рішення також залежить від того, скільки нового простору потрібно щодня для додаються в базу даних. Сухий залишок такий: треба включити автоматичне збільшення розміру файлів і поставити йому розумне значення в абсолютних числах, не вдаючись до відсотків.


Схема зберігання


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


Відповідь: Спосіб зберігання великих об'єктів (LOB) даних може мати величезний вплив на продуктивність запитів, тому вкрай важливо вибрати правильний підхід. Детальний аналіз всіх варіантів виходить за рамки цієї колонки, але ось кілька рекомендацій:


По-перше, чи завжди дані будуть менше 8000 байт? Якщо так, то спробуйте використовувати тип даних (n) char або (n) varchar, але тільки не один зі справжніх типів даних LOB таких, як XML, (n) varchar (max), varbinary (max), (n) text або image, якщо тільки це не абсолютно необхідно. Якщо справжній тип LOB потрібен через обсяг даних, не використовуйте (n) text або image, так як в SQL Server 2005 ці типи даних оголошені застарілими. Вони не настільки функціональні, як більш нові типи даних LOB.


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


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


Є також нюанси з даними фіксованої і змінної довжини, іноді навіть потрібно швидкий потоковий доступ до даних. У цьому випадку слід подумати про використання існуючого в SQL Server 2008 типу даних FILESTREAM. Більш глибокий аналіз всіх типів зберігання даних LOB ви знайдете в записі "Importance of choosing the right LOB storage technique“.


Критичні перевірки та компроміси


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


Відповідь: Запобіжні перевірки цілісності є важливою частиною будь-якого комплексного плану обслуговування користувача і системних баз даних. Також важливо використовувати метод перевірки сторінок. У базах даних SQL Server 2005 і більш пізніх версій дозволите перевірку контрольних сум сторінки. З базах даних SQL Server 2000 використовуйте виявлення розірваних сторінок.


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


Ось лише декілька з них.


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


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


Суть в тому, що все залежить від вас і вашого відчуття комфортності. У серпні 2009 року я провів опитування у своєму блозі: 37% із 276 респондентів виконують перевірки цілісності тижні, а 25% виконують їх щодня. Повні результати мого опитування та багато іншої інформації, корисної для визначення частоти перевірок, ви знайдете в моєму блозі www.sqlskills.com / BLOGS / PAUL / post / Importance-of-running-regular-consistency-checks.aspx.


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


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

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

Ваш отзыв

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

*

*