Optimizing Complex T-SQL queries / stored procedures

Generally when asked about optimizing T-SQL queries, people say to do this and that. But, after working extensively on SQL Server, I found that there are no fixed rules set forth that would work in all the senarios. The optimization technique that is used in one query may reduce the performance when applied in another query resulting in longer execution time. But, there is a way in SQL Server to identify and apply optimization technique. Its called the "EXECUTION PLAN" which is an option available in query analyzer.

The following steps will result in identying better optimization rule.

1. Take the query / stored procedure that is taking too long to execute.
2. In the Query Analyzer click on the show estimated execution plan.
3. A graphical representation will be shown how the compiler executes the code section and an estimated cost will be shown. The general rule of thumb is to confine this cost below 20%.
4. If there are table scans, create non-clustered indexes so that the execution will be faster.
5. Make sure that the fields that are used in joins have indexes on them.
6. Avoid sub - queries. Instead use joins (this may not be ideal in all situations the vice versa may work).
7. If a stored procedure needs to be optimized, see for the temp tables. Temp tables use lot of memory and disk storage should be kept in mind. If the temp table is used in the very next piece of code and no where else later, try replacing the temp table with a Common Table Expression (CTE).
8. CTEs scope is very limited to the next statement. But one advantage is CTE stores the data in the RAM and gives the data on the fly.

These are some of the steps that can be followed. But, again the optimization completely depends on the kind of query and environment. Also, there are some additional steps that can be followed while optimizing a stored procedure. The following are the steps that can be looked at.

1. Use SET NOCOUNT to ON so that the number  of records affected will not be returned.
2. Generally system stored procedures that exist in Master database starts with sp_ProcName. Avoid a prefix sp while creating stored procedures in databases other than master because SQL Server initially scans master database for the stored procedures starting with sp. If a procedure is created with sp_proc in some user's database and the same procedure exists in master, the user proc will not execute.