This weblog is intended to help T-SQL developers get the best performing query plans from SQL Server. Some of the posts describe simple mistakes that can be easily avoided. Other posts describe complex solutions to limitations in SQL Server. Lastly, some posts describe recent improvements in SQL Server, and the means to employ them best.
November 2005 - Posts
-
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 Read More...
|
-
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 Read More...
|
-
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 Read More...
|
-
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 Read More...
|
-
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 Read More...
|
-
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 Read More...
|
-
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 Read More...
|
-
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 Read More...
|
-
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 Read More...
|
-
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 Read More...
|
-
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 Read More...
|
