Создание и управление индексами. Создание и управление встроенными процедурами. Оптимизация производительности запросов. Управление транзакциями и блокировками, страница 17

Результат работы процедуры выглядит следующим образом:

---------- ------ результат  30

(1 row(s) affected)

Явная компиляция процедур

Встроенные процедуры могут быть перекомпилированы, но вы не должны это делать часто, и только когда:

  Передаётся значение параметра в встроенную процедуру, которая возвращает широко меняющийся набор данных.

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

  Значение параметра, который вы указываете, не имеет типа.

Сервер SQL предоставляет методы для явного перекомпилирования процедуры.

Оператор CREATE PROCEDURE ... [WITH RECOMPILE] указывает, что сервер не должен кэшировать план выполнения процедуры. Вместо этого, опция перекомпилирует процедуру каждый раз, когда она вызывается.

Следующий пример создаёт процедуру, которая перекомпилируется каждый раз, когда она вызывается.

USE Northwind

GO

CREATE PROC dbo.OrderCount

@CustomerID nchar(10)

WITH RECOMPILE

AS

SELECT count(*)

FROM [Orders Qry]

WHERE CustomerID = @CustomerID

GO

Оператор EXECUTE ... [WITH RECOMPILE] создаёт новый план выполнения каждый раз, когда процедура выполняется, если вы указали опцию WITH RECOMPILE. Новый план выполнения не сохраняется в КЭШе. Используйте эту опцию, когда параметр, который вы передаёте, сильно отличается от того, который вы часто используете. После выполнения желательно снова выполнить процедуру с обычным параметром. Эту опцию часто используют, если данные сильно изменились с момента последней компиляции.

Следующая опция перекомпилирует процедуру в момент выполнения:

EXEC CustomerInfo WITH RECOMPILE

Встроенная процедура sp_recompile перекомпилирует указанную встроенную процедуру или триггер для последующего выполнения. Если параметр @objectname указывает таблицу или просмотрщик, все процедуры, которые используют этот объект будут перекомпилированы для дальнейшего использования.

Используйте процедуру sp_recompile с опцией tablename если вы добавили новый индекс в основную таблицу, на которую ссылается встроенная процедура и если вы думаете, что производительность повысится от использования индекса.

Следующий пример перекомпилирует все процедуры и триггеры, которые ссылаются на таблицу Customers.

EXEC sp_recompile Customers

Вы можете использовать DBCC FREEPROCCACHE для очистки всех планов выполнения встроенных процедур из КЭШа.

Выполнение расширенных процедур

Расширенные процедуры – это функции внутри DLL, которые увеличивают функциональность SQL Server. Они выполняются схожим с встроенными процедурами образом, и они поддерживают входные параметры, возвращают статус выполнения и выходные параметры.

Следующий пример выполняет sp_cmdshell расширенную процедуру, которая отображает список файлов и поддиректорий, с помощью выполнения команды ОС dir:

EXEC master..xp_cmdshell 'dir c:\'

Результат выполнения:

output                                                                                                                                                                                                                                                          

---------------------------------------------------------------------------

Volume in drive C is Windows 2003 Server

Volume Serial Number is E04D-393F

NULL

Directory of c:\

NULL

03.04.2003  20:00           278 688 $LDR$

28.04.2003  10:34    <DIR>          Documents and Settings

28.04.2003  10:55    <DIR>          Inetpub

29.04.2003  10:50    <DIR>          Program Files

30.01.2003  20:28    <DIR>          RoxioTmp

04.04.2002  21:23            19 133 SCANDISK.LOG

03.06.2003  08:50    <DIR>          System Volume Information

22.05.2003  14:30    <DIR>          Temp

03.04.2003  20:00           457 515 txtsetup.sif

06.05.2003  15:37    <DIR>          WINDOWS

28.04.2003  10:27    <DIR>          wmpub

4 File(s)        755 355 bytes

8 Dir(s)   3 196 006 400 bytes free