SQL Stored Procedure Performance Improvement

Purpose: Use this job aid to review ways to improve the performance of stored procedures.

• To improve the performance of stored procedures, you can
• Use the SET NOCOUNT ON statement. Place this statement just after the AS keyword in the body of the procedure. This prevents SQL Server from sending messages to the client after SELECT, INSERT, UPDATE, MERGE and DELETE statements are executed. This eliminates unnecessary network overhead.
• Use schema names to create or reference database objects in a procedure. This prevents permission and access problems that are caused by a user's schema being assigned to objects that don't have a schema. It will also allow the Database Engine to resolve object names without having to search multiple schemas. This will decrease processing time.
• Don't use wrapping functions on columns which contain WHERE and JOIN clauses. This will define the column as non-deterministic and will prevent the query processor from accessing the indexes.
• Avoid using SELECT*, to prevent Database Engine errors that might cause procedure execution to stop.
• Don't process or return large volumes of data. Using stored procedures to process or return only small sets of data increases efficiency. It reduces the amount of data that has to be transmitted via a network, and reduces the processing that the client application has to do.
• Improve error handling using the Transact-SQL TRY...CATCH construct. This allows for more accurate error reporting with less programming.
• Prevent passing NULL to columns that don't allow null values by using the DEFAULT keyword. The DEFAULT keyword must be in all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQL statements.
• Eliminate null values from queries by using the modification statements that convert nulls and include logic. Null values in Transact-SQL are problematic because a null is a placeholder for an unknown value. This can cause strange behavior when using AGGREGATE functions or querying for result sets.
• Use NULL or NOT NULL for each column in a temporary table to ensure consistent nullability behavior.