1. плани запитів
2. Знайомимося з планами виконання запитів

Анотація: Прочитавши цю лекцію, ви зможете: генерувати плани запитів, Читати плани запитів, розробляти бази даних відповідно до планованого способом використання даних, застосовувати Групові та некластерізованний індекси, індексувати стовпці XML, індексувати уявлення, виконувати дефрагментацію індексів, використовувати Помічник з налаштування ядра СУБД

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

плани запитів

Коли сервер SQL Server виконує запит, спочатку потрібно визначити найкращий спосіб виконання. Для цього потрібно розрахувати, як і в якому порядку звертатися до даних і з'єднувати їх, як і коли виконувати обчислення та агрегації і т. Д. За це відповідає підсистема, яка називається Query Optimizer (оптимізатор запиту). Оптимізатор запиту використовує статистичні дані про розподіл даних, метадані, пов'язані з об'єктів в базі даних, інформацію індексу і інші чинники для обчислення декількох можливих планів виконання запиту. Для кожного з цих планів оптимізатор запиту передбачає його вартість на основі статистики за цими даними і вибирає план з мінімальними витратами ресурсів на виконання. Звичайно, SQL Server не вирахував всіх можливих планів для кожного запиту, оскільки для деяких запитів самі ці обчислення можуть забрати більше часу, ніж виконання найменш ефективного з усіх планів. Отже, SQL Server використовує складні алгоритми, щоб знайти план виконання з розумною вартістю, близькою до мінімально можливої. Після того, як план виконання згенерований, він зберігається в буферному кеші (на що SQL Server виділяє більшу частину своєї віртуальної пам'яті). Потім план виконується тим способом, який оптимізатор запиту повідомляє ядру бази даних (компоненту database engine).

Чи зможе Query Optimizer (оптимізатор запитів) згенерувати ефективний план для конкретного запиту, залежить від наступних факторів:

• Індекси інфляції. Подібно змісту в книзі, індекс бази даних дозволяє швидко знайти певні рядки в таблиці. У таблиці може бути не один індекс. Завдяки наявності в таблиці індексів, оптимізатор запитів SQL Server може оптимізувати доступ до даних, вибравши для використання відповідний індекс. Якщо індекси відсутні, у оптимізатора запитів залишається тільки один варіант, який полягає в скануванні всіх даних, наявних в таблиці, в пошуку потрібних рядків. Далі в цій лекції наводиться інформація про те, як працюють індекси і як їх розробляти і проектувати.
• Статистика розподілу даних: SQL Server зберігає статистику про розподіл даних. Якщо ця статистика відсутня або застаріла, оптимізатор запитів не зможе обчислити ефективний план виконання запиту. У більшості випадків, статистичні дані генеруються і оновлюються автоматично. Далі в цій лекції розповідається про те, як генеруються статистичні дані і як можна управляти статистикою.

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

Примітка.

Звичайно, можливо, що неефективно виконаний запит виконувався відповідно до хорошим планом. У цих випадках справа не в оптимізації запиту. Швидше за все, проблема криється зовсім в іншому, наприклад, в проекті запиту, конфлікті доступу до даних, операцій введення / виводу, пам'яті, використання ЦПУ, мережевих ресурсів і т. П. Щоб отримати додаткову інформацію з цих проблем, рекомендуємо ознайомитися з офіційним документом "Troubleshooting Performance Problems in SQL Server 2005" (Пошук і рішення проблем з продуктивністю в SQL Server 2005), який доступний за наступним посиланням: http://www.microsoft.com/ technet / prodtechnol / sql / 2005 / tsprfprb.mspx .

Знайомимося з планами виконання запитів

1. У меню Start (Пуск) виберіть All Programs ,. Microsoft SQL Server 2005, SQL Server Management Studio (Всі програми, Microsoft SQL Server 2005, Среда SQL Server Management Studio). Натисніть кнопку New Query (Створити запит), щоб відкрити вікно нового запиту, і змініть контекст виконання на базу даних Adventure Works, вибравши її зі списку Available Databases (Доступні бази даних).
2. Виконайте наступну інструкцію SELECT. Код цього прикладу є в файлах прикладів під ім'ям Viewing Query Plans .sql. SELECT SalesOrderID, OrderQTY FROM Sales.SalesOrderDetail WHERE ProductID = 712 ORDER BY OrderQTY DESC
3. Щоб вивести на екран план виконання для цього запиту, натисніть комбінацію клавіш (Ctrl + L) або виберіть з меню Query (Запит) команду Display Estimated Execution Plan (Показати передбачуваний план виконання). План виконання показаний на наступному малюнку.

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

   • SQL Server звертається до даних за допомогою операції Clustered Index Scan (Перегляд кластерізованного індексу). Це сканування являє собою реальну операцію доступу до даних і детально розглядається далі.
   • Дані переходять до оператора Sort (Сортування), який сортує дані на основі пропозиції ORDER BY.
   • Дані пересилаються клієнтові.

   Ми розглянемо найважливіші оператори, які використовує SQL Server, коли будемо вивчати індекси і з'єднання. Повний список операторів можна знайти в Електронної документації SQL Server 2005, тема "Піктограми графічного представлення плану виконання".

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

4. Затримайте вказівник над оператором Clustered Index Scan (Перегляд кластерізованного індексу). Відкриється вікно з текстом на жовтому тлі, показане на наступному малюнку.

   У цьому вікні відображається детальна інформація про операції. До сих пір ми знали тільки те, що SQL Server отримує дані за допомогою операції сканування. Але в цьому вікні видно, що він виконує операцію Clustered Index Scan (Перегляд кластерізованного індексу) (яка детально розглядається нижче) на кластерізованний індексі таблиці Sales.SalesOrderDetail, а також пошук ProductID 712. Ця інформація знаходиться в секції Predicates (Предикати). Крім того, показані передбачувана вартість і передбачувана кількість рядків, а також розмір рядка. У той час, як кількість рядків оцінюється на основі статистики, яку SQL Server зберігає для цієї таблиці, значення вартості обчислюються на основі статистики і значень еталонної системи. Отже, значення вартості не слід використовувати для того, щоб розрахувати, скільки часу запит буде виконуватися на комп'ютері. Ці цифри можуть використовуватися тільки для виявлення більш дешевої або більш дорогої операції.

5. Цю інформацію про операторів можна побачити також у вікні Properties (Властивості) в SQL Server Management Studio. Щоб відкрити вікно Properties (Властивості), клацніть правою кнопкою миші на значку оператора і виберіть з контекстного меню команду Properties (Властивості).
6. Плани запитів можна також зберегти. Щоб зберегти план запиту, клацніть в панелі плану правою кнопкою миші і виберіть з контекстного меню команду Save Execution Plan As (Зберегти план виконання як). План зберігається в форматі XML з розширенням .sqlplan. Його можна відкрити через SQL Server Management Studio. вибравши з меню File (Файл) команди Open, File (Відкрити, Файл).
7. Те, що ви бачили до сих пір - це передбачуваний план виконання запиту, але можна переглянути і дійсний план виконання. Дійсний план виконання аналогічний передбачуваному планом виконання, але включає також дійсні (не припускав) значення кількості рядків, кількості перемоток і т. Д. Щоб включити в запит дійсний план виконання, натисніть (Ctrl + M) або виберіть з меню Query (Запит) команду Include Actual Execution Plan (Включити дійсний план виконання). Потім натисніть F5 і виконайте запит. Результати запиту відображаються як зазвичай, але ви побачите також план виконання, який показаний на вкладці Execution Plan (План виконання).