Аналіз роботи MS SQL Server, для тих хто бачить його вперше

Опубліковано продовження: частина 2

Нещодавно зіткнувся з проблемою - занедужав SVN на ubuntu server. Сам я програмую під windows і з linux «на Ви»... Погуглив помилково - безрезультатно. Помилка виявилася найбільш типовою (сервер несподівано закрив з'єднання) і ні про що конкретне не говорить. Отже, треба занурюватися глибше і аналізувати логи/налаштування/права/тощо, а з цим, якраз, я «на Ви».

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

Писати я буду не про лінукс - проблему хоч і вирішив, але професіоналом навряд чи став. Напишу про більш знайомий мені MS SQL. Благо, вже доводилося багато разів відповідати на питання і список типових вже готовий.

Для кого пишу

Якщо ви адміні в Сбері (або в Яндексі або < інша топ-100 компанія >), ви можете зберегти статтю в обране. Так, знадобиться! Коли до вас, в черговий раз, з одними і тими ж питаннями прийдуть новачки - Ви дасте їм посилання на неї. Це заощадить Ваш час.

Якщо без жартів, ця СУБД часто використовується в невеликих компаніях. Часто спільно з 1С або іншим ПЗ. Окремого БД-адміна таким компаніям тримати витратно - треба буде викручуватися звичайному ІТ-шнику. Для таких і пишу.

Які проблеми розглянемо

Якщо сервер вам повідомляє «закінчилося місце на диску Е» - глибокий аналіз не потрібен. Не будемо розглядати помилки, рішення яких очевидно з тексту повідомлення. Також не будемо розглядати помилки за якими гугл відразу видає посилання на msdn з рішенням.

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

Найперше

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

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

В інтернеті повно рецептів як це робити, наводяться приклади скриптів. Припущу, що всі ті методи для «профі» і новачкам незрозумілі. Що ж, опишу спосіб найпростіший: для його впровадження вам потрібно тільки володіння мишкою.

Скорочення і програми

• SSMS - додаток «Microsoft SQL Server Management Studio», знаходиться в «Пуску». Встановлюється окремою галочкою (Client management tools) з дистрибутива сервера. Починаючи з 2016 версії, доступно безкоштовно на сайті MS у вигляді окремого додатку. Старші версії студії нормально працюють з молодшими версіями сервера. Навпаки - теж іноді працюють (основні функції).

docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms “SSMS is free! It does not require a license to install and use.”

• Profiler - програма «SQL Server Profiler», знаходиться в «Пуску», встановлюється разом з SSMS.
• Performance Monitor (Системний монітор) - оснастка панелі керування. Дозволяє моніторити лічильники продуктивності, журналювати і переглядати історію замірів.

Оновлення статистики за допомогою «плану обслуговування»:

• запускаємо SSMS;
• з'єднуємося з потрібним сервером;
• розгортаємо в Object Inspector дерево: Management\Maintenance Plans (Плани обслуговування)
• правою кнопкою на вузлі, вибираємо «Maintenance Plan Wizard»
• у візарді мишкою відзначаємо потрібні нам завдання:
  • rebuild index (перебудувати індекс)
  • update statistics (оновити статистику)
• зазначити можна обидва завдання відразу, або зробити два плани обслуговування за одним завданням у кожному (дивимося «важливі зауваження» нижче);
• далі, відзначаємо галочками потрібну нам БД (або кілька). Робимо це для кожного завдання (якщо вибрали два завдання - буде два діалоги з вибором БД).
• Next, Next, Finish

Після цих дій у вас створиться (а не виконається) «план обслуговування». Запуск можна виконати вручну - правою кнопкою на ньому, вибрати «Execute». Або налаштувати запуск через «SQL Agent».

Важливі зауваження:

• Оновлення статистики - неблокуюча операція. Ви можете виконувати робочий режим. Додаткове навантаження звичайно створить, але ж у вас і так все гальмує, буде трохи більше - непомітно.
• Перебудова індексу - блокуюча операція. Запускати тільки в неробочий час. Є виняток - Enterprise редакція сервера допускає виконання «онлайнового ребилда». Цей параметр вмикає галочку у налаштуваннях завдання. Зауважте, що галочка є у всіх редакціях, але працює тільки в Enterprise.
• Звичайно, ці завдання необхідно виконувати регулярно. Пропоную простий спосіб визначення, як часто це робити:
  • при перших проблемах виконуєте план обслуговування;
  • якщо допомогло - чекаєте поки не почнуться проблеми знову (як правило, до чергового закриття місяця/розрахунку зп/і т. п. масових операцій);
  • термін нормальної роботи і буде вам орієнтиром;
  • наприклад, налаштуйте виконання плану обслуговування вдвічі частіше.

Сервер працює повільно - що робити?

Ресурси, які використовуються сервером

Як і будь-яка інша програма, серверу потрібні: час процесора, дані на диску, обсяги оперативної пам'яті та пропускна здатність мережі.

Оцінити брак того чи іншого ресурсу в першому наближенні можна за допомогою Task Manager (Диспетчер завдань), як би по кепськи це не звучало.

Завантаження ЦП

Подивитися завантаження в диспетчері зможе навіть школяр. Тут нам треба просто переконатися, що якщо процесор завантажений, то саме процесом sqlserver.exe.

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

Завантаження диска

Багато хто дивиться тільки завантаження процесора, але не треба забувати що СУБД - це сховище даних. Обсяги даних зростають, продуктивність процесорів зростає, а швидкість HDD практично не змінюється. З SSD ситуація краща, але терабайти на них зберігати витратно.

Виходить так, що я частіше стикаюся з ситуаціями, коли вузьким місцем стає саме дискова система, а не ЦПУ.

Для дисків нам важливі такі показники:

• середня довжина черги (операцій введення-виведення очікуючих виконання, штук);
• швидкість читання-запису (в Мб/с).

Серверна версія менеджера завдань, як правило (залежить від версії системи), показує і те й інше. Якщо ні - запускаємо оснастку панелі керування Performance Monitor (Системний монітор). Нас цікавлять лічильники:

• Фізичний (логічний) диск/Середній час читання (записи)
• Фізичний (логічний) диск/Середня довжина черги диска
• Фізичний (логічний) диск/Швидкість обміну з диском

Розгорнуто - можна почитати мануали виробника, наприклад тут social.technet.microsoft.com/wiki/contents/articles/3214.monitoring-disk-usage.aspx. Коротко:

• Черга бажано щоб не перевищувала 1. Допустимі короткочасні сплески, якщо вони швидко спадають. Сплески можуть бути різними залежно від вашої системи. Для простого рейду-дзеркала з двох HDD - черга більше 10-20 проблема. Для крутої бібліотеки з супер кешуванням я бачив сплески до 600-800 які миттєво розсмоктувалися, не призводячи до затримок.
• Нормальна швидкість обміну теж залежить від типу дискової системи. Звичайний (настільний) HDD «качає» по 50-100 Мб/с. Хороша дискова бібліотека по 500 Мб/с і більше. Для дрібних випадкових операцій швидкість менша. Приблизно так і орієнтуйтеся.
• Ці параметри треба дивитися в комплексі. Якщо ваша бібліотека качає 50Мб/с і при цьому вибудовується черга в 50 операцій - явно щось не так з залізом. Якщо черга вибудовується при прокачуванні близької до максимальної - то швидше за все диски не винні - вони просто більше не можуть - треба шукати спосіб зменшити навантаження.
• Навантаження треба дивитися роздільно по дисках (якщо їх декілька) і зіставляти з розміщенням файлів сервера. Менеджер завдань може показати найактивніші файли. Це зручно використовувати, щоб переконатися, що навантаження йде саме від СУБД.

Чим можуть бути викликані проблеми з дисковою системою:

• проблеми з залізом
  • погорів кеш, різко впала продуктивність;
  • дискова система використовується чимось ще;
• Брак оперативної пам'яті. Свопінг. Погіршилося кешування, продуктивність впала (дивимося розділ про ОП нижче).
• Збільшилося навантаження користувача. Необхідно оцінити роботу користувачів (проблемний запит/новий функціонал/збільшення кількості користувачів/збільшення обсягу даних/тощо).
• Фрагментація даних БД (дивимося ребилд індексів вище), фрагментація файлів системи.
• Дискова система досягла своїх максимальних можливостей.

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

• не змішуйте файли ОС з файлами даних БД. Розміщуйте їх на фізично різних носіях щоб система не конкурувала з СУБД за введення-висновок.
• БД складається з файлів двох видів: дані (* .mdf, * .ndf) і логи (* .ldf). Файли даних, як правило, більше використовуються на читання. Логи - більше на запис (причому запис - послідовний). З розуміння цього факту, слід рекомендація розміщувати логи і дані на фізично різних носіях, щоб запис в лог не переривала читання даних (як правило, операція запису має пріоритет вище ніж у читання).
• MS SQL для обробки запитів може використовувати «тимчасові таблиці». Вони зберігаються у системній базі tempdb. Якщо у вас високе навантаження на файли цієї БД - то можна спробувати винести її на фізично окремі носії.

Резюмуючи розташування файлів, використовуйте принцип «розділяй і володарюй». Оцініть, до яких файлів ідуть звернення і спробуйте їх розподілити на різні носії. Також, використовуйте особливості RAID систем. Наприклад, RAID-5 читає швидше ніж пише - що добре підходить для файлів даних.

У продовженні:

• аналізуємо використання ВП і мережі.
• дивимося детально роботу користувачів використовуючи SSMS, profiler і прямі запити до системних уявлень.
• план і статистика запитів (розглянемо кілька способів отримання). live query statistics.
• waits (очікування). поточна інформація та статистика.
• проблеми з підключенням до сервера. процеси/порти/протоколи