Дуже важливі можливості оптимізації SQL Server пов'язані з підсистемою роботи з блокуваннями. Реалізація цієї підсистеми в SQL Server викликає справедливі нарікання у фахівців, і для багатьох практичних завдань ручна настройка блокувань є необхідною.
У SQL Server передбачено п'ять головних рівнів блокувань:
q блокування рівня бази даних (DB). Такі блокування автоматично накладаються на будь-яку базу даних, до якої підключений користувач. В основному вони призначені для заборони виконання з базами даних, до яких підключені користувачі, певних дій, наприклад, видалення даних;
q блокування рівня об'єкта (наприклад, TAB). Такі блокування можуть накладатися на таблицю або індекс як при виконанні звичайних запитів, так і при виконанні службових операцій з цими об'єктами;
q блокування рівня екстента (EXT). Такі блокування нечасто можна побачити в SQL Server Management Studio або в результатах виконання процедури sp_lock. Вони використовуються тільки при таких службових операціях, як створення нових таблиць, збільшення розміру файлів баз даних і т. П .;
q блокування рівня сторінки (PAG). Такі блокування використовуються SQL Server дуже часто. При їх застосуванні блокується вся сторінка розміром 8 Кбайт, з усіма записами, які в ній знаходяться. Такий тип блокування може застосовуватися як для сторінок даних, так і для сторінок індексів;
q блокування рівня записи / ключа (RID / KEY). Такі блокування накладаються на окремі записи. Блокування типу RID накладаються на записи в таблиці без кластерного індексу (heap), а блокування типу KEY - на записи в таблицях, для яких передбачено кластерний індекс.
Блокування також розрізняються за типами (загальні, виняткові, блокування очікування і т. П.), Але для цілей оптимізації важливий тільки їх рівень.
За замовчуванням SQL Server управляє рівнем блокувань автоматично. Для більшості операцій SQL Server спочатку намагається використовувати тільки блокування на рівні запису. Якщо запит на читання або зміна даних стосується великої кількості записів, то для економії ресурсів SQL Server може прийняти рішення про використання блокувань більш високого рівня. Таке підвищення рівня називається ескалацією блокувань (lockescalation).
Відзначимо деякі технічні моменти, пов'язані з ескалацією блокувань.
У більшості ситуацій SQL Server спочатку намагається використовувати блокування рівня запису або сторінки. Який саме рівень блокувань використовувати - записи або сторінки, визначається перед початком виконання запиту. Рішення про ескалацію блокувань SQL Server приймає в двох ситуаціях:
q коли запит намагається застосувати до одного об'єкту (до таблиці або індексу) більше 5000 блокувань на рівні запису або сторінки. Значення 5000 блокувань взято з документації, але на практиці SQL Server іноді продовжує використовувати блокування рівня запису або сторінок і при кількості в сотні тисяч таких блокувань. При цьому SQL Server ніколи не виробляє ескалацію з рівня запису до рівня сторінки, а відразу намагається накласти блокування на таблицю;
q коли місце в області оперативної пам'яті, відведеної для роботи з блокуваннями, закінчується. На кожну блокування SQL Server відводить 96 байт. Розмір області пам'яті для роботи з блокуваннями SQL Server за замовчуванням (при встановленому параметрі сервера Lock. Рівному 0) налаштовується динамічно. Як тільки розмір цієї області досягає 40% від загального обсягу пам'яті, яку використовує процес SQL Server (вона обмежується операційною системою або параметром MAX SERVER MEMORY), SQL Server автоматично намагається зробити ескалацію блокувань. Якщо розмір області оперативної пам'яті досяг 60% від обсягу пам'яті, максимально доступною SQL Server. створення нових блокувань не проводиться, а клієнту повертається помилка 1204 "unable to allocate lock resource" (неможливо розмістити ресурси блокування).
Якщо SQL Server не вдалося підвищити рівень блокування (наприклад, на рівні таблиці вже є блокування інший транзакції), то повторні спроби будуть проводитися через кожні 1250 нових блокувань на рівні запису або сторінки.
Моніторинг подій ескалації блокувань можна проводити за допомогою Профілювальники, вибравши в ньому подія Lock: Escalation.
Зовні механізм ескалації виглядає дуже логічним, проте на практиці з ним виникають проблеми:
q при виконанні операцій, які повинні бути виконані з великою кількістю записів в таблиці, SQL Server намагається спочатку використовувати блокування рівня запису. В результаті на установку і подальше зняття таких блокувань витрачається значна кількість системних ресурсів. Цього можна було б уникнути, якщо відразу застосувати для виконання операції потрібний рівень блокувань (PAG або TAB);
q друга, більш важлива проблема, полягає в тому, що SQL Server застосовує ескалацію блокувань, в тому числі і на потужних серверах, з якими одночасно працює велика кількість користувачів. Типова ситуація виглядає таким чином: в базі даних є велика таблиця, з якої постійно працюють користувачі (назвемо її головною таблицею). За рахунок ескалації блокувань кількість записів, які одночасно блокують користувачі, автоматично збільшується, в результаті чого інші користувачі не можуть отримати до них доступ. Таким чином, при досягненні певної кількості користувачів робота з цією таблицею різко не може. Особливо неприємно те, що заблокованими виявляються ті записи, з якими користувачі насправді не працюють: просто вони потрапили на одну сторінку з іншими записами, відкритими в даний момент.
Для вирішення цих проблем в залежності від ситуації можна використовувати різні способи:
q перша можливість, яка дозволяє взагалі позбутися про блокувань при запитах на читання даних, - використовувати відповідний рівень ізоляції транзакцій. При операціях запису блокування не накладаються при використанні рівнів:
· READ COMMITED. коли для параметра бази даних READ_COMMINTED_SNAPSHOT встановлено значення ON;
Такі рівні ізоляції цілком можна використовувати для запитів зі статистичною інформацією, коли абсолютною точністю інформації можна пожертвувати заради продуктивності. Для фінансових звітів такий спосіб може не підійти;
q ще одне радикальне рішення - перевести базу даних в стан R EAD - ONLY. В цьому випадку при зверненні до неї ніякі блокування використовуватися не будуть, що може дати дуже великий виграш в продуктивності і підвищити кількість користувачів, які можуть працювати з таблицею одночасно. Але з очевидних причин таке рішення підходить далеко не завжди;
q якщо вам потрібно спочатку підвищити рівень блокування, то можна використовувати хинти NOLOCK. ROWLOCK. PAGLOCK і TABLOCK в запитах. Мінус такого підходу - не завжди є можливість змінювати код програми;
· Для некластерного індексу;
· Для кластерного індексу (ці ж налаштування будуть одночасно застосовані для таблиці);
· Для таблиці, у якій немає кластерних індексів (дані знаходяться в "купі" - heap). Ця можливість є недокументованою, але її цілком можна використовувати.
Використовуючи значення OFF для параметра ALLOW _ PAGE _ LOCKS в цій команді, ви можете позбутися від блокувань рівня сторінки - головної причини проблем одночасного доступу користувачів.
q якщо проблема, на вашу думку, може відбуватися через те, що для області блокування в пам'яті виділено недостатньо місця, то ви можете спробувати налаштувати розмір цієї зони вручну. Для цього можна використовувати параметр конфігурації сервера Lock. а також спробувати збільшити кількість оперативної пам'яті, доступної SQL Server. за допомогою збільшення фізичної оперативної пам'яті на сервері або за допомогою параметра MAX SERVER MEMORY;
q ще одна радикальна можливість - використовувати прапори трасування. Прапор 1211 просто відключає будь-яку ескалацію блокувань. З цим параметром потрібно бути дуже обережним, т. К. Ви можете різко знизити продуктивність роботи сервера і збільшити витрату оперативної пам'яті. Крім того, при вичерпанні оперативної пам'яті, що відводиться на блокування, ви ризикуєте отримати помилку 1204. Другий прапор - 1224. Він відключає ескалацію блокувань, вироблену за лічильником (5000 блокувань на нижньому рівні), однак при вичерпанні місця в області оперативної пам'яті, що відводиться під блокування, ескалація все одно буде відбуватися.
Якщо одночасно встановити прапори 1224 і 1211, то пріоритет буде відданий прапору 1224.