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 as temporary stored procedures, but it does not store data in the system tables, avoiding contention problems. [7.0, 2000, 2005] Updated 6-6-2005
*****
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_”. [6.5, 7.0, 2000, 2005] Updated 6-12-2006
*****
Before you are done with your stored procedure code, review it for any unused code, parameters, or variables that you may have forgotten to remove while you were making changes, and remove them. Unused code just adds unnecessary bloat to your stored procedures, although it will not necessarily negatively affect performance of the stored procedure. [6.5, 7.0, 2000, 2005] Updated 6-12-2006
*****
For best performance, all objects that are called within the same stored procedure should all be owned by the same object owner or schema, preferably dbo, and should also be referred to in the format of object_owner.object_name or schema_owner.object_name.
If the object owner’s or schemas are not specified for objects, then SQL Server must perform name resolution on the objects, which causes a small performance hit.
And if objects referred to in the stored procedure have different owners or schemas, SQL Server must check object permissions before it can access any object in the database, which adds unnecessary overhead. Ideally, the owner or schema of the stored procedure should own all of the objects referred to in the stored procedure.
In addition, SQL Server cannot reuse a stored procedure “in-memory plan” over if the object owner or schema is not used consistently. If a stored procedure is sometime referred to with its object owner’s or schema name, and sometimes it is not, then SQL Server must re-execute the stored procedure, which also hinders performance. [7.0, 2000, 2005] Updated 6-6-2005
*****
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 two 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.
Learn more about how to use sp_executesql in the SQL Server Books Online. [7.0, 2000, 2005] Updated 6-12-2006
*****
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.
[7.0, 2000, 2005] Updated 04-03-2006
*****
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 for it 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 stored procedure.
- 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.
[7.0, 2000, 2005] Updated 04-03-2006