The principles of query optimization are quite simple:
1. Each FOREIGN KEY and WHERE clause predicate column should be indexed (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.
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.
9. Examine the execution plan for ways to improve the query.
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.
1. Each FOREIGN KEY and WHERE clause predicate column should be indexed (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.
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.
9. Examine the execution plan for ways to improve the query.
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.
No comments:
Post a Comment