How cand Optimize SQL Server Stored Procedures

Question Posted / nithya

1 Answers
4219 Views
I also Faced
E-Mail Answers

How cand Optimize SQL Server Stored Procedures ..

Answer / nithya

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.

Is This Answer Correct ?

3 Yes

0 No

Post New Answer