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

  • Ian Jose's WebLog

    Auto-create and Auto-update Statistics

    • 4 Comments
    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...
  • Ian Jose's WebLog

    Ascending Keys and Auto Quick Corrected Statistics

    • 4 Comments
    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...
  • Ian Jose's WebLog

    Make Functions Schema-bound

    • 0 Comments
    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...
  • Ian Jose's WebLog

    Use Parameters or Literals for Query Inputs

    • 4 Comments
    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...
  • Ian Jose's WebLog

    Query Processor Modelling Extensions in SQL Server 2005 SP1

    • 1 Comments
    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...
  • Ian Jose's WebLog

    Use Condition-Specific Stored Procedures

    • 7 Comments
    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...
  • Ian Jose's WebLog

    Create Multi-Column Statistics

    • 1 Comments
    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...
  • Ian Jose's WebLog

    Regularly Update Statistics for Ascending Keys

    • 3 Comments
    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...
  • Ian Jose's WebLog

    Disabling Constant-Constant Comparison Estimation

    • 0 Comments
    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...
  • Ian Jose's WebLog

    Avoid Unnecessary Data Type Conversions

    • 2 Comments
    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...
  • Ian Jose's WebLog

    Limit Use of Multi-Statement TVFs and Table Variables

    • 0 Comments
    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...
  • Ian Jose's WebLog

    Use Function Results

    • 0 Comments
    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...
  • Ian Jose's WebLog

    Simplify statements with IF

    • 2 Comments
    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...
  • Ian Jose's WebLog

    Limit Non-order Preserving Expressions

    • 1 Comments
    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...
  • Ian Jose's WebLog

    Create Statistics for All Union Inputs

    • 0 Comments
    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...
Page 1 of 1 (15 items)