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"...
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...
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...
Date: 08/12/2009
Distinct Aggregation Considered Harmful
Distinct aggregation (e.g. select count(distinct key) …) is a SQL language feature that results in...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
Date: 10/27/2006
Statistics Profile Output Formatting
Statistics profile output is an important tool when it comes to troubleshooting query plan issues....
Date: 10/20/2006
Showplan Trace Events
Besides SSMS, another great tool available to database developers and DBAs to view query plans and...
Date: 10/17/2006
Viewing and Interpreting XML Showplans
As mentioned in our previous blog posting, SQL Server 2005 supports Showplan generation in XML...
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...
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...
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...
Date: 09/25/2006
Greetings!
This blog will serve as the new spot for content from the SQL Server Query Processor team. The old...
Date: 09/20/2006