SQL server Stored Procedure and Query Optimization

SQL SERVER – Stored Procedure/Query Optimization Tips/Hints 

• SET NOCOUNT ON/Use NOLOCK: SELECT statement, DML (Delete, Update, Insert) statement, Serialized SQL code etc. returns messages/rowcount affected by SQL statement. Getting messages/row count is quit useful while debugging or parsing the code/scrip output only, If you implement SET NOCOUNT ON in the SQL scripts, this disables returning extra messages.SET NOCOUNT is usefull for following



Optimize Performance

• SQL Script/SP contains multiple Transact-SQL Code.
• SQL Script/SP contains loops.
• SQL Script/SP contains multiple SELECT/DML Statements.

• Use Fully Qualified objects name:  
• Object's Fully qualified name should be used with all objects referenced inside the stored procedure. This assist COMPILER in order to find the pre-existing complied plan instead of searching the objects in other schema before finally deciding to use a cached plan, 

• USE ISOLATION Level: This avoid making locks on referencing objects.
• Do not use nested CTE: They have large turn around time
• Avoid Use of Functions in SELECT statement they cause high I/O. 
• Avoid use of SQL Server cursors they are in memory looping constructs and cause high I/O and heavy on memory consumption
• Avoid Indefinite looping: Do not use heavy loops instead try use of Joins
• Use TRY-Catch for error handling: 
• Use Transaction control Mechanist (BEGIN TRY CATCH TRY along with ROLLBACK and COMMIT  )
• USE SELECT 1 instead SELECT * In Conditional statemen: 
• Use the sp_executesql instead of the EXECUTE statement they reuse the cached code and also not prone to SQL Injection.