SQL Server Stored Procedures Administration

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 a 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 starts with the prefix sp_ to distinguish them from the
user-created stored procedures. The system stored procedures are stored in the
system databases such as 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 with each execution. After
the first execution, SQL Server has parsed, optimized and compiled the stored
procedure, so they run quickly without needing to repeat the parsing, optimizing
and compiling steps each time the stored procedures executed. Since 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, since your client
will send to server only the 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. 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 for any problems. In this case, everyone is updating
tables in the same order and there will be less risk of deadlocks. 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 applications that
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 large
amounts 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. Therefore, 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:

  • 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 runs. Since the stored procedure
    execution plan will never be 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 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 to reflect changes 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 the
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 not 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 the 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.

»


See All Articles by Columnist
Alexander Chigrik

Alexander Chigrik
Alexander Chigrik
I am the owner of MSSQLCity.Com - a site dedicated to providing useful information for IT professionals using Microsoft SQL Server. This site contains SQL Server Articles, FAQs, Scripts, Tips and Test Exams.

Latest Articles