1чу вы. скорее uvfio. ие оулеге осушесгв.тять запросы иа выоорку диапазона к.иочеп н не будете использовагь столбцы первпчны.х ключей в ORDER BY. По-следовательньЙ! к.частериый первпчньйг ключ к)жeт привести к то.му, что 1К)Льзоваге. п1 будут прегенловагь на одну п ту же обласгь в базе данных при лобавлеипп заиисе11, те.м са.мы.м со.здав;1я горячую точку (hot.spoc). По воз-.мож!их-ти избепйгге этого, псг.олы^уя к-часгерные ключи, распределяющие данные по таблице более равномерно.

о Если при работе пользователей с таблицсГ! часто возникают конфликты, особенно когда .\и )жсство пользователе) ! пыга!0тся добавлять нов1яе зат!си. возл!ОЖно, 6локиро!И<а на уровне страниц ненр1!е.х!ле.ма. Попробуйте пр!ь\!е-нить системную храни-%!у!о процедуру spjndexoptions, чтоо!)! отключить блокировки )!а уровне cipaHini для этой таблицы. Откл!оче!И!е блокировки страниц заставляег сервер задействовать блокировки на уровне записей или таблицы. Это предотвращает автоматическую эскалацию блокировок уровня записей до блокировок уровня страниц, увелич1!вая тем са.м!ял! возможность одновре-N!e!iHoro доступа.

о При.меияйте вычисляемые С14)лбцы для осу1цествления частых вычислений, в,\!есто то!Ю чтобы получать их с помощью SQL каждый раз, когда вы !!Ыпол-няете за!!р(Ю к табл1!!1е. Это сиитакс1!ческ1! более компактно и снижает затраты на создание 1!ла!!а выпол!!е!!1!Я, а также уменьи!ает размер операторов SQL,

KOTOp!)ie Д().ЧЛ<!!Ы !!рОХ0Д!-!ТЬ ПО ССТ!!.

о Тестируйте ваши базы дашгых с разл1!чным кол1!чеством aannceii, чтобы точно ПОНЯТЬ, какой объе.м может поддерживать ваи!а база даннь!Х. Это позволит ва.м зара!!ес узнать, какова вместг!МОсть вап!ен модел!!, и воз.можио, укажет !ia серьезные проблемь! !i Д1!за/ше. База да!!ных, которая отл1!чно работает с нескольки.\!и ть!сячам1! 3a!inceii, .\!ожет быть nepei-ружена при росте до .милл1!она.

О Есл1! ничего не !!Омогает, испол1>зунте ограниченную денор.мализацию, что-бь! улуч1!!ить !1ро1!звол!!тель!!ость. Болсс подробну!0 информац1!Ю читзйте в разделе Дeнop!a;!l!Зaция> датее в этой главе.

Рекомендации, касающиеся индексов

о Создава11те 1!!1дексьг. которые 0!1Т1!\!!13атор может !!Спол!х50вать. Вообще говоря, кластер!!ые И1!дексь! являются лучши.ми для выборок диапазонов и упорядоченных запросов. Кластер![Ь!е индексы также подходят для ключей с высокой плотностью (то есть с .множеством дублирующихся значений). Поскольку записи физическ!! отсорт1!ровапы, запросы, осуществляющие поиск с 1!спользова!И1ем этих неун!1кааьных значений, будут находить их с минимальным количество.м o!iepainiii ввода-вывода. Некластерные индексы хорошо подходят для запросов, возвращаюиц!Х единственную запись.

О Дела 1те !!ек,!астерпь!е !-!ндексы !!астолько селективны.ми (например, с низкой плотностью), насколько это воз.можио. Селективность индекса .может б!)1ть

вычлслена с по.чилиью ([формулы Селективность - Количество уникальных ключей/Количество записей. Мек.тастерные пндсксы с селективностью меныне!! 0,1 неэффективны, и оптимизатор не будет нх пгпользовагь. Некла-стериые индексы ;1учше Bceio подходят для нахождепня едннственных запн-ceii. Очевидно, дублирующиеся ключи вынуждают сервер работать интенсивнее, чтобы найти конкретную запись.

О В дополнение к созданию индексов с высокой селект1ннюстью упорядочивайте в соответствии с селективностью ключевые столбцы в индексе из нескольких столбцов, расно;1агая более селектнвпьи^ столбцы в начале. Так как сервер проходит дерево индекса для нахождения данного значения ключевого столбца, прп.\кчнлн1е высокоселектнвиых ключевых столбцов означает, что е.му придется осуществить .меньше оисраций ввода-вывода, чтобы достигнуть листового уровня индекса, соответственно, запрос будет вьшолпяться быстрее.

О При создании индексов по.\тпте о к.ночевых операциях и транзакциях в базе дан1и>1Х. Создавайте индексы, которые оптимизатор запросов с.\южег задействовать для обработки ваишх наиболее критичных транзакций.

О Со.здавайте индексы для обработки наиболее частых условий соединенпй. Если вы часто соединяете две таблицы по некоторо.му множеству столбцов, создайте по ним индекс, чтобы ускорить соедннеиие.

О Удаляйте неиспользуемые ипдексьт Если вы видите, что планы выполнения запросов должны использовать индекс, но он пе при.меияется, избавьтесь от nei-Q. Передела11те его, если это имеет смысл, пли вообще уберите, в зависи.мости от того, что будет лучше в вашей конкретно! ! С!!туа1Н!п.

О Создавайте индексы по внешним !ст!0ча1\!. Для внеши1!х ключей необходим уникалыи.н ! 1!ндекс в таблице, !ia котору!0 0!i сс!ялается, но для таблиц!л, ссылающеГюя на В!1ешипй кл!Оч, никаких трсбова!н1й нет. Созла!И!е индекса

в ЗаВИСИ.МО!! Табл!ШС может уСК0р!!Т1> проверку цеЛОСТНОСТ!! В!!еШН!1Х КЛ!0-

чей, которая осуществляется в резул!1тате л!одиф!!кац!1й oc!ioB!ioii таблицы, и ускорить !Н5Июл1!е!1ие соедгшсния этих двух таблиц.

О Создавайте врс.\!ен!!Ь!е индексы для обслуж!1ва!!ня нечастых отчетов н запросов пользова-!елей. Д;!я отчета, необход1!.мого раз в !од 1!л1! раз в !!ол!Ода, может не требоваться индекс, поддерж1!васл!Ь!Й весь 1 од. Создайте индекс не-посредстве1!Но перед выпо.ч!1ен1!С.\1 отчета !! удалите его сразу же i!oc;!e выполнения, если это будет быстрее, чем вь!!1ол!гение отчета без индекса.

О Может быть полезно удалять н С!1ова создавать индексь! !!рн вы!1ол1!е!!ИН BULK INSERT-операций. BULK INSERT-onepa!H!H, особен![о те, которые работа!ОТ с несколькими клиентами, чаще всего будут вынол!1Яться быстрее без индексов. Это больше не догма, как было pai!buie, но здравый смысл подсказывает, что чем меньше работы выполняется в процессе массовой загрузки, те.м быстрее она должна выполняться.

О Есл1! опти.мизатор может получ!ггь все !!еобходимыс да!Н!ые из нек;!астер!10-го индекса, не обращатясь к таблице, он так и сделает. Это наз!лвастся покрытие и1!дексо.м , а индексы, которые пр!! 3iOi\! 1!Спользуются, наз1>1ва!0т !!0-

крыиаютимп индексами, Ec.iii is [)e3\-,H)iaie iiooaLiiemni иебо.чьшого сюлСпул НЛП столбцон к существующему ин:1екс\ в не.м будут все даппые. пеобходп-.\n.ie частым заироса.м, вы обнаружите, что это значительно увеличит скорость запросов, Покрьщаюции'! иекластертл!! иидекс люжно ирнменттть в.место иеско,ил<их класте1)ных индексов иа одну таблицу.

о Позвольте SQL Server авто.матичсччл! но.исржпвать информацию о статистике для ващих баз датщых. Это гарантирует, что она будет достаточно свежей, и избавляет бо.чьшииство прн.южений от необходимости вручную пере-со.здавать статистику индексов,

О Поскольку система автоматического управления cTarncTHKoii SQL Server использует выборочные значения д.тя быстрого создания статистики, она .может быть недостаточно репрезентативно!!. Есл1! ог!т!1.\и!затор за1!росов pe!i!a-ет не при.менять индексы, котор!ле, как в!з! сч1!таете. он должен !1рн.менить, i!onpo6yine об!10В1!ть стат1!стику иилексо!з !фучную с по.м01ЦЬ!0 UPDATE STATISTICS...WITH FULLSCAN.

О Вь! .можете задействовать DBCC DBREINDEX() для 1сресозда!!1!я штдексов таб-Л1ИЦЯ. Это ОД!!!! 1!з с!10собов удалить не1!с!10Л!)Зуемое !1ространство из тaбл!-цы 1!Л1! !!зме!!!1ть парамстр FILLFACTOR одного i!3 ее и!!де!<сов. Вот !!ример:

ПБСС DBREINDtX; Xuslomers. pK Ci.stciw-b; DBCC DBREINOCXCCustoiners . MOO)

Оба этих при,\!ера пересоздают !ice 1!нлексь! таб.тиц1>! Customers баз!)1 данн!лх Northwind. В nepiio,\! !трил!ере .мы передаем в DBREINDEX 1!азвание класте1)1!0!-о !!!i-декса. Еслг! пересоздат!> кластер1!ь!й 1!идскс таб:!П!и>1, ее 1!екластер!!ь!е н!!дексы также будут пересоздань!. Во втором при.\!ере .\!Ы передаем пусту!о строку в качестве названия и!!декса. Это также приводит к !1ересозда1!ию всех 1!Идексов таблицы. Од!1а хорО!пая особен!!осгь DBREINDEX cocto!IT в том, что она атомарна: либо все указа1!ные 1И-1дексы ула;!Я10тс.ч !! создаются снова, л!1бо ш! один 1!3 н!!х. Этот процесс вкл!0чает i! !1ндексы, создан!!ь!е сервером для 1!0ддержки ограничений, таких как 1!11дексь! для первичных it у!!!1каль!1ых кл!0че11. Фактиче-скт! DBREINDEX 1!редставляет собой ел1!иствен!Н)!1! ci!0(:o6 !1ересоздать !!ндексь1 перв1!чиых i! уникаль!1ых ключей, i!e удаляя сначала связанные с Н1!.ми orpaim-чения. Так как друп!е таблищя .MOiyr зависеть от перв!1чного пл1! у!1икатьио1 о ключа табл!!ць!, это .могло бь! быть isecbMa сложно. К счастью, DBREINDEX авто.матически заботится об это.\! - ко.манда может уда.тит!) и пересоздать любо11 1!!!декс таблиц, независи.мо от !1а'!ИЧ1!я завис1!Л!ь!х таблиц или ограничений.

О Для просмотра ин([)ор.мации о фрагл!е!!тации таблиц и их индексов можно !!спол1ззовать команду DBCC SHOWCONTIG. Эта 1!1!(])орх!ац!!Я по.может вам ре-U!HTb, сто1!т ли 1!ерестрап!Шть кластер1!!>1Й 1!ндекс табл1щы.

О Как я угго.\!инал в разделе Советы по 0!1тимизац1!1! npi! проектирова1гии баз ла1И1ЫХ>, есл1! часто воз!И1кают К011фл!!кты пр!! од!10вре.\!енной вставке .мно-жество.\! пол!хЮвателей, блокировка !ia уровне страниц .может быть непрне.м-;!е.ма. HonpooyiiTe при.мен1!ть ci!CTe.Miiy!0 процедуру spjndexoptions, чтобы от-кл!04!!ть блокировки на уро!Ите страи1!ц для это!0 1И1декса. Очключетте блок1!ровк1! на уровне страниц пр1!ведет к то.му, что сервер заде11ствует бло-к!1ровку на уров![е 3ai!i!ceii 1!ли таблищл. Посколь!чу эскалац!!Я блокировок

уровня записей до олокнровки уровня тао.тицы происходит не очень часто, это должно нривести к увеличению воз.\к)жностп одповре.чклгного доступа.

О Благодаря тому, что опти.%н1затор запросов может нспольз(№ать несколько индексов одной таблицы, несколько индексов, состоящих из о.пюто ключа. ,\югут быть в oonie.m э(])фективнее, че.м индекс с составным ключом. Это происходит вследствие того, что оптн.\Н1затор \южет обращаться к нидекса.м по отдельности, а зате.м объединять их для получе1шя рез\льтирую:него .%н1оже-ства. Данный подход более nioKnii по сравнению с п]5нмене1И1е.м составного индекса, так как индексы, состоящие нз од1юго ключа, .можно указывать в любых ко.\юи!1ацнях. Для составного ключа это не так - вы должтп.! использовать сюлбцы составною к.1юча в порядке с.тева направо.

О Применяйте Index Tuning Wizard, чтобы определить оптим^иьные индексы для запросов. Это очень хорошгй! инстру.мент, способньй! сканировать файлы трассировки SQL Profiler для определения индексов, которые .\югут увеличить производительность. Вы люжете получить к не.му доступ нз .меню Tools ► Wizards ► Management ► Index Tuning Wizard в Enterprise Manager или из .меню Query ► Perform Index в Query Analyzer.

Рекомендации no оптимизации SELECT

о Старайтесь делать так, чтобы столбцы поиска в запросе соьнадали с са.мы.\и1 левыми столбца.ми в индексе. Индекс по storjd, ord num никак не похюжет запросу, фильтрующему результаты по столбцу ord num.

О Создавайте предложения WHERE таки.м образо.м, чтобы онтн.\и1затор запросов мог распознать и использовать аргу.менты поиска. Смотрите раздел Аргументы поиска далее в главе для получения более подробно!! 1!!г()ор,\1ацин.

О Не используйте DISTINCT или ORDER BY просто на всяк1!1Г случай . Применяйте их, если вам необходимо удал!!ть дублирующиеся значения 1!ли гарантировать некоторый порядок результ1!ру!Ощего .множества соответственно. Если оптимизатор не найдет индекс для их выполнен1!я, е.му, возможно, придется создать промежуточную рабочую таблицу, что .может привести к !1аде-нию производительности.

О Применяйте UNION ALL вместо UNION, есл!1 у вас нет необходи.мост!! удалять дублируюиц!еся значения из объел1!ненного резул!5Т!!рующе1ю Х!Ножсства. Поскольку UNION удаляет дублирующ!1еся значе!!ия, в это.м случае ир!!лется отсортировать илг! построить хеш-таблицу по резуЛ1зТ!!рующе.му л!ножеству перед его возвращением. Очевид1!о, есл!! это1ю можно !!збежать, про!гзвол!!-тельность увеличится - иногда значительно.

О Как я упоминал ранее, вы .можете применить SET LOCK TIMEOUT, чтобы ko!i-тролировать, сколько времени соединение будет ожидать заблокирован1!1ян ресурс. При старте сеанса @@LOCK TIMEOUT возвращает -1; это означает, что время ожидания не было еще уста1!Овлено. Вы можете уста!10внть LOCK TIMEOUT равным иоложительно.му цело.му числу, чтобы ко!!тролировать количество миллисекунд, которое запрос будет ждать заблок!!рованны11 ресурс.

В значительно загруженных при.тожеииях ото нн(ида необходимо, чтобы ni)ii-ложенне не казалось зависшим.

О Ecjhi запрос содержит предикат IN, которьи! включает список постоянных значений (а пе подзапрос), хчгорядочивайте эти значения в соответствии с час-totoii их появления во внешнем запросе, ec;ni вы хорошо знакомы со свои.ми данными. Обычный подход ~ упорядочивать значения в алфавитном ii.iii числовом порядке, ио это может быть не оити.мально. Поскольку этот предикат licpHCT результат, как только будет найдено единственное совпадение с любым из его значенш !, по.местив те из них, которые встречаются чаще, в на-чаю списка, .мы ускори.м выполнение запроса, особенно когда столбец, ио к(гторо.му осуществляется поиск, пе и.меет индекса.

о Задействуйте соедипепия в.место вложенных подзапросов. Подзапрос .может требовать выполнения вложенных итерашй! - цикла внутри цикла. Во вре.мя в.юженной итерации все записи внутренней таблицы просматриваются для каждой записи внеии!ей таблицы. Это хорошо работает для небольших таблиц и было единственной стратегии соединения, поддерживаемой SQL Server до версии 7.0. Однако как только таблицы становятся больше, данный подход становится все менее эф(])ект11вным. На.много лучше осуществлять нор-.малыюе соединение таблиц и позволить опти.мизатору решить, как его лучше обработать. Опти.мизатор об1лчно са.м превращает ненужные подзапросы в соединения, ио всегда правильнее с са.мого начата писать хороший код.

О 11збегайте, если это возможно, перекрестных соединенш ! (CROSS JOIN). Если только ва.м действительно не необходимо декартово произведение двух таблиц, используйте более сжатую фор.му соединения для связи одной таблицы с другоЛ Возвращение декартова произведения, а затем удаление дублирующихся значений с помощью DISTINCT или GROUP BY - обычная проблема новичков, которая чаще всего вызывает трудности с производительностью.

О Вы можете задействовать расширение ТОР , чтобы 0!раничить количество записей, возвращаемых запросом. Это особенно удобно при присваивании значений пере.меиным с помощью оператора SELECT, поскольку чаще всего необходи.мы значения только нз первой записи таблицы.

О Вы можете при.менить предложение OPTION оператора SELECT, чтобы напря-щю влиять на оптимизатор запросов с по.мощью подсказок (hints). Вы также .можете указывать подсказки для определенных таблиц и соединений. Как правило, В1)1 должны позволить оптимизатору обработать ваши запросы, но иногда встречаются ситуации, когда созданный им план выполнения далек от идеала. 11спользуя подсказки для запроса, таблицы и соединения, вы .можете заставить опти.мизатор применить определенный тип соединения, группировки или объединения, а также какой-то конкретный индекс и так да,лее. Раздел 110 оператору SELECT Transact-SQL в Books Online оптгсывает все воз-.можиые подсказки и их воздействие на запросы.

О Если вы сравниваете производительность двух запросов, чтобы определить наиболее эф(;е1сгивный способ доступа к данным, убедитесь, что механизмы кэширования SQL Server не испортят результаты ваших тестов. Один из способов гарантировать это - перезапускать сервер между выполнение.м

запросов. /Jpyroii за1\Л1()чае1ся в псио.тьзовап'лп пелоку.мептпроваппы.х ко-.\ian:i DBCC д.чя очистки пеоиходи.хиял K.viiieii. DBCC FREEPROCCACHE очищает ироцедуршлй KDHi: DBCC DROPCLEANBUFFERS очищает все к:ши.

Рекомендации по оптимизации INSERT

о Поскольку при вьию.тлении SELECT...INTO не жу{)па,И1руется вставка (. тдель-1гьтх записс!!. этот .метод часто намного 6ыст])ее, че.м обычпьп ! INSERT. Однако он блокирует спсте.мные таблицы, так что iipiLMenniTTe его осторожно. Если вы ири.меияетс SELECT...INTO л.1я со.здаппя болыио!! таблнц.ы. другие пон^зо-вателн не всегда CMoiyr создавать об)д-1ЛТ)1 в базе дапньтх, пока SELECT... INTO не заверщптся. Это .может и.меть cepbennijic Н(к;1ел(:гвия прежде всего в c;iy-чае tempdb, так как другие ио.чьзовате.-т не смогут создавать временные объекты, что способно очень си.тьпо паврелть вашему приложению, вызьишть naiHiKy и недовольство. Эп) ие означает, что вы пе должны применять SELECT... INTO - просто будьте осторожны, чтобы не ,\кшопо;и1зировать базу да1П1ЫХ, когда вы это делаете

О BULK INSERT быстрее, че.м INSERT, в случае загрузки внепишх даншях, даже если эта операция полностью журиалн()уется, поскольку она осуществ.1яется иа более ннзко.м уровне сервера. ГЬиользуйте ее в.место длинных сценарнев с INSERT для затрз'Зки больпшх обьс.\юв данных.

Рекомендации по оптимизации операций массового копирования

о Применяйте новую команду BULK INSERT в.место угил1ггы Ьср для вынолпення операций .Maccouoii загрузки. Хотя на са.мо.м гтжпем уровне они задействуют один и тот же .\и.ханизм. .тайные, загружасли.ш с по.мощью BULK INSERT, не используют протокол Tabular Data Stream. Open Data Services н не передаются по сети. Они посылаются напря.мую SQL Server, как в виде набора записей OLE-DB. Таки.м об1)а.зо.м, это на.много быст])ее - иногда в два раза - по сравнению с утилитой Ьср. Обратная! сторона этого метода заключается в том, что файл с данны.ми ло.-океи быть доступен по сети машине, на которой выполняется SQL Sen-.er. Это .может привести к значительным проблемам при передаче по глобальньтм сетя.м (WAN, wide area network), в которых различные сстмен-п,! сети .могут быть изо.чированы друг от друга, но в которых вы все же люжете получать доступ к SQL Server с но.мопп>ю маршрутизируемых протоколов, нанри.мер ТСР/П^.

О Если это воз.\южно, блокируйте таблицы во время операций массовой загрузки (нанри.мер, BULK INSERT). Это .\и)жет значительно увеличить скорость загрузки, уменьшив необходихюеть в конкуренттях блокировках таблицы. Самый лучштн! способ сделать это - включть опцию блокировки таблтты при MaccoBoii загрузке (table lock on bulk load) с ио.\илцью системной про-

цедуры sp tableoption. хотя ,t;im конкрешых операций массово!) загрузки вы можете указать подсказку TABLOCK.

о Для того чтобы BULK INSERT была мииима.чьно журна.Ифуемой, до.чжиы выполняться чеплре \тловия;

□ должна существовать 15озмож11ость заблокир(Л5ать табл1И1у (схг рекомендации для sp tableoption);

□ опция select/into bulk copy должна быть включена для базы данных, в которую загружаются данные;

□ таблица не \южет участвовать в рштзикации;

□ если табл11ца имеет индексы, они также должны быть пустыми.

Миии.мальио журна.чируе.\нле (или пежу;лт;и[Т1руе.мые па языке Books Online) операции .MaccoBoii загрузки обычно быстрее, че.м журна^чируемые операции, иногда значительно, ио даже журналируе.мая ко.манда BULK INSERT быстрее последовательности операторов INSERT, поскольку она выполняется на более иизко.м уровне SQL Server. Я называю эти операции миии.мальио журнали-руемыми, поскольку выделение стра!игц или экстентов все равно журналиру-ется, независи.мо от режшма, в котором работает операция .массовой загрузки (это позволяет откатить сделанные ею изменения).

о Bill .можете осуществлять .массовую загрузку одн1Л5ре.мснно с нескольких клиентов, если вьиюлияются следуюпше условия;

□ в таблипе ие должно быть индексов;

□ 0П1Ц1Я select /Into bulk copy должна быть включена для базы да1ни>[х;

□ таблица, в ксггорую производится за1-рузка, должна быть заблокирована (как я упоминал, лучш1п1 С1Юсоб сделать это - примеитггь spjableoption).

Для парачлельной MaccoBoii загрузки требуется ODBC-версия интерфейса .массовой загрузки, так что программы, исиользуюпте DBLibrary (например, утилтгга Ьср в SQL Server 6.5), ие могут применяться. Как и в случае большинства последовательных onepamiii, 1!ыполне!И1е небольших ее частей параллельно люжет привести к значительному увеличению производительности. Вы можете определить непрерывшле .\и10жсства с ио\ющью FIRSTROW/LASTROW, чтобы разбить большой входной фа11л 1Щ несколько порций BULK INSERT.

о Старайтесь направлять массовую вставку в промежуточную таблтщу - предпочтительно в отдельной базе данных. Поскольку с лиингматьной журиали-руе.мой версией BULK INSERT нельзя использовать индексы по таблице (вютючая созданные для по/цержки PRIMARY КЕ¥-огра!И1чения), лучше создавать таблицы, задача которых будет заключаться в максимально быстром при!Ш-тии данных BULK INSERT. По.местив эти таблицы в отдельную базу данных, вы сможете избежать того, что журиат траизакц1п 1 станет неактуальным для вашей базы данных из-за onepannii массовой загрузки. Фактически вы .можете ие включать опцию select into/bulkcopy для других баз даи!Ш1Х, кро.ме рабочей. После загрузки данных в рабочую область вы .vюжeтe задействовать храни.мые процедуры, чтобы переместить их из одной базы данных в другую.

О Если вы хотите применять оиеранин массово!! :saipy3Kii, ocoocimo в с.тучае иара1ле;1ьно11 Л!ассовой загруз!<и, удал!1те индексы т-аблнцы, в коч'орую вы загружаете данные, а !!осле загрузк!! созла11те их. Поскольку теперь нек.тастер-ные индексы ссылаются i!a кластерныг! индекс (есл1! таковой имеется), а ие на са\!у табл!!1:у, постоянное !iepei-acoiibiBai!ne не1стастер11ых инлекго!!, что было характер!!о для операций Л!ассово!1 за1рузки, теперь в прошлом. На самом деле удале1!11е !1Ндексов перед onepaui!ch массовой за1-рузкн может не сказаться иа про!!Звод1!тельности. Как !i в случае друг!1х реко.мендащ!!! в этой главе, проверя1!те их все методо.м i!po6 и о!ш!бок. Понробу! !те оба этих с!ю-соба и пос.\!отрнте, какой из них б!>1стрее. Б!з!вают ситуаци!!, когда удален!1е !!Ндексов перед o!!epa!Uiefi массово1ю !<оп!!рован1!я приводит к увел!1чен!!10 1!роиз1юд1!тельности в несколько раз, так что эта проверка ctoi!t вашс.о драгоценного вре.\!ен1!.

О Рассмотрите возможность разб!!е1!!1Я больших onepaunii BULK INSERT на па!<еты с помощью параметра BATCHSIZE. Это уменьшает на1рузку на журна.ч транзакций, [!оскольку каждый пакет фиксируется отдельно. Положнтельигщ сторона в том, что очень боль!!!ие операцщ! вставки будут выполняться быстрее, а также возрастет возможность одновремеино!Ю доступа. Обрат1!ая же сторона - таблица будет оставлена в 1!ромежуточном состоян!!!!, если 0!1ерац1!я прервется по како11-либо причине. Пакет, который загружался, ко1да о!тера-ция была прервана, будет откачен; однако все !1акеты до это1 о .\!0\!е!1та останутся в базе данных. Помня об это.м, полезно 1!о.мест!!ть \!алснькн!1 столбец LoadNumber is вашу табли!1у, чтобы л!ож!!о было о!1ре;лелнть за!1НС!1, лобавлен-Н!>!е каждой операцией .массово!! загрузк!!.

Рекомендации, касающиеся DELETE и UPDATE

о Посколь!<у при ис!!ользовании TRUNCATE TABLE удален!1е отдельн!ях 3ani!ceii не жур!!алируется, эта команда на1\!ного 6i>!CTpee, чем обычная журиал1!руе-мая ко.манда DELETE. Как и все .\!инима-!Ь!!о жур!1алнруем!>!е операции, TRUNCATE TABLE делает !!еактуальнь!м жур!!ал транзакщтй, так что 1!р1!.\!еня!1-те ее с осторожностью.

О Команды DELETE и UPDATE обычно огра!И1чиваются 1!редложение.м WHERE, так что за.\!ечания относ1-!тсльно аргу,\!ентов по1!ска для операторов SELECT также !!рименимы и к !тм. Че.\! быстрее удастся найти мод1!ф!!!и!руе.мые зап!!С1!, тел! б!ястрее онг! будут обработан!>1.

Рекомендации для курсоров

о Не злоупотребляйте курсора.ми, есл!! только в этом пет абсолютно!! необхо-ди.мости (на!!ример, 1!0д дуло.м пистолета ил1! когда !1риезжает ва!1!а теща). Постарайтесь найти решен1-!е задачи без курсоров. Вы будете удивлен!)!, узнав сколько задач ре!нается с пол!Ощыо могущественного 01!ератора SELECT.

о Для очень больших резульчирмощих множесть рассмогрше воз.можчосгь применения асинхронных курсоров. Заде11Ствование асинхронного курсора нозво-лит продолжить обработку, пока курсор будет заполняться. При использовании асинхронного курсора OPEN .может выполниться практически .мгновенно. Более подробную инс{)ормацию см. в главе 13, Курсоры .

о Не примеияГгге статические курсоры ilih курсоры, состоящие из набора k.iio-чей (keyset), если только вам на са.\ю.м деле не нужны их уникальные особен-1ЮСТИ. Открытие статического или KEYSET-курсора приводит к созданию временной таблицы, так чтобы курсор мог ссылаться на вторую копию записей или ключей таблицы. Очевидно, этого ио воз.можности следует избегать.

о Если 1!а.м не нужно изменять возврашае.\п,1е курсоро.м данные, В1слючнте в eio определение ключевое слово READ ONLY. Это избавит вас от воз.\южности случайного 1кз.менения данных, а сервер будет знать, что загиюи, по которы.м проходит курсор, ие из.меиятся.

О Р1спользу1 1те опцию курсора FAST FORWARD вместо FORWARD ONLY, если вы создаете результирующие множества только для чтения и однонаправленного доступа. FAST FORWARD создает курсор с атрибута.ми FORWARD ONLY, READ ONLY с некоторыми дополнителыплми внутрениилш оптимизациями.

о Опасайтесь .моди4)икации ключевых столб1юв с по\ющью дииа.мических курсоров по таблицам с пеуникаль!и>и1и ключа.ми кластерного индекса, поскольку это может привести к проблеме Хэллоуина. SQL Server внутренне делает неуникатьные ключи кластерного индекса упикачьными, добавляя к ни.м но-рядковьи! но.мер. Если вы .\юдис})ицируетс один из этих ключей, возможно, что вы по.тучите уже существующее значение, а сервер добавит к нему суффикс, из-за которого это значение будет передвинуто ниже в результирующем .множестве (если курсор был упорядочен по кластсрно.му индексу). Поскольку курсор дина.мический, при выборке оставшегося результирующего .множества эта запись опять попадет в него, и процесс снова повторится, что в результате приведет к бесконечно.му циклу.

О Избегайте .чюдификацни большого количества записей с использованием циюта по курсору в транзакции, так как в результате каждая из.мененная вами запись, в завт1Сн.мости от уровня изоляшш транзакции, .\южет остаться заблокированной до завершения транзакции.

Рекомендации для хранимых процедур

о Когда это воз.можио, используйте хранимые процедуры вместо пря.мых запросов. Чтобы план выполнения пря.\юго запроса, находящийся в кэше SQL Server, был использован повторно, последующий запрос должен быть точно таки.\1 же и полностью квалифицировать все объекты, иа которые он ссылается. Если хотя бы что-то в последующем запросе изменилось - параметры, названия объектов, ключевые настройки окружения, - план пе будет применен повторно. Хороший способ обойти эти огра1ИИ1ения для прямых запросов - задействовать системную хранимую процедуру sp executesql. Она представляет

couuii нечто срс-лнсг ,\1с;ьл.\ .\)а!и1.м1нм; И;Д1иел>).1.мп л 111.)я.\1ы,\т заириса.ми TraiKsact-SQL, rio.siso.iMJ-i иынолнл11> при.мые запросы с измеплелгылп! параметрами. Это дает во..можпость повторно использовать itTaiibi вьню.цкнил нря.мы.х запросов без иеобходи.мости но.пюго совналеиня нх текста.

о Если вы знаете, что п.тли иьи1о.1М1мт>! iieoo.ibiiioii части хранп.мо!! процедуры должен быть перестроен при к а;клом ее вышлтпеини (nanpn.vup. н.з-за и.з.ме-neHiiii данных, которые делают план не (ч.пи-о.м от1гмал1ЛЦ>1.м), но не хотите вызывать Д01Т0Л!И1телыН)1е иак.аалтге расхольк свя.зашпле с iiepecTpoeinie.vi каждый раз плана пьн1о.н1епия д.чя liccii процедуры, попробуйте выделить эту часть в отдельиук.) проислуру. Это позво.чпт иьнюлиять переспроеиие ее нлаш! выполнения при каждо.м занхчча-, ие ,5атр.итпи1Я бо.тыиую прошлуру. Если же это нево.з.можно. нонробчтгге иси{)Льз(Л!а1 ь с|)ункцп1о ЕХЕС() для выполнения этого кода нз ocuouHoii процелуры. по супн.ству созда15 процедуру в процедуре. Поскольку она вьнюлияегся дниа.мнчески, эта подпроцедура будет и.меть новьи'! план выпсынсиия при каждом запуске, ие в.тияя на план выполнения Bceii храни.мой процедуры.

о Если это воз.\10ж!Ю, прп.мепяГпе выходные пара.метры храт1мых процеду]) в.место резульгируюиньх .множеств. Если ва.м нем^ходи.мо вернуть результат вычпс-ieHHii или найти (vuHiciBcnHoe значение в таблице, вертпе его в виде выходг101Ч) пара.метра хранимой процедуры, а не в впле ре.зу.чьтирующего ,\nio-жества, состоящего из едннс1венной записи. Даже если вы возвращаете .\nio-жество столбцов, выходные пара.метры храншюй нроне.турьг на.хншго Э(1)фек-тнвнее, чем нолпоне!нгые рсзульгнруюище .хиюжсчлва.

О Используйте выходные пара.метры курсч)рпого тина is.mccto курсоров типа пожарного рукава-> (результирующих .чпюжссгв), 1ч;.1н isa.m необходн.мо вернуть .множество записей нз одной храпн.мон ироцед.\-ры в другую. Этот подход более гнбк1Н1 п позволяет Biopoii процедуре заве;)шиться быстрее, поскольку не нронсходит обрабспкн резу.чьтнрующего .множества. Вызывающая процедура затем \южет обработать записи, возвращенные курсоро.м.

О Уменьннгге количество информации, передаваемой но сети .между серверо.м и клиентом. Один ил эффектнынях способов стелать это - отключить сообщение DONE IN PROC. Вы можете отключить ылвод этих сообщеигн! иа уровне процедуры с помоан>ю SET NOCOUNT пли на уровне сервера с помощью флага трассировки 36-40. Это .может прнвестн к oipo.NHioii разнице в производительности, особенно в случае дово.чьио .мед.чеппых сетей, таких как глобальные. Если вы реннпе не использовать ф.ча!- трасснрош^и 3640, ко.манда SET NOCOUNT ON должна быть в са.мо.м начале всех ваших хратгмых процедур.

О При.меняйте DBCC PROCCACHE для вьнюла 1Н1с|)ор.мац1т о процедурном кэше, когда вы онти.уикзнруете запросы. ПрнменяГпе DBCC FREEPROCCACHE для очистки процедурного кэша, ччхкбы многократные запуски процедуры не нспорти;п1 результаты тестироватгя. Задействуйте DBCC FLUSHPROCINDB, чтобы вызывать пересоздание планов вынолиешш для всех процедур в заданной базе данных.

о Вы .можете использовать запросы к таблице syscacheobjects в базе данных для нрбслютра ннфор.машп! о кэшированш! процедур, триггеров и других объектов. Одна ключевая часть тчфор.мацпн, которую предоставляет syscacheobjects, -