datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
SQL Server Scripts SQL 2005 SQL 2008 Articles


Query and Stored Procedure Optimization

By Kalman Toth, M.Phil. Physics, M.Phil. Computing Science, MCDBA, MCITP

January 2, 2012

When I see a forum question about the mysterious locks placed by the SQL Server Database Engine, I know the assistance seeker way off the beaten path. If the server and the database properly configured and maintained, the DBA or developer doesn't have to worry about locking or at least very rarely.

In a stored procedure each query has to be optimized. There is one performance aspect 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 considered for indexing (PRIMARY KEY is indexed automatically).

2. Example for other indexing candidates: GROUP BY column in frequent/business critical query.

3. 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. Similar consideration for very frequent variable length column UPDATE.

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

5. REBUILD indexes every weekend. Update statistics for all tables in all databases every night (AUTO_UPDATE_STATISTICS should be disabled). Example: INDEX REBUILD SAT night, UPDATE STATISTICS MON-THU nights. Query to check last statistics update dates:

SELECT                                SCHEMA_NAME(schema_id)                    AS SchemaName

                              ,              OBJECT_NAME(o.object_id)                      AS ObjectName

                              ,              type                                                                   AS ObjectType

                              ,                                                                  AS StatsName

                              ,              STATS_DATE(o.object_id, stats_id)           AS StatsDate

FROM sys.stats s INNER JOIN sys.objects o ON o.object_id=s.object_id

WHERE OBJECTPROPERTY(o.object_id, N'ISMSShipped') = 0

AND LEFT(s.Name, 4) != '_WA_'

ORDER BY ObjectType, SchemaName, ObjectName, StatsName;


6. Program WHERE clauses with SARGABLE predicates; don't use dateadd(dd,1,OrderDate) for example because that formulation will prevent the database engine from performing an INDEX SEEK on the (indexed) OrderDate column.

7 . For sizable temporary storage use temporary tables (#tblA) instead of table variables (@tblA).

8. Examine the query/sproc to streamline it; eliminate potential overhead. Instead of one very complex query with CTE-s/views, design multi-statement script solution with temporary tables (index if needed).

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

10. New table should be thin & fixed - narrow and without variable size columns.

With the exception of 8 and 9, all the optimization steps can be carried out without being a super-expert in T-SQL. That would roughly take care of 99% of performance problems. Before you go down looking at "esoteric" (for experts only) server / database performance related data, just make sure you did the basics. One bad query can bring down a mighty SS to its knees! Generating tons of performance data along the way. An expert of course can quickly sort out things and find the offending query. However, for everybody including the expert, it is simpler and safer to start with optimization basics.


Performance tuning related articles:

General Index Design Guidelines

Techniques for Indexing Low-Selectivity Columns in SQL Server

Index Usage Statistics

Reorganizing and Rebuilding Indexes

Execute UPDATE STATISTICS for all SQL Server Databases

When should I include the WITH RECOMPILE option when creating a stored procedure?

Slow in the Application, Fast in SSMS? Understanding Performance Mysteries

How To: Optimize SQL Queries

Server Memory Options

Chapter 14 — Improving SQL Server Performance

Catch-all queries

Don't use Column=@Param OR @Param IS NULL in your WHERE clause

The SQL Server Query Optimizer

Making the Most Out of the SQL Server 2005 Performance Dashboard

SQL Server Performance Survival Guide

Best Practices Every SQL Server DBA Must Know


Exam Prep 70-461
Exam 70-461