Regularly Update Statistics for Ascending Keys

Ascending key columns, such as IDENTITY columns or datetime columns representing real-world timestamps, can cause inaccurate statistics in tables with frequent INSERTS because new values all lie outside the histogram.  Consider updating statistics on such columns frequently with a batch job if your application seems to be getting inadequate query plans for queries that have…

3

Create Statistics for All Union Inputs

When a query requires statistics on the result of a UNION or UNION ALL operation, create needed statistics uniformly on all UNION or UNION ALL inputs.  It is not sufficient to create statistics on a subset of the UNION or UNION ALL inputs, even when those inputs are dominant.  For example, SELECT * FROM Lineitem…

0

Create Multi-Column Statistics

When a query has a multi-column condition, consider creating multi-column statistics if you suspect that the optimizer is not producing the best plan for the query. You get multi-column statistics as a by-product of creating a multi-column index, so if there is already a multi-column index that supports the multi-column condition, there is no need…

1

Auto-create and Auto-update Statistics

For a large majority of SQL Server installations, the most important best practice is to use auto create and auto update statistics database-wide. Auto create and auto update statistics are on by default. If you observe bad plans and suspect that missing or out of date statistics are at fault, verify that auto create and…

4

Simplify statements with IF

In some cases, a SQL statement can be simplified by using procedural logic.  Instead of issuing one complex query to cover multiple cases with UNION or OR, it is better to use and if..else logic to separate the cases into different SQL statements.  In this way, the optimizer optimizes multiple simpler statements instead of a…

2

Limit Use of Multi-Statement TVFs and Table Variables

Limit use of multi-statement table valued functions (TVFs) and table variables in situations where getting a high-performance plan is requied.  Both multi-statement TVFs and table variables have no statistics. The optimizer must guess the size of their results. Similarly, result column do not have statistics, and the optimizer must resort to guesses for predicates involving…

0

Use Condition-Specific Stored Procedures

The optimizer chooses the best plan for an SP given the current parameter values.  This plan is then reused regardless of whether the nature of the parameters changes from call to call.  If an application is aware that input parameters have a small number of significant behaviors, then the application may use condition-specific SPs.  For…

7

Use Function Results

Built-in functions with literal constant inputs are simplified during optimization to resultant constant values.  However, user defined functions or built-in functions with variable[1] inputs are only simplified to resultant constants for the purposes of plan optimization for the following functions:   Lower Upper RTrim Datediff Dateadd Datepart Substring Charindex Length SUser_SName IsNull GetDate GetUTCDate  …

0

Limit Non-order Preserving Expressions

Expressions with column transformations that do not retain the original column order do not benefit from column statistics.  This can lead to poor plans.  Assume for purposes of illustration that we’ve added a column to Sales.SalesOrderHeader in AdventureWorks as follows: ALTER TABLE Sales.SalesOrderHeader ADD SalesOrderFlag tinyint For example, consider the following:       SELECT * FROM…

1

Avoid Unnecessary Data Type Conversions

SQL Server adds implicit data type conversions when types don’t match.  This can have unintended results both on query results, but also on the query plan.  It is important to use literal constants that match in type column they are being compared with wherever possible.  A common mistake in writing T-SQL is to always use…

2