Одного разу кілька місяців тому я був розбуджений наполегливим гудінням свого BlackBerry: клієнти, які використовували одну з моїх баз даних, скаржилися, що звернення до створеного Web-додатком при завантаженні часто респонденти користуються послугами сторінки займає від 20 до 30 секунд. Протягом попередніх тижнів продуктивність поступово погіршувалася і знаходилася тепер на такому рівні, що невелика додаткове навантаження могла зупинити систему. Я повинен був знайти причину проблеми, причому зробити це слід було невідкладно. Як я з'ясував, мала місце несправність, яка виникла через поєднання фрагментації в таблицях і файлах бази даних і неефективного наповнення сторінки. І, розібравши завдання послідовно, я вирішив її.

позначимо проблему

Насамперед я відкрив монітор продуктивності, щоб побачити, станом якого з чотирьох апаратних компонентів - процесора, пам'яті, диска або мережі - обумовлено уповільнення. Лічильник Processor:% Processor Time був в області норми, SQL Server: Buffer Manager: Free Pages показував понад 2000 відкритих сторінок, а Network Interface: Bytes Total / sec був тільки на 1/20 від можливостей мережі гигабитного Ethernet. Physical Disk: Disk Bytes / sec були, відповідно, від 100 до 200% вище норми для нашого сервера, так що джерелом проблеми, здавалося, було використання диска.

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

Жорсткий диск як стримуючий фактор

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

Отже, обмеженість обертання диска визначає порядок і довжину значного числа запитів за даними. Компонент введення / виведення SQL Server читає і записує дані з максимально можливою швидкістю і вибудовує сторінки в тій послідовності, в якій вони розташовані на диску. Мета полягає в тому, щоб читати дані зі сторінок в порядку, близькому до їх нумерації, наскільки це можливо, тому що в результаті ми отримаємо найменший час очікування того моменту, коли потрібна секція виявиться під голівкою. Лічильники монітора продуктивності Avg Disk Bytes / Transfer, Avg Disk Bytes / Read і Avg Disk Bytes / Write повідомляють, скільки байтів задіюється при кожній операції введення / виводу. Буфера диска гарантують, що база даних SQL Server ніколи не буде мати менш ніж 8196 байт за оборот диска, але що нам потрібно, так це послідовні 65,536 (або більше) байт за оборот (65,536 байт, або 64 Кбайт). Якщо ви бачите, що дана величина менше, ніж 65,536, значить, виникли проблеми з фрагментацією даних.

Монітор продуктивності показував надмірне використання диска, але, щоб з'ясувати причину, мені потрібен був інший інструмент. FileMon - це вільно поширюваний інструмент від Sysinternals ( http://www.sysinternals.com ), Який описує все, що читає і пише, і число байтів за операцію для кожного файлу. Встановивши в FileMon фільтр для контролю тільки файлів бази даних, можна побачити, скільки байтів SQL Server читає і пише по кожному з групи файлів. Коли я зауважив, що FileMon повідомляє тільки про 8196 байт, прочитаних в файлі, який мав таблицю в 2,5 і 1 Гбайт в індексах, я подумав, що, ймовірно, виявив винуватця погіршення продуктивності.

Важливо пам'ятати, що кожен раз процес введення / виводу включає затримку, під час якої контролер чекає, коли диск повернеться під читає / друкарську голівку. Тому завжди на прочитання восьми сторінок потрібно більше часу, ніж на одну сторінку такого ж обсягу. Крім того, компонент введення / виведення для ефективного доступу не може впорядковувати читання, тому що не в змозі передбачити, які запити на нього чекають в майбутньому. Оптимізація диска відбувається, коли комбінують зберігання даних великими порціями з мінімально можливим зверненням до диска. Звіт FileMon повідомляє, що компонент введення / виведення прочитав табличную сторінку в 2,5 Гбайт за один раз, значить, щось заважало двигуну згрупувати запити в послідовність. Тому потрібно поглянути на деякі таблиці і з'ясувати причину.

виявлення фрагментації

Корисний інструмент для ідентифікації ступеня фрагментації в таблиці - команда SQL Server DBCC SHOWCONTIG. На жаль, її запуск погіршує продуктивність, яку треба як раз поліпшити, тому що команда створює велику кількість операцій введення / виводу. C ключем FAST вплив на продуктивність сервера менше, ніж якщо запускати команду без нього. Але навіть виконання команди DBCC SHOWCONTIG FAST уповільнює систему досить сильно, якщо вона вже перевантажена. Найкраще при використанні Database Consistency Checker (DBCC) - якщо, звичайно, є час - виконати повне сканування і встановлювати TABLERESULTS, щоб сканувати таблицю у вихідний файл і потім зберегти результати.

Я написав на мові T-SQL збережену процедуру uspBuildFraglist, текст якої показаний в лістингу 1. Вона повинна автоматизувати роботу команди DBCC SHOWCONTIG WITH TABLERESULTS. UspBuildFraglist в циклі проходить за списком таблиць у зазначеній базі даних і для кожної таблиці запускає DBCC SHOWCONTIG WITH TABLE RESULTS, ALL_INDEXES, який показує інформацію про фрагментацію даних і індексів зазначеної таблиці. Результати спочатку зберігаються в тимчасовій таблиці, потім переміщаються в постійну таблицю в базі даних, яка була призначена для утримуваних даних, створених адміністративними процесами підтримки на зразок цього.

Лістинг 1. Процедура uspBuildFraglist

Постійна таблиця має поле з ім'ям LastScanTime, що служить для відстеження переглядів таблиці. Умова IF в лістингу 1 існує для перевірки такого поля в кожній таблиці і ігнорує виконання оператора DBCC, якщо в цей день таблиця була просканувати. Така перевірка дозволяє процедурі uspBuildFraglist багаторазово запускатися без дублювання роботи, виконаної попередніми переглядами.

Друге властивість процедури uspBuildFraglist - це затримка після кожного сканування. З тексту коду у фрагменті B видно, що перевіряється таблиця sysprocesses, щоб визначити, блокує чи процес перегляду якісь процеси. Якщо так, процедура чекає 30 секунд. Якщо немає, процедура чекає 5 секунд, а потім запускає наступне сканування. Можливість робити паузу між переглядами - елементарне перевага сканування по кожній таблиці окремо замість сканування відразу всієї бази даних. Це простий спосіб мінімізувати можливі проблеми з потенційними блокуваннями, які можуть бути викликані DBCC.

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

Інтерпретація результатів роботи DBCC

Для того щоб інтерпретувати висновок даних команди DBCC, проведені за допомогою uspBuildFraglist, потрібно спочатку визначити, чи має Інспектовані таблиця кластерний індекс. Оскільки кластерний індекс фізично впорядковує дані на диску згідно індексувати полю, фрагментація зазвичай з'являється, коли дані видаляються з таблиці, а після цього не було вставлено ніяких даних, які заповнили б пусте місце. Зазвичай причина появи такого типу фрагментації в наявності кластерного індексу при одноманітно збільшується первинному ключі, наприклад по унікальному полю. На жаль, за замовчуванням первинний ключ повинен задіяти кластерний індекс, і багато проектувальників баз даних повинні використовувати унікальні поля як первинні ключі.

Інший простий спосіб фрагментирования як кластеризованих, так некластерізованних індексів полягає в розподілі даних через якийсь час. У системах OLTP зазвичай нові рядки додаються майже одночасно і невеликими блоками. Тому в усі таблиці в групі файлів буде розміщуватися по одному або два блоки додаткових даних одночасно. Це означає, що блоки додаткових даних можуть бути розкидані по всьому файлу. Якщо нові рядки додаються з однією швидкістю по всіх таблиць, розподіл буде в достатній мірі рівним. Якщо в одну таблицю додається більше рядків, ніж в інші, тоді додаткові рядки таблиці можуть групуватися, тоді вони будуть просто маленькими островами безперервності в море розділених рядків. SQL Server ніяк не намагається розміщувати рядки таблиці разом. Тому в міру зростання системи OLTP завжди фрагментируются.

Кращий індикатор фрагментирования індексів доступний при оцінці результатів сканування логічної фрагментації за допомогою команди DBCC SHOWCONTIG. Ці результати показують відсоток часу, коли сторінки індексу не слідують один за одним в порядку, заданому індексом. Так як більшість даних в індексі зберігається на рівні листя, будь-яке значення більше 0% означає великий відсоток операцій введення / виводу на диску і менше ймовірність, що компонент введення / виведення в SQL Server може читати окремий блок додаткових даних замість восьми індивідуальних сторінок.

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

Проблеми можуть бути і з розбиттям сторінок. Проста порада: для того, щоб зменшити розбиття сторінки, можна використовувати фактор заповнення, який залишає місце для нових даних. Розбиття сторінок призводить до суттєвого, хоча і короткочасного зменшення продуктивності, у міру того як рядки переміщуються на нову сторінку; однак проблема в тому, що SQL Server доводиться читати більшу кількість сторінок, оскільки сторінки заповнені не до кінця. Для таблиці з 1 млн. Сторінок даних і 20-відсотковим коефіцієнтом заповнення результатом буде 1,2 млн. Сторінок, в которцих буде додатково всього 16 Мбайт даних. Ключове питання - чи буде короткостроковий ефект від розбиття статті більше, ніж довготривалий ефект від читання більшої кількості сторінок? Якщо виходить, що підтримка розриву в сторінках, дає ефект коефіцієнта заповнення в 50%, це говорить про те, що підтримка високої середньої щільності сторінки так само важливо, як наявність низької фрагментації.

Фрагментація файлів бази даних

Якщо монітор продуктивності показує надмірну кількість операцій введення / виводу, про що це говорить? І якщо FileMon показує принаймні 65,536 байт при виконанні вводу / виводу? Це означає, що файл самої бази даних фрагментований. Монітор продуктивності виводить в звіті фізичні метрики диска, незважаючи на те що FileMon описує введення / виведення на рівні файлової системи. Щоб завантажити сторінку з даними, операційна система Windows повинна знайти зміщення в файлі, щоб встановити фізичне розташування байтів на диску. Система NTFS намагається зменшити фрагментацію диска, визначаючи місце для файлу в найбільшому шматку вільного простору на диску, без спроби оптимізувати таке розміщення. Якщо база даних розширювалася і стискалася регулярно (тобто використовувалася команда DBCC SHRINKDB), тоді можливо, що деякі з файлів бази даних розподілені по диску, і малоймовірно, що ці частини впорядковані, а менеджер зберігання впорядкує сторінки за номером.

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

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

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

Рішення задачі

Ключові проблеми, які я виявив: велика таблиця і фрагментований файл бази даних, а також неефективна щільність заповнення сторінок. Я розглядав всі три проблеми, які мені необхідно усунути, разом, щоб вирішити проблему продуктивності бази даних. Перша: найбільші, найбільш наповнені, використовувані для вирішення таблиці потребують індексі із зовнішнім ключем. Ця конкретна проблема справді не була викликана фрагментацією, а була пов'язана зі скануванням таблиці розміром в 2,5 Гбайт із зовнішнім ключем під час поновлення таблиці з первинного ключового полю. Таке сканування було причиною великої кількості операцій дискового введення / виводу, так що не можна було встановити, чи існували інші проблеми з диском. Додавання індексу радикально зменшило час модернізації і час введення / виведення операцій з диском приблизно до 80% при операції модернізації. Щоб вирішити залишилися дві проблеми, я написав збережену процедуру uspDefragTables, яку нижче ми розглянемо більш докладно.

Друга проблема, яку я виявив, полягала в тому, що таблиця була невпорядкований, тобто її дані були «складені» в тому порядку, в якому вони встановлені належним чином. Це саме по собі не страшно; сторінки таблиці були лише трохи фрагментовані. З того моменту як дані були отримані, приблизно від 75 до 80% даних загальної кількості вставлених в усі таблиці бази даних зберігалися близько один до одного. Пам'ятайте проте, що логічна дефрагментація даних в DBCC SHOWCONTIG непридатна до неврегульованим даними; тому такі дані з 0% фрагментації все ще можуть бути негативним фактором при формуванні запитів до даних додатка.

Фактично це була така організація Даних, яка при поиска Даних віклікала ефект, подібний фрагментації. Найбільша таблиця містіть деталізовану інформацію за різнімі данімі. А структура невпорядкованіх даних до табліці змушує розміщуваті чісленні детальні дані на тій же странице. Результуюча ефект у відновленні всех Даних для одного запиту (якіх может буті кілька десятків за кілька хвилин) змушують SQL Server ігноруваті таблицю и читать кілька рядків з кожної десятої Сторінки на запит. Мало того, що це закінчується великою кількістю операцій введення / виводу, це майже гарантує, що сторінки, що містять додаткові рядки, не будуть заповнені безперервно. Крім того, оскільки таблиця має тисячі операцій вставки в хвилину, будь-які додаткові дані, ймовірно, містять тільки кілька рядків для будь-якого даного запиту.

Третя проблема, яка робить організацію даних менш ефективною, - це коли команда DBCC SHOWCONTIG показує, що щільність заповнення сторінки менше 50%. Мається на увазі, що для зберігання даних таблиця використовувала сторінки більше двох разів. Занадто велика кількість напівпорожніх сторінок, розкиданих по файлу бази даних, означає в реальності погану продуктивність.

Процедура uspDefragTables з лістингу 2 вирішує проблеми з щільністю заповнення сторінки і організацією даних. Процедура виконує ітерацію по всіх рядках таблиці, яка є результатом виведення команди DBCC SHOWCONTIG з збереженої процедури uspBuild-Fraglist, і вона шукає таблиці, які перевищують рівень фрагментації, вказаний в параметрі @MaxFrag, як показано у фрагменті коду А лістингу 2.

Лістинг 2. Процедура uspDefragTables

Процедура UspDefragTables виконує команду DBCC DBREINDEX для будь-якого індексу, який має логічну фрагментацію або фрагментацію в даних, або якщо середня щільність заповнення сторінки становить менше ніж 100 @ MaxFrag. Для кластеризованих індексів ця команда реорганізує всю таблицю. Для некластерізованних індексів вона реорганізує індекс на рівні листя.

Зауважимо, що процедура uspDefragTables безумовно ігнорує невпорядковані дані (indexID = 0), тому що команда DBCC DBREINDEX з такими даними не працює. Замість DBCC DBREINDEX можна створити дефрагментірован некластерізованний індекси. Для поліпшення організації будуть потрібні інші методи, які часто доводиться застосовувати вручну.

Найбільш загальна рекомендована методика для дефрагментації невпорядкованих даних полягає у створенні кластерізованного індексу, потім згодом індекс негайно видаляється. Видалення індексу не торкнеться фізичного порядку сторінок, проте можна отримати ефект від більш високої середньої щільності заповнення сторінки і більш низькою фрагментації без витрат на підтримку кластерізованного індексу по сильно заповненої таблиці. Слід визначитися з вибором полів, використовуваних для побудови кластерізованного індексу, відштовхуючись від того, як ваш додаток використовує дані, а не від того, як дані будуть вставлені згодом. Кращий варіант для таблиць - це використовувати поле зовнішнього ключа для наявних деталізованих даних так, щоб кластерізованний індекс групував дані по первинному ключу пов'язаної таблиці. Будь-які нові дані потрапляють в кінець таблиці, і, по крайней мере, існуючі дані зберігаються в безперервних сторінках і додаткових блоках.

підтримка дефрагментації

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

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

Моріс Льюїс ( [email protected] ) - адміністратор баз даних в компанії Digichart, провайдера послуг додатків для охорони здоров'я