How to tune every query

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.

Read More on Query Tuning

SQL Server – AUTO CREATE STATISTICS - SQL Server DBA

SQL Server – Find high impact queries with sys.dm_exec_query_stats
SQL Server execution plan as text - SQL Server DBA

SQL Server Query Optimizer and Statistics - SQL Server DBA

SQL Performance tuning - Asking the right question