Налаштування SQL Server 6.5 на обробку запитів c високою продуктивністю, MS SQL Server, Бази даних, статті

Моріс Льюїс, Журнал “SQL Server Magazine ONLINE”

Адміністраторам баз даних напевно доводилося налаштовувати сервер бази даних на швидку і ефективну обробку посилаються додатками запитів. Незалежно від того, ким було розроблено програму, – Сторонніми фірмами або своєю командою програмістів, на програму краще дивитися, як на чорний ящик, в якому нічого змінити не можна. Виконати настройку продуктивності на рівні сервера можна різними способами: поліпшенням організації введення / виведення даних на диски, збільшенням пам'яті або створенням і модифікацією індексів. Але з іншого боку, продуктивність прикладної системи залежить від конструкції бази даних і від написаних для неї запитів. Ті розробники додатків, що використовують бази даних, які розуміють, яким чином SQL Server оптимізує і обробляє запити, зазвичай створюють програмне забезпечення, що володіє найкращою продуктивністю. І у них не виникає проблем і неприємних сюрпризів при розширенні масштабу програми від невеликої системи до великого проекту. Існує кілька простих прийомів для SQL Server 6.5, які забезпечать оптимальну продуктивність, якщо застосувати їх до бази даних і до запитів.

Що оптимізувати?

SQL Server 6.5 використовує вартісної оптимізатор запитів. Для більшості запитів найбільший внесок у вартість вносять операції введення / виводу, пов'язані з використанням диска. Оскільки швидкість роботи жорсткого диска в сотні разів нижче швидкості виконання операцій в оперативній пам'яті, то що б не робилося для зменшення числа звернень до диску, безумовно, вплине на продуктивність. Тому на базовому рівні спочатку слід спробувати оптимізувати фізичний ввід / вивід, – зчитування сторінки з жорсткого диска, а вже потім логічний уведення / висновок, – зчитування сторінки пам'яті.

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

Як приклад розглянемо просте об'єднання двох таблиць:

SELECT .....
......

Один із клієнтів запитав автора статті, чому цей запит виконується так довго. Після того, як автор статті запустив утиліту SHOWPLAN і глянув на план запиту, відповідь став очевидним: таблиця Payroll_Checks не мала індексу за стовпцем empId. У таблиці Employees містилося близько 10000 записів, а таблицю Payroll_Checks становила 750 000 рядків. Оскільки індекс відсутній, SQL Server сканував таблицю Payroll_Checks 10 000 разів. Коли автор перервав виконання запиту, сервер вже виконав 15000000 логічних операцій введення / виводу. Створення індексу по стовпцю скоротило час обробки до секунд, а число логічних операцій введення / виведення до приблизно 750000.

Магічний розмір рядка

Перший крок в мінімізації операцій вводу / виводу – переконатися в тому, що рядок зроблена настільки компактною, наскільки це можливо. У SQL Server рядка не можуть тягнутися на кілька сторінок. У SQL Server 6.5 заголовок сторінки не може перевищувати 32 байтів, а дані – займати більш 2016 байтів. Кожен рядок даних містить також область переповнення рядка. Максимально допустима довжина окремого рядка складає 1962 байт, включаючи область переповнення. Це обмеження вибрано з таким розрахунком, щоб вставляється або видаляється рядок бази даних змогла б також поміститися в рядок журналу транзакцій. Тому, хоча довжина одного рядка і не перевищить 1962 байтів, два рядки можуть повністю зайняти всі 2016 байтів, відведених під дані на сторінці. Наслідком цього є той факт, що певні значення довжини рядка можуть істотно знизити відсоток операцій введення / виводу. Наприклад, якщо довжина рядка становить 1009 байтів, то на сторінці вміститься лише один рядок. Якщо ж зменшити довжину рядка всього тільки на один байт, то на сторінку помістяться два рядки. Тобто, можна наполовину знизити звернення до введення / висновку для таблиці, прибравши всього один байт! Аналогічні ситуації мають місце для наступних розмірів рядків: 673, 505, 404 байтів і т.д. Якщо вам вдасться зберегти розмір рядки нижче зазначених меж, то тим самим ви зменшите частку операцій введення / виводу відповідно на 33, 25 і 20 відсотків.

Всі рядки можуть мати кілька байтів переповнення, які слід враховувати при розрахунках. Зазначимо, що переповнення рядків змінної довжини більше, ніж переповнення рядків фіксованої довжини. Щоб з'ясувати, чи є у вас на сторінках невикористовуване місце, запустіть DBCC SHOWCONTIG. Це дозволить вам визначити середню щільність сторінки і середнє число вільних байтів на сторінці. Найбільш ймовірними кандидатами на звання "чемпіона за марнотратства простору" будуть ті таблиці, у яких середня кількість вільних байтів близько до розміру рядка.

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

Непередбачений введення / висновок при оновленнях

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

UPDATE ....
     9102 .....

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

UPDATE .....

Якщо au_id є первинним (або унікальне) ключем, то оновлення першого рядка може призвести до порушення обмеження унікальності, особливо якщо au_id – монотонно зростаюча величина. Але ж оператор UPDATE коректний, так яким же чином SQL Server зможе виконати його без порушення обмежень? Тут SQL Server вдається до використання режиму відстроченого оновлення, при якому операція оновлення розбивається на дві частини: спочатку видаляється стара рядок, а потім вводиться нова, що містить потрібну установку.

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

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

SQL Server 6.5 здатний виконувати операції оновлення чотирма різними способами. Найшвидшим є пряме оновлення заміщенням. При цьому не відбувається ніяких переміщень, а в журнал транзакцій поміщається єдиний запис, що містить інформацію про те, які байти отримали нові значення. Найповільнішим способом є відстрочене оновлення, яке було описано вище. Обидва інші способу являють собою прямі оновлення (тобто ніяких зайвих записів в журнал транзакцій не виробляється), але запис нових значень відбувається не на те ж саме місце, на якому містилися оновлювані дані. Тому деякі переміщення даних все-таки мають місце. Щоб оновлення, яке ви збираєтеся зробити, проводилося в режимі прямого поновлення заміщенням, повинен бути виконаний такий довгий список умов, що в даній статті просто не представляється можливим все це викласти. В "SQL Server 6.5 Books Online" (BOL) є розділ, званий "Прямий режим оновлення" (The update mode is direct). У ньому перераховані деякі умови, які неодмінно повинні виконані, щоб SQL Server справив оновлення прямим заміщенням. Однак у BOL іноді плутаються пряме оновлення і заміщення, що призводить до некоректності деяких міркувань. Найповнішим опублікованим описом різних типів оновлень визнана книга "Всередині SQL Server 6.5" (Inside SQL Server 6.5) Рона Саукапа, випущена в 1997 році видавництвом Microsoft Press.

Два основні умови, які неодмінно повинні бути виконані, щоб оновлення проводилося в прямому режимі методом заміщення, полягають в наступному. По-перше, не можна оновлювати ключові стовпці в кластеризувати індексі, а по-друге, таблиця не може бути позначена для реплікації. Модифікації кластеризувати індексу змушують SQL Server переміщати рядок на нове фізичне місце, що відповідає її змісту. А це завжди супроводжується спочатку видаленням, а потім вставкою рядка. При реплікації відбувається читання журналу та формування команд ODBC для передплатників. Тому комбінація видалення / вставка представляється найбільш простим описом операції оновлення. Обидві ситуації виключають оновлення прямим заміщенням.

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

Прекрасні результати приносить знання цих обмежень та їх врахування при конфігуруванні бази даних, особливо коли ви прагнете вичавити все можливе з продуктивності при оновленнях. Застосування методів, забезпечують пряме заміщення, дозволяє заощадити на введенні / виводі при записі в журнал, на введенні / виводі при читанні логічних сторінок журналу, і крім того, зберегти час на резервування та відновлення журналу, а також при відновленні бази даних. При проектуванні баз даних корисно дотримуватися стандарту, відповідно до якого слід використовувати лише стовпці фіксованої довжини, що не містять невизначені значення. Якщо ви, читачу, програмуєте, то пам'ятайте про властивості оновлюваних стовпців і враховуйте їх вплив на продуктивність при написанні операторів UPDATE. Крім того, ретельно вибирайте момент для запуску цих операторів.

Аргументи пошуку

Найпростіший спосіб знизити обсяг введення / виводу, необхідного для обробки запиту – це зменшити кількість рядків, які повинен проаналізувати SQL Server. Це робиться за допомогою завдання вибіркових критеріїв пошуку в обороті WHERE, що входить в структуру запиту. Ці критерії звичайно називаються аргументами пошуку. Вони допомагають оптимізатору запитів, даючи підказки щодо того, який метод доступу до даних виявиться найшвидшим. Аргументи пошуку видаються як наступного запису:

Ім'я стовпця оператор [Ім'я стовпця або константа]

де оператором може бути один з наступних знаків порівняння =, <,>, <=, <=. Аргументи пошуку можуть бути з'єднані булевих оператором AND. Фраза BETWEEN ….. AND також допустима, оскільки задається нею умова може бути по-іншому сформульовано за допомогою операторів> = і <=. Нижче наведено кілька прикладів аргументів пошуку:

LastName = .....
LastName  >= .....
OrderDate .....

Зверніть увагу на те, що не був згаданий жоден негативний оператор. Для обробки запиту, що містить оборот WHERE (ProductId <> 2) SQL Server переглядає кожен рядок, перевіряючи, не дорівнює Чи її значення двом. Навіть індекс по ProductId не так вже сильно полегшує ситуацію, якщо тільки рядки, які містять значення 2 не становлять дуже незначну частину таблиці. Майже у всіх випадках SQL Server виконає цей запит переглядом таблиці, а не індексу.

З точки зору оптимізації запитів оператор LIKE майже настільки ж неефективний, як і оператор NOT. Якщо у вашому запиті присутній, наприклад, такий оборот

WHERE LastName LIKE '% Мс%',

то SQL Server почне пошук заданого зразка в усьому стовпці. Індекс не допоможе, тому можна припускати, що оптимізатор запитів вибере сканування таблиці. Існує тільки один тип винятків, – Коли аргумент пошуку виглядають, наприклад, наступним чином:

WHERE  LastName LIKE 'Le%'.

Різниця полягає в тому, що цей критерій пошуку логічно еквівалентний висловом


WHERE LastName >= ‘Le’ AND LastName < ‘LF’,

яке за визначенням є аргумент пошуку.

Взагалі кажучи, аргументи пошуку допомагають запитам тим, що полегшують оптимізатору запитів визначення ступеня селективності індексу при обробці даного запиту. Обороти, що використовують оператори =, <, >, Є саме такими аргументами пошуку, оскільки вони обмежують область пошуку тільки рядками, що потрапляють в результуючий набір. Оператор = обмежує область пошуку до єдиного рядка, а оператори <і> звужують її до деякого діапазону.

Селективність обороту відображає, наскільки ефективно аргумент пошуку звужує область перегляду. Цей показник може бути визначений відношенням числа повертаються рядків до сумарної кількості рядків у таблиці. (Наведене визначення навмисне трохи спрощено, для того щоб зробити обговорення більш наочним.) Низький відсоток означає, що оборот володіє високою селективністю, навпаки, високий відсоток відповідає слабкою селективності. Оскільки оператор AND коммутатівен (тобто, a AND b означає те ж саме, що і b AND a), оптимізатор запитів може вибирати для обробки запиту найбільш селективний оборот з числа обертів, об'єднаних оператором AND. Це виправдано, адже вибір найбільш селективного обороту здатний помітно знизити обсяг виконуваних операцій введення / виводу.

Як приклад розглянемо запит

SELECT ....
     "213-46-8915"....

Обидва обороту, складові оборот WHERE, є аргументами пошуку. Але стовпець state (штат), швидше за все, не забезпечить отримання одного значення, а стовпець au_id неодмінно гарантує це, так як він є первинним ключем таблиці. Щоб зрозуміти, що оборот au_id = "213-46- 8915" має дуже високу селективність, а оборот state = "СА", навпаки, середньої або навіть низькою, навряд чи потрібно знати що-небудь ще. Звичайно, якби знайшлася лише один рядок, у стовпці state якої було б значення, відповідне штату СА, то обидва обороту були б однаково селективні.

Оптимізатор запитів вирішує, наскільки селективен аргумент пошуку, виходячи зі статистики відповідного індексу. Статистика дає приблизне уявлення про те, скільки записів буде задовольняти заданому критерію. У такому випадку, якщо оптимізатор запитів знає, скільки рядків міститься в таблиці, і скільки рядків буде повернуто при використанні умов обох частин обороту WHERE, то не складе праці вирішити, який індекс доцільно використовувати. (Застосування статистики в SQL Server 7.0 більш детально описано в статті Кейлі Ділана "Статистика SQL Server: корисний інструмент оптимізатора запитів".) У даному запиті, якщо є індекси та за стовпцем state, і за стовпцем au_id, то оптимізатор запитів вибере індекс по au_id. Якщо ж індекс по au_id відсутня, а по state створений, то оптимізатор запитів вибере його. Це цілком логічно, оскільки в будь-якому випадку застосування індексу більш селективно, ніж сканування всієї таблиці. При відсутності обох індексів єдиним залишаються рішенням є сканування таблиці для виявлення всіх рядків, які задовольняють умовам.

(Детальніше робота оптимізатора запитів викладена в книзі "SQL Server 6.5 корпорації Microsoft" ("Microsoft SQL Server 6.5 unleashed"), випущеної видавництвом у 1998 році. В ній розглянуто кілька найбільш поширених сценаріїв. Звичайно, опрацювання кожного прикладу забере час, але зате ви станете набагато краще писати запити, якщо розберетеся в тому, яким чином діє оптимізатор. У книзі "Усередині SQL Server 6.5" ("Inside SQL Server 6.5") також добре розказано про роботу оптимізатора запитів.)

Індексування: швидше мистецтво, ніж наука

Неможливо писати ефективні запити, нічого не знаючи про індекси таблиць. Без хороших індексів навіть найпростіші запити можуть жахливим чином сповільнити роботу системи. Єдиний спосіб захисту від цього, – знати будову даних і розглядати індекси в якості невід'ємної частини ваших запитів.

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

А тепер окреслимо основи концепції правильного вибору типу індексів і стовпців, по яких вони повинні будуватися. Насамперед, оскільки для кожної таблиці можна створити лише один кластеризувати індекс, його треба будувати так, щоб задовольнити максимально можливу кількість запитів. Кластеризувати індекси найбільше корисні для запитів, що використовують умови на діапазон значень. Це обумовлено тим, що рівень листя такого індексу містить дані, відсортовані в порядку значень індексу. Найбільший виграш від застосування кластеризувати індексу виходить в тих випадках, коли оборот WHERE запиту містить оператори>, <і BETWEEN …. AND, а також оборот GROUP BY, в яких стовпці перераховані в тому ж порядку, що і в індексі. Хоча це може і не допомогти в пошуку рядків, але кластеризувати індекс здатний поліпшити продуктивність системи при обробці оборотів ORDER BY, якщо і в індексі і в обороті ORDER BY використані одні й ті ж стовпці, причому в співпадаючому порядку.

Оскільки проміжний рівень кластеризувати індексу вкрай малий, він чудово працює при пошуку унікальних значень. Однак некластерізованние індекси краще працюють для "точкових" запитів, які повинні знайти невелике число рядків. Обороти WHERE з оператором = є першими кандидатами на побудову некластерізованних індексів за відповідними стовпцями. Цей тип індексу також дуже хороший для функцій агрегування MIN і MAX, бо легко знайти першу і останню запису для діапазону значень, якщо скористатися рівнем листя індексу. Нарешті, некластерізованние індекси дуже істотно прискорюють виконання функції COUNT, так як сканування рівня листя індексу відбувається набагато швидше сканування таблиці.

Куди рухатися далі?

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

Моріс Льюїс (morris@holitech.com) Є президентом компанії Holitech, що спеціалізується на консалтингу та навчанні технологій Internet і розробкам корпорації Microsoft в області баз даних.

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


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

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

Ваш отзыв

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

*

*