1. Use SET NOCOUNT ON
SQL Server return information messages when running statements thus increasing network traffic. These messages can be suppressed by setting the NOCOUNT ON to decrease network traffic.
2. Use fully qualified procedure name
A fully qualified object name is server.database.schema.objectname. SQL Server can swiftly find the complied plan instead of looking for procedures in other schemas. Also SQL Server has a better chance to resue the stored procedures execution plans.
3. Use sp_executesql instead of Execute for dynamic queries
sp_executesql allows for cache plan result and protects from SQL Injection.
4. IF EXISTS
Used to check the existence of a record. When possible, use SELECT 1 instead of SELECT *. We just need for the existence of a record.
5. Avoid naming user stored procedure as sp_procedurename
If a stored procedure begins with sp_ then SQL Server first searches it in the master database and then in the current user database.
6. Avoid using cursors whenever possible
Avoid using loops and cursors. Cursor uses a lot of resources for overhead processing to maintain current record position.
7. Keep transaction short and crisp
The longer the transaction the longer the locks will be held based on isolation level. This may result in deadlocks.
8. Use stored procedures instead of direct queries
Only the stored procedure name will be send instead of the queries text. Also, stored procedure are compiled and runs faster then direct queries.
9. Break down very large stored procedure into several sub-procedures and call them from a controlling stored procedure
Stored procedures will be recompiled when any structure changes where made to a table referenced by the stored procedures. If you break down a very large stored procedure into several sub-procedures, chances are the only a few single sub-procedure will be recompiled instead of all the sub-procedures.
10. Avoid using temporary table inside your stored procedure
Using temporary tables instead stored procedure reduces the change to reuse the execution plan.
11. Use SQL Sever Profiler to determine which stored procedures has been recompiled too often
To check the stored procedure has been recompiled, run SQL Server Profiler and choose category called SP:Recompile or trace the event SP:StmtStarting.
12. Check your stored procedure execution plan for any issue.
Use the execution plan to determine any issues you might have in your stored procedure.
13. Avoid using ORDER BY when you don’t have to.
Order By clause use a lot of resources in the query. Let the application layer handle this instead.
Sources:
https://www.sqlservergeeks.com/improve-stored-procedure-performance-in-sql-server/
Comments