Ian Jose's WebLog

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...

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...

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...

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...

Use unmodified parameters or literal constants in query statements to ensure that the optimizer can determine a representative value and optimize accordingly. If you use a local variable in a query predicate instead of a parameter or literal, the optimizer...

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...

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...

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...

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...

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...

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...

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...

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...

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...

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...