Наша команда-партнер Artmisto
Ключовий індикатор продуктивності (далі KPI) бази даних є єдиним повноцінним показником, здатним оцінити загальну продуктивність бази на рівні управління протягом часу.
Одноманітність є критичним фактором в оцінці будь-якого KPI. Визначення базового рівня, а потім постійне повторення абсолютно того ж вимірювання є єдиним способом виявлення тенденцій і оцінки результатів оптимізації. При цьому тести повинні повторюватися через регулярні проміжки часу.
Ключовий індикатор продуктивності повинен порівнювати дані, отримані в "чистій" тестової середовищі, а також фактичні дані, беручи до уваги ефект масштабування системи. Щоб отримати реальний індикатор KPI, команда розробників повинна пред'явити особливі вимоги до тестів і методів обчислення. Я рекомендую масштабувати результати трьох послідовних тестів, щоб отримати в результаті кожного одну третину і щоб їх сума, тобто початкова повноцінна метрика, становила одиницю, як показано на робочому аркуші і діаграмі рис. 49.7.
Puc. 49.7. Ключовий індикатор продуктивності обчислюється на основі трьох тестів в програмі Excel
Повноцінна метрика продуктивності бази даних у вигляді робочої книги Excel доступна на сайті книги.
Періодичне тестування продуктивності
При періодичному тестуванні вимірюється продуктивність бази даних в реальних умовах на основі сценарію і бази даних, створених на етапі тестування. Час виконання може реєструватися в змінних Т-SQL, а також в програмі SQL Server Profiler.
Якщо архітектура використовує рівень абстракції даних, то всі виклики тестів повинні виконуватися саме до нього. Якщо ж база даних використовує більш незвичайний підхід за допомогою моделі "клієнт / сервер", то при тестуванні доведеться змішувати різні запити. Якщо база даних використовує архітектуру, орієнтовану на Web-служби, природно, будуть потрібні запити саме до цих служб. Для балансування операцій вилучення та оновлення даних для таблиць буде потрібно створити досить представницьку вибірку, що враховує особливості клієнтських додатків і пакетних процесів.
Початкове виконання повторюваного тесту продуктивності встановлює базову лінію вимірювання. При кожному наступному виконанні тесту вихідна база даних відновлюється і запускається вихідний сценарій. Повторюваний тест продуктивності найбільш корисний для оцінки результатів оптимізації в "чистій" середовищі - тобто несхильною іншим процесам, недоступною іншим користувачам і змін даних.
На швидкість відгуку системи впливають численні фактори. Щоб оцінити, який з них впливає на конкретне зміна продуктивності, слід в кожному тесті змінювати тільки один з них, після чого вимірювати час відгуку деяким однаковим методом. Кожен запуск тесту продуктивності повинен починатися з одних і тих же даних, а сервер, який використовується для тестування, повинен бути вільний від будь-яких інших процесів, підключень і користувачів.
Ви можете запускати два тести: один для одного користувача, а інший для багатокористувацької середовища при наявності конкуренції, при цьому одночасно запускаючи кілька примірників відмінних тестів. Ці тести повинні бути комбінацією операції читання і запису, генерації даних для звітів і масових оновлень, як можна ближче імітують реальне середовище експлуатації бази даних.
Для тестування сценарію багато режиму краще написати додаток .NET, що створює безліч підключень і звернень до бази даних і таким чином імітує інтенсивну завантаження. Чим точніше це навантаження (кількісно і якісно) відображає запити реальних користувачів, тим краще тестування. Не забувайте, що не всі конкурентні користувачі завантажують базу даних в кожен момент часу. Результатом цього тесту повинно стати кількість транзакцій, які здатна підтримувати база даних.
Щоб оцінити необхідну кількість транзакцій, я деякий час присвячував спостереженнями і дослідженнями (прогулювався по організації і записував, яка кількість користувачів і який тип завдань вони виконують, а також як часто ці завдання виконуються).
Збір даних продуктивності
Вимірюючи реальний час відгуку збережених процедур рівня абстракції даних, тест продуктивності у виробничому середовищі збирає інформацію про діяльність користувачів.
Дані про продуктивність у виробничому середовищі можна збирати декількома способами.
? SQL Server Profiler може здійснювати трасування на стороні сервера і збирати час виконання всіх процедур, що зберігаються. Трасування можуть записуватися в базу даних і аналізуватися для тонкої настройки продуктивності або отримання даних про реальну завантаженні. Таку трасування легко запустити і зупинити, що надає вам повну свободу у виборі часу проведення вимірювань.
? Програмний код в кінці кожної процедури, що може обчислювати тривалість її виконання і записувати результат в журнал для подальшого аналізу. Залежно від конфігурації трасування в програмі Profiler, цей метод, швидше за все, дасть вам менше деталей, ніж сам SQL Server Profiler. Залежно від складності коду протоколювання, виникає ще одна проблема, пов'язана з цим методом: таке протоколювання досить складно відключити, якщо ви вирішили проводити вимірювання тільки протягом декількох годин або днів.
? Програмний код додатку може протоколювати інформацію від часу клацання користувачем на кнопці відправки форми до завершення оновлення форми. Незважаючи на те що цей метод дає повну картину реальної діяльності користувачів, він надає найменший обсяг інформації, необхідної для настройки.
У той час як всі представлені методи накладають на базу даних додаткове навантаження і існує безліч змінних величин, я вважаю, що SQL Server Profiler забезпечить вас кращу інформацію з мінімальними витратами.
Тестування впливу масштабування на продуктивність
Цей вид тесту є всього лише варіацією регулярного тесту ефективності. Його метою є перевірка меж масштабування і корисності бази даних в майбутньому. Можливості масштабованості бази даних вимірюються шляхом вимірювання часу відгуку при повній очікуваної завантаженні і половинному завантаженні. Для розширення початкових робочих таблиць генеруються нові дані, щоб подвоїти поточний робочий кількість рядків. Кількість конкурентних користувачів можна також подвоїти в додатку .NET. Після того як база даних була розширена, виконується повторюється тест.
У SQL Server схема бази даних і запити можуть тестуватися на завантаження тільки тоді, коли обсяг даних перевершує обсяг пам'яті сервера в кілька разів. Поведінка індексів відрізняється, коли SQL Server має можливість завантажити всі необхідні сторінки даних і індексів в пам'ять. Коли всі ці сторінки знаходяться в пам'яті, логічні читання стають єдиним суттєвим фактором. Так як при цьому не використовуються фізичні звернення до диска, продуктивність самих індексів стає практично несуттєвою.
СУБД SQL Server оптимізована для інтелектуального кешування даних
На замітку в пам'яті, що впливає на наступні тести. Пам'ять може бути поповнена за допомогою зупинки і перезапуску сервера, а також за допомогою команд dbcc DropCleanBuffersІ DBCC FreeProcCache.
У пакет оновлень SP1 були додані нові динамічні уявлення Увага! управління, які виконують моніторинг виділення пам'яті для запитів, -
sys.dm_exec_query_memory_grants і sys.dm_exec_query_resource_ semaphores.
У прикладах створення ключових індикаторів продуктивності бази даних ви можете використовувати базу PerfTest, доступну на сайті книги.
резюме
Вимірювання продуктивності є головним аспектом настройки і оптимізації бази даних. Для вимірювання загальної продуктивності бази даних ваша організація може використовувати ключові індикатори продуктивності (KPI), а для її оптимізації будуть потрібні детальні показники ефективності, здатні допомогти ідентифікувати проблему і усунути її. Програми SQL Server Profiler і System Monitor, разом з новими динамічними уявленнями управління, надає вам детальну інформацію про продуктивність бази даних.
Освоївши методи спостереження за ефективністю оптимізації, в наступному розділі ми вивчимо настройку ключових засобів SQL Server - запитів та індексів. Завдяки засобам вимірювання і вмінню налаштовувати запити і індекси ви зможете створювати високопродуктивні бази даних.
Аналіз запитів та налаштування індексів
Основні принципи інформаційної архітектури, викладені в розділі 1, свідчать, що головна передумова до планування будь-яких сховищ даних - це ознайомлення з вимогами до продуктивності.
Теорія оптимізації проклала шлях до досягнення цієї мети і заклала фундамент для проектування високопродуктивних баз даних, виявивши залежності між різними рівнями оптимізації.
Індексам теорія оптимізації відводить середній рівень. Вони залежать від якості проектування нормалізованої схеми і запитів. Без цих двох складових складно очікувати від індексації хороших результатів.
Немає ніяких сумнівів, що індексація життєво важлива для високої продуктивності бази даних. На індекси слід дивитися з перспективи загальної моделі бази. Вони залежать від рівнів схеми і запитів і в той же час забезпечують настройку масштабованості і конкуренції сервера.
Аналіз запитів та налаштування індексів є областю, в якій СУБД SQL Server 2005 була успішною. Сервер надає велику інформацію про план виконання запиту, його індекси швидкі, будуються за алгоритмом збалансованого дерева, їх легко налаштовувати, і навіть без всякої настройки вони прекрасно працюють.
Джерело: Нільсен, Пол. Microsoft SQL Server 2005. Біблія користувача. : Пер. з англ. - М.: ТОВ "І.Д. Вільямс ", 2008. - +1232 с. : Ил. - Парал. тит. англ.
