Optimizing SQL Queries

** Use stored procedures instead of heavy-duty queries.
This can reduce network traffic as your client will send to the server only the stored procedure name
(perhaps with some parameters) instead of all the text from a large heavy-duty query. Stored procedures can be used to enhance security.

** Avoid recompile the stored procedures.
Stored procedures are recompiled using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement.
Running stored procedures with sp_recompile

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.

Recompilations cannot be helped. So don’t create stored procedures with recompile.
Specify KEEPFIXED PLAN (to avoid recompiles) Forces the query optimizer not to recompile until schema is changed.

** Include the SET NOCOUNT ON statement in your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.
This can reduce network traffic due to the fact that your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

** Call stored procedures using their fully qualified name.
The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name.
Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

** Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
This special prefix is reserved for system stored procedures. Although using this prefix will not prevent a user defined stored procedure from working, what it can do is to slow down its execution ever so slightly.

The reason for this is that by default, any stored procedure executed by SQL Server that begins with the prefix "sp_", is first attempted to be resolved in the Master database. Since it is not there, time is wasted looking for the stored procedure.

If SQL Server cannot find the stored procedure in the Master database, then it next tries to resolve the stored procedure name as if the owner of the object is "dbo". Assuming the stored procedure is in the current database, it will then execute. To avoid this unnecessary delay, don't name any of your stored procedures with the prefix "sp_".

** Before you are done with your stored procedure code, review it for any unused code
That you may have forgotten to remove while you were making changes, and remove it. Unused code just adds unnecessary bloat to your stored procedures.

** If you need to delete all the rows in a table, doesn’t use DELETE to delete them all, as the DELETE statement is a logged operation and can take time. To perform the same task much faster, use the TRUNCATE TABLE instead, which is not a logged operation.

** Indexes should be considered on all columns that are frequently accessed by the WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.Without an index, each of these operations will require a table scan of your table, potentially hurting performance.

** Try to avoid using temporary tables inside your stored procedures.
Using temporary tables inside stored procedures reduce the chance to reuse the execution plan.

** Use the sp_executesql stored procedure instead of temporary stored procedures.Temporary stored procedures do not support the reuse of execution plans. Use the sp_executesql system stored procedure instead of temporary stored procedures in order to have a better chance of reusing the execution plans.

** Use SQL Server Profiler
To determine which stored procedures have been recompiled too often.
Deadlocks etc..