Ian Jose's WebLog
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, so
Ascending Keys and Auto Quick Corrected Statistics
A common problem for some SQL Server applications are cases where data typically ascends. For...
Author: ianjo Date: 04/24/2006
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...
Author: ianjo Date: 04/24/2006
Disabling Constant-Constant Comparison Estimation
SQL Server 8.0 did not perform cardinality estimates based on the comparion of two constants....
Author: ianjo Date: 03/28/2006
Make Functions Schema-bound
Create user defined functions with the SCHEMABINDING clause where possible. In the absence of this...
Author: ianjo Date: 01/31/2006
Regularly Update Statistics for Ascending Keys
Ascending key columns, such as IDENTITY columns or datetime columns representing real-world...
Author: ianjo Date: 11/10/2005
Create Statistics for All Union Inputs
When a query requires statistics on the result of a UNION or UNION ALL operation, create needed...
Author: ianjo Date: 11/10/2005
Create Multi-Column Statistics
When a query has a multi-column condition, consider creating multi-column statistics if you suspect...
Author: ianjo Date: 11/10/2005
Auto-create and Auto-update Statistics
For a large majority of SQL Server installations, the most important best practice is to use auto...
Author: ianjo Date: 11/10/2005
Simplify statements with IF
In some cases, a SQL statement can be simplified by using procedural logic. Instead of issuing one...
Author: ianjo Date: 11/10/2005
Limit Use of Multi-Statement TVFs and Table Variables
Limit use of multi-statement table valued functions (TVFs) and table variables in situations where...
Author: ianjo Date: 11/10/2005
Use Condition-Specific Stored Procedures
The optimizer chooses the best plan for an SP given the current parameter values. This plan is then...
Author: ianjo Date: 11/10/2005
Use Function Results
Built-in functions with literal constant inputs are simplified during optimization to resultant...
Author: ianjo Date: 11/10/2005
Limit Non-order Preserving Expressions
Expressions with column transformations that do not retain the original column order do not benefit...
Author: ianjo Date: 11/10/2005
Avoid Unnecessary Data Type Conversions
SQL Server adds implicit data type conversions when types don’t match. This can have unintended...
Author: ianjo Date: 11/10/2005
Use Parameters or Literals for Query Inputs
Use unmodified parameters or literal constants in query statements to ensure that the optimizer can...
Author: ianjo Date: 11/10/2005