Tune queries by using the SQL Optimizer
I’ve tuned thousands of sql queries. There are common patterns and methods, but there isn’t a guaranteed one-size fits all approach. SQL Server uses the SQL Optimizer.The Optimizer is tasked with analysing potential execution plans. The Optimizer selects the lowest cost execution plan based on the execution plans available.
The Optimizer can only work with the information available. If the Optimizer does not have all relevant information , it will select an execution plan with worse performance.
1) Example 1: The SQL Server query optimizer uses statistics to estimate the number of rows (aka cardinality) for a sql query . Cardinality influences the SQL Server query plan. If the statistics are not up to date , the Optimizer cannot choose the optimal plan.SQL Server Query Optimizer and Statistics
2)Example 2: Using functions in WHERE clauses can cause query performance problems. The query optimizer attempts to create a cardinality estimate. If the Optimizer does not know the value to look up – it will base the cardinality estimate on the average per distinct value. Functions and WHERE clause and suboptimal query plans
3)Example 3: Queries not using indexes effectively. Index order is important. Consider a telephone book – what is more useful , searching by first name or last name? The answer is last name. Missing Indexes. Equality and Inequality on sys.dm_db_missing_index_details
These examples cover a large majority of query tuning issues.
When I’m asked to tune a query , I’m looking for some information
Supply the Query Data Model
I request all relevant information is supplied about data, rows,indices, DDL , constraints (within and outside SQL Server) and any other usage patterns.
Is the query answering the question?
Quite often a query is supplied which doesn’t even answer the question or uses a very verbose method, when a simpler more set-based approach would be more effective.
Armed with this information, I progress through analysis . Once I’m satisfied the correct query is formed – either the original or new query, I assess whether the SQL Optimizer is supplied the full information to make the best choice.