Ascending Keys and Auto Quick Corrected Statistics

A common problem for some SQL Server applications are cases where data typically ascends.  For example, datetime columns where the column represents a current date.  SQL Server builds statistics with the assumption that the data will by in large be similar in the future.  However, when data typically ascends, most new insertions are out of the…

4

Query Processor Modelling Extensions in SQL Server 2005 SP1

Trace flag 2301, available in SQL Server 2005 SP1, enhances the modelling ability of the query optimizer to better handle complex statements.  Improved modelling can lead to dramatically faster performing query plans in some cases.  These extensions to the query processor modelling abilities can lead to increased compile time and so should only be used by applications…

1

Disabling Constant-Constant Comparison Estimation

SQL Server 8.0 did not perform cardinality estimates based on the comparion of two constants.  Instead, SQL Server 8.0 guessed at the resulting selectivity.  The reasoning for this is that one or more of the constants may be statement parameters, which would change from one execution of the statement to the next.  However, SQL Server…

0

Make Functions Schema-bound

Create user defined functions with the SCHEMABINDING clause where possible.  In the absence of this clause, SQL Server must assume at compile time that statements using the function may bind to a function which accesses and updates data.  Note that even if the UDFs do not access data or perform any updates, the SQL Server…

0

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