количсст 13() ц.таиов в клпс лля конкретпогс; объекта. Эта ни()(;рма1П1я пили.-жет вам определить, применяется ли повторно илаи выполнения, юлда вы ваиускаеге процедуру. Syscacheobjects - это псевдотаблица, на са.мо.м деле она не существует - сервер .материализует ее каждый раз, когда вы к ней обращаетесь (.можно вьпи)Л1иггь SELECT 6BJECTPROPERTY(OBJECT ID(syscacheobjects), TablelsFake), чтобы убедиться и эго.м). Вот хра1пьмая процедура, которая запрашивает- syscacheobjects и показьшает ва.м процед\рньп1 кэш:

LSi: ir.aSle-

l- CBJtCJD! sp helpproccache) IS NOT NU! L CRCh PROC sp ne;pDruccacne

CRf.AlL PCCLOuKl ;p ielRp-c;-.cacie gCbfiame ьуьпате = NLl I . jorccsoriy var-cnar;3) = NC . gexectaD-eonly varchar-;3)-ко

OOLc.ii. sp heproccache

0Т/1Сание: Выводит информацию о нрОибдуонсн кзше

Использование: sp helprocc3che (Зс)Рпате=наззание бсзы данных: AL! д):я все/ Ы].

@prccson!y=[yesN0] выводить только хранико.е процедуры.

!2executableoriiy=[yesN0] зызодить только планы выполнения Возвращает-: (Ничего)

Разработал: Ken Нет.аегзог. Ешат!: Khen(3knen.co;ii Версия: 1.3

.1р/.мер- F.XLC ьр г.е'рргсссаспе AL . l?-pioconly- YES

Созла-ia; 1999-06-С2. Дата тоследие/ мсдификаци-/: 1999 03-11

SET NGCOu;-lT ON

DECLARE Csqlstr arcnarCSOOO;

If -tgdonaiTie /?; GOiO help DBCC PROCCACiiF PRINT

SET gsqlstr-

SELECT LEFT;u.namfc,30; AS ProceCire,

LEFT-;cacneobjtype,30j AS Type of Plan, СОинтг) AS Nu.nber of Plans FROM inaster.. Syscacheobjects с JOIN ?. .sysobjecLs о CN (c.obj id-o. :di WHERE dbTd = db Td( ?) -

CASE ?procsonly WHEN YES IHEN and ODjtype = Proc ELSE END-CASE (Pexecutapieonly WHEN YES IHEN

and cacheobjtype = Executable Plan ELSE END+ GROUP BY o.naiTie. cacheobjtype ORDER BY o.naiTie, cacheobjtype

IF ((acbname-ALL)

EXEC sp MSfoneachdb @corimandl- PRINI ***D:splaying the procedure cache fon database: ? ,

(acomiTianc2=PRIN; . CicommandSsqlstr

?я;:; *- L4Li6ync г'п iocecre саспе for сааЬеье. -DBJiA/iE) PR!Ml

SET esGsf,r=KEPLACE(@sq;st.r, ? .DB NAME()) hXLC(i!!sq;s:r)

РГТ.-\ С

EXEC sp jscge L objectname=sp ne:proccache,

(!idesc-l ISIS infcnridiion abcuL the procedure сзспе'.

C3pa-aiTietGrs=if00ra(r.eriae of dataoase to ist; pass ALL to :ist all.

(aprccsonly=[yesNC] rst stored procedures only.

(3execL.tflb;eonly=[yesN0] lst executable plans only,

(Pexarae-EXEC sp he ipproccacne ALl . (3prcconiy= YES .

(aauthor=Ken Hencerson.

(Эетз i > khenfPkren. com.

(?vers1on= 1. (previs ;or.= 3,

@Gatecreated=6/2./99. Xdatel astchangec=8/11/99 RETURN -1

EXEC sp hepproccache ALL (результаты сокращены)

num proc buffs num prcc Duffs used num proc buffs active proc cache size

574 5/4 162 617

proc cache used

***Displaying tl;e procedure cacne for Catabase: master

Procedure Type of Plan Number of Plans

sp databases Compilec Plan ]

Compiled Plan

sp dir Executable Plan 3

sp execjtesq: Extended Proc 1

sp helpproccache Compiled Plan 1

sp MSforeach worker Comoiled Plan 1

sp MSforeacndb Compileo Plan 1

sp table Compiled Plan 1

sp table Executable Plan 1

sp usage Compiled Flan i

sp usage Executable Plan 1

syscomments Parse Tree 1

***Displaying the procedure cache for database; mscb

Procedure Type of Plan Number of Plans

sysindexes Parse Tree 1

sysobjects Parse Tree 1

systypes Parse Tree 1

syjccjns .-a-se .-t>e

у5 ;с?ле1 Pdr-se Tree

bysctjec:- -dse oe

Оор'йугд ;.ги procecjre cicre for datflbibr puCt -recto.-fr yre cf Pdr.

d,jtror crob3tac ii <есь'1ао I e Р^сг 1

CK dtJtr,orb aj iC esEA5/93 Pdrse Tree i

sy5 rcle es Parse free i

sysotjecls Parse Tree

systypes Parse T-ec 1

**D-5playing the pccedure cache for databcir. SCW S

Procedure Type of Plan Number cf Plans

**D!bplaying the procedure cache for datacase- tempdb

Procedure Type of Plan Nunben of Pians

Аргументы поиска (SARGs)

Стараргтесь создавать запросы, в которых оптилтзатор может выде;тть аргументы поиска. SARG или аргумент поиска, - это часть запроса, которую опти-.\игзатор потенпна.1ьно .\южет исполь.зовать сов.местно с индексом для ограничения результатов, возвращаемых BanpocoNr Аргументы поиска и.меют форму

Столбец оператор Ко1стаиа/Г,ерег еиная

(тер.\пп[ы могут быть тименены), где Столбец - столбец таблицы; оператор - =, >=, <=, >, <, о, !=, !>, !<, BETWEEN и LIKE (некоторые выр;гження LIKE могут быть преобразованы к аргументам поиска, некоторые - ист) и Константа/Переменная - это значение константы, ссылка на пере.менную или одна из нескольких функций.

Аргументы поиска могут быть объединены с по.мощью AND для создания составных выраже!ии 1. Правило для определения, является ли выражение аргу-.ментом поиска, заключается в следующе.м: выражение может быть аргу.менто.м поиска, если оптимизатор .может определить, что оно представляет собой сравнение значения ключа индекса и константы или переменной. Выражение, которое сравнивает два столбца или два выражения, не является аргу.ментом поиска. Общая оинтбка всех новичков заключается в использовании столбца в выражении при сравнении его с константой или псрсменноГг Чаше всего это приводит

к тому, что иыралчеипе ие премстаи.тясг coot/,! аргумент пписк-а, потому что оптимизатор ие знает, что 4)актнческн вычисляет выралчснпе, - это иеиззестно до иыиолнения. В(Я' пример такого запроса.

-- Н? легай-0 -ак -SEIECT с :у. state.

;-,ге;;е а. гспе-. .

state

=2:0 Alto CA 9430;

Измененньи! запрос вьн-.1яд1П так:

SELECT city, state, zip

FROM authors

wHERE аи 1па[Г;е-С,Л1

A4D аи Тпзте=Апп'

Чтобы увидеть разнтщу, которая по.тучн.тась в результате этою небольшого нзме11е!Н1я, дава11те нос.мотрн.м на планы выполиення. сгенерированные д;1Я каждого из запросов. Чтобы включить показ плана выполнепня в Query Analyzer, наж.чите сочетатте клавиш CtrhK н.тн выберите Show Execution Plan нз меню Query н выполните запрос. На рис. 16.1 показан план выполнения для первого запроса, а на рис. 16.2 - для второго.

й й V >

у- > и й И(;

- h .r--Z .1.-. - :-.;>.

5ELECT city, зсас* Ziv FPOK 1

1

Query 1; Query cost (relative -.0 the batch): 100.03 Query text: SZLtCT cit?. state, zip FFQH authota MHEFE 1

Рис. 16.1. План выполнения для запроса, в котором оптимизатор не может выделить арг/менты поиска

Рис. 16.2. План выполнения для запроса с аргументами поиска

Вы можете посмотреть подробности коикретио1о тага плана вьиюлнения. наведя на него указатель .мьини. Планы выполнения читаются справа налево, так что начните с са.мого правого узла и затем продвигайтесь влево. Видите разницу? Конкатепагшя столбцов aujname и au fname в перво.м запросе не дает использовать тшлекс aunmind, ключа.ми которого являются оба столбца. В.место отого первый запрос должен задействовать кластерный ттдекс таблицы, ключо.м которого является столбец aujd, ие очень полезный для па.хождения автора ио и.меии (фактически производится сканирование таблии1>1). Напротив, второй запрос .\южет ис1к)льзовать индекс по и.метги автора, поскольку этот запрос избегает запутывания оптимизатора иеиуж1гой конкатенацией строк.

Давайте рассмотри.м еще несколько запросов, чтобы определить, .можно ли в них выделить аргументы поиска. Для сравнения начнем с добавления нескольких индексов. Выполните следуюнцйт сценарий для созла!И1я дополш!-тельных индексов:

USE pubs

CREATE INDEX qiy ON saies (qty) CREATE INDEX pub nanie ON publisners (pub naii;e) CREATE INDEX hi range ON roysched (hi range) USE No-thwird

CREATE INDEX ContactName ON Customers (ContactName)

Вот запрос, который выбпрас! загпгси т таблицы sales базы даипы.х pubs па

оспопапии значений столбца qty:

SELECr * FROM sales WHERE qty+1 > 1С

Содержит лн предложение WHERE аргу.мент поиска? /Давайте носмотрн.м на план выполнения этого запроса (рис. 16.,3). (Знтн.\н1затор выбрал сканнрование iciacTepnoro индекса - но сунгеству - это последовательное чтение Bceii таблицы, даже при то.м, что существует индекс по столбцу qty. Почему? Пото.му что столбец qty в запросе задействован в выражении. Как я упоминает ранее, вк.тю-ченне столбца таблицы в выражение не позволяет е.му быть полезиы.м опти-.мнзатору, то есть быть аргу.менто.м поиска. Давайте переиннге.м нред;10же1Н1е WHERE запроса, исключив столбец qty из выражения (рис. 16.4):

SELECT * FROM sales WHERE qLy > 9

aetiea

-cy 1: Ouery cost TV ,text; SELECT

relative to Che batch): 1ао.СОч TROH aaXes ЫНЬКЕ qcy->l > 10

Cost: 0\

143. \it Ch t t lodw Scan

Cost Scenrangactmwedindex, trisrw oi aitngs

Phriical operation: LoQKat opetetion. How count Etttmaled torn tav I/O cMt: CPU i;o.l:

Numbn Ы емесын: Sub*ie co lr , Aiounwni: .

CViSlered index Scan Oosleied Index Seen 27

Рис. 16.3. План выполнения запроса к таблице sales

Поскольку освобожденный теперь столбец qty сравнивается с константой, опти.мизатор решает использовать индекс, который .мы добавили ранее. Вот другой при.мер (рис. 16.5):

SELECT * FROM autnors wHERE au Iname LIKE IGrX

Рис. 16.4. Новый, улучшенный план выполнения запроса к таблице sales

Рис. 16.5. План выполнения запроса к таблице authors

И ciiolia оити.мизаюр pemn. i нри.чпчт гь псаинрованпе кмасгерного индекса вмести ]ого, чтоиы :!аде11ств()иать пек.частериьи'! нилекс, который мы создали по столбцу aujname. Причина проста - он не можег преобразовать маску LIKE в пригодный д.чя прп.\1сн(.чп1я арг\мен1 поиска. Дава11тс перепишем з.чирос, чтобы в нем .можно бьгчо выделить аргхчменч поиск;, (рис. К).6);

SEI ЕС; 3u incnie. zjj-.c-.- FRCM Sut.ors

ivHERE au Iname ;.IKt- Gri

m sat Sam am AiuHinr

Рис. 16.6. План выполнения улучшенного запроса к таблице authors

ПРИМЕЧАНИЕ -------

Помимо очевидного синтаксического различия, маски LIKE этих двух запросов еиде и работают по-pasHOMy. Строго говоря, они задают не один и тот же вопрос. Я предполагаю здесь, что запрос к authors спрашивает обо всех именах, начинаюидихся с Gr , даже при том что первая маска начинается с группового символа.

Теперь оптимизатор решает использовать некластернын ]Н1декс по таблице, который включает столбец aujname. Внутренне оптн.мнзатор преобразует ои 1пйте LIKE Gr%

К

au папе > GQ AND au паж- GS

Это iio.!iio;i>iei iiaiiiii и индексе песклм); может быть HaiLicHo и индексе (или б.ти значснню), и ключи, стедующие .за ;ит\1. дет доституто знамени/ (JS>. С1г\пю.т так что GQ иа диа 311аче1П1я впереди что гарантир}ет- iia.xoAViemic иервото зна посмотрим иа но.хожий .запрос с .твумя v\

SELECT *

ESCM pii.b isrer:

>;i-ERE pL;C nane ..\t Nc.vjKoorr-

A вот ruan вьп1о.тне1П1Я зил о .запроса образование ире:п(гжение WHERE к тако.му

итмые значения глюча. Значение чСЦ

ЖТИИТИИ! К.ТЮЧ. С00Т1!еТСГВуЮЩИ] 1 .ЭТО.М}

читаются последовательно, пока не бу-< и.меет значение .\SC1I, равное 251. (ji . лополнеиного .иобы.м си.мволом, ченпя. начинающе!()ся с (}! . Давайте )\iinoBbT\ui ciTMBo.Ta.Mii:

(обратите !inH.\iainie iki внутреннее !ipe-, как в !1редылуще,м примере) (p!ic. 16.7),

г .> - iQuwTSemiOT.

f.-.., .: ...... -. .,. , ,. .-

Ш

Cuery text; select £ram p-iPlд^-егД vf.ec- pxih narrg LIKZ -НаШДсслЧ

Coat: 49i

Index Seek

Phjincaf operation

-- Logrcat oi>e<alJon

Рои count . . Eiiiaiutr-d row tun.

Ai.VA CPU colt-

a Subtiee

Aigumenc

----n. ,,.4.-,-,г,гап--.

Рис. 16.7. Когда это возможно, оптимизатор преобразует выражения с в аргументы поиска

В1лражения с LIKE, которые можно преобразовать к виду х больше, чем у. и .меньше, чем г , оити.мизатор может задействовать в качестве аргументов поиска, иначе они не .мо!-ут быть пр!1ме!1еи!>! опти.ми.-игторо.м. Вот другой запрос, использующий столбец qty в табл1!це sales базы данных pubs (рис. 16.8):

SELECT * FRO.M sales

a-.LERE qcy ЬЧлЕЕ|ч 20 Al-i.j 30

Рис. 16.8. Оптимизатор запросов преобразует BETWEEN в составной аргумент поиска

И снова онтн.мнзатор преобразовал предложенне WHERE в два выражения, которые, как он рецщл, будут эффективнее. Он преобразовал предложение BETWEEN в cocTaBHoii аргумент поиска, задеГгствующнп операторы >= н <= для реализации поведения BETWEEN.

Вот пример, в которо.м константа по.мещена слева от оператора (рнс. 1G.9):

SELECT * FROM oscheo

WHERE 5CC0 < hi range

Как видите, порядок элементов значения не и.меет аргу.мент ноиска псе равно корректно определяется, н нсполызуется соответствующий ему индекс.

Дава!1те посмотрим на дру]-ой запрос к таблице sales. В не.м применяется поиск по двум столбца.м:

SELECT * FROM sales

WHERE qty > 40 OR stor- icl-6360

До версии 7.0 сервер использовал бы только один индекс таблтнщк незаыкн.мо от того, сколько столбцов из этой таблицы вы указали в предложеннн WHERE. Теперь это больше не происходит, и, как вы .можете увидеть нз плана вьпюлне-ния запроса, кластерньн! индекс таблгпгы п некластерньн! индекс по столбцу qty, который мы создали ранее, служат для создания результирующего .\шожества. Поскольку мы объединяем два аргу.меита ноиска посредство.м OR. они обрабатываются парал.тельно с при.менснне.м подходящих индексов, а затем обт>едиияют-ся с помощью onepaniHi хеширования (hash match) не[к)срелствстн10 иерел тем, как будет возвращено результирующее множество (рис. 16.10).