Загальний план оптимізації і настройки запитів SQL Server, Інші СУБД, Бази даних, статті

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


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


Більшість компаній зазвичай працюють тільки із запитами після впровадження, коли вони створюють проблему і поміщаються в список “багів” деякого типу. Так, ви можете виявити проблемні запити таким способом, але якщо ви будете чекати, поки вони не з’являться в списку “багів”, ви потрапите в цейтнот, “фіксуючи” проблему, роблячи резервну копію програми і запускаючи його. Якщо пощастить, то вам вистачить часу, щоб застосувати нові знання з налаштування продуктивності, які ви придбали за час, що минув з моменту, коли в останній раз працювали над запитом при вирішенні нової проблеми. Цей спосіб настройки запитів за фактом може дозволити, а може і не дозволити вам ідентифікувати і виправити ключовий момент втрати продуктивності ваших збережених процедур, оскільки він має справу з тими запитами, які настільки погані, що привертають до себе основну увагу. Кращий план дозволив би вам не тільки працювати над проблемними запитами по мірі їх появи, але й отримати списки запитів, які демонструють проблемну продуктивність до того, як вони потрапляють в список “багів”. Тоді ці запити можуть оптимізуватися, коли у вас є на це час або коли ви адаптуєте їх до нової версії програми. Використання потужності SQL Profiler і виконання простий трасування для визначення продуктивності ваших збережених процедур може полегшити отримання цих нових списків запитів. Таку трасування просто створити, і, в залежності від установки, запускати в моменти максимального і мінімального завантаження протягом декількох днів, щоб отримати хорошу вибірку даних.


Створення списку запитів за допомогою трасування SQL Profiler


Щоб отримати списки запитів, вам буде потрібно створити і виконати трасування, що фіксує всі події збереженої процедури, а також такі стовпці даних:


– EventClass
– EventSubClass
– TextData
– CPU
– Reads
– Writes
– Duration
– SPID
– StartTime
– EndTime
– NestLevel
– ObjectID
– ObjectName
– ObjectType

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


Тепер, коли ви зберегли ваші дані в таблиці, створіть збережені процедури, які видадуть найгірші по продуктивності процедури, враховуючи тривалість (тривалість виводиться в мілісекундах), SP: CacheMiss (відсутність в кеші), SP: Recompile (перекомпіляція), Reads (читання) і Write (записи). Створіть списки з найбільшими длительностями, найбільшим числом читань, найбільшим числом записів і збереженими процедурами, які включають події SP: CacheMiss і SP: Recompile. Можливо, вам буде потрібно кілька спроб, щоб зрозуміти, яке число операцій читання і запису вважається надмірним у вашій базі даних, але якщо ви бачите збережену процедуру, яка перевищує середнє значення цих показників, з неї можна почати. Решта списки простіше для визначення проблемних запитів, на які можна впливати відразу.


Тепер, коли ви отримали свої списки запитів, що підлягають переробці, – довго виконуються запити, запити з надмірним числом читань і записів, запити, які перекомпілюються, – що потрібно робити, щоб оптимізувати їх?


Довго виконуються і нові запити


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


– Знайти те, що забарвлене червоним кольором. Аналізатор запитів (Query Analyzer) офарбить значки червоним, якщо визначить, що там є певні проблеми. Зазвичай червоний колір означає, що статистичні дані індексів, які використовуються цією частиною запиту, застаріли, проте, це може означати і інші речі. Звертайте увагу на будь-які значки, які мають червоний колір, і думайте, як усунути проблеми.
– Розібратися, як ви отримуєте дані з таблиць. Чи виконує запит сканування таблиці (Table Scan)? Чи можете ви зробити що-небудь, щоб замінити сканування індексу або кластеризованого індексу (Index Scans або Clustered Index Scans) на пошук в індексі або пошук в кластерному індексі (Index Seeks або Clustered Index Seeks)? Чи можете ви переробити запит, щоб використовувати кластеризованих індекс замість некластеризованих індексу? Просте переміщення пошуку базових даних від сканування таблиці на пошук в індексі або кластеризованих індексі, як правило, вирішує проблему повільно виконує запиту. В результаті ви отримуєте дуже швидкий спосіб покращити роботу більшості проблемних запитів.
– Зверніть увагу на вартість (Cost) кожного сегмента запиту. Слід знати, що вартість кожного сегмента приблизно відповідає відсотковій частці часу, який буде потрібно для його виконання. Виділіть найбільші відсоткові частки і подумайте, чи можете ви оптимізувати цей сегмент коду будь-яким способом. Це не означає, що вам потрібно зробити так, щоб максимальна вартість перестала бути такою; мета полягає в тому, щоб оптимізація сегмента просто зробила виконання цього сегмента більш швидким, ніж раніше.

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



Перекомпіляція запитів


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



Отже, ви вивчили план виконання, проаналізували код і виявили повторну компіляцію; що тепер? Лише тільки те, що все виглядає прекрасно, ще не означає, що запит буде задовільно виконуватися в кожному окремому сценарії. Ви повинні придумати різні тести для вашого запиту. Як він виконується на наборах даних різного об’єму? Як він виконується з різними наборами параметрів? Що буде, якщо ви запустите запит багаторазово, використовуючи численні сполуки в Query Analyzer? Що б ви не думали про своє конкретному випадку, потрібно виконувати трасування і аналіз. Тільки не кажіть: “Я налаштував цей запит, і він готовий”, – якщо ви запустили його тільки один раз. Проявіть твердість і переконайте ваших клієнтів і менеджерів, що вам потрібен час, щоб повністю перевірити новий або недавно перероблений запит. Це більш важливо, ніж швидка установка латок, щоб тільки виконати роботу в строк.


Як було сказано, це тільки загальний план. Додайте в нього будь-які нові пункти, які прийшли вам в голову. Розширте мої етапи своїми напрацюваннями, нагромадженими за роки, і рішеннями загальних проблем, які ви виявили в своїй базі даних. Як би ви не вирішили оптимізувати ваші запити, запишіть це, щоб у вас було щось “формальне”, що можна передати іншим у вашій групі або просто використовувати, щоб дати відповіді на питання інтерв’ю, який захопив мене зненацька, якщо вам це коли-небудь знадобиться.

Randy Dyess (Оригінал: An Introduction to SQL Server Query Tuning)
Переклад: Моісеєнко С.І.
Оригінал перекладу


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


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

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

Ваш отзыв

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

*

*