SQL Server Performance Tuning Part 4

Working on Performance Tuning is very tough task specially on large scale data hence the minor changes will give dramatic (+ Ve or -Ve) impact on performance.

Before going to tune the query as an experience developer you should aware on

If your application stops working suddenly, it may not be a database issue. For example, maybe you have a network problem. Investigate a bit before you approach the DBA!

The below are the few generic points for tuning.

Stored Procedure Level Tuning Tips

1. Include SET NOCOUNT ON statement:

2. Use schema name with object name:

3. Do not use the prefix “sp_” in the stored procedure name:

4. Use IF EXISTS (SELECT 1) instead of (SELECT *):

5. Use the sp_executesql stored procedure instead of the EXECUTE statement.

6. Try to avoid using SQL Server cursors whenever possible specially avoid cursors over temporary tables.

7. Keep the Transaction as short as possible:

8. Use TRY-Catch for error handling to get the accurate info.

9.  Use the sp_executesql stored procedure instead of temporary stored procedures.

10. Try to avoid using temporary tables inside your stored procedures.

Using temporary tables inside stored procedures reduce the chance to reuse the execution plan.

11. Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.

Using DDL statements inside stored procedures also reduce the chance to reuse the execution plan.

12. Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.

13. Use SQL Server Profiler to determine which stored procedures have been recompiled too often.