Index-T-SQL-SP----------Optimization Tips Overview

[ragav...@gmail.com]

Index Optimization Tips
***********************

· Try to create indexes on columns that have integer values rather
than character values.

· Limit the number of indexes, if your application updates data very
frequently.

· Create clustered index instead of nonclustered to increase
performance of

· Avoid creating a clustered index based on an incrementing key.

· Create a clustered index for each table.
If you create a table without clustered index, the data rows will not
be stored in any particular order. This
structure is called a heap. Every time data is inserted into this
table, the row will be added to the end of the
table. When many rows will be added a "hot spot" can occur. To avoid
"hot spot" and improve concurrency, you
should create a clustered index for each table.

Transact-SQL Optimization Tips
******************************

· Try to restrict the queries result set by using the WHERE clause.

· Try to restrict the queries result set by returning only the
particular columns from the table, not all table's
columns.

· Use views and stored procedures instead of heavy-duty queries.

· Try to avoid using SQL Server cursors, whenever possible.

· If you need to return the total table's row count, you can use
alternative way instead of SELECT COUNT(*)
statement.

· Try to use constraints instead of triggers, whenever possible.

· Use table variables instead of temporary tables.

· Try to avoid the HAVING clause, whenever possible.

· Try to avoid using the DISTINCT clause, whenever possible.

· Include SET NOCOUNT ON statement into your stored procedures to
stop the message indicating the number of rows
affected by a T-SQL statement.

· Try to use UNION ALL statement instead of UNION, whenever possible.

The UNION ALL statement is much faster than UNION, because UNION ALL
statement does not look for duplicate rows,
and UNION statement does look for duplicate rows, whether or not they
exist.

Stored Procedures Optimization Tips
***********************************

· Use stored procedures instead of heavy-duty queries.

· Include the SET NOCOUNT ON statement into your stored procedures to
stop the message indicating the number of rows
affected by a Transact-SQL statement.

· Call stored procedure using its fully qualified name.

· 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.

· If you have a very large stored procedure, try to break down this
stored procedure into several sub-procedures,
and call them from a controlling stored procedure.

· Try to avoid using temporary tables inside your stored procedure.
Using temporary tables inside stored procedure reduces the chance to
reuse the execution plan.

· Try to avoid using DDL (Data Definition Language) statements inside
your stored procedure.
Using DDL statements inside stored procedure reduces the chance to
reuse the execution plan.

· 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 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.