SQL Server Stored Procedures Optimization Tips

Here are twelve helpful tips for ensuring that you’ve constructed your SQL Server stored procedures to perform in the most efficient manner possible.

1. Use stored procedures instead of heavy-duty queries.

This can reduce network traffic as your client will send to the server
only the stored procedure name (perhaps with some parameters) instead of all the text from a large
heavy-duty query. Stored procedures can be used to enhance security
and conceal underlying data objects as well. For example, you can give the
users permission to execute the stored procedure to work with
restricted sets of columns and data.

2. Include the SET NOCOUNT ON statement in your stored procedures to
stop the message indicating the number of rows affected by a Transact-SQL
statement.

This can reduce network traffic due to the fact that your client will not receive
the message indicating the number of rows affected by a Transact-SQL
statement.

3. Call stored procedures using their fully qualified name.

The complete name of an object consists of four identifiers: the server
name, database name, owner name, and object name. An object name that
specifies all four parts is known as a fully qualified name.

Using fully qualified names eliminates any confusion about which stored
procedure you want to run and can boost performance because SQL Server
has a better chance to reuse the stored procedures execution plans if
they were executed using fully qualified names.

4. Consider returning the integer value as a RETURN statement
instead of returning an integer value as part of a recordset.

The RETURN statement exits unconditionally from a stored procedure,
so the statements following RETURN are not executed. Though the
RETURN statement is generally used for error checking, you can use
this statement to return an integer value for any other reason.
Using the RETURN statement can boost performance because SQL Server
will not create a recordset.

5. Don’t use the prefix “sp_” in the stored procedure name
if you need to create a stored procedure to run in a database
other than the master database.

The prefix “sp_” is used in the system stored procedures names.
Microsoft does not recommend using the prefix “sp_” in
user-created stored procedure names as SQL Server always
looks for a stored procedure beginning with “sp_” in the following
order: the master database, the stored procedure based on the
fully qualified name provided, followed by the stored procedure using dbo
as the owner (if one is not specified).

When you have the
stored procedure with the prefix “sp_” in a database other than
master, the master database is always checked first. If the
user-created stored procedure has the same name as a system
stored procedure, the user-created stored procedure will never
be executed.

6. Use the sp_executesql stored procedure instead of the EXECUTE statement.

The sp_executesql stored procedure supports parameters. So, using the
sp_executesql stored procedure instead of the EXECUTE statement improves
readability of your code when many parameters are used.

When you use the sp_executesql stored procedure to execute a Transact-SQL
statement that will be reused many times, the SQL Server query optimizer
will reuse the execution plan it generates for the first execution when
the change in parameter values to the statement is the only variation.

7. Use the sp_executesql stored procedure instead of temporary stored procedures.

Microsoft recommends using temporary stored procedures when
connecting to earlier versions of SQL Server that do not support
the reuse of execution plans. Applications connecting to
SQL Server 7.0 or SQL Server 2000 should use the sp_executesql
system stored procedure instead of temporary stored procedures
in order to have a better chance of reusing the execution plans.

8. If you have a very large stored procedure, try to break down
the stored procedure into several sub-procedures, and call them
from a controlling stored procedure.

The stored procedure will be recompiled when any structural changes
are made to a table or view referenced by the stored procedure
(an ALTER TABLE statement, for example), or when a large number of
INSERTS, UPDATES or DELETES are made to a table referenced by a
stored procedure. So, if you break down a very large stored
procedure into several sub-procedures, there’s a chance that only
a single sub-procedure will be recompiled, while other sub-procedures
will not.

9. Try to avoid using temporary tables inside your stored procedures.

Using temporary tables inside stored procedures reduce the chance to
reuse the execution plan.

10. Try to avoid using DDL (Data Definition Language) statements
inside your stored procedure.

Using DDL statements inside stored procedures also reduce the chance to
reuse the execution plan.

11. Add the WITH RECOMPILE option to the CREATE PROCEDURE statement
if you know that your query will vary each time it is run from the
stored procedure.

The WITH RECOMPILE option prevents reusing the stored procedure
execution plan, so SQL Server does not cache a plan for this procedure
and the procedure is always recompiled at run time. Using the WITH RECOMPILE
option can boost performance if your query will vary each time it is
run from the stored procedure, because in this case the wrong execution
plan will not be used.

12. Use SQL Server Profiler to determine which stored procedures
have been recompiled too often.

To check if a stored procedure has been recompiled, run SQL Server
Profiler and choose to trace the event in the “Stored Procedures”
category called “SP:Recompile”. You can also trace the event
“SP:StmtStarting” to see at what point in the procedure it is
being recompiled. When you identify these stored procedures,
you can take some correction actions to reduce or eliminate
the excessive recompilations.


»


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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles