Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


How will you fine tune a stored procedure or what are the
steps that should be taken to fine tune or optimize a
stored procedure?



How will you fine tune a stored procedure or what are the steps that should be taken to fine tune ..

Answer / aneesh

# Use stored procedures instead of heavy-duty queries.
This 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. 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 the restricted set of the columns and data.


*****

# Include the SET NOCOUNT ON statement into your stored
procedures to stop the message indicating the number of rows
affected by a Transact-SQL statement.
This can reduce network traffic, because your client will
not receive the message indicating the number of rows
affected by a Transact-SQL statement.


*****

# Call stored procedure using its 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.


*****

# Consider returning the integer value as an RETURN
statement instead of 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 RETURN statement
can boost performance because SQL Server will not create a
recordset.


*****

# 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 to use the prefix "sp_"
in the user-created stored procedure name, because 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,
the stored procedure using dbo as the owner, if one is not
specified. So, when you have the stored procedure with the
prefix "sp_" in the database other than master, the master
database is always checked first, and if the user-created
stored procedure has the same name as a system stored
procedure, the user-created stored procedure will never be
executed.


*****

# 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 improve readability of your code when
there are many parameters are used. When you use the
sp_executesql stored procedure to executes a Transact-SQL
statements 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.


*****

# Use sp_executesql stored procedure instead of temporary
stored procedures.
Microsoft recommends to use the 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 to have a better chance to reuse
the execution plans.


*****

# If you have a very large stored procedure, try to break
down this stored procedure into several sub-procedures, and
call them from a controlling stored procedure.
The stored procedure will be recompiled when any structural
changes were made to a table or view referenced by the
stored procedure (for example, ALTER TABLE statement), 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, you get chance that only a single
sub-procedure will be recompiled, but other sub-procedures
will not.


*****

# Try to avoid using temporary tables inside your stored
procedure.
Using temporary tables inside stored procedure reduces the
chance to reuse the execution plan.


*****

# Try to avoid using DDL (Data Definition Language)
statements inside your stored procedure.
Using DDL statements inside stored procedure reduces the
chance to reuse the execution plan.


*****

# 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 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.


*****

# Use SQL Server Profiler to determine which stored
procedures has been recompiled too often.
To check the 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 ?    11 Yes 0 No

Post New Answer

More SQL Server Interview Questions

What is the default sql server instance name?

0 Answers  


What is Stored Procedure? What is Views in sql server? Difference between a User Defined Function and a Stored Procedure Difference between a primary key and a unique key? What is a join and explain different types of joins. Difference between temp table and table variable Difference between Triggers and Stored Procedures Difference between UNION ALL Statement and UNION What is COALESCE / Why do we use COALESCE? Why we use SET ROWCOUNT in Sql How many clustered index can have a table How many types of local tables in SQL SERVER Difference between DELETE and TRUNCATE What is Aggregate Functions? What is Row_Number()? What are Ranking Functions? What is NOLOCK? What is CTE? What are the Advantages of using CTE? What is the STUFF function and how does it differ from the REPLACE function? What are the difference between clustered and a non-clustered index? What are the different index configurations a table can have? Difference between a HAVING CLAUSE and a WHERE CLAUSE? Difference between SET and SELECT Provide all the built in string function of SQL SERVER Difference between char and varchar data types Define candidate key, alternate key, composite key. What are constraints? Explain different types of constraints. What is a self join? Explain it with an example. How will you convert table row to a column comma separated value

4 Answers   ACS,


How to Create Login and User through query in Sql server 2005.and also how to delete Login and User through query?

2 Answers  


Where views are stored in sql server?

0 Answers  


What is application role in sql server database security? : sql server security

0 Answers  






how to use DTS package in 2000,2005,2008 in sql server

0 Answers   Microsoft,


Do you know what guidelines should be followed to help minimize deadlocks?

0 Answers  


What is in place upgrade in sql server?

0 Answers  


How many types of dimensions are there and what are they? : sql server analysis services, ssas

0 Answers  


What is indexing in sql server with example?

0 Answers  


List the different normalization forms?

0 Answers  


What is normalization? What number of normalization shapes are there?

0 Answers  






Categories