
	About Us
	SSWUG Articles
	Articles

	Administering
	Comparison
	General
	Know How
	Replication
	Tuning
	Undocumented
	UDF
	SQL 6.5

	FAQ
	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

Stored Procedures Administration

Alexander Chigrik
chigrik@mssqlcity.com

Introduction

A stored procedure is a precompiled collection of Transact-SQL statements stored under a name and processed as a unit that you can call from within another Transact-SQL statement or from the client applications.

SQL Server ships with number of stored procedures, which can be used for managing the database and displaying information about databases and users. These stored procedures are called system stored procedures. The system stored procedure's name started with the prefix sp_ to distinguish them from the user-created stored procedures. The system stored procedures are stored in the system databases such master and msdb. You can create your own stored procedures by using the CREATE PROCEDURE statement. Stored procedures can have input and output parameters and can issue an integer return code.

Using stored procedures has a number of advantages over giving users direct access to the underlying data. These are:

Performance reasons

Security reasons

Reliability reasons

Performance Reasons for Using the Stored Procedures

Using stored procedures has a positive benefit to performance. Stored Procedures run quickly because they do not need to repeat parsing, optimizing and compiling each time it is executed.. After the first execution, SQL Server has parsed, optimized, and compiled stored procedure, so they run quickly without needing to repeat the parsing, optimizing, and compiling steps each time the stored procedures executed. Because stored procedures run on the SQL Server, they reduce the client computer's loading and can get benefits from the power server hardware. Using stored procedures instead of heavy-duty queries can reduce network traffic, because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text.

Security Reasons for Using the Stored Procedures

Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with a restricted set of the columns and data, while not allowing permissions to select or update underlying data objects. By using the store procedures, the permission management could also be simplified. You can grant EXECUTE permission on the stored procedure instead of granting permissions on the underlying data objects.

Reliability Reasons for Using Stored Procedures

Stored procedures can be used to enhance the reliability of your application. For example, if all clients use the same stored procedures to update the database, the code base is smaller and easier to troubleshoot any problems. In this case, everyone is updating tables in the same order and the risk of deadlocks will be less. Stored procedures can be used to conceal the changes in database design too. For example, if you denormalize your database design to provide faster query performance, you can only change the stored procedure, but all your applications, which use the results returned by this stored procedure, will not be rewritten.

Stored Procedures Execution Plans

When you execute a stored procedure for the first time, the SQL Server query optimizer builds an execution plan for the stored procedure, so that it can run quickly without needing to repeat the parsing, optimizing, and compiling steps each time it is executed. Reusing the execution plan is one of the main advantages of using the stored procedures. However, the execution plan is not stored in memory permanently.

Because the stored procedure execution plan can be outdated, for example when a large amount of data modifications are made to a table referenced by a stored procedure, you may need to recompile the execution plan. SQL Server 2000 automatically recompiles the stored procedure execution plan when one of the following conditions are met:

Any schema changes of the objects referenced in the stored procedure were made.
An index used by the execution plan of the stored procedure is dropped.
A large amount of data modifications are made to a table referenced by a stored procedure.
The new distribution statistics were generated.
The execution plan was deleted from memory, because the memory is required for other objects.
A table has trigger(s) and the number of rows in the inserted or deleted tables grows significantly.

Reusing an execution plan saves the time spent on the stored procedure compilation, but in many queries, especially complex joins on large tables, the compilation time is significantly less than the time needed for execution. So, you may need to recompile the stored procedure execution plan to increase the chance that the best plan be used. There are three ways to cause SQL Server to recompile the stored procedure execution plan, they are:

Including a WITH RECOMPILE clause in a CREATE PROCEDURE statement. When you include a WITH RECOMPILE clause in a CREATE PROCEDURE statement, SQL Server will not cache a plan for this procedure and the procedure will be recompiled every time it will be run. Because the stored procedure execution plan will never been cached, you should use the RECOMPILE option in a CREATE PROCEDURE statement very carefully.

Including a WITH RECOMPILE clause in a EXECUTE statement. When you include a WITH RECOMPILE clause in a EXECUTE statement, the stored procedure execution plan will be recompiled when you run this EXECUTE statement. You can use this option if the parameters you are supplying are atypical or if the data has significantly changed.

Using the sp_recompile system stored procedure to causes stored procedures to be recompiled the next time they are run. To cause stored procedures to be recompiled the next time they are run, you can use the sp_recompile system stored procedure. You can use the sp_recompile system stored procedure when you want your stored procedure reflects inchanges in indexes or data values.

Because SQL Server 2000 can recompile stored procedures and execution plans automatically, in most cases it is not necessary to use the sp_recompile system stored procedure or a WITH RECOMPILE clause, and you can rely on SQL Server decisions to recompile execution plan.

Stored Procedures Cache

The stored procedure cache is an area of memory where SQL Server keeps the compiled execution plans. Once a stored procedure has been executed, the execution remains in memory, so the subsequent users; each with a different execution context (including parameters and variables) can share one copy of the procedure in memory. SQL Server 2000 has one unified cache, where it stores data pages with the stored procedures and queries plans. Because SQL Server 2000 can dynamically allocate more memory when it is needed, the execution plan can stay in the cache as long as it is useful. However, if there is no enough memory for the current SQL Server work, the older plans could be deleted from the cache to free up memory.

Each execution plan has an associated cost factor that indicates how expensive the structure is to compile. The more expensive the stored procedure is to compile, the larger its associated cost factor it will have, and vice versa. Each time the stored procedure is referenced by a connection, its cost factor is incremented. Therefore, a cached plan can have a big cost factor when the object is referenced by a connection very frequently or when recreation of the execution plan is very expensive. The lazywriter process is responsible for determining whether to free up the memory the cache is using, or keep the plan in cache. This process periodically scans the list of objects in the procedure cache. If the cost of a buffer is greater than zero when the lazywriter inspects it, the lazywriter decrements the cost factor by one.

Every time a cached plan is reused, its cost reverts to its original value. The lazywriter process deletes the execution plan from the cache when the following three conditions are met:

The memory is required for other objects and all available memory is currently in use.

The cost factor for the cached plan is equal to zero.

The stored procedure is not currently referenced by a connection.

The frequently referenced stored procedures do not have their cost factor decremented to zero and are not aged from the cache. Even though the plan's cost factor will be equal to zero, it will stay in cache unless memory is required for other objects.

When you tune your stored procedures to maximize performance, you may want to clear the stored procedures cache to ensure fair testing. In this case, you can use the DBCC FREEPROCCACHE statement to clear the procedure cache.

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc