Оптимізація запитів в SQL Server 2005, статистика баз даних

Оптимізація запитів в SQL Server 2005, статистика баз даних SQL Server 2005, CREATE STATISTICS, UPDATE STATISTICS, SET NOCOUNT ON, плани виконання запитів, кількість логічних читань (logical reads), хинти оптимізатора (optimizer hints), MAXDOP, OPTIMIZE FOR, керівництва по планам виконання (plan guides), sp_create_plan_guide

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

Ставлення до оптимізації запитів у багатьох фахівців неоднозначне. З одного боку, робота програмного модуля Query Optimizer, який генерує плани виконання запитів, викликає безліч справедливих нарікань і в SQL Server 2000, і в SQL Server 2005. Query Optimizer часто вибирає не найоптимальніші плани виконання запитів і в деяких ситуаціях програє аналогічним модулів з Oracle і Informix. З іншого боку, ручна оптимізація запитів - процес надзвичайно трудомісткий. Ви можете витратити багато часу на таку оптимізацію і, врешті-решт, з'ясувати, що нічого оптимізувати не вдалося: план, запропонований Query Optimizer спочатку, виявився найбільш оптимальним (так буває в більшості випадків). Крім того, може статися так, що створений вами вручну план виконання запитів через якийсь час (після додавання нової інформації в базу даних) виявиться неоптимальним і буде негативно впливати на продуктивність при виконанні запитів.

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

Статистика - це спеціальна службова інформація про розподіл даних в стовпчиках таблиць. Уявімо, наприклад, що виконується запит, який повинен повернути всіх Іванових, які проживають в місті Санкт-Петербурзі. Припустимо, що у 90% записів в цій таблиці одне і те ж значення в стовпці Місто - "Санкт-Петербург". Звичайно, з точки зору виконання запиту спочатку вигідніше вибрати в таблиці всіх Іванових (їх явно буде не 90%), а потім вже перевіряти значення стовпця Місто для кожної відібраної записи. Однак для того, щоб дізнатися, як розподіляються значення в стовпці, потрібно спочатку виконати запит. Тому SQL Server самостійно ініціює виконання таких запитів, а потім зберігає інформацію про розподіл даних (яка і називається статистикою) в службових таблицях бази даних.

Для баз даних SQL Server 2005 за замовчуванням встановлюються параметри AUTO_CREATE_STATISTICS і AUTO_UPDATE_STATISTICS. При цьому статистика для стовпців баз даних буде створюватися і оновлюватися автоматично. Для найбільших і важливих баз даних може вийти так, що операції по створенню і оновленню статистики можуть заважати поточній роботі користувачів. Тому для таких баз даних іноді ці параметри відключають, а операції по створенню і оновленню статистики виконують вручну в нічний час. Для цього використовуються команди CREATE STATISTICS і UPDATE STATISTICS.

Тепер поговоримо про оптимізацію запитів.

Перше, що необхідно зробити, - знайти ті запити, які в першу чергу підлягають оптимізації. Найпростіше це зробити за допомогою Профілювальники, встановивши фільтр на час виконання запиту (фільтр Duration в вікні Edit Filter (Редагувати фільтр), яке можна відкрити за допомогою кнопки Column Filters на вкладці Events Selection вікна властивостей сеансу трасування). Наприклад, в число кандидатів на оптимізацію можуть потрапити запити, час виконання яких склав понад 5 секунд. Крім того, можна використовувати інформацію про запити, яка надається Database Tuning Advisor.

Потім потрібно перевірити, встановлюється чи для ваших з'єднань, збережених процедур і функцій параметр NOCOUNT. Встановити його можна за допомогою команди SET NOCOUNT ON. При установці цього параметра, по-перше, відключається повернення з сервера і висновок інформації про кількість рядків в результатах запиту (т. Е. Не відображається рядок "N row (s) affected" на вкладці Messages (Повідомлень) вікна роботи з кодом при виконанні запиту в Management Studio). По-друге, відключається передача спеціального серверного повідомлення DONE_IN_PROC, яке за замовчуванням повертається для кожного етапу збереженої процедури. При виклику більшості збережених процедур потрібен тільки результат їх виконання, а кількість оброблених рядків для кожного етапу нікого не цікавить. Тому установка параметра NOCOUNT для збережених процедур може серйозно підвищити їх продуктивність. Підвищується швидкість виконання і звичайних запитів, але в меншому ступені (до 10%).

Після цього можна приступати до роботи з планами виконання запитів.

План виконання запиту найпростіше переглянути з SQL Server Management Studio. Для того щоб отримати інформацію про очікуване плані виконання запиту, можна в меню Query (Запит) вибрати команду Display Estimated Execution Plan (Показати очікуваний план виконання). Якщо ви хочете дізнатися реальний план виконання запиту, можна перед його виконанням встановити в тому ж меню параметр Include Actual Execution Plan (Включити реальний план виконання). У цьому випадку після виконання запиту у вікні результатів в SQL Server Management Studio з'явиться ще одна вкладка Execution Plan (План виконання), на якій буде представлено реальний план виконання запиту. При наведенні покажчика миші на будь-який з етапів можна отримати про нього додаткову інформацію (рис. 11.15).

Мал. 11.15. План виконання запиту в SQL Server Management Studio

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

q спочатку у вікні Management Studio виконайте команду SET STATISTICS IO ON. В результаті після кожного виконання запиту буде виводитися додаткова інформація. У ній нас цікавить значення тільки одного параметра - Logical Reads. Цей параметр означає кількість логічних читань при виконанні запитів, т. Е. Скільки операцій читання довелося провести при виконанні даного запиту без урахування впливу кеша (кількість читань і з кешу, і з диска). Це найбільш важливий параметр. Кількість фізичних читань (читань тільки з диска) - інформація не надто представницька, оскільки залежить від того, чи були перед цим звернення до даних таблиць чи ні. Статистика за часом також є величиною змінною і залежить від інших операцій, які виконує в цей час сервер. А ось кількість логічних читань - найбільш об'єктивний показник, на який в найменшій мірі впливають додаткові фактори;

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

Хінтів оптимізатора в SQL Server 2005 передбачено багато. Прочитати інформацію про них можна в Books Online (в списку на вкладці Index (Індекс) потрібно вибрати Query Hints [SQL Server] (хинти запитів [SQL Server]), Join Hints (хинти Джойна) або Table Hints [SQL Server] (Табличні хинти [SQL Server])). Найчастіше використовуються такі хинти:

q NOLOCK, ROWLOCK, PAGLOCK, TABLOCK, HOLDLOCK, READCOMMITTEDLOCK, UPDLOCK, XLOCK - ці хинти використовуються для управління блокуваннями (див. розд. 11.5.7 );

q FAST колічество_строк - буде обраний такий план виконання запиту, при якому максимально швидко буде виведено вказану кількість рядків (перше з початку набору записів). Якщо користувачеві потрібні саме перші записи (наприклад, останні замовлення), то для їх максимально швидкого завантаження у вікно програми можна використовувати цей хинт;

q FORCE ORDER - об'єднання таблиць при виконанні запиту буде виконано точно в тому порядку, в якому ці таблиці перераховані в запиті;

q MAXDOP (від Maximum Degree of Parallelism - максимальний ступінь розпаралелювання запиту) - за допомогою цього хинта вказується максимальна кількість процесорів, які можна буде використовувати для виконання запиту. Зазвичай цей хинт використовується в двох ситуаціях:

· Коли через перемикання між процесорами (context switching) швидкість виконання запиту сильно знижується. Така поведінка була характерна для SQL Server 2000 на багатопроцесорних системах;

· Коли ви хочете, щоб якийсь важкий запит надав мінімальний вплив на поточну роботу користувачів;

q OPTIMIZE FOR - цей хинт дозволяє вказати, що запит оптимізується під конкретне значення переданого йому параметра (наприклад, під значення фільтра для WHERE);

q USE PLAN - це найпотужніша можливість. За допомогою такого хинта можна явно визначити план виконання запиту, передавши план у вигляді строкового значення в форматі XML. Хінт USE PLAN з'явився тільки в SQL Server 2005 (в попередніх версіях була можливість явно визначати плани виконання запитів, але для цього використовувалися інші засоби). План в форматі XML можна написати вручну, а можна згенерувати автоматично (наприклад, клацнувши правою кнопкою миші по графічному екрану з планом виконання, представленому на рис. 11.15, і вибравши в контекстному меню команду Save Execution Plan As (Зберегти план виконання як)).

У SQL Server 2005 з'явилася нова важлива можливість, яка дозволяє вручну змінювати план виконання запиту без необхідності втручатися в текст запиту. Дуже часто буває так, що код запиту можна змінити: він жорстко "прошитий" в коді відкомпільованого додатки. Щоб впоратися з цією проблемою, в SQL Server 2005 з'явилася збережена процедура sp_create_plan_guide. Вона дозволяє створювати так звані керівництва за планами виконання (plan guides), які будуть автоматично застосовуватися до відповідних запитах.

Якщо ви аналізуєте запити, які надсилає до бази даних якесь додаток, то має сенс в першу чергу звернути увагу на наступні моменти:

q наскільки часто в планах виконання запиту зустрічається операція Table Scan (Повне сканування таблиці). Цілком може виявитися, що звернення до таблиці за допомогою індексів буде ефективніше;

q використовуються чи в коді курсори. Курсори - дуже простий засіб з точки зору синтаксису програми, але надзвичайно неефективне з точки зору продуктивності. Дуже часто можна уникнути застосування курсорів, використовуючи інші синтаксичні конструкції, і отримати великий виграш в швидкості роботи;

q використовуються чи в коді тимчасові таблиці або тип даних Table. Створення тимчасових таблиць і робота з ними вимагають великої витрати ресурсів, тому по можливості потрібно їх уникати;

q крім створення тимчасових таблиць, значної витрати системних ресурсів вимагає і зміна їх структури. Тому команди на зміну структури тимчасових таблиць повинні відразу залучати вашу увагу. Зазвичай є можливість відразу створити тимчасову таблицю з усіма необхідними стовпцями;

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

q якщо додаток передає на сервер команди EXECUTE, то має сенс подумати про те, щоб замінити їх на виклик збереженої процедури sp_executesql. Вона має переваги в продуктивності в порівнянні зі звичайною командою EXECUTE;

q підвищення продуктивності іноді можна домогтися, усунувши необхідність повторної компіляції збережених процедур і побудови нових планів виконання запитів. Потрібно звернути увагу на застосування параметрів, постаратися не змішувати в коді процедури, що команди DML і DDL і стежити за тим, щоб визначити спеціальні установки SET ANSI_DEFAULTS, SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS і SET CONCAT_NULL_YIELDS_NULL не змінювалися між запитами (будь-яка зміна таких параметрів призводить до того, що старі плани виконання вважаються недійсними). Зазвичай проблема може виникнути тоді, коли ці параметри встановлюються на рівні окремого запиту або в коді процедури, що.

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