Управление процессом компиляции хранимой процедуры

Компиляция происходит при первом вызове хранимой процедуры. При последующих вызовах хранимой процедуры SQL Server пользуется планом выполнения, который содержится в процедурном кэше. Это позволяет зна­чительно повысить быстродействие системы, поскольку при этом экономится время, затрачиваемое на осуществление компиляции. Однако процедурный кэш не является безразмерным, на его поддержание уходит значительная часть оперативной памяти сервера. Поэтому рекомендуется размещать в нем планы выполнения только наиболее интенсивно используемых хранимых процедур. Можно предписать системе не осуществлять кэширование плана выполнения тех хранимых процедур, что вызываются от случая к случаю. В этом случае повторная компиляция процедуры будет производиться при каждом ее вызове. С другой стороны, перекомпилировать хранимую процедуру при каждом ее вызове зачастую бывает выгодно, поскольку при этом создается новый план, который в большей степени учитывает интенсивное изменение статистических данных. Чтобы сделать это возможным, при определении хранимой процедуры воспользуйтесь ключевым словом RECOMPILE.

СОВЕТ:

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

Можно спровоцировать принудительную перекомпиляцию хранимой проце­дуры с именем procedure, воспользовавшись следующей хранимой процедурой:

sp_recompile [@objname =] 'procedure'. В результате при следующем вызове указанной хранимой процедуры оптими­затором запросов будет произведена перекомпиляция ее кода.

Управление автоматическим выполнением хранимых процедур

Вы можете предписать системе инициировать выполнение хранимой процедуры ав­томатически при каждом запуске SQL Server. Зачастую подобный подход использу­ется для осуществления различных административных задач. Например, можно на­писать процедуру, которая при старте системы производит проверку целостности базы данных, а затем осуществляет резервное копирование этой базы данных. Ино­гда автоматически запускаемые хранимые процедуры используют для создания временных таблиц в базе данных tempdb. Это гарантирует, что при каждом запуске SQL Server вы будете получать одинаковый набор временных таблиц.

Определить для хранимой процедуры возможность автоматического запуска могут только участники стандартных ролей сервера sysadmin и setupadmin. Для этой цели используется специальная хранимая процедура

sp_procoptioir[[(aprocName =] 'procedure']

Параметр procedure определяет имя хранимой процедуры, для которой необ­ходимо сконфигурировать автоматический запуск. При этом в процессе выпол­нения данная процедура будет обладать тем же набором прав, что и участник стандартной роли сервера sysadmin.

Каждая автоматически запущенная процедура выполняется в контексте от­дельного соединения с сервером. Поэтому чем больше подобных хранимых процедур запущено, тем меньше соединений остается для пользователей. Чтобы свести к минимуму число соединений, задействованных для обслуживания авто­матически выполняемых хранимых процедур, Microsoft рекомендует построить процесс запуска процедур по следующей схеме. При старте сервера запускается всего одна хранимая процедура, которая затем вызывает другую. Таким образом, для выполнения процедур требуется всего лишь одно соединение. При этом, однако, необходимо следить за тем, чтобы не превысить допустимый уровень вложенности хранимых процедур, равный 32. При попытке превысить этот уровень система выдает сообщение об ошибке. Получить информацию о степени вложенности можно при помощи функции @@NESTLEVEL

Модификация хранимой процедуры

Одним из достоинств хранимых процедур является возможность их многократной модификации без необходимости изменения кода использующих их приложений. Наиболее удобный способ изменения кода хранимой процедуры предоставляет утилита Enterprise Manager. Для этого достаточно дважды щелкнуть на требуемой процедуре и в появившемся окне отредактировать ее код. Когда все изменения будут сделаны, достаточно нажать на кнопку Check Syn­tax и, если проверка синтаксиса пройдет успешно, подтвердить изменения.

Чтобы модифицировать хранимую процедуру только средствами Transact-SQL, используйте следующую команду:

ALTER PROCEDURE] procedure_name [;number]

[ {©parameter data_type } [VARYING] [= default] [OUTPUT] ] [,...n]

[WITH

{ RECOMPILE

| ENCRYPTION

j RECOMPILE. ENCRYPTION} ]

[FOR REPLICATION]

AS

sql_statement [...n]

Параметры этой команды полностью идентичны аналогичным параметрам команды CREATE PROCEDURE. По сути дела, мы просто переопределяем хранимую процедуру. Это равносильно тому, как если бы мы уничтожили хранимую процедуру, а затем создали ее заново. Но, в отличие от подобного подхода, изменение хранимой процедуры посредством команды ALTER PROCEDURE заключается в том, что при этом сохраняются все предоставленные ранее разрешения на использование этой процедуры.

Удаление хранимых процедур

Решившись на удаление хранимой процедуры, тщательно обдумайте, насколько обосновано это решение. Вполне возможно, что впоследствии вам может потребоваться данная хранимая процедура, а в SQL Server не предусмотрена возможность отмены удаления объектов. Хранимые процедуры занимают не так уж много пространства, чтобы их удаление принесло много выгоды.

Можно удалить хранимую процедуру непосредственно в Enterprise Manager либо воспользоваться следующей командой Transact-SQL:

DROP PROCEDURE {procedure} [,...n]

Процедура будет немедленно удалена. Это означает, что будут удалены запись о ее имени из таблицы sysobjects и текст кода процедуры из таблицы syscomments.

Удаление расширенных хранимых процедур происходит другим образом. В Enterprise Manager выберите системную базу данных master и перейдите к объекту Extended Stored Procedure. В контекстном меню требуемой расширенной хранимой процедуры выберите пункт Delete. Необходимо будет подтвердить свою готовность удалить данную процедуру, поэтому нажмите кнопку Drop All. Удалить расширенную хранимую процедуру средствами Transact-SQL можно следующим образом:

spjjropextendedproc [@functname =] 'procedure'

Независимо от того, каким способом вы воспользуетесь, удалена будет не сама динамически подгружаемая библиотека, а лишь ассоциированное с ней имя процедуры, содержащееся в таблице sysobjects.

Использование индексов

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

Индекс представляет собой дополнение к таблице, помогающее ускорить по­иск необходимых данных за счет физического или логического их упорядочивания. Индекс является набором ссылок, упорядоченным по определенной колонке таблицы, которая называется индексируемой колонкой. Физически индекс представляет собой всего лишь упорядоченный набор значений из индексированной колонки с указателями на места физического размещения исходных строк в структуре базы данных. В индексе хранится не информация обо всей строке данных, а лишь ссылка на нее. Когда пользователь выполняет запрос, обращающийся к индексированной колонке, сервер автоматически использует индекс для поиска требуемых значений. Использование индексов позволяет избежать полного сканирования таблицы. В SQL Server 7.0 реализованы эффективные алгоритмы поиска нужного значения в упорядоченной последовательности данных. Ускорение поиска достигается именно за счет того, что данные представляются упорядоченными. Физически, в зависимости от типа используемого индекса, данные могут быть не упорядочены, а храниться в порядке их добавления в таблицу.

К настоящему времени разработаны эффективные математические алгоритмы поиска данных в упорядоченной последовательности. Одним из таких алгоритмов является метод деления пополам.

Планирование и использование индексов

Когда выполняется изменение строк таблицы, то помимо обновления самих данных необходимо выполнить обновление всех индексов. Обновление индексов требует практически столько же времени, сколько и обновление данных. При создании индексов следует ограничиться 4 или 5 индексами.

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

Ключевые колонки — это такие колонки, которые задают критерии выборки данных, например порядок сортировки. Не стоит индексировать колонки, которые только считываются и не играют никакой роли в определении порядка выполнения запроса. Не следует индексировать слишком длинные колонки, например колонки с адресами или названиями компаний, достигающие длины несколько десятков символов. В крайнем случае, можно создать укороченный вариант такой колонки, выбрав из нее до десяти первых символов, и индексировать ее. Индексирование длинных колонок может существенно снизить производительность работы сервера. Индекс является самостоятельным объектом базы данных, но он связан с определенной колонкой таблицы. Работа индексов базируется на возможности уникально идентифицировать строку в таблице. Именно эта возможность обеспечивает быстрый поиск нужных данных.

Наиболее эффективной структурой для поиска данных в машинном представлении являются В-деревья (B-tree). В SQL Server 7.0 реализовано несколько типов индексов.

Кластерный индекс

Принципиальным отличием кластерного индекса(Clustered Index) от индексов других типов является то, что при его определении в таблице физическое распо­ложение данных перестраивается в соответствии со структурой индекса. Информация об индексе и сами данные физически располагаются вместе

Использование кластерных индексов способно существенно увеличить производительность поиска данных даже по сравнению с обычными индексами. Если в таблице определен не кластерный индекс, то сервер должен сначала обратиться к индексу, а затем найти нужную строку в таблице. При использовании кластерных индексов следующая порция данных располагается сразу же после найденных ранее данных. Благодаря этому отпадают лишние операции обращения к индексу и нового поиска нужной строки в таблице.

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

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

Некластерный индекс

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

Для идентификации нужной строки в таблице некластерный индекс организует специальные указатели (row locator). Эти указатели содержат информацию об идентификационном номере фата (ID file), в котором хранится строка, а также об идентификационном номере страницы и номере искомой строки на этой странице. Если же в таблице определен кластерный индекс, то указатель ссылается не на физическое положение строки в базе данных, а на соответствующий элемент кластерного индекса, описывающего эту строку. Это позволяет не перестраивать структуру некластерных индексов всякий раз, когда кластерный индекс меняет физический порядок строк в таблице. Изменяется только кластерный индекс, а некластерные индексы обновляют только индексируемое значение, но не указатель.

Если при построении некластерного индекса кластерный индекс был не уникален, то SQL Server автоматически добавляет к нему дополнительные значения, которые делают его уникальным. Для пользователя эти дополнительные значения не видны, и он может работать с кластерным индексом как обычно.

В одной таблице можно определить до 249 некластерных индексов. Однако в большинстве случаев следует ограничиться 4-5 индексами.

Уникальный индекс

Уникальные индексы(Unique Indexes) гарантируют уникальность значений в индексируемой колонке. Сервер не разрешит вставить новое или изменить существующее значение таким образом, что в результате этой операции в колонке будет существовать два одинаковых значения.

Уникальный индекс является своеобразной надстройкой и может быть реализован как для кластерного, так и для некластерного индексов. В одной таблице могут существовать один уникальный кластерный индекс и множество уникальных некластерных индексов.

Используйте кластерные индексы тогда, когда это действительно необходимо. Для обеспечения целостности данных в колонке можно определить ограничение целостности UNIQUE или PRIMARY KEY, а не прибегать к использованию уникальных индексов. Использование уникальных индексов только для обеспечения целостности данных является неоправданной тратой пространства в базе данных.

Создание индексов

Формат команды CREATE INDEX на Transact SQL имеет вид:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]

INDEX index_name ON table (column [...n])

UNIQUE - при указании этого ключевого слова будет создан уникальный индекс. При создании такого индекса сервер выполняет предварительную проверку колонки на уникальность значений. Если в колонке есть хотя бы два одинаковых значения, индекс не создается и сервер выдает сообщение об ошибке. В индексируемой колонке также желательно запретить хранение значений NULL, чтобы избежать проблем, связанных с уникальностью значений. После того как для колонки создан уникальный индекс, сервер не разрешает выполнение команд INSERT и UPDATE, которые приведут к появлению дублирующихся значений.

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

NONCLUSTERED - создаваемый индекс будет некластерным. В таблице можно определить до 249 некластерных индексов.

Index_name - имя индекса, по которому он будет распознаваться командами Transact-SQL. Имя индекса должно быть уникальным в пределах таблицы.

table (column [...n]) - имя таблицы, в которой содержатся одна или несколько индексируемых колонок. В скобках указываются имена колонок, на основе которых будет построен индекс. Не допускается построение ин­декса на основе колонок с типом данных text, ntext, image или bit. Если указывается несколько колонок, то создаваемый индекс будет смешанным(composite index). В один смешанный индекс можно включить до 16 колонок.

Для удаления индекса используется команда DROP INDEX, имеющая следующий синтаксис:

DROP INDEX 'table.index' [...n]

Аргумент 'table.index' определяет удаляемый индекс в таблице. За одну операцию удаления можно удалить несколько индексов в любой таблице базы данных для этого их достаточно перечислить их через запятую.

Использование представлений

Представление (View) для конечных пользователей выглядит как таблица, но при этом само не содержит данных, а лишь представляет данные, расположенные в таблице. Физически представление реализовано в виде SQL-запроса, на основе которого производится выборка данных из одной или нескольких таблиц или представлений. Простейшее представление, созданное на основе одной таблицы и не имеющее фильтров, содержит точно такой же набор данных, как и исходная таблица. Более сложные представления содержат информацию из нескольких таблиц, причем можно фильтровать список строк, которые будут включены в представление.

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

При обращении к представлению сервер проверяет правильность всех ссылок в запросе. Проверяется, существуют ли объекты, требующиеся для выполнения запроса, определяющего представление. Если одна из таблиц, на которые ссылается запрос, была уничтожена, то представление будет нельзя использовать и при попытке обратиться к нему пользователи получат сообщение об ошибке.

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