Optimization hints - Stored Procedure

• 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 execute 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.
• 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. 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.
• 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. 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.

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