SQL Server Query Optimization - INDEXING

SQLUSA.com

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

, s.name 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

http://www.sqlusa.com/bestpractices2008/rebuild-all-indexes/

http://www.sqlusa.com/bestpractices2008/performancetuning/missingindexes/

http://www.sqlusa.com/bestpractices/coveringindex/

http://www.sqlusa.com/bestpractices/fastclusteredindex/

http://www.sqlusa.com/bestpractices/configureharddisk/

http://sqlserverpedia.com/wiki/Best_Practices_for_Creating_Indexes

Techniques for Indexing Low-Selectivity Columns in SQL Server

Index Usage Statistics

Reorganizing and Rebuilding Indexes

Execute UPDATE STATISTICS for all SQL Server Databases