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. [7.0, 2000, 2005] Updated 04-03-2006
*****
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. [7.0, 2000, 2005] Updated 04-03-2006
*****
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. [6.5, 7.0, 2000, 2005] Updated 04-03-2006
*****
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 application needs to be notified of the problem, it happens before any stored procedure processing takes place, preventing wasted effort and boosting performance. [6.5, 7.0, 2000, 2005] Updated 6-12-2006
*****
When calling a stored procedure from your application, it is important that you call it using its qualified name. Such as:
exec 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. [7.0, 2000, 2005] Updated 6-12-2006
*****
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. [6.5, 7.0, 2000, 2005] Updated 6-12-2006
*****
One way to help ensure that stored procedures are reused from execution to execution of the same stored procedure is to ensure that any SET options, database options, or SQL Server configuration options don’t change from execution to execution of the same stored procedure. If they do, then SQL Server may consider these same stored procedures to be different, and not be able to reuse the current query plan stored in cache.
Some examples of this include when you change the language used by the stored procedure (using SET) and if you change the Dateformat (using SET). [7.0, 2000, 2005] Updated 6-12-2006
*****
If you find that a particular stored procedure recompiles every time it executes, and you have determined that there is nothing you can do about the recompiles, and if that stored procedure is very large, consider the following option. Try to determine what part or parts of the stored procedure is causing the recompiles. Once you have done this, break out this troublesome code into its own stored procedure, and then call this stored procedure from the main stored procedure. The advantage of this is that is it takes much less time to recompile a smaller stored procedure than a larger stored procedure. [7.0, 2000] Updated 6-12-2006
]]>