Query and Stored Procedure Optimization

In a stored procedure each query has to be optimized. There is one performance feature with stored procedures which goes beyond queries: slow down due to parameter sniffing.

The principles of query optimization are quite simple:

1. Each FOREIGN KEY and WHERE clause predicate column should be indexed (PRIMARY KEY Columns are indexed automatically).

2. Don’t User functions with Column in Where Clause. If you use any function than Database Engine could not perform Index Seek on that Column.

3. Example for other indexing candidates: Group By Column in frequent / business critical query.

4. Use FILLFACTOR for dynamic tables; example FILLFACTOR 80 if table will grow 10% during the week (requires experimentation); FILLFACTOR 80 leaves 20% empty space for growth.

5. Assign clustered index (PK default is clustered index, but not a requirement.) to a column which is used in business critical range searches.

6.Index should be REBUILD on every weekend.

7. Examine the query/sproc to streamline it; eliminate potential overhead.

8. Examine the execution plan for ways to improve the query.