 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 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 (AUTO_UPDATE_STATISTICS should be disabled). Example: INDEX REBUILD SAT night, UPDATE STATISTICS MON-THU nights.
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
When should I include the WITH RECOMPILE option when creating a stored procedure?
Slow in the Application, Fast in SSMS? Understanding Performance Mysteries
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
|
