THE SQL Server Blog Spot on the Web

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

Browse by Tags

All Tags » Query Optimizer   (RSS)
Showing page 2 of 4 (35 total posts)
  • Query Optimizer Gone Wild - Full-Text

    Overall, SQL Server has become a very capable and mature product, with a very powerful engine and sophisticated query optimizer. Still, every now and then, a certain query structure throws the optimizer for a loop resulting in an execution plan that will take forever. The key to identifying this type of problem begins with the exeuction ...
    Posted to Joe Chang (Weblog) by jchang on February 19, 2012
  • Forcing a Parallel Query Execution Plan

    This post is for SQL Server developers who have experienced the special kind of frustration, which only comes from spending hours trying to convince the query optimizer to generate a parallel execution plan.  This situation often occurs when making an apparently innocuous change to the text of a moderately complex query; a change which ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on December 22, 2011
  • 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 Paul White: Page Free Space (Weblog) by Paul White on September 20, 2011
  • Oracle Index Skip Scan

    There is a feature, called index skip scan that has been in Oracle since version 9i. When I across this, it seemed like a very clever trick, but not a critical capability. More recently, I have been advocating DW on SSD in approrpiate situations, and I am thinking this is now a valuable feature in keeping the number of nonclustered indexes ...
    Posted to Joe Chang (Weblog) by jchang on June 12, 2011
  • New Book on the Query Optimizer

      A friend of mine Benjamin Nevarez has written a book on the inner workings of the Query Optimizer and it was just released. I read a preview and was definitely impressed so I expect the full version to be just as good. More details can be found here and it is available at Amazon here as well. I hope you enjoy it. Andy
    Posted to Andrew Kelly (Weblog) by Andrew Kelly on April 9, 2011
  • Advanced TSQL Tuning: Why Internals Knowledge Matters

    There is much more to query tuning than reducing logical reads and adding covering nonclustered indexes.  Query tuning is not complete as soon as the query returns results quickly in the development or test environments.  In production, your query will compete for memory, CPU, locks, I/O and other resources on the server.  Today’s ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on February 22, 2011
  • Heaps of Trouble?

    If you’re not already a regular reader of Brad Schulz’s blog, you’re missing out on some great material.  In his latest entry, he is tasked with optimizing a query run against tables that have no indexes at all.  The problem is, predictably, that performance is not very good.  The catch is that we are not allowed to create any ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on December 9, 2010
  • A Tale of Two Index Hints

    If you look up Table Hints in Books Online, you’ll find the following statement: If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error. The interesting thing there is ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on September 22, 2010
  • Inside the Optimizer: Plan Costing

    Summary: A detailed look at costing, and more undocumented optimizer fun. The SQL Server query optimizer generates a number of physical plan alternatives from a logical requirement expressed in T-SQL.  If full cost-based optimization is required, a cost is assigned to each iterator in each alternative plan, and the plan with the lowest ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on September 1, 2010
  • Inside the Optimizer: Constructing a Plan – Part 4

    Summary: More undocumented ways to explore how the query optimiser works. This post is part of a series: Part1 Part 2 Part3 Part4 Documented Rule-Affecting Options It might surprise you to learn that most T-SQL users have written queries that disable one or more of the optimiser rules already. Join hints { LOOP | HASH | MERGE | ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on July 31, 2010
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement