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 » Undocumented   (RSS)
Showing page 2 of 3 (23 total posts)
  • Query Optimizer Deep Dive - Part 1

    This is the first in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Australia. Links to other parts of this series: Part 2 Part 3 Part 4 Introduction The motivation ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on April 27, 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
  • Can a SELECT query cause page splits?

    Books Online has this to say about page splits: When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row.  This reorganization is known as a page split.  A page split makes room for new records, but can take time to perform and is a resource ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on August 29, 2011
  • SQL Server, Seeks, and Binary Search

    The following table summarizes the results from my last two blog entries, showing the CPU time used when performing 5 million clustered index seeks: In test 1, making the clustered index unique improved performance by around 40%. In test 2, making the same change reduced performance by around 70% (on 64-bit systems – more on that later).  ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on August 8, 2011
  • Join Performance, Implicit Conversions, and Residuals

    You probably already know that it’s important to be aware of data types when writing queries, and that implicit conversions between types can lead to poor query performance.  Some people have gone so far as to write scripts to search the plan cache for CONVERT_IMPLICIT elements, and others routinely inspect plans for that type of thing when ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on July 18, 2011
  • 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
  • Sorting, Row Goals, and the TOP 100 Problem

    When you write a query to return the first few rows from a potential result set, you’ll often use the TOP clause.  To give a precise meaning to the TOP operation, it will normally be accompanied by an ORDER BY clause.  Together, the TOP…ORDER BY construction can be used to precisely identify which top ‘n’ rows should be returned. The ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on August 26, 2010
  • Inside the Optimizer: Row Goals In Depth

    Background One of the core assumptions made by the SQL Server query optimiser’s model is that clients will consume all of the rows produced by a query.  This results in plans that favour the overall execution cost, though it may take longer to begin producing rows.  Let’s look at an example: The optimiser chooses to perform the ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on August 18, 2010
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement