Аптымізацыя запытаў у SQL Server 2005 года, статыстыка баз дадзеных

Аптымізацыя запытаў у SQL Server 2005 года, статыстыка баз дадзеных SQL Server 2005 года, CREATE STATISTICS, UPDATE STATISTICS, SET NOCOUNT ON, планы выканання запытаў, колькасць лагічных чытанняў (logical reads), хинты аптымізатар (optimizer hints), MAXDOP, OPTIMIZE FOR, кіраўніцтва па планах выканання (plan guides), sp_create_plan_guide

Калі ўсе астатнія спосабы аптымізацыі прадукцыйнасці ўжо вычарпаныя, то ў распараджэнні распрацоўшчыкаў і адміністратараў SQL Server застаецца апошні рэзерв - аптымізацыя выканання асобных запытаў. Напрыклад, калі ў вашай задачы цалкам неабходна паскорыць стварэнне нейкага аднаго спецыфічнага справаздачы, можна прааналізаваць запыт, які выкарыстоўваецца пры стварэнні гэтай справаздачы, і пастарацца змяніць яго план, калі ён неоптимален.

Стаўленне да аптымізацыі запытаў у многіх спецыялістаў неадназначнае. З аднаго боку, праца праграмнага модуля Query Optimizer, які генеруе планы выканання запытаў, выклікае мноства справядлівых нараканняў і ў SQL Server 2000, і ў SQL Server 2005. Query Optimizer часта выбірае не самыя аптымальныя планы выканання запытаў і ў некаторых сітуацыях прайграе аналагічным модулях з Oracle і Informix. З іншага боку, ручная аптымізацыя запытаў - працэс надзвычай працаёмкі. Вы можаце выдаткаваць шмат часу на такую ​​аптымізацыю і, у рэшце рэшт, высветліць, што нічога аптымізаваць не ўдалося: план, прапанаваны Query Optimizer першапачаткова, апынуўся найбольш аптымальным (так бывае ў большасці выпадкаў). Акрамя таго, можа здарыцца так, што створаны вамі ўручную план выканання запытаў праз нейкі час (пасля дадання новай інфармацыі ў базу дадзеных) апынецца неаптымальнай і будзе зніжаць прадукцыйнасць пры выкананні запытаў.

Адзначым таксама, што для выбару найлепшых планаў пабудовы запытаў Query Optimizer неабходная правільная інфармацыя аб статыстыцы. Паколькі, па вопыту аўтара, далёка не ўсе адміністратары ведаюць, што гэта такое, раскажам аб статыстыцы падрабязней.

Статыстыка - гэта спецыяльная службовая інфармацыя аб размеркаванні дадзеных у слупках табліц. Уявім, напрыклад, што выконваецца запыт, які павінен вярнуць усіх Івановых, якія пражываюць у горадзе Санкт-Пецярбургу. Выкажам здагадку, што ў 90% запісаў у гэтай табліцы адно і тое ж значэнне ў слупку Горад - "Санкт-Пецярбург". Вядома, з пункту гледжання выканання запыту спачатку выгадна абраць у табліцы ўсіх Івановых (іх відавочна будзе не 90%), а затым ужо правяраць значэнне слупка Горад для кожнай адабранай запісу. Аднак для таго, каб даведацца, як размяркоўваюцца значэння ў Стоўбцах, трэба спачатку выконвае запыту. Таму SQL Server самастойна ініцыюе выкананне такіх запытаў, а потым захоўвае інфармацыю аб размеркаванні дадзеных (якая і называецца статыстыкай) ў службовых табліцах базы дадзеных.

Для баз дадзеных SQL Server 2005 па змаўчанні ўсталёўваюцца параметры AUTO_CREATE_STATISTICS і AUTO_UPDATE_STATISTICS. Пры гэтым статыстыка для слупкоў баз дадзеных будзе стварацца і абнаўляцца аўтаматычна. Для самых вялікіх і важных баз дадзеных можа атрымацца так, што аперацыі па стварэнні і абнаўленню статыстыкі могуць перашкаджаць бягучай рабоце карыстальнікаў. Таму для такіх баз дадзеных часам гэтыя параметры адключаюць, а аперацыі па стварэнні і абнаўленню статыстыкі выконваюць уручную ў начны час. Для гэтага выкарыстоўваюцца каманды CREATE STATISTICS і UPDATE STATISTICS.

Зараз пагаворым аб аптымізацыі запытаў.

Першае, што неабходна зрабіць, - знайсці тыя запыты, якія ў першую чаргу падлягаюць аптымізацыі. Прасцей за ўсё гэта зрабіць пры дапамозе профилировщика, усталяваўшы фільтр на час выканання запыту (фільтр Duration ў акне Edit Filter (Рэдагаваць фільтр), якое можна адкрыць пры дапамозе кнопкі Column Filters на ўкладцы Events Selection вокны уласцівасцяў сеансу трасіроўкі). Напрыклад, у лік кандыдатаў на аптымізацыю могуць патрапіць запыты, час выканання якіх склала больш за 5 секунд. Акрамя таго, можна выкарыстоўваць інфармацыю аб запытах, якая прадастаўляецца Database Tuning Advisor.

Затым трэба праверыць, устанавлен Ці для вашых злучэнняў, захоўваемых працэдур і функцый параметр NOCOUNT. Ўсталяваць яго можна пры дапамозе каманды SET NOCOUNT ON. Пры ўсталёўцы гэтага параметру, па-першае, адключаецца вяртанне з сервера і выснова інфармацыі пра колькасць радкоў у выніках запыту (т. Е. Не адлюстроўваецца радок "N row (s) affected" на ўкладцы Messages (Cообщения) вокны працы з кодам пры выкананні запыту ў Management Studio). Па-другое, адключаецца перадача спецыяльнага сервернага паведамлення DONE_IN_PROC, якое па змаўчанні вяртаецца для кожнага этапу захоўваемай працэдуры. Пры выкліку большасці захоўваемых працэдур патрэбен толькі вынік іх выканання, а колькасць апрацаваных радкоў для кожнага этапу нікога не цікавіць. Таму ўстаноўка параметру NOCOUNT для захоўваемых працэдур можа сур'ёзна павысіць іх прадукцыйнасць. Павышаецца хуткасць выканання і звычайных запытаў, але ў меншай ступені (да 10%).

Пасля гэтага можна прыступаць да працы з планамі выканання запытаў.

План выканання запыту прасцей за ўсё прагледзець з SQL Server Management Studio. Для таго каб атрымаць інфармацыю аб чаканым плане выканання запыту, можна ў меню Query (Запыт) абраць каманду Display Estimated Execution Plan (Адлюстраваць чаканы план выканання). Калі вы хочаце даведацца рэальны план выканання запыту, можна перад яго выкананнем ўсталяваць у тым жа меню параметр Include Actual Execution Plan (Уключыць рэальны план выканання). У гэтым выпадку пасля выканання запыту ў акне вынікаў у SQL Server Management Studio з'явіцца яшчэ адна ўкладка Execution Plan (План выканання), на якой будзе прадстаўлены рэальны план выканання запыту. Пры навядзенні паказальніка мышы на любы з этапаў можна атрымаць пра яго дадатковую інфармацыю (мал. 11.15).

Мал. 11.15. План выканання запыту ў SQL Server Management Studio

У плане выканання запыту, як відаць на малюнку, можа быць мноства элементаў. Разабрацца ў іх, а таксама прапанаваць іншы план выканання - задача досыць складаная. Трэба сказаць, што кожны з магчымых элементаў аптымальны ў сваёй сітуацыі. Таму звычайна этапы аптымізацыі запыту выглядаюць так:

q спачатку ў акне Management Studio выканайце каманду SET STATISTICS IO ON. У выніку пасля кожнага выканання запыту будзе выводзіцца дадатковая інфармацыя. У ёй нас цікавіць значэнне толькі аднаго параметру - Logical Reads. Гэты параметр азначае колькасць лагічных чытанняў пры выкананні запытаў, т. Е. Колькі аперацый чытання прыйшлося правесці пры выкананні дадзенага запыту без уліку ўплыву кэша (колькасць чытанняў і з кэша, і з дыска). Гэта найбольш важны параметр. Колькасць фізічных чытанняў (чытанняў толькі з дыска) - інфармацыя не вельмі прадстаўнічая, паколькі залежыць ад таго, ці былі перад гэтым звароту да дадзеных табліцах ці не. Статыстыка па часе таксама з'яўляецца велічынёй зменнай і залежыць ад іншых аперацый, якія выконвае ў гэты час сервер. А вось колькасць лагічных чытанняў - найбольш аб'ектыўны паказчык, на які ў найменшай ступені ўплываюць дадатковыя фактары;

q затым спрабуйце змяніць план выканання запыту і даведацца сумарная колькасць лагічных чытанняў для кожнага плана. Звычайна план выканання запыту змяняецца пры дапамозе хинтов (падказак) аптымізатар. Яны відавочна паказваюць аптымізатар, які план выканання варта выкарыстоўваць.

Хинтов аптымізатар ў SQL Server 2005 прадугледжана шмат. Прачытаць інфармацыю пра іх можна ў Books Online (у спісе на ўкладцы Index (Індэкс) трэба выбраць Query Hints [SQL Server] (Хинты запытаў [SQL Server]), Join Hints (Хинты джойнов) або Table Hints [SQL Server] (таблічнай хинты [SQL Server])). Часцей за ўсё выкарыстоўваюцца наступныя хинты:

q NOLOCK, ROWLOCK, PAGLOCK, TABLOCK, HOLDLOCK, READCOMMITTEDLOCK, UPDLOCK, XLOCK - гэтыя хинты выкарыстоўваюцца для кіравання блакаваннямі (гл. разд. 11.5.7 );

q FAST количество_строк - будзе абраны такі план выканання запыту, пры якім максімальна хутка будзе выведзена паказанае колькасць радкоў (першых з пачатку набору запісаў). Калі карыстальніку патрэбныя менавіта першыя запісы (напрыклад, апошнія заказы), то для іх максімальна хуткай загрузкі ў акно прыкладання можна выкарыстаць гэты хинт;

q FORCE ORDER - аб'яднанне табліц пры выкананні запыту будзе выканана дакладна ў тым парадку, у якім гэтыя табліцы пералічаныя ў запыце;

q MAXDOP (ад Maximum Degree of Parallelism - максімальная ступень распаралельвання запыту) - пры дапамозе гэтага хинта паказваецца максімальная колькасць працэсараў, якія можна будзе выкарыстоўваць для выканання запыту. Звычайна гэты хинт выкарыстоўваецца ў двух сітуацыях:

· Калі з-за пераключэння паміж працэсарамі (context switching) хуткасць выканання запыту моцна зніжаецца. Такія паводзіны было характэрна для SQL Server 2000 на шматпрацэсарных сістэмах;

· Калі вы хочаце, каб нейкі цяжкі запыт аказаў мінімальны ўплыў на бягучую працу карыстальнікаў;

q OPTIMIZE FOR - гэты хинт дазваляе паказаць, што запыт аптымізуецца пад канкрэтнае значэнне перадаванага яму параметру (напрыклад, пад значэнне фільтра для WHERE);

q USE PLAN - гэта самая магутная магчымасць. Пры дапамозе такога хинта можна відавочна вызначыць план выканання запыту, перадаўшы план у выглядзе радковага значэння ў фармаце XML. Хинт USE PLAN з'явіўся толькі ў SQL Server 2005 году (у папярэдніх версіях была магчымасць відавочна вызначаць планы выканання запытаў, але для гэтага выкарыстоўваліся іншыя сродкі). План ў фармаце XML можна напісаць ўручную, а можна згенераваць аўтаматычна (напрыклад, пстрыкнуўшы правай кнопкай мышы па графічным экрану з планам выканання, прадстаўленаму на мал. 11.15, і выбраўшы ў кантэкстным меню каманду Save Execution Plan As (Захаваць план выканання как)).

У SQL Server 2005 з'явілася новая важная магчымасць, якая дазваляе ўручную змяняць план выканання запыту без неабходнасці ўмешвацца ў тэкст запыту. Вельмі часта бывае так, што код запыту нельга змяніць: ён жорстка "прашыты" у кодзе адкампіляванага прыкладання. Каб справіцца з гэтай праблемай, у SQL Server 2005 з'явілася захоўваемая працэдура sp_create_plan_guide. Яна дазваляе ствараць так званыя кіраўніцтва па планах выканання (plan guides), якія будуць аўтаматычна прымяняцца да адпаведных запытам.

Калі вы аналізуеце запыты, якія накіроўвае да базы дадзеных нейкі дадатак, то мае сэнс у першую чаргу звярнуць увагу на наступныя моманты:

q наколькі часта ў планах выканання запыту сустракаецца аперацыя Table Scan (Поўнае прагляд табліцы). Цалкам можа апынуцца, што зварот да табліцы пры дапамозе індэксаў будзе больш эфектыўна;

q выкарыстоўваюцца Ці ў кодзе курсоры. Курсоры - вельмі просты сродак з пункту гледжання сінтаксісу праграмы, але надзвычай неэфектыўнае з пункту гледжання прадукцыйнасці. Вельмі часта можна пазбегнуць прымянення курсораў, выкарыстоўваючы іншыя сінтаксічныя канструкцыі, і атрымаць вялікі выйгрыш у хуткасці працы;

q выкарыстоўваюцца Ці ў кодзе часовыя табліцы або тып дадзеных Table. Стварэнне часовых табліц і праца з імі патрабуюць вялікага выдатку рэсурсаў, таму па магчымасці трэба іх пазбягаць;

q акрамя стварэння часовых табліц, значнага расходу сістэмных рэсурсаў патрабуе і змена іх структуры. Таму каманды на змяненне структуры часовых табліц павінны адразу прыцягваць вашу ўвагу. Звычайна ёсць магчымасць адразу стварыць часовую табліцу з усімі неабходнымі слупкамі;

q часам запыты вяртаюць больш дадзеных, чым рэальна патрабуецца з дадаткам (лішняе колькасць слупкоў або радкоў). Вядома, гэта не спрыяе павышэнню прадукцыйнасці;

q калі дадатак перадае на сервер каманды EXECUTE, то мае сэнс падумаць аб тым, каб замяніць іх на выклік захоўваемай працэдуры sp_executesql. Яна валодае перавагамі ў прадукцыйнасці ў параўнанні з звычайнай камандай EXECUTE;

q павышэння прадукцыйнасці часам можна дамагчыся, устараніўшы неабходнасць паўторнай кампіляцыі захоўваемых працэдур і пабудовы новых планаў выканання запытаў. Трэба звярнуць увагу на прымяненне параметраў, пастарацца не змешваць у кодзе захоўваемай працэдуры каманды DML і DDL і сачыць за тым, каб параметры падлучэння SET ANSI_DEFAULTS, SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS і SET CONCAT_NULL_YIELDS_NULL не змяняліся паміж запытамі (любая змена такіх параметраў прыводзіць да таго, што старыя планы выканання лічацца несапраўднымі). Звычайна праблема можа ўзнікнуць тады, калі гэтыя параметры ўсталёўваюцца на ўзроўні асобнага запыту або ў кодзе захоўваемай працэдуры.

Адзначым, што ў любым выпадку стварэнне планаў выканання запытаў ўручную і выкарыстанне хинтов - гэта крайні сродак, якога варта па магчымасці пазбягаць.