THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Browse by Tags

All Tags » Query Optimizer » Statistics   (RSS)
  • How to Find the Statistics Used to Compile an Execution Plan

    In this post, I show you how to determine exactly which statistics objects were used by the query optimizer to produce an execution plan. Trace Flags We will need three undocumented trace flags.  The first one (3604) is well-known – it redirects trace output to the client so it appears in the SSMS messages tab. The second trace flag is ...
    Posted to Page Free Space (Weblog) by Paul White on September 20, 2011
  • Fooling the Query Optimizer

    Did you ever wanted to know which execution plans the Query Optimizer would generate for your queries should your tables have millions of records? You can actually generate those plans by using the undocumented ROWCOUNT and PAGECOUNT options of the UPDATE STATISTICS statement. These options can be used on small or empty tables and can be ...
    Posted to Benjamin Nevarez (Weblog) by Ben Nevarez on January 13, 2010
  • How the Query Optimizer Uses Statistics – Part II

    I got a question from a reader of my post How the Query Optimizer Uses Statistics ( and I thought that it would be a good idea to post my answer here. Basically the request was to expand the previous example for a predicate with two columns. ...
    Posted to Benjamin Nevarez (Weblog) by Ben Nevarez on September 12, 2009
  • Statistics Used by the Query Optimizer in SQL Server White Paper

    In case you are not aware, the excellent white paper “Statistics Used by the Query Optimizer in SQL Server” has been updated for SQL Server 2008.   You can find it here   Statistics Used by the Query Optimizer in SQL Server 2008 Writer: Eric N. Hanson and Yavor Angelov Contributor: Lubor ...
    Posted to Benjamin Nevarez (Weblog) by Ben Nevarez on September 4, 2009
  • How the Query Optimizer Uses Statistics

      This post shows how the Query Optimizer uses statistics to estimate the selectivity of expressions during query optimization.   You can also use this as a second part of my last post, The Query Optimizer and Parameter Sniffing. Here I will show you the advantage of the use of statistics when the Query Optimizer can “sniff” the ...
    Posted to Benjamin Nevarez (Weblog) by Ben Nevarez on September 3, 2009
  • The Query Optimizer and Parameter Sniffing

      As we all know, the SQL Server Query Optimizer uses statistics to estimate the cardinality and selectivity of predicates of a query to produce an optimal execution plan. The Query Optimizer accomplishes this by first inspecting the values of the query parameters. This behavior is called parameter sniffing and it is a good thing. Getting an ...
    Posted to Benjamin Nevarez (Weblog) by Ben Nevarez on August 27, 2009
  • The Query Optimizer and Computed Columns

    In my previous post I talked about contradiction detection as one of the early steps in query optimization. Another interesting step performed during query optimization is the automatic matching of computed columns. Although computed columns have been available in previous versions of SQL Server, the automatic matching feature was introduced until ...
    Posted to Benjamin Nevarez (Weblog) by Ben Nevarez on August 10, 2009
Privacy Statement