We all know that the Stored Procedures provide a faster mean of accessing data as stored procedures are in complied state already. Even with the basic advantage that Stored Procedures provide, there are several factors that enhance the performance of stored procedures.
Below provided are some very important and very basic steps that any SQL Developer will need to know and adhere to.
• Include SET NOCOUNT ON statement into your stored procedures
Add SET NOCOUNT ON as a first statement in SP to reduce network traffic, as message for affecting rows by Transact-SQL statement will not be delivered to client. This will reduce extra overhead.
• Call SP using its qualified name
In SQL server resource name consists of four identifiers.
o Server Name
o Database Name
o Owner Name
o Object Name
Name with above combination is called full qualified name.
By using full qualified name we can reuse SP execution plans if they were executed using fully qualified names and this will increase performance of SP.
• SP name not to begin with “SP_”
When we are creating new sp then do not start SP name with “SP_” because SP_ prefix is used by system objects. If we have written prefix as SP_ then it will look for this sp in master database and then the current database. By doing this, it will increase some extra overhead for SQL server. If SP with the same name is available in master database then it will execute this sp from the master database instead of current database.
• Avoid temporary tables in SP
When temporary table is used in SP, it might reduce chance to reuse the execution plan.
• Avoid DDL statements inside SP
Avoiding DDL statement in SP will increase chance to reuse execution plan.
• Use stored procedures instead of heavy queries.
Use stored procedure instead of long written query, it will reduce network traffic because client will only pass SP name with or without parameters instead of long query.
• Break large SP into several small sp
SP gets recompile if any structural changes made in tables/views referenced by SP. Or any large number insert, update or delete operations are performed in table. So if we break down large sps into several small sps then it will reduce compilation time.
• Add WITH RECOMPILE Option
Add WITH RECOMPILE option to preventing SP output to be cached in SQL server and query gets executed every time without using previous execution plan. This option can boost query performance if sp contains multiple queries and based on condition query gets executed.
• Use SQL Profiler
Use SQL Profiler to identify which query gets recompile very often. User can take some proactive steps for recompilation.
• Use Try Catch block
Use Try Catch block for better error handling in SP
• Avoid Cursor
Cursor user more overhead to maintain current state, current position, record set. This will decrease performance. If possible try to avoid cursor or instead of cursor use CTE (Common Table Expression) for better performance.
If cursor is unavoidable then use forward only cursor for better result.
• Transaction Scope
Transaction scope can blocking and locking database for long time this might take database to deadlock state.
So plan transaction scope accordingly.
• Use sp_executesql keyword instead of the EXECUTE keyword
If we have used EXECUTE keyword for dynamic query, then SQL server will not re use execution plan and it will be evaluated again and again.
If user want to reuse execution plan then needs to use sp_executesql.
It will use the same execution plan again and again for dynamic query as it is doing for SP.