Presenter: Andy Novick
Topic: Query Optimization – Specific Techniques to Make Queries Faster
Executing individual queries is the heart of what SQL Server does for us and many a hour is spent trying to optimize the performance of a single query. This presentation show that by examining query plans, specific techniques can be applied that transform slow queries into speedy ones. Using a series of examples Andy will demonstrate these techniques and show how the query plan and other tools point the way to a better solution.
Some of the techniques are:
- Optimize for specific conditions
- Managing parameter sniffing, auto parameterization and when to use the hints RECOMPILE, OPTIMIZE FOR or switch to dynamic SQL.
- Using a table of numbers
- Creating parallelism with CROSS APPLY
- Rewrite Functions for order of magnitude speedup
- Introduce a #temp table to get statistics and a better plan
- Adding redundant predicates
- Suppressing unnecessary statistics creation
Biography:
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience. His consulting practice focuses on building applications, including software products that use SQL Server’s capabilities to the maximum. He has developed both OLTP and data warehouse databases with multi-terabytes scale. He has particular expertise in automating data management for such large databases. Other recent projects have included ETL, Security and SQL Injection prevention. Andy is the originator of the popular “SQL Server Load-fest” event conduced in Waltham, MA.
FREE! Query Optimizer eBook and SQL Prompt trial
This free ebook from Red Gate Software will take you from the fundamentals of Statistics, Cost Estimation, Index Selection, and the Execution Engine, and guide you through the inner workings of the Query Optimization process, and throws in a pragmatic look at Parameterization and Hints along the way. Download it now
