Налаштування індексів

Озброївшись знаннями про індекси і планах виконання запиту, можна виявити деякі проблеми продуктивності і вирішити їх, використовуючи індекси

Відсутність індексів

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

I SQL Server 2005 може відобразити статистику використання індексів за допомогою на-S VS щью Дінаміческіх уявлень управління Зокрема, sysdm_db_index_ * I * operational_stats і sysdm_index_usage_stats розкривають інформацію ■ про те, як використовуються індекси Додатково існують чотири динамічних подання управління, які розкривають інформацію про те, які індекси шукав оптимізатор запитів, але не зміг знайти Це sysdm_ missing_index_groups, sysdm_missing_index_group_stats, sysdm_ missing_index_columns і sysdm_missing_index_details

У першому наводиться прикладі використовується простий запит в базі даних OBXKites Наступний простий код витягує дані і впорядковує їх за StartDate:

SET STATISTICS TIME ON

USE Adventureworks

SELECT WorkOrderlD

FROM ProductionWorkOrder ORDER BY StartDate

Буде отримано наступний результат (час з статистики):

SQL Server Execution Times:

CPU time = 126 ms, elapsed time = 679 ms

Цей запит використовує сканування кластеризованого індексу, за чим слід операція сортування для впорядкування даних по StartDate У даному прикладі операція сортування є ознакою того, що відсутня індекс

Створимо індекс по StartDate, щоб скоректувати план виконання:

CREATE INDEX WOStartDate ON ProductionWorkOrder (StartDate)

Повторний запуск запиту показує значне зменшення часу роботи процесора – з 126 до 63 мілісекунд При цьому новий план виконання запиту покаже тільки сканування некластерізованний індексу по WOStartDate

Наступний, більш складний план запиту також розкриває потреба в індексі:

USE OBXKites

SELECT LastName, FirstName, ProductName

FROM dboContact JOIN dbo[Order]

ON ContactContactID = [Order]ContactID JOIN dboOrderDetail

ON [Order]OrderlD = OrderDetailOrderlD JOIN dboProduct

ON OrderDetailProductID = ProductProductID JOIN dboProductCategory

ON ProductProductCategorylD = ProductCategoryProductCategorylD

WHERE ProductCategoryName = ’Kite’

ORDER BY LastName, FirstName

Будуть отримані наступні результати (час з статистики):

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 146 ms

Загальний час виконання мало зважаючи невеликих розмірів бази даних Проте план виконання запиту виявляє деякі проблеми (рис 504) Одна з цих проблем виявляється в тому, що виконується сканування таблиці, обєднаної з використанням операції порівняння купи Порівняння купи обєднує таблиці OrderDetail і Product Єдиний індекс таблиці OrderDetail в цій копії бази OBXKites є Некла- стерізованним первинним ключем, що включає в себе стовпець OrderDetail ID

Ми почнемо вирішення цієї проблеми з створення некластерізованний індексу по полю ProductID При наявності цього індексу сканування таблиці і порівняння купи замінюються скануванням індексу і вкладеним циклом Однак некластерізованний індекс не покриває потреби запиту, тому генерується додаткова сторінка даних (рис 505)

Пошук закладки

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

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

В якості ілюстрації можна використовувати наступний приклад Припустимо, що в предметному покажчику книги ви виявили потрібну тему, але вона посилається на 200 розрізнених сторінок книги Звернення до 200 сторінкам в тексті книги є витратною за часом операцією (див рис 505)

Рис 504 Операції сканування таблиці і порівняння купи є чіткою ознакою відсутності індексу

Puc 505 Операція пошуку в RID і вкладений цикл виявляють проблему пошуку закладки

Пошук закладки фізично виконується методом добування інформації з сторінок даних та обєднання її з результатами ^ кластеризованого індексу В даному випадку це операція пошуку RID, так як не існує кластеризованого індексу, однак це може бути операцією пошуку в кластеризувати індексі або його Сканування

Для вирішення проблеми пошуку закладки слід додати поле OrderDetail ID до непастеризованого індексом ProductID, щоб обєднуються дані безпосередньо витягали з некластерізованний індексу У Як альтернативу можна перетворити первинний ключ в кластерізованний індекс, який замінить RID в некластерізованний індексі ключами кластеризованого індексу Обидва рішення дозволять некластерізованний індексом ProductID повністю покрити вимоги запиту до таблиці OrderDetail і уникнути пошуку закладки Так як дані OrderDetail часто витягуються по зовнішньому ключу OrderlD, він є найкращим кандидатом на кластерізованний індекс З цієї причини кращим рішенням є додавання поля OrderDetail ID в індекс ProductID

Оптимізується аргумент пошуку

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

■ Безліч умов, обєднаних оператором AND, є SARG, в той час як обєднані оператором OR – ні

■ Умови, що містять заперечення (о,>, <, Not Exists, Not In і Not Like), не є оптимизируемого. Дуже легко довести, що певна рядок існує. У той же час, щоб довести, що її не існує, доведеться перевірити всі рядки.

■ Умови, що починаються з символу макропідстановки, не використовують індекси Індекс допоможе легко знайти прізвище Smith, але для того щоб знайти всі прізвища, які містять ith, доведеться просканувати всі рядки

■ Умови з виразами не сумісні з SQL Server, тому повинні бути розбиті з використанням алгебри для полегшення перевірки правильності даних, що вводяться

■ Якщо пропозиція WHERE містить у собі функцію, наприклад строковую, то для перевірки всіх рядків із застосуванням до даних функції буде потрібно сканування таблиці

Вибірковість індексів

Ще одним аспектом налаштування індексів є їх вибірковість Більш виборчий індекс враховує більше значень і відбирає меншу кількість даних одним своїм значенням Первинний ключ або унікальний індекс має найбільшу вибірковість

Індекс, що має всього кілька значень, розподілених по великої таблиці, є менш виборчим Малоізбірательние індекси можуть виявитися зовсім марними в запитах Стовпець, який має в тисячу рядків всього три значення, є поганим кандидатом на створення індексу Бітовий стовпець має малу вибірковість, і його індексування марно

SQL Server використовує свою внутрішню статистику індексів для відстеження їх вибірковості Команда DBCC Show_Statistics видає звіт про те, коли востаннє була оновлена ​​статистика, і видає основну інформацію про статистику індексів, а також

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

Use СНА2

DBCC Show_Statistics (Customer, IxCustomerName)

Будуть отримані наступні результати (відформатовані і усічені повний лістинг містить деталі про кожен значенні індексу):

Statistics for INDEX IxCustomerName.

Rows                                                                                                                   Average

Updated Rows Sampled Steps Density key length

May 1,02 42 42                                  33                     00                           11547619

All density Average Length Columns

3 03 03 031E-2 66904764                               LastName

2 3809524E-2 11547619                                  LastName, FirstName

DBCC execution completed If DBCC printed error messages, contact your system administrator

Іноді зміна порядку ключових стовпців здатна підсилити вибірковість індексу та поліпшити його продуктивність Однак будьте обережні, так як продуктивність інших запитів може сильно залежати від поточного порядку ключових стовпців в індексі

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*