Stored Procedure optimiaztion Tips
SQL SERVER – Stored Procedure Optimization Tips – Best Practices by Pinal Dave We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles. Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced. view source print ? 01. CREATE PROC dbo.ProcName 02. 03. AS 04. 05. SET NOCOUNT ON ; 06. 07. --Procedure code here 08.