Stored Procedure Optimization

Stored Procedure Optimization

SET NOCOUNT OFF improves performance when coding stored procedures, triggers, and functions.
Turns of the N rows affected verbiage and eliminates messages from the server to the client for each step in a stored procedure.

CREATE PROC ABC
AS
SET NOCOUNT ON
< stored procedure code >
SET NOCOUNT OFF
GO

Mixing DDL and DML operations causes a recompile
Certain operations on temporary tables cause a recompile
Refer to temp tables created locally
Don’t declare cursors that reference a temp table
Don’t create temp tables while in a loop.