1. Whenever a client application needs to send Transact-SQL to SQL Server, send it in the form of a stored procedure instead of a script or embedded Transact-
SQL. This not only reduces network traffic (only the EXECUTE or CALL is issued over the network), but it can speed up the Transact-SQL because the code in the stored procedure
residing on the server is already pre-compiled. In addition, after a stored procedure is run for the first time, it stays cached in SQL Server’s memory where it can potentially be
reused, further reducing overhead on the SQL Server. Keep in mind that just because you use a stored procedure does not mean that it will run
fast. The code you use within your stored procedure must be well designed for both speed and reuse.
2. To help identify performance problems with stored procedures, use the SQL Server's Profiler Create Trace Wizard to run the "Profile the Performance of a Stored Procedure"
trace to provide you with the data you need to identify poorly performing stored procedures.
3. Include in your stored procedures the statement, "SET NOCOUNT ON". If you don't turn this command on, then every time a SQL statement is executed, SQL Server will send
a response to the client indicating the number of rows affected by the statement. It is rare that this information will ever be needed by the client. Using this statement will help
reduce the traffic between the server and the client.
4. Keep Transact-SQL transactions as short as possible. This helps to reduce the number of locks, helping to speed up the overall performance of your SQL Server
application. Two ways to help reduce the length of a transaction are to: 1) break up the entire job into smaller steps so each step can be committed as soon as possible; and 2)
take advantage of SQL Server statement batches, which acts to reduce the number of round-trips between the client and server.
5. When a stored procedure is first executed (and it does not have the WITH RECOMPILE option), it is optimized and a query plan is compiled and cached in SQL
Server's buffer. If the same stored procedure is called again from the same connection, it will used the cached query plan instead of creating a new one, saving time and boosting
performance. This may or may not be what you want. If the query in the stored procedure is exactly the same each time, then this is a good thing. But if the query is dynamic
(the WHERE clauses changes from one execution of the stored procedure to the next), then this is a bad thing, as the query will not be optimized when it is run, and the performance of the
query can suffer greatly. If you know that your query will vary each time it is run from the stored procedure, you will want to add the WITH RECOMPILE option
when you create the stored procedure. This will force the stored procedure to be re-compiled each time it is run, ensuring the query is optimized each time it is run.
6. Design your application to allow your users to cancel running queries. Not doing so may force the user to reboot the client, which can cause unresolvable performance problems.
7. Many stored procedures have the option to accept multiple parameters. This in and of itself is not a bad thing. But what can often cause problems is if the parameters are
optional, and the number of parameters varies greatly each time the stored procedure runs. There are two ways to handle this problem, the slow performance way
and fast performance way. If you want to save your development time, but don't care about your application's performance, you can write your stored procedure generically
so that it doesn't care how many parameters it gets. The problem with this method is that you may end up
unnecessarily joining tables that don't need to be joined based on the parameters submitted for any single execution of the stored procedure.
Another, much better performing way, although it will take you more time to code, is to include IF...ELSE logic in your stored procedure, and create separate queries for each
possible combination of parameters that are to be submitted to the stored procedure. This way, you can be sure you query is as efficient as possible each time it runs.
8. Here's another way to handle the problem of not knowing what parameters your stored procedure might face. In fact, it will probably perform faster than the tip
listed above. Although the above tip is a good starting point, it's not complete. The problem is the query-plans, the pre-compilation of stored procedures, that SQL Server does for you. As
you know, one of the biggest reasons to use stored procedures instead of ad-hoc queries is the performance gained by using them. The problem that arises with the above tip is
that SQL Server will only generate a query-plan for the path taken through your stored procedure when you first call it, not all possible paths.
Let me illustrate this with an example. Consider the following procedure (pre-compilation doesn't really have a huge effect on the queries used here, but these are just for
illustration purposes):
CREATE PROCEDURE dbo.spTest (@query bit) AS
IF @query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO
Suppose I make my first call to this procedure with the @query parameter set to 0. The query-plan that SQL Server will generate will be optimized for the first query ("SELECT *
FROM authors"), because the path followed on the first call will result in that query being executed. Now, if I next call the stored procedure with @query set to 1, the query plan that SQL
Server has in memory will not be of any use in executing the second query, since the query-plan is optimized for the authors table, not the publishers table. Result: SQL Server
will have to compile a new query plan, the one needed for the second query. If I next call the procedure with @query set to 0 again, the whole path will have to be followed from
the start again, since only one query-plan will be kept in memory for each stored procedure. This will result in sub-optimal performance.
As it happens I have a solution, one that I've used a lot with success. It involves the creation of what I like to call a 'delegator'. Consider again spTest. I propose to rewrite it like this:
CREATE PROCEDURE dbo.spTestDelegator (@query bit) AS
IF @query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
CREATE PROCEDURE dbo.spTestFromAuthors AS
SELECT * FROM authors
GO
CREATE PROCEDURE dbo.spTestFromPublishers AS
SELECT * FROM publishers
GO
The result of this restructuring will be that there will always be an optimized query-plan for spTestFromAuthors and spTestFromPublishers, since they only hold one query. The only
one getting re-compiled over and over again is the delegator, but since this stored procedure doesn't actually hold any queries, that won't have a noticeable effect on
execution time. Of course re-compiling a plan for a simple 'SELECT *' from a single table will not give you a noticeable delay either (in fact, the overhead of an extra stored
procedure call may be bigger then the re-compilation of "SELECT * FROM AnyTable"), but as soon as the queries get bigger, this method certainly pays off.
The only downside to this method is that now you have to manage three stored procedures instead of one. This is not that much of a problem though as the different
stored procedures can be considered one single 'system', so it would be logical to keep all of them together in the same script, which would be just as easy to edit as a single stored
procedure would be. As far as security is concerned, this method shouldn't give you any extra headaches either, as the delegator is the only stored procedure directly called by the
client, this is the only one you need to manage permissions on. The rest will only be called by the delegator, which will always work as long as those stored procedures are owned by
the same user as the delegator.
I've had large successes using this technique. Recently I developed a (partial full-text) search engine for our reports database, which resulted in a stored procedure that
originally ran about 20 seconds. After employing the above technique, the stored procedure only took about 2 seconds to run, resulting in a ten-fold increase in performance!
9. While temporary stored procedures can provide a small performance boost in some circumstances, using a lot of temporary stored procedures in your application can
actually create contention in the system tables and hurt performance. Instead of using temporary stored procedures, you may want to consider using the SP_EXECUTESQL
stored procedure instead. It provides the same benefits on temporary stored procedures, but it does not store data in the systems tables, avoiding the contention problems.
10. If you are creating a stored procedure to run in a database other than the Master database, don't use the prefix "sp_" in its name. This special prefix is
reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever
so slightly.
The reason for this is that by default, any stored procedure executed by SQL Server that begins with the prefix "sp_", is first attempted to be resolved in the Master database.
Since it is not there, time is wasted looking for the stored procedure.
If SQL Server cannot find the stored procedure in the Master database, then it next tries to resolve the stored procedure name as if the owner of the object is "dbo". Assuming
the stored procedure is in the current database, it will then execute. To avoid this unnecessary delay, don't name any of your stored procedures with the prefix "sp_".
11. Before you are done with your stored procedure code, review it for any unused code that you may have forgotten to remove while you were making changes, and
remove it. Unused code just adds unnecessary bloat to your stored procedures.
12. For best performance, all objects that are called within the same stored procedure should all be owned by the same owner, preferably dbo. If they are not,
then SQL Server must perform name resolution on the objects if the object names are the same but the owners are different. When this happens, SQL Server cannot use a stored
procedure "in-memory plan" over, instead, it must re-execute the stored procedure, which hinders performance.
13. When you need to execute a string of Transact-SQL, you should use the sp_executesql stored procedure instead of the EXECUTE statement. Sp_executesql
offers to major advantages over EXECUTE. First, it supports parameter substitution, which gives your more options when creating your code. Second, it creates query execution
plans that are more likely to be reused by SQL Server, which in turn reduces overhead on the server, boosting performance. Sp_executesql executes a string of Transact-SQL
in its own self-contained batch. When it is run, SQL Server compiles the code in the string into an execution plan that is separate from the batch that contained the sp_executesql and its string.
14. SQL Server will automatically recompile a stored procedure if any of the following
happens:
· If you include a WITH RECOMPILE clause in a CREATE PROCEDURE or EXECUTE statement.
· If you run sp_recompile for any table referenced by the stored procedure.
· If any schema changes occur to any of the objects referenced in the stored procedure. This includes adding or dropping rules, defaults, and constraints.
· New distribution statistics are generated.
· If you restore a database that includes the stored procedure or any of the objects it references.
· If the stored procedure is aged out of SQL Server's cache.
· An index used by the execution plan of the stored procedure is dropped.
· A major number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure.
· The stored procedure includes both DDL (Data Definition Language) and DML (Data Manipulation Language) statements, and they are interleaved with each other.
· If the stored procedure performs certain actions on temporary tables.
15. One hidden performance problem of using stored procedures is when a stored procedure recompiles too often. Normally, you want a stored procedure to compile
once and to be stored in SQL Server's cache so that it can be re-used without it having to recompile each time it is used. This is one of the major benefits of using stored
procedures. But in some cases, a stored procedure is recompiled much more often than it needs to be recompiled, hurting your server's performance. In fact, it is possible for a
stored procedure to have to be recompiled while it is executing! Here are three potential problems you want to look out for when writing stored procedures.
Unnecessary Stored Procedure Recompilations Due to Row Modifications and Automated Statistics Update
If your database has the "Auto Update Statistics" database option turned on, SQL Server will periodically automatically update the index statistics. On a busy database, this could
happen many times each hour. Normally, this is a good thing because the Query Optimizer needs current index statistics if it is to make good query plan decisions. One side effect of
this is that this also causes any stored procedures that reference these tables to be recompiled. Again, this is normal, as you don't want a stored procedure to be running an
outdated query plan. But again, sometimes stored procedures recompile more than they have to. Here are some suggestions on how to reduce some of the unnecessary
recompilations:
· Use sp_executesql instead of EXECUTE to run Transact-SQL strings in your stored
procedures.
· Instead of writing one very large stored procedure, instead break down the stored procedure into two or more sub-procedures, and call then from a controlling storedprocedure.
· If your stored procedure is using temporary tables, use the KEEP PLAN query hint, which is used to stop stored procedure recompilations caused by more than six
changes in a temporary table, which is the normal behavior. This hint should only be used for stored procedures than access temporary tables a lot, but don't make many
changes to them. If many changes are made, then don't use this hint.
Unnecessary Stored Procedure Recompilations Due to Mixing DDL and DML Statements in the Same Stored Procedure
If you have a DDL (Data Definition Language) statement in your stored procedure, the stored procedure will automatically recompile when it runs across a DML (Data
Manipulation Language) statement for the first time. And if you intermix both DDL and DML many times in your stored procedure, this will force a recompilation every time it
happens, hurting performance. To prevent unnecessary stored procedure recompilations, you should include all of your
DDL statements at the first of the stored procedure so they are not intermingled with DML statements.
Unnecessary Stored Procedure Recompilations Due to Specific Temporary Table Operations Improper use of temporary tables in a stored procedure can force them to be recompiled
every time the stored procedure is run. Here's how to prevent this from happening:
· Any references to temporary tables in your stored procedure should only refer to
tables created by that stored procedure, not to temporary tables created outside your
stored procedure, or in a string executed using either the sp_executesql or the EXECUTE statement.
· All of the statements in your stored procedure that include the name of a
temporary table should appear syntactically after the temporary table.
· The stored procedure should not declare any cursors that refer to a temporary
table.
· Any statements in a stored procedure that refer to a temporary table should
precede any DROP TABLE statement found in the stored procedure.
· The stored procedure should not create temporary tables inside a control-of-flow
statement.
16. To find out if your SQL Server is experiencing excessive recompilations of stored procedures, a common cause of poor performance, create a trace using Profiler and track
the SP:Recompile event. A large number of recompilations should be an indicator if you potentially have a problem. Identify which stored procedures are causing the problem, and
then take correction action (if possible) to reduce or eliminate these excessive recompilations.
17. Stored procedures can better boost performance if they are called via Microsoft Transaction Server (MTS) instead of being called directly from your application.
A stored procedure can be reused from the procedure cache only if the connection settings calling the stored procedure are the same. If different connections call a stored procedure,
SQL Server must load a separate copy of the stored procedure for each connection, which somewhat defeats the purpose of stored procedures. But if the same connection calls a
stored procedure, it can be used over and over from the procedure cache. The advantage of Transaction Server is that it reuses connections, which means that stored procedures
can be reused more often. If you write an application where every user opens their own connection, then stored procedures cannot be reused as often, reducing performance.
18. Avoid nesting stored procedures, although it is perfectly legal to do so. Nesting not only makes debugging more difficult, it makes it much more difficult to identify and
resolve performance-related problems.
19. If you use input parameters in your stored procedures, you should validate all of them at the beginning of your stored procedure. This way, if there is a validation
problem and the client applications needs to be notified of the problem, it happens before any stored procedure processing takes place, preventing wasted effort and boosting
performance.
20. When calling a stored procedure from your application, it is important that you call it using its fully qualified name. Such as:
exec database_name.dbo.myProcedure
instead of:
exec myProcedure
Why? There are a couple of reasons, one of which relates to performance. First, using fully qualified names helps to eliminate any potential confusion about which stored procedure
you want to run, helping to prevent bugs and other potential problems. But more importantly, doing so allows SQL Server to access the stored procedures execution plan
more directly, and in turn, speeding up the performance of the stored procedure. Yes, the performance boost is very small, but if your server is running tens of thousands or more
stored procedures every hour, these little time savings can add up.
21. If a stored procedure needs to return only a single value, and not a recordset, consider returning the single value as an output statement. While output
statements are generally used for error-checking, they can actually be used for any reason you like. Returning a single value as at output statement is faster than returning a single
value as part of a recordset.
SQL. This not only reduces network traffic (only the EXECUTE or CALL is issued over the network), but it can speed up the Transact-SQL because the code in the stored procedure
residing on the server is already pre-compiled. In addition, after a stored procedure is run for the first time, it stays cached in SQL Server’s memory where it can potentially be
reused, further reducing overhead on the SQL Server. Keep in mind that just because you use a stored procedure does not mean that it will run
fast. The code you use within your stored procedure must be well designed for both speed and reuse.
2. To help identify performance problems with stored procedures, use the SQL Server's Profiler Create Trace Wizard to run the "Profile the Performance of a Stored Procedure"
trace to provide you with the data you need to identify poorly performing stored procedures.
3. Include in your stored procedures the statement, "SET NOCOUNT ON". If you don't turn this command on, then every time a SQL statement is executed, SQL Server will send
a response to the client indicating the number of rows affected by the statement. It is rare that this information will ever be needed by the client. Using this statement will help
reduce the traffic between the server and the client.
4. Keep Transact-SQL transactions as short as possible. This helps to reduce the number of locks, helping to speed up the overall performance of your SQL Server
application. Two ways to help reduce the length of a transaction are to: 1) break up the entire job into smaller steps so each step can be committed as soon as possible; and 2)
take advantage of SQL Server statement batches, which acts to reduce the number of round-trips between the client and server.
5. When a stored procedure is first executed (and it does not have the WITH RECOMPILE option), it is optimized and a query plan is compiled and cached in SQL
Server's buffer. If the same stored procedure is called again from the same connection, it will used the cached query plan instead of creating a new one, saving time and boosting
performance. This may or may not be what you want. If the query in the stored procedure is exactly the same each time, then this is a good thing. But if the query is dynamic
(the WHERE clauses changes from one execution of the stored procedure to the next), then this is a bad thing, as the query will not be optimized when it is run, and the performance of the
query can suffer greatly. If you know that your query will vary each time it is run from the stored procedure, you will want to add the WITH RECOMPILE option
when you create the stored procedure. This will force the stored procedure to be re-compiled each time it is run, ensuring the query is optimized each time it is run.
6. Design your application to allow your users to cancel running queries. Not doing so may force the user to reboot the client, which can cause unresolvable performance problems.
7. Many stored procedures have the option to accept multiple parameters. This in and of itself is not a bad thing. But what can often cause problems is if the parameters are
optional, and the number of parameters varies greatly each time the stored procedure runs. There are two ways to handle this problem, the slow performance way
and fast performance way. If you want to save your development time, but don't care about your application's performance, you can write your stored procedure generically
so that it doesn't care how many parameters it gets. The problem with this method is that you may end up
unnecessarily joining tables that don't need to be joined based on the parameters submitted for any single execution of the stored procedure.
Another, much better performing way, although it will take you more time to code, is to include IF...ELSE logic in your stored procedure, and create separate queries for each
possible combination of parameters that are to be submitted to the stored procedure. This way, you can be sure you query is as efficient as possible each time it runs.
8. Here's another way to handle the problem of not knowing what parameters your stored procedure might face. In fact, it will probably perform faster than the tip
listed above. Although the above tip is a good starting point, it's not complete. The problem is the query-plans, the pre-compilation of stored procedures, that SQL Server does for you. As
you know, one of the biggest reasons to use stored procedures instead of ad-hoc queries is the performance gained by using them. The problem that arises with the above tip is
that SQL Server will only generate a query-plan for the path taken through your stored procedure when you first call it, not all possible paths.
Let me illustrate this with an example. Consider the following procedure (pre-compilation doesn't really have a huge effect on the queries used here, but these are just for
illustration purposes):
CREATE PROCEDURE dbo.spTest (@query bit) AS
IF @query = 0
SELECT * FROM authors
ELSE
SELECT * FROM publishers
GO
Suppose I make my first call to this procedure with the @query parameter set to 0. The query-plan that SQL Server will generate will be optimized for the first query ("SELECT *
FROM authors"), because the path followed on the first call will result in that query being executed. Now, if I next call the stored procedure with @query set to 1, the query plan that SQL
Server has in memory will not be of any use in executing the second query, since the query-plan is optimized for the authors table, not the publishers table. Result: SQL Server
will have to compile a new query plan, the one needed for the second query. If I next call the procedure with @query set to 0 again, the whole path will have to be followed from
the start again, since only one query-plan will be kept in memory for each stored procedure. This will result in sub-optimal performance.
As it happens I have a solution, one that I've used a lot with success. It involves the creation of what I like to call a 'delegator'. Consider again spTest. I propose to rewrite it like this:
CREATE PROCEDURE dbo.spTestDelegator (@query bit) AS
IF @query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO
CREATE PROCEDURE dbo.spTestFromAuthors AS
SELECT * FROM authors
GO
CREATE PROCEDURE dbo.spTestFromPublishers AS
SELECT * FROM publishers
GO
The result of this restructuring will be that there will always be an optimized query-plan for spTestFromAuthors and spTestFromPublishers, since they only hold one query. The only
one getting re-compiled over and over again is the delegator, but since this stored procedure doesn't actually hold any queries, that won't have a noticeable effect on
execution time. Of course re-compiling a plan for a simple 'SELECT *' from a single table will not give you a noticeable delay either (in fact, the overhead of an extra stored
procedure call may be bigger then the re-compilation of "SELECT * FROM AnyTable"), but as soon as the queries get bigger, this method certainly pays off.
The only downside to this method is that now you have to manage three stored procedures instead of one. This is not that much of a problem though as the different
stored procedures can be considered one single 'system', so it would be logical to keep all of them together in the same script, which would be just as easy to edit as a single stored
procedure would be. As far as security is concerned, this method shouldn't give you any extra headaches either, as the delegator is the only stored procedure directly called by the
client, this is the only one you need to manage permissions on. The rest will only be called by the delegator, which will always work as long as those stored procedures are owned by
the same user as the delegator.
I've had large successes using this technique. Recently I developed a (partial full-text) search engine for our reports database, which resulted in a stored procedure that
originally ran about 20 seconds. After employing the above technique, the stored procedure only took about 2 seconds to run, resulting in a ten-fold increase in performance!
9. While temporary stored procedures can provide a small performance boost in some circumstances, using a lot of temporary stored procedures in your application can
actually create contention in the system tables and hurt performance. Instead of using temporary stored procedures, you may want to consider using the SP_EXECUTESQL
stored procedure instead. It provides the same benefits on temporary stored procedures, but it does not store data in the systems tables, avoiding the contention problems.
10. If you are creating a stored procedure to run in a database other than the Master database, don't use the prefix "sp_" in its name. This special prefix is
reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever
so slightly.
The reason for this is that by default, any stored procedure executed by SQL Server that begins with the prefix "sp_", is first attempted to be resolved in the Master database.
Since it is not there, time is wasted looking for the stored procedure.
If SQL Server cannot find the stored procedure in the Master database, then it next tries to resolve the stored procedure name as if the owner of the object is "dbo". Assuming
the stored procedure is in the current database, it will then execute. To avoid this unnecessary delay, don't name any of your stored procedures with the prefix "sp_".
11. Before you are done with your stored procedure code, review it for any unused code that you may have forgotten to remove while you were making changes, and
remove it. Unused code just adds unnecessary bloat to your stored procedures.
12. For best performance, all objects that are called within the same stored procedure should all be owned by the same owner, preferably dbo. If they are not,
then SQL Server must perform name resolution on the objects if the object names are the same but the owners are different. When this happens, SQL Server cannot use a stored
procedure "in-memory plan" over, instead, it must re-execute the stored procedure, which hinders performance.
13. When you need to execute a string of Transact-SQL, you should use the sp_executesql stored procedure instead of the EXECUTE statement. Sp_executesql
offers to major advantages over EXECUTE. First, it supports parameter substitution, which gives your more options when creating your code. Second, it creates query execution
plans that are more likely to be reused by SQL Server, which in turn reduces overhead on the server, boosting performance. Sp_executesql executes a string of Transact-SQL
in its own self-contained batch. When it is run, SQL Server compiles the code in the string into an execution plan that is separate from the batch that contained the sp_executesql and its string.
14. SQL Server will automatically recompile a stored procedure if any of the following
happens:
· If you include a WITH RECOMPILE clause in a CREATE PROCEDURE or EXECUTE statement.
· If you run sp_recompile for any table referenced by the stored procedure.
· If any schema changes occur to any of the objects referenced in the stored procedure. This includes adding or dropping rules, defaults, and constraints.
· New distribution statistics are generated.
· If you restore a database that includes the stored procedure or any of the objects it references.
· If the stored procedure is aged out of SQL Server's cache.
· An index used by the execution plan of the stored procedure is dropped.
· A major number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure.
· The stored procedure includes both DDL (Data Definition Language) and DML (Data Manipulation Language) statements, and they are interleaved with each other.
· If the stored procedure performs certain actions on temporary tables.
15. One hidden performance problem of using stored procedures is when a stored procedure recompiles too often. Normally, you want a stored procedure to compile
once and to be stored in SQL Server's cache so that it can be re-used without it having to recompile each time it is used. This is one of the major benefits of using stored
procedures. But in some cases, a stored procedure is recompiled much more often than it needs to be recompiled, hurting your server's performance. In fact, it is possible for a
stored procedure to have to be recompiled while it is executing! Here are three potential problems you want to look out for when writing stored procedures.
Unnecessary Stored Procedure Recompilations Due to Row Modifications and Automated Statistics Update
If your database has the "Auto Update Statistics" database option turned on, SQL Server will periodically automatically update the index statistics. On a busy database, this could
happen many times each hour. Normally, this is a good thing because the Query Optimizer needs current index statistics if it is to make good query plan decisions. One side effect of
this is that this also causes any stored procedures that reference these tables to be recompiled. Again, this is normal, as you don't want a stored procedure to be running an
outdated query plan. But again, sometimes stored procedures recompile more than they have to. Here are some suggestions on how to reduce some of the unnecessary
recompilations:
· Use sp_executesql instead of EXECUTE to run Transact-SQL strings in your stored
procedures.
· Instead of writing one very large stored procedure, instead break down the stored procedure into two or more sub-procedures, and call then from a controlling storedprocedure.
· If your stored procedure is using temporary tables, use the KEEP PLAN query hint, which is used to stop stored procedure recompilations caused by more than six
changes in a temporary table, which is the normal behavior. This hint should only be used for stored procedures than access temporary tables a lot, but don't make many
changes to them. If many changes are made, then don't use this hint.
Unnecessary Stored Procedure Recompilations Due to Mixing DDL and DML Statements in the Same Stored Procedure
If you have a DDL (Data Definition Language) statement in your stored procedure, the stored procedure will automatically recompile when it runs across a DML (Data
Manipulation Language) statement for the first time. And if you intermix both DDL and DML many times in your stored procedure, this will force a recompilation every time it
happens, hurting performance. To prevent unnecessary stored procedure recompilations, you should include all of your
DDL statements at the first of the stored procedure so they are not intermingled with DML statements.
Unnecessary Stored Procedure Recompilations Due to Specific Temporary Table Operations Improper use of temporary tables in a stored procedure can force them to be recompiled
every time the stored procedure is run. Here's how to prevent this from happening:
· Any references to temporary tables in your stored procedure should only refer to
tables created by that stored procedure, not to temporary tables created outside your
stored procedure, or in a string executed using either the sp_executesql or the EXECUTE statement.
· All of the statements in your stored procedure that include the name of a
temporary table should appear syntactically after the temporary table.
· The stored procedure should not declare any cursors that refer to a temporary
table.
· Any statements in a stored procedure that refer to a temporary table should
precede any DROP TABLE statement found in the stored procedure.
· The stored procedure should not create temporary tables inside a control-of-flow
statement.
16. To find out if your SQL Server is experiencing excessive recompilations of stored procedures, a common cause of poor performance, create a trace using Profiler and track
the SP:Recompile event. A large number of recompilations should be an indicator if you potentially have a problem. Identify which stored procedures are causing the problem, and
then take correction action (if possible) to reduce or eliminate these excessive recompilations.
17. Stored procedures can better boost performance if they are called via Microsoft Transaction Server (MTS) instead of being called directly from your application.
A stored procedure can be reused from the procedure cache only if the connection settings calling the stored procedure are the same. If different connections call a stored procedure,
SQL Server must load a separate copy of the stored procedure for each connection, which somewhat defeats the purpose of stored procedures. But if the same connection calls a
stored procedure, it can be used over and over from the procedure cache. The advantage of Transaction Server is that it reuses connections, which means that stored procedures
can be reused more often. If you write an application where every user opens their own connection, then stored procedures cannot be reused as often, reducing performance.
18. Avoid nesting stored procedures, although it is perfectly legal to do so. Nesting not only makes debugging more difficult, it makes it much more difficult to identify and
resolve performance-related problems.
19. If you use input parameters in your stored procedures, you should validate all of them at the beginning of your stored procedure. This way, if there is a validation
problem and the client applications needs to be notified of the problem, it happens before any stored procedure processing takes place, preventing wasted effort and boosting
performance.
20. When calling a stored procedure from your application, it is important that you call it using its fully qualified name. Such as:
exec database_name.dbo.myProcedure
instead of:
exec myProcedure
Why? There are a couple of reasons, one of which relates to performance. First, using fully qualified names helps to eliminate any potential confusion about which stored procedure
you want to run, helping to prevent bugs and other potential problems. But more importantly, doing so allows SQL Server to access the stored procedures execution plan
more directly, and in turn, speeding up the performance of the stored procedure. Yes, the performance boost is very small, but if your server is running tens of thousands or more
stored procedures every hour, these little time savings can add up.
21. If a stored procedure needs to return only a single value, and not a recordset, consider returning the single value as an output statement. While output
statements are generally used for error-checking, they can actually be used for any reason you like. Returning a single value as at output statement is faster than returning a single
value as part of a recordset.
No comments:
Post a Comment