1) Add SET NONCOUNT OFF - to prevent your stored procedure from returning number of rows affected, thus reducing network traffic
2) Use fully qualified object names and table names - preceeding the object names with schema name reduces the time it takes to search all schemas for the object, thus reducing the time it takes for a stored proceedure to runMake sure object names and table names
3) Avoid SQL Server searching master database for your stored proceedure by ensuring that none of the proceedure names are preceeded with "sp_", use "usp_" instead, to specify that it's a user stored procedure, not a system one.
4) If proceedure returns an integer value, use RETURN statement to return a single integer value as opposed to returning the value as a part of a recordset.
The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using the RETURN statement can boost performance because SQL Server will not create a recordset.
5) Replace all
EXECUTE statements with
sp_executesql. sp_executesql makes your code more reusable because it takes parameters and eliminates the possibility for sql injection.
The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute a sql batch with a parameter, and then execute the same batch with a different value for the parameter, the execution plan created the first time around will be reused for the different value of the parameter. The reuse of the existing complied plan will result in improved performance.
6) When checking for an existance of a particular record, Use IF EXISTS (SELECT 1) instead of (SELECT *) since it minimizes data processing
7) Add indexes to the fields most often used in WHERE clauses and for JOINS. Indexing the right fields can help to significantly improve the performance.
8) Utilize TRY-CATCh blocks, it is much more efficient than the old way of error checking after each sql statement.
BEGIN TRY
--Your t-sql code goes here
END TRY
BEGIN CATCH
--Your error handling code goes here
END CATCH
9) Utilize temp tables. For instance if the same query runs multiple times, run it only once, dumping the results into a temp table.
10) use
EXISTS instead of JOINS. If the table you are joining with is not contributing any columns to your query (the select list), then you are using unnecessary IO resources on the background.
11) Avoid using
DISTINCT. Usage of
DISTINCT may mean you have a bad table design somewhere
12) Limit the SELECT list by returning only the columns you need, since returning too many columns can have a drastic effect on your query. Not only will it increase you chances for bookmark lookups (or key lookups), but the network and disk latency add to the query. Not to mention you will be squeezing more data into your buffer cache.
13)Use the least amount of tables to Compile Your SELECT list.
An example would be let’s say you need to join on 2 tables in order to get your result set. If one of the tables contains all the fields needed for the SELECT list, but you are also able to get the same field(s) from the other table, always go with only returning the values from the one table. Doing so will limit the number of IO operations necessary to give you your result.
14)Index temp tables
Temp tables are treated just like permanent tables according to SQL. They can have indexes & statistics. The only downfall is that they often cause recompiles for the statement when the result sets differ. To counter this read reducing temp table recompiles or use table variables if you have to.
15) Break down large stored procedures into several sub-procedures and call them from controlling stored procedure.
The stored procedure will be recompiled when any structural changes are made to a table or view referenced by the stored procedure (an ALTER TABLE statement, for example), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, there's a chance that only a single sub-procedure will be recompiled, while other sub-procedures will not.
16) Try to avoid using temporary tables inside your stored procedures.
Using temporary tables inside stored procedures reduce the chance to reuse the execution plan.
17) 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.
18) 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.
The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is always recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure, because in this case the wrong execution plan will not be used.
19) Use SQL Server Profiler to determine which stored procedures have been recompiled too often.
To c
heck if a stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.
