WHILE gloop<=10 BEGIN

INSERT fdiml VALUES (iaioop*50) INSERT #dini2 VALUES (@loop*25) SET @loop=?loop+l

SET (aioop=l

WHILE (aioop<=1000000 BEGIN

INSERT #factta&le VALUES ((@loop / lOOOCOM. 10-(giooD / ICOCOO)) SET iaioop=(aioop+l

SELECT COUNT(*)

FROM #facttable f JOIN #dimi d OK (f.diinl-d.diml) JOIN #dim2 i ON (f .dini2=i ,dim2)

Ha рис. 16.26 показан плаи выполпепия этого запроса.

IHILt eioop-.-lOOOOOO BCaiN

.INSERT Stacttable VALUES ;:.01oop-/ Ш00ОО; <! 10- :01ocp аСОООП: ; SET eioopSioopVl

Query i: Query coat (relative to the batch); IQO.004

Query text! SELECT CQUHTfl fROfl gtacttaule ; JOI tfdiwl a ОЫ u.dirni-d.airnl) JQIM ffdirn2 1 ОЫ £. ашгч dimZ)

Stream Aogceoat.. iJestcd Loops/In.. Weated Loops/In. . JK£acttabie

Coat: Z4

Cost: 2i

1 г . ШМ

Рис. 16.26. Сервер может применять специальные методы оптимизации для запросов

со звездообразной схемой

Полусоединения

Когда в таблице фактов в звездообразной схеме существуют индексы по столбцам измерений, используемых в запросе, оптимизатор будет задействовать эти индексы для осуществления nepeceneinm индексов с таблицами измерен.пй. Каждая таб-

.тпца ii.i.MepeHiiii будет объслииеиа г соотиетгтпуюиш.м индексо.м таб:нн1ы фактов, а затем будет выио,нтети) нересечение :зтнх результатов перед иолучетте.м занисеГ! из таблицы фактов. Этот ирие.м позволяет опти.мпзатору получить .записи и.з таблтни ! фактов наиболее э(})фектнБны.м способо.м - то есть после того, как количество членов в результирующем .множестве будет vMOibujeno за счет пересечения индексов.

Оптимизация группировок

Обычный ход coobiTiui при вынолненин запроса с GROUP BY и соелнненпямп за-к.тючаегся в осуп1ествлении соединишя до rpyinntpoBKH данных. Иногда, однако, быстрее сначала сгруппировать данные, особеьню при работе с очень больпн1.м колнчество.м за1Н1сей, кото[)ые будут объединены в сравнительно небольшое число групп. В прошло.м раз[)аботчики на Transact-SQL выполняли этот вид оптимизации вручную, обычно - с по.\ющью хранимых процедур и временных таблиц. Теперь же онти.\Н1затор .чюжет иоге)ши;ътьно распознать ситуацнн, в которых начальная группировка иредночтительнее, и будет детютвовать соответственно.

Оптимизация предикативных предложений

Оптилшзатор .\южет обнаружить, когда нреликаттшныс предложення ассоциативны, и устранить ненужные шаги для вынолпення соединения. Вот запрос, который это иллюстрирует:

stT nocoun: ox-create TABLE #грр: (Kl int ident-ty PRIMARY KE) CREATE TABLE #1глр2 (kl 1nt identity PRliMARY KEYj CREATE TABLE #:,mp3 (kl -nl iaentity PRIMARY KEY)

DECLARE (31 OOP int SET (31ocp=l

wl-ilLE {aioop<=10 BEGIN

INSERT #tmpl DEFAULT VALUES INSERT #tmp2 DEFAUL--- VALUES INSERT #tnip3 DEFAULT VALUES SET glocp-iaioop+i

SELECT COUNK*;

from #tfflpl tl. #tmp2 t2. #tiiip3 t3 WhERE~ tl.kl=t2.kl and t2.kl=t3.kl go

DROP TABLE #tmpl. #tiTip2. #tmp3

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

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

Этот запрос сначача заполняет три временные таблицы, а зате.м соедшшет все три таблицы по одному и то.му же столбцу. Обычно ход вьтолнення будет таким: соедтптение #tmpl и #tmp2, затем #tmp2 и #tmp3 и зате.м соеД1Н1ен1к^ полу-

Мастер настройки индексов

чпыпихся результапис Если псе ipn rao.iiiubi соединяются ini одному и тому же столбиу(а.м), оптимизатор зан[)осов может устранить од1И1 из тих трех пииои. С0СДТИ1ИБ #tmpl и #tmp3, а затем cocynniini результат с #tmp2. План вьню.июния кода нз примера показывает, что как раз зто ои н делает (pin-. 16.27), Как видите, план начинается с соедниеиня #tmpl и #tmp3 с но.\к)и[ью вложенных цик.юв, 3aTe.\t результат 3Toii онерации соединяется с #tmp2 лля ()ор.\и1ровання резуль-тируюпюго .\июжества.

Щ SQL Scfvcf Oueiy Ала1)а

Isrr e:ooF 1 END

SELECT -N. JT .

Гр;Я tmpl tl. Straps C2, #tirp3 t3 KHERE tl KI t2.)tl t2 lcl-t3 )tl

SELECT Cqsz: 04

3>

stream A4 Eeg c. . .Nested 1.30ps/In.. Иеэсеа Loops/In.. jr.Kpl

Cu*ry 31: Querv coat (relative ro the f.Stcii); 12.90H

Cuery text: gElECT COUHT;ГЙОЙ Htrty. tf-.mp; t2, #tffF t3 OHERE ti.kl-tZ.kl AJJ& t-J - tcl-t Э . 3cl

Рис. 16.27. Оптимизатор способен обнаруживать ассоциативные предикативные предложения

Мастер настройки индексов

SQL Server предостав;1яет хорошее средство для определения индексов, необхо-;и1мых для выпо.тиения чего угодно, начтн1ая от некоторого запроса до всего приложения. Вы .можете получить доступ к Index Tuning Wizard из меню Зиег/ ► Perform Index Analysis в Query Analyzer ilih Tools ► Wizards ► Management ► Index Tuning Wizard в Enterprise Manager. Оба этих средства используют один и тот же BcrpoenHbiii мехаиггз.м.

Perforni Index Analysis в Query Analyzer люжно нри.меитгть, чтобы иредпо-ЮЖ1гть (и опщюиальпо создать), какие индексы увеличат ироизводительиость таиного запроса. Чтобы исследовать эту воз.можность, дава11те со.здадим нети1-

1996-07-04 00:00:00.000 Vins et alccols Chevalier 440,0000 1996-07-05 00:00:00.000 Spez-dlilater 1865.4000

1996-07-08 00:00:00.000 Hanari Carnes 1813.0000

1996-07-08 00:00:00.000 Victuaiiles en stock 670.8000

1996-07-09 00:00:00.000 SupreTOS celces 3730.0000

После создания таблиц выделите запрос с соединением и наж.мите сочетание клавиш Ctrl+I, чтобы Query Analyzer проанатнзнровал индексы, использованные в запросе. Он пореко.мендуст два новы.х некластерных индекса: одни по таблрнге OrdersNI и другой по таблице OrderDetailsNI (рнс. 16.28). Любопытно, что ании-затор не порекомендовал индекс по таблице CustomersNI. Это произошло, скорее всего, потому, что он считает запрос с примене!П1ем сканирования таб.тицы CustomerNI столь же ;к|)фективным, как и использование индекса по таб.чице.

Index Tuning Wizard в Enterprise Manager работает подобным образом, но он спроектирован для работы с целыми базами данных нлн объектами базы данных, а не с конкретны.ми запроса.мн. Чтобы воспользоваться им, выполните следующие шаги:

1. Запустите профайлер (Profiler) и запустите трассировку, которгш отражала бы выполненне операторов Transact-SQL.

2. Запустите ваше приложение, сосредоточьтесь на областях, которые работают не очень хорошо.

3. Сохраните трассировку в файл,

4. Запустите Index Tuning Wizard в Enterprise Manager.

5. Выберите выполнение полного аштлиза, если только база да)Н1ых не настолько большая, что это будет нецелесообразно,

6. Выберите вашу базу данных н включите в анатиз все объекты, если вы ие знаете наверняка, что конкретный объект не используется.

7. В качестве исходной ипфopuцIH[ для настройки укажите фа11л, которьй'! вы сохранили ранее из Profiler.

8. После этого мастер создаст рекомендатш, касающиеся индексов, на основании указанной ва.ми рабочей нагрузки.

дексироьаниыс исрсии таблиц Orders, Order Details i: Customers в 6a,ie даниых Northwind ii соелини.м их B.viecTe:

St T NOCLuN ON USE Ncrti;w;na

SFlECT INTO GrosrsM (-Ш Zrckn

SElFS * Г<:с JrcerDeta-sNI ЕЯСМ [Craer Leta-;sj

SEi.EC * INiC Custor.ersN! FROM Custcmers

SELECT c.CrcerOate. с.СотсвпуНаяе. SWCJ.lnuPrice c.Quarmj) AS BefcreD-sccu.nt

FROM CrdersM о JOIN OrderCetaiiSNI d ON (o.CrderlD-d.OrderlD. JOIN CustcmersNi с ON (o.CustonieriD=c.CustcnerID) GROUP Br cOrdc-Oate, с.CcmpanyNare ORDER BY cCrder-SatG, cCcnpanyNa-ie

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

OrderDate ConipanyNdne BeforeOiscount

9, IJlcviKiiiri-e кнопку Analysis, пппы .лпоГип.-с (),iiiab(i,\;;i мл-я < рсК!).\1еила!.и1ям1! мастера.

Вы MOyKcjc прог.мотрет!) ,\ц]о;кес1 во огчелов и co.\ [)amirb ца диск.

(SckiccaetoSi.ji-JHiiev.HTend&Jjnie.s*. E ;iog(news-*.rfae siiiciwi

Рис. 16.28. с помощью Perform Index Analysis s Query Analyzer можно определить

необходимые индексы

Профайлер

Инстру.мент Profiler ио.лкпяет ва.м устанав.ишагь трассировку, чтобы наблюдать за де11ствиями сервера и некоторыми событиями, таки.\и1 как вынолиение операторов Transact-.SQL. Он находится в папке SQL Server пли в меню Tools в Enterprise Manager.

Использование Profiler для nacTpoiiKii Banieii снсте.\пл зaк.h(jчaeтcя в сборе событий, сгенерированных подснсте.мо!! хрансиня n.ni реляционно!! подснстемой сервера, а затем в nacrpoilKe сервера, запроса или базы данных и гивторио.м вос-произвеле1П1и .этих событий для опенки успениюсти настройки.

Вы .можете настроить трассировку, чтобы определить cmhujko.m долго выпол-няюшиеся запросы, запросы, которые прнво.игг к б.юкировка.м, .запросы, задей-ствующие полное скаиироваине таблиц, и так далее. Bi>i .можете устанавливать как закрытые трассировки, так и достуннь[е всем пользователя.м. Этот ти1СТ])\-

,\Kin также ик.тюмает .\jacrcp, коТ()])Ы1 ио.можег ва.м в uacTpoiiKe часто п^чю.Н)-зуе.мы.х трассировок.

Одна из са.мы.ч .\и)щиы.\ воз.можностеп этого uucT[)y,\ieuTa снособиость 1К)с-ироизБо.иггь ио.тучеилые события. Отсюда и с.тед^ет его название, и это как раз то, что нозио.тит ва.м иостененно настраивать ваш сервер, воснрон.шодя нооб.чо-дн.мые события (юс.те кажло!! нопыткн nacTpoiiKU.

Perfmon

Монитор пронзводнте.тьности Windows NT (Perlonuance Monitor) нозтю.тяст собирать огромное количество onepaHHOJHK)ii статистики и статистики, св>к!ан-uoii с ироизводительностью SQL Server. Вы можете uaiiTii его как в нанке SQL Sen/er, так и в папке NT Administrative tools. Он также доступен нз панели инструментов Profiler. Обычно лучше запускать Perfmon пз Profiler или групигл SQL Server, поскольку в результате автоматически будет включено .унюжестио счетчиков, связан)нях с SQL Server (рнс. 16.29).

паз

дКО-й(- * Ттй/::Ч-.1сООС10.

State. CbUfttM

1.000 и и СоппвсПог OCOOICOO ToWSwvetMemayikBt

1 ООО SDL CompJeaons/sec OQIOCOm PagaReadsAec

SCLSer.wBjdei wit SQLSetvef.Senerai Sialitoci SQLSwvet Memwy Msnegtw SCLSmvoi $1 SlebsiKS SQLS ve..Bjti M№*w

\Ш1£Ы,450

WKHEN 4S0 \\{;ИЕН .450

Рис, 16.29. Запуск Perfmon из Profiler включает несколько счетчиков SQL Server

Perfmon может отображать счетчикл пролзводнтельности в различных форматах, по наиболее поиул>фиый из них - гистограмма, формат по умолчанию.

Д;:а!)а.\).\и1 с/пюп.уас'уся кажлыс три схК>мл;<! и ; ршЬичсскп <лч)6] )ажае 1 .чианс-иия TtKViiui.x иыпраииых счетчлкои.

Паиио.к.е часто примепяелиле счетчики Perfmon - Buffer Cache:Buffer Cache Hit Ratio. General Statistics:SQL Cache Memory(KB) и Databases:Percent Log Used. Для некоторых счегчиков, вк.иочая Percent Log Used, необходи.мо, чтобы вы выбрали 6a:j\ ла]Н1Ых (рис. 16.30).

~3. ., ,

.3ae:u, agr:-

ООО

iSw 1000 Эшт Cac** Hii Плл

i WM SOL C -tJanor /!

WHE4 4W

Рис. 16.30. Для некоторых счетчиков Perfmon необходимо указать базу данных

Пользовательские счетчики

SQL Server определяет десять пользоваге;1Ьских счегчикои, которые вы можете ис11ольз()вать, чтобы следить за производительностью пз ваашх приложении Для ycTaiioBKH этих счетчиков из приложепия используется системная храни-Мс1>1 процедура sp user counterN. Вы можете просматр1И}ать их в Perfmon. устапс)-вии Query counter, равным соответствующему экзелшляру объекта SQLServer: User Settable. На рис. 16.31 показана установка пользовательского счетчика в Perfmon.

DBCC-команды, связанные с Perfmon

с помоп^ыо двух команд, DBCC PERFMON и DBCC SQLPERF, можно получить полезную информашио из Performance МошЧог с помон1ыо Transact-SQL DBCC PERFMON возвращает массу информации в текстовом формате, которая также отображается графически самим Perfmon, а DBCC SQLPERF (LOGSPACE) возвра-uiaeT значение счетчика Percent Log Used для каждой базы данных сервера.

1000 iJiej Contwdenj OOCa-.fXO lotalSeiv-jMsfftoyiKBI

3030000 PBri>! :

SCLS vef SOL SiatKtc

Рис. 16.31. Вы можете определять пользовательские счетчики в Perfmon и устанавливать их в ваших приложениях

Заключение

Transact-SQL предоставляет разнообразные методы оптимизации занросот Правильное проектироваипе баз данных и создание запросов, использууюши: преимущества архитектуры ваших баз данных, - первоочередная задача. Хор<-ш'схя архитектура базы данных и оптимальные запросы работают в rapMoifni друг с другом. Наст]юйка нроизводнтельностн - cлoжIfaм тема, раскрытии которой можно пос[}ятить несколько КИНГ. Ключ к ycHeniuoii настройке нрои--водительностп - изучение инструментов, знанне работы сервера, а также ynoi-ство. необходимое для планомерной работы над ироблемалт, связанными с нр(-изводительностью.

Административный Transact-SQL

Думать ~ самая трудная работа. Может быть, потому столь немногие атим занимаются.

Генри Форд

Хотя само по себе ад.мипист1)ирование баз данных выходит за рамки этой книги, мы все-таки немного углубимся в административный Transact-SQL. Во многих организациях грань между програм.\и1стом и ад.мииистратором баз данных весьма условна, поэтому вне зависимости от того, являетесь ли вы адмтщистрато-ром, практические знания в области ко.манд и синтаксиса административного Transact-SQL могут оказаться весь.ма полезными.

Для выполнения стандартных операций администрирования часто создают специальные хранимые процедуры и сценарии иа Transact-SQL. Создание резервных копий, обновление статистики индексов и внесение изменений в хранилища данных - вот при.меры задач, которые эти ироцедуры обычно выполняют. С помощью службы SQL Server Agent тих запуск .\южно запланировать на то вре.мя, когда использование ресурсов системы относительно невелико.

Оскар Уайльд говорил, что прогрессирующая серьезность ведет к развитию тупости. В какой-то мере администрирование баз данных является следствием прогресса разработки ии(};ормациоиных систем. После создания приложения кто-то должен заботиться о нем и иодлерживать его в рабочем состоянии, включая базу данных. Обь1чно это скучная и неблагодарная работа, поэтому при всяком удобном случае имеет смысл применять сценарии и хранимые ироцедуры для ее автоматизации.

Графические средства администрирования

Было время, когда для решения задач администрирования SQL Server опытные люди первььм делом запускали ISQL - этот старый добрый предшественник утилиты Query Analyzer. Поскольку я начинал работать с сервером, когда в нем было очень мало настоящих средств адмтии-1стрироваиия, многие годы это было .моей инстинктивной реакцией. Средства адмтгнистрирования, которые постав-

14 Зак. 70

.тя.тпсь с SQL Ser\er (n с его дво1(;роди1)1.\1 брато.м Sybase) бы.in сто.ть несовер-шепны (noNniHTe пресловутую утилиту SAF?), что другого выбора почти пе было. Однако теперь, с росто.м сложности SQL Server и непрерывным совершенствован! ш.м его графичсски.х средств, такой необходи.\к)Стн бо.тьше пет. Сегодня не так уж важно, насколько глубоко вы знаете Transact-SQL, - лучши.м выбором для aд.vнIHиcтpпpoвaння сервера является ири.мепенне хнтожества удобных графиче-ски.х средств, входящих в комплект поставки. Enterprise Manager с ,\п-1ожеством встроенных возможностей и утилит является весьма .моии1Ым средством администрирования. Прошли те времена, когда для вьтолнення CBoeii работы адмн-ттстратор бы.ч вынужден прибегать к помощи Transact-SQL н програ.м.м сто-рошнтх производителей. И.мея в распоряжении нпфокие возможности, которые в настоящее вре.мя предоставляет SQL Server, упратмять n.\f с по.мощ1)Ю одного лишь Transact-SQL сегодня было бы трудно, если вообще возможно. Кро.ме того, Enterprise Manager насто.чько усовершенствовался, что необходимость в средствах адмннистрировання сторонних ироизводптелен резко снизилась. Этот продукт предлагает богатьнТ ассортимент средств п воз.можностей, которые ясны и просты в использовантн!. Поэтому, прежде че.м ca.vto.viy писать .массу сложных процедур, нрп.меняя синтаксис Transact-SQL (который иногда весь.ма замысловат), стотгг проверить, что предлагается в составе продукта бесплатно.

Системные хранимые процедуры

Итак, прежде чем решать, что и как авто.матнзировать, следует внимагельтю изучить систе.чп1ые процедуры, поставляемые вместо с серверо.м. В состав сервера включено .множество .мощных и удобных процедур. Они дополняют Transact-SQL полезной фу1И<цпона.чьностью, которой нет в са.мом языке, - от простого вывода метаданных до установки настроек базы данных и конфигурирования сервера. Процедуры, включенные в табл. 17,L не систематизированы по степени важности, и их список далеко ие полон. Они приводятся с целью пробудить ваш гн1терес к готовььм пронедура.м, чтобы в дальне11ше.ч вы .могли ознако.миться с ни.ми са.уюстоятельно.

Таблица 17,1. Хранимые процедуры, применяемые в администрировании Процедура Назначение

Процедуры каталогов, такие как Выдают данные об объекте, хранящиеся в системном ката-sp tables, sp columns, логе. По аналогии с представлениями информационной

sp stored procedures, sp statistic5 схемы в ANSI SQL-92, могут использоваться вместо прямых и т. д. ссылок на системные таблицы в целях исключения зави-

симости от структуры системы

sp addJob, sp addJobschedule, Применяются для управления автоматизированными за-sp addJob5erver, sp addjobstep дачами службы SQL Server Agent

sp autostats Используется для включения и выключения автоматиче-

ской поддержки статистики для данного индекса или индексов

sp create removable Создает переносимую базу даниых