Tips, Tricks, and Advice from the SQL Server Query Processing Team

The query processing team - query optimization & execution - providing tips, tricks, advice and answers to freqeuently-asked questions in a continued effort to make your queries run faster and smoother in SQL Server.

Mystery of memory fraction in Showplan XML

If you examine Showplan XML, you may find an entry like <MemoryFractions Input="1" Output="1"...

Author: SQL Server Query Processor Team Date: 03/11/2010

Understanding SQL server memory grant

This article describes how query memory grant works in Microsoft SQL Server. It applies to both...

Author: SQL Server Query Processor Team Date: 02/16/2010

Understanding SQL Server Fast_Forward Server Cursors

SQL Server's server cursor model is a critical tool to many application writers. Fast_forward...

Author: SQL Server Query Processor Team Date: 08/12/2009

Distinct Aggregation Considered Harmful

Distinct aggregation (e.g. select count(distinct key) …) is a SQL language feature that results in...

Author: SQL Server Query Processor Team Date: 09/22/2008

Store Statistics XML in database tables using SQL Traces for further analysis.

Since SQL Server 2005, query plan as well as statistics of query execution can be captured in XML...

Author: SQL Server Query Processor Team Date: 06/01/2007

Index Build strategy in SQL Server - Part 4-2: Offline Serial/Parallel Partitioning (Non-aligned partitioned index build)

Source PartitionedWhile the table is partitioned, we may want to change the way it is partitioned...

Author: SQL Server Query Processor Team Date: 05/14/2007

Index Build strategy in SQL Server - Part 4-1: Offline Serial/Parallel Partitioning (Non-aligned partitioned index build)

Recall that in the previous posts on index build, we defined "aligned" as the case when base object...

Author: SQL Server Query Processor Team Date: 05/08/2007

How to Check Whether the Final Query Plan is Optimized for Star Join Queries?

The star join optimization technique is an index based optimization designed for data warehousing...

Author: SQL Server Query Processor Team Date: 04/09/2007

Hash Warning SQL Profiler Event

One of the less well-known warning events that is logged to SQL Profiler trace is the Hash Warning...

Author: SQL Server Query Processor Team Date: 02/01/2007

Index Build strategy in SQL Server - Part 3: Offline Serial/Parallel Partitioning (Aligned partitioned parallel index build)

Aligned partitioned parallel index build In case of parallel build we scan and sort partitions in...

Author: SQL Server Query Processor Team Date: 01/19/2007

Index Build strategy in SQL Server - Part 3: Offline Serial/Parallel Partitioning

There are two main categories of partitioned index build: Aligned (when base object and in-build...

Author: SQL Server Query Processor Team Date: 01/16/2007

Index Build strategy in SQL Server - Part 2: Offline, Parallel, No Partitioning (Non stats plan (no histogram))

Build (serial) (write data to the in-build index) | X (Merge exchange) / | \ Sort… Sort… Sort...

Author: SQL Server Query Processor Team Date: 12/13/2006

Index Build strategy in SQL Server - Part 2: Offline, Parallel, No Partitioning

The type of parallel index build plan in SQL server depends on whether or not we have a histogram...

Author: SQL Server Query Processor Team Date: 12/11/2006

Query Execution Timeouts in SQL Server (Part 2 of 2)

Checklist for time out errors Memory pressure: In most cases timeouts are caused by insufficient...

Author: SQL Server Query Processor Team Date: 11/22/2006

Index Build strategy in SQL Server - Part 1: offline, serial, no partitioning

Builder (write data to the in-build index) | Sort (order by index key) | Scan (read data from...

Author: SQL Server Query Processor Team Date: 11/20/2006

Query Execution Timeouts in SQL Server (Part 1 of 2)

This short article provides a checklist for query execution time out errors in Yukon. It does not...

Author: SQL Server Query Processor Team Date: 11/14/2006

Using ETW for SQL Server 2005

ETW stands for “Event Tracing for Windows” and it is used by many Windows applications to provide...

Author: SQL Server Query Processor Team Date: 11/12/2006

Index Build strategy in SQL Server - Introduction (II)

  • Building Partitioned Index vs. Building non-Partitioned Index: The data of partitioned tables and...

Author: SQL Server Query Processor Team Date: 11/09/2006

Index Build strategy in SQL Server - Introduction (I)

Index Build strategy in SQL Server may vary depending on users needs. Each of these Index Build...

Author: SQL Server Query Processor Team Date: 11/08/2006

Intro to Query Execution Bitmap Filters

One of the least understood Query Execution operators is the Bitmap. I'd like to give a fairly brief...

Author: SQL Server Query Processor Team Date: 10/27/2006

Statistics Profile Output Formatting

Statistics profile output is an important tool when it comes to troubleshooting query plan issues....

Author: SQL Server Query Processor Team Date: 10/20/2006

Showplan Trace Events

Besides SSMS, another great tool available to database developers and DBAs to view query plans and...

Author: SQL Server Query Processor Team Date: 10/17/2006

What's this cost?

Oftentimes when people include actual execution plan (Ctrl-M, see previous posts for a good primer...

Author: SQL Server Query Processor Team Date: 10/11/2006

Viewing and Interpreting XML Showplans

As mentioned in our previous blog posting, SQL Server 2005 supports Showplan generation in XML...

Author: SQL Server Query Processor Team Date: 10/06/2006

Introduction to Showplan

Showplan is a feature in SQL Server to display and read query plans. While some of you may already...

Author: SQL Server Query Processor Team Date: 09/29/2006

Compilation Time Issues in OLTP Applications When Upgrading to SQL 2005

I've helped a lot of people upgrade their SQL 2000 applications to SQL 2005. While many of these...

Author: SQL Server Query Processor Team Date: 09/27/2006

Query Processing -- introduction to the blog!

Since we’re starting a new blog for the whole query processing team, we think it’s appropriate to...

Author: SQL Server Query Processor Team Date: 09/25/2006

Greetings!

This blog will serve as the new spot for content from the SQL Server Query Processor team. The old...

Author: SQL Server Query Processor Team Date: 09/20/2006