Десять найважливіших проблем продуктивності SQL Server 2005 для додатків сховищ даних та
Робочі навантаження реляційних сховищ даних або побудови звітів відрізняються невеликими обсягами великих транзакцій. Таким додаткам часто властиве переважне навантаження при операціях читання (наприклад, у додатках підтримки рішень, аналізу та побудови звітів) з періодичними видачами даних або пакетними завантаженнями. Про ці особливості важливо пам'ятати, розглядаючи значущість проектування бази даних, використання ресурсів та продуктивності системи. Нижче розглянуто найважливіші вузькі місця або проблеми продуктивності, яких слід уникати при створенні та підтримці сховищ даних та додатків побудови звітності.
Проблеми проектування бази даних, якщо….
Виконуються надмірні операції сортування. Якщо одні й самі операції сортування доводиться виконувати знову і знову, цього можна уникнути з допомогою відповідних індексів.
Виконуються зайві підстановки відносних ідентифікаторів на невпорядкованих таблицях. Підстановки ідентифікаторів означають, що для отримання не включених у індекс стовпців, що використовується, потрібні додаткові операції введення-виведення. Цього можна уникнути за допомогою включених до об'єднання некластеризованих індексів.
Пошук ключа за ключом кластеризації схожий на об'єднання, однак у XML Showplan це позначено як пошук. Цього можна уникнути за допомогою включених до об'єднання некластеризованих індексів.
Відсутній потенційно вигідний індекс по стовпцях, що беруть участь у об'єднанні, що призводить до об'єднання хешуванням. Уникнути об'єднання HASH JOIN можна, створивши індекси стовпців об'єднання.
Проблеми використанняпроцесора….
Якщо показники signal waits > 25% від загальної кількості показників очікування, отже, процесор є «вузьким місцем». Дивіться показники очікування signal waits та total waits у динамічному поданні sys.dm_os_wait_stats. Показники signal waits означають час, проведений у черзі виконання в очікуванні процесора. Високе значення signal waits вказує на вузьке місце для обробки процесором.
Уникайте недоцільного повторного використання плану. Повторне використання плану виконання запиту добре, якщо запит ідентичний. Однак параметризація запиту, що дозволяє повторне використання плану, має сенс тільки якщо результат запиту (і проміжні робочі таблиці) такі самі, як у початковому плані. Якщо розмір набору результатів істотно змінюється в залежності від значень параметрів, що є типовою ситуацією для сховищ даних, повторне використання плану може призвести до погіршення результатів. Невідповідний план запиту може призводити також до збільшення часу виконання запиту та зростання навантаження на пам'ять або підсистему введення-виведення. Тому в таких випадках витрати на створення плану кращі за його повторне використання. На відміну від додатків баз даних OLTP, запити сховищ даних не завжди ідентичні в тому, що стосується наборів результатів або оптимальних планів виконання.
Вузьке місце у підсистемі пам'яті….
Різке значне зниження очікуваного терміну життя сторінки. Додатки сховищ даних (наприклад, великі транзакції) можуть зазнавати значного зниження очікуваного терміну життя сторінки. Це відбувається через скидання кешу великої операції читання. Див. об'єкт системного монітора SQL Server Buffer Manager.
Очікуючі запитина надання пам'яті. лічильник запитів, які очікують надання пам'яті об'єкта SQL Server Memory Manager системного монітора. Виділення великих обсягів пам'яті характерне для додатків сховищ даних. Додавання пам'яті може покращити ситуацію, інакше виконання неможливо до того, як відбудеться виділення пам'яті.
Несподівані падіння або низький рівень ефективності роботи кеш-пам'яті SQL. Падіння або постійно низький рівень ефективності роботи кеш-пам'яті може вказувати на нестачу пам'яті або відсутні індекси.
Вузьке місце в підсистемі введення-виведення….
Найкращим показником продуктивності запису диска є час у секундах читання і запис. Коли навантаження на систему введення-виводу незначне, черга диска не використовується, тому час на запис або читання буде мінімальним. Зазвичай завершення операції читання потребує 4-8 мілісекунд за умов нормального завантаження. Факторами, що впливають на продуктивність системи введення-виведення, є кількість шпинделів і такі показники продуктивності диска, як кількість послідовних та випадкових операцій (за даними виробника). Зі зростанням кількості операцій введення-виведення може виникати черга дискових операцій. Результатом черги диска є зростання часових витрат на операції читання та запису. Періодичне зростання часу на читання-запис може бути прийнятним для багатьох додатків. Для потреб високопродуктивних додатків OLTP використовуються складні підсистеми мережевого зберігання SAN, що забезпечують велику масштабованість введення-виведення та надійну обробку піків активності введення-виведення. Постійно високі значення часу на операцію читання (15 мс) вказують на вузьке місце в дисковій підсистемі.
Високі значення часу наоперацію читання. лічильники продуктивності логічних або фізичних дисків системного монітора. Завантаження сховищ даних може здійснюватися за допомогою операцій вставки, оновлення та видалення з веденням журналу, або за допомогою операцій масового копіювання без ведення журналу. Ведення журналу операцій вимагає запису до журналу транзакцій. Швидкість запису в журнал транзакцій може досягати 1мс і менше у високопродуктивних середовищах SAN. Для багатьох програм періодичний сплеск середнього часу на операцію запису прийнятний, враховуючи високу вартість складних підсистем SAN. Однак, постійно високі значення середнього часу на операцію запису є точним показником вузького місця в дисковій підсистемі.
Причиною важких операцій введення-виведення, наприклад, сканувань таблиць та діапазонів, може бути відсутність індексів.
Вузьке місце через блокування….
Суперництво індексів. Шукайте у поданні sys.dm_db_index_operational_stats високі значення очікування блокувань та замикань. Порівняйте із запитами блокувань та замикань.
Високі середні значення очікування блокувань та очікувань. Середні значення очікування блокувань та замикань записів обчислюються розподілом часу очікування блокувань у мілісекундах на очікування. Середній час очікування блокування в мілісекундах, розрахований за даними sys.dm_db_index_operational_stats, є середнім часом кожного блокування.
Звіт про процес блокування повідомляє про тривалі блокування. Див. команду sp_configure для встановлення порогового значення інтервалу очікування заблокованих процесів звіт про заблоковані процеси профільника у події «Помилки та попередження».
Велика кількість взаємоблокувань. Див. графічне подання взаємоблокувань впрофільувальнику у випадку блокування для визначення інструкцій, що беруть участь у взаємоблокуванні.
Вузьке місце в мережі….
Великі затримки мережі, а також програма, що постійно звертається до бази даних.
Пропускна спроможність мережі недостатня. лічильники пакетів в секунду та поточні лічильники пропускної здатності в об'єкті мережного інтерфейсу системного монітора. Длякадрів TCP/IP реальна пропускна спроможність розраховується як кількість пакетів за секунду * 1500 * 8 /1000000 Мбіт/сек.
Проблеми статистики очікувань.
Оскільки навантаження сховищ даних та звітів є в основному операціями читання, сумісними з іншими операціями читання, зазвичай несумісні монопольні блокування виникають при пакетному завантаженні даних або періодичних завантаженнях. Якщо статистика очікувань відображає LCK_x. або PAGELATCH_EX, див. пояснення sys.dm_os_wait_stats у посібнику “SQL Server 2005 Performance Tuning using Waits & Queues”.
Якщо статистика очікувань в sys.dm_os_wait_stats відноситься до операцій введення-виводу, таких, як ASYNCH_IO_COMPLETION, IO_COMPLETION, LOGMGR, WRITELOG, або PAGEIOLATCH_x, то є вузьке місце в операціях введення-виведення.
Великі сховища даних можуть отримати переваги від більшої кількості індексів. Індекси можуть використовуватись у запитах, щоб уникнути сортування. Для застосування сховища даних витрати на побудову індексу істотні тільки в момент завантаження даних.
Дивіться записи про відсутні індекси в sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups та sys.dm_db_missing_index_details
Надмірна фрагментація створює проблеми великих операцій вводу-вывода. Функція таблиці динамічного керуванняsys.dm_db_index_physical_stats повертає відсоток фрагментації у стовпці avg_fragmentation_in_percent. Фрагментація має перевищувати 25%. Від зниження фрагментації індексів можуть виграти операції сканування великих діапазонів даних, звичайні у додатках сховищ даних та звітів.
Для прискорення завантаження можна застосувати секціювання таблиць
На відміну від OLTP, додаткам сховищ даних та звітів властиво невелика кількість великих транзакцій SELECT, які сильно відрізняються один від одного. Ці деталі суттєві для проектування бази даних, використання ресурсів та продуктивності системи. Ці відмінності призводять до суттєвих цілей і профілів використання ресурсів.