Як підвищити продуктивність SQL Server, MS SQL Server, Бази даних, статті

Келен Ділану, Журнал “Windows 2000 Magazine”

Сьогодні я хочу запропонувати читачам 10 рекомендацій з налаштування, які допоможуть збільшити продуктивність SQL Server на 90%.

Оптимізувати продуктивність Microsoft SQL Server 2000 і SQL Server 7.0 не так-то просто, але в більшості випадків адміністратор може домогтися непоганих результатів, доклавши зовсім трохи зусиль. Тут діє старе правило 90:10, тобто 90% успіху досягається після витрати всього 10% зусиль. Але не можна забувати, що підвищити продуктивність ще на 10% вдасться, лише витративши інші 90% зусиль при налаштуванні.

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

Щоб підняти продуктивність вище середнього стандартного рівня, потрібно приділити SQL Server зовсім небагато уваги. Якщо адміністратору знадобиться додаткова інформація, рекомендую звернутися до матеріалів, які перелічені в урізанні «Знання – сила».

1. Про важливість апаратних засобів

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

Якщо продукт встановлений на хорошій машині, то модернізація апаратних засобів рідко дозволяє підвищити продуктивність більш ніж на 10%. Але якщо додаток на базі SQL Server працює на сервері, до якого одночасно звертаються кілька сот користувачів, а сервер оснащений лише одним жорстким диском і мінімальним об’ємом оперативної пам’яті, 64 Мбайт, то просте розширення оперативної пам’яті до 128 Мбайт призведе до різкого підвищення швидкодії.

В ідеальному випадку, для кожних 10 одночасних з’єднань слід збільшувати оперативну пам’ять на 10 Мбайт; крім того, необхідна додаткова пам’ять для зберігання всіх даних користувача, системних даних та індексів. Рекомендується вибрати дискову конфігурацію, яка дозволить зберегти дані користувача (файли. Mdf і. Ndf) і журнали (файли. Ldf) на інших фізичних дисках, керованих окремими контролерами. Файли користувачів потрібно зберігати на RAID-масиві. Слід також витратитися на два найшвидших процесора, які тільки доступні для компанії. Це мінімальні вимоги до апаратних засобам.

2. Не захоплюйтеся настройками

Розробники Microsoft заклали в SQL Server 2000 і SQL Server 7.0 здатність до самонастроювання. Наприклад, механізм SQL Server може визначити оптимальний режим використання пам’яті, допустиму кількість блокувань і частоту контрольних точок.

Змінювати слід лише ті прийняті за умовчанням параметри, які не впливають на продуктивність. До таких параметрів належать функції користувача та функція перетворення року в однорядкове резюме формат (Бітова карта функцій користувача вказує, які функції активізовані для кожного користувача з’єднання; функція перетворення року в однорядкове резюме формат задає метод інтерпретації двозначного позначення року).

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

Виняток становить параметр max async I / O. При роботі з SQL Server 7.0 він налаштовується в залежності від рівня складності і числа контролерів в підсистемі введення / виведення. Значення max async I / O визначає максимальне число чекаючих обробки асинхронних запитів вводу / виводу, які можуть бути спрямовані сервером до будь-якого файлу. Якщо база даних охоплює кілька файлів, то параметр застосовується до кожному з них.

За замовчуванням значення max async I / O становить 32 (всього 32 операції запису і 32 операції читання можуть очікувати обробки для кожного файлу), воно оптимально для багатьох систем. Щоб з’ясувати, чи потрібно змінити стандартне значення для даної системи, слід заглянути в SQL Server Books Online (BOL). SQL Server 2000 не має параметра max async I / O і визначає оптимальне значення автоматично.

3. Приділіть час проектування

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

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

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

4. Формуйте індекси

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

Створивши корисні індекси, можна підвищити продуктивність на кілька порядків замість декількох відсотків. Наприклад, за відсутності індексу SQL Server доведеться прочитати все 10000 сторінок таблиці. Якщо завдяки індексації число прочитаних сторінок зменшиться до 10, то це буде рівносильно збільшенню швидкості обробки сторінок на 100 000%.

Знання архітектури індексів і методів оптимізації запитів SQL Server допоможе сформувати оптимальні індекси, а початківці адміністратори можуть скористатися для цього майстром Index Tuning Wizard. Щоб відкрити його з SQL Server Enterprise Manager, слід клацнути на кнопці Wizard панелі інструментів і заглянути в розділ Management Wizards.

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

5. Ефективно використовуйте SQL

SQL – мова, орієнтований на обробку наборів, а не окремих рядків. T-SQL, запропонований Micro-soft діалект мови SQL, використовує серверні курсори для звернення до одному рядку за один раз, а проте більшість рішень, в яких застосовуються серверні курсори, будуть на кілька порядків повільніше, ніж рішення, в яких для виконання тих же завдань використовуються пропозиції SELECT і UPDATE. Застосування таких функцій мови, як підлеглі запити, похідні таблиці і вирази CASE для маніпулювання наборами рядків, прискорить підготовку рішень і допоможе домогтися максимальної продуктивності SQL Server.

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

Це завдання можна вирішити й за допомогою пропозиції UPDATE і корелювала підлеглого запиту. В даному реченні таблиця titles бази даних pubs використовується як таблиці продуктів; для кожного найменування підсумовуються величини в поле qty таблиці sales.

UPDATE titles
SET ytd_sales =
(Select sum(qty) FROM sales
WHERE title_id = titles
.title_id)

6. Вивчайте тонкощі T-SQL

Microsoft T-SQL – вдосконалена версія стандартної мови ANSI-SQL. Використання його можливостей дозволяє істотно підвищити продуктивність системи.

Наприклад, припустимо, що необхідно виставити всі продукти на продаж, встановивши ціну на них в залежності від торішнього обсягу продажів. Ціна повинна бути на 25% нижче поточної, якщо число проданих одиниць менше 3000; якщо обсяг продажів був від 3000 до 1000 одиниць, то ціна продукту повинна бути знижена на 20%; знижка 10% надається на продукти, обсяг продажів яких перевищив 10 000 одиниць. Очевидне рішення – Використовувати пропозицію UPDATE з відповідними значеннями знижок після індивідуального перегляду рядків продуктів за допомогою курсору. Проте вираз T-SQL CASE дозволяє обчислити відповідні знижки за допомогою одного оператора.

У наведеному нижче прикладі пропозицію UPDATE використовує таблицю titles бази даних pubs, в якій є поле ціни, оновлюється пропозицією, і поле ytd_sales, де зберігається інформація про продажі за минулий рік. Цей запит не буде працювати, якщо попередньо була виконана операція з рекомендацією 5; в поле ytd_sales перебуватиме набір інших величин.

UPDATE titles
SET price = CASE
WHEN ytd_sales < 3000 THEN
price * 0.75
WHEN ytd_sales between 3000
and 10000 THEN price * 0.80
WHEN ytd_sales > 10000 THEN
price * 0.90
END
WHERE price IS NOT NULL

Інші елементи T-SQL, що підвищують ефективність запитів, – оператор TOP, використовуваний разом з ORDER BY; індексовані подання (тільки SQL Server 2000); розділені (partitioned) подання.

7. Правильно застосовуйте блокування

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

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

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

Рівень ізоляції транзакцій може бути змінений, і дія блокувань, що допускають читання, може бути продовжено до кінця транзакції – це означає, що ніхто не може змінити дані після зчитування. Таким чином, метод зміни рівнів ізоляції гарний у тому випадку, якщо потрібно зарезервувати дані тільки для особистого користування. Однак його не варто застосовувати в багатокористувацьких системах. Я рекомендую встановити рівень ізоляції транзакцій Committed Read (приймається за умовчанням) і змінювати його тільки в разі крайньої необхідності.

8. Скоротіть число перекомпіляції

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

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

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

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

9. Грамотно програмуйте програми

Чим глибшими знаннями про пристрій SQL Server володіє автор клієнтських програм, тим вища якість складених їм вихідних текстів. Наприклад, він не допустить взаємодії з користувачем посеред транзакції (див. п. 7).

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

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

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

10. Підвищуйте свою кваліфікацію

Якщо наведені вище поради не допомогли у вирішенні конкретних проблем, рекомендую звернутися до численних загальнодоступних джерелах, якими користуються досвідчені фахівці з SQL Server. Зокрема, подивіться конференції Micro-soft. Відшукати на сервері msnews.microsoft.com конференції, в назвах яких міститься позначення sqlserver, можна за допомогою будь-якої програми читання новин (наприклад, Microsoft Outlook Express).

Ці рекомендації – лише вершина айсберга, яка відкриває не більше 10% всіх можливостей. Тепер, коли відомо, які параметри SQL Server можна налаштувати, належить розібратися в тому, як це зробити. Пройде небагато часу, і, ймовірно, в адміністратора виникне бажання докласти ті 90% зусиль, які необхідні, щоб вичавити із продукту останні 10% продуктивності.

Келен Ділана – Незалежний консультант та інструктор по SQL Server. Має сертифікати MCT і MCSE. Автор книги «Inside SQL Server 2000» (Microsoft Press). З нею можна зв’язатися за адресою: kalen@sqlmag.com.

Знання – сила

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

1. Про важливість апаратних засобів

Додаткові рекомендації по конфігурації можна отримати на Web-сайті Microsoft Developer Network (MSDN), MSDN Online. Зверніть увагу на звіт «Microsoft SQL Server 7.0 Storage Engine Capacity Planning Tips» (msdn.microsoft.com/library/ default.asp?url=/library/ techart/storageeng.htm).

2. Не захоплюйтеся конфігуруванням

Більш детальна інформація про конфігуруванні SQL Server міститься в матеріалах SQL Server Books Online (BOL) і статті Генрі Ло «Microsoft SQL Server 7.0 Performance Tuning Guide» (msdn.microsoft.com/ library/default.asp?url=/library/ techart/msdn_sql7perftune.htm). Раджу також взяти участь у семінарі Дам’єна Ліндауера Microsoft Seminar Online, «Microsoft SQL Server 7.0 Performance Tuning and Opti-mization – The Server Perspective» (msdn.microsoft.com/seminar/ 1033/19991028teperftun1/seminar.htm). Відвідуючи вузол Microsoft Seminar Online, справтеся про інші семінарах, присвячених SQL Server.

3. Приділіть час проектування

На жаль, немає книги по SQL Server або курсу Microsoft Official Curriculum (MOC) початкового рівня, в яких тема проектування реляційних баз даних була б висвітлена достатньо повно. Ймовірно, розробники Microsoft не приділили уваги цій темі, тому що вона не залежить від конкретних програмних продуктів. Гарною відправною точкою для початківців розробників послужить колонка Мішель А. Пулі «Solutions by Design »в журналі SQL Server Magazine. Статті можна знайти за адресою: www.sqlmag.com/articles/ index.cfm?authorid=436.

4. Формуйте індекси

Тим, хто бажає отримати додаткову інформацію про індексацію і оптимізаторі запитів SQL Server, раджу для початку прочитати все, що написано про індекси в BOL. Фірма Microsoft випустила дві «білі книги »про майстра Index Tuning Wizard:« Index Tuning Wizard for Microsoft SQL Server 7.0 »(msdn.microsoft.com/library/ default.asp?url=/library/techart/ msdn_sqlindex.htm) І «Index Tuning Wizard for Microsoft SQL Server 2000» msdn.microsoft.com/library/ default.asp?url=/library/techart/itforsql.htm). Додаткову інформацію з цієї теми можна отримати з курсів MOC Course 2013: Optimizing Microsoft SQL Ser-ver 7.0 і Course 2073: Programming Microsoft SQL Server 2000 Database. Більш докладно про цих курсах можна дізнатися за адресою: www.microsoft.com/trainingandservices.

5. Ефективно використовуйте SQL

Не обмежуйтеся книгами за мовою T-SQL. Для ознайомлення з принципами програмування на ANSI-SQL я рекомендую прочитати книгу Джо Селко «Joe Celko’s SQL for Smarties: Advanced SQL Program-ming», друге видання (видавництво Morgan Kaufmann Publishers, 1999).

6. Вивчайте тонкощі T-SQL

Є роботи, в яких наводяться приклади програмування на T-SQL. Це книги Іцка Бен-Гана і д-ра Тома Моро «Advanced Transact-SQL for SQL Server 2000» (видавництво Apress, 2000) та Кена Хендерсона «The Guru’s Guide to Transact-SQL »(видавництво Addison-Wesley, 1999).

7. Правильно застосовуйте блокування

Прочитайте всі доступні матеріали про застосовувані за замовчуванням механізмах блокування SQL Server, у тому числі документи BOL, колонку Inside SQL Server Келен Ділана в SQL Server Magazine (www.sqlmag.com) І наступні статті Microsoft: «INF: How to Monitor SQL Server 7.0 Blocking» (support.microsoft.com/support/ kb/articles/q251/0/04.asp), «INF: Un-derstanding and Resolving SQL Server 7.0 and 2000 Blocking Problems» (support.microsoft.com/support/ kb/articles/q224/4/53.asp) І «INF: How to Monitor SQL Server 2000 Blocking» (support.microsoft.com/support/ kb/articles/q271/5/09.asp).

8. Скоротіть число перекомпіляції

Про перекомпіляції збережених процедур можна прочитати в BOL. У статті Microsoft «INF: Troubleshooting Stored Procedure Recompilation» (support.microsoft.com/support/ kb/articles/q243/5/86.asp) Розказано про те, як звести перекомпіляцію збережених процедур до мінімуму.

9. Грамотно програмуйте програми

Додаткову інформацію про налаштування клієнтських програм, що використовуються разом з SQL Server, надає підготовлене для TechEd 2000 слайд-шоу Дам’єна Ліндауера «Building High Performance Applications with SQL Server 2000» (commnet.us.teched.mscorpevents. com.slides/5-303%20w.ppt). Корисний матеріал – стаття Microsoft «Troubleshooting Application Performance with SQL Server» (support.microsoft.com/support/ kb/articles/q224/5/87.asp).

10. Підвищуйте свою кваліфікацію

Крім перегляду телеконференцій на сервері msnews.microsoft.com можна звернутися на Web-сторінку SQL Server Newsgroups компанії Microsoft (www.microsoft.com/sql/ support/newsgroups.htm) І відшукати конференції там. Тим, хто віддає перевагу працювати з матеріалами на базі Web, рекомендую звернутися на дискусійні форуми Windows 2000 Magazine (www.win2000mag.net/ forums/application/main.cfm?cfapp=57).

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


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

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

Ваш отзыв

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

*

*