Mystery of memory fraction in Showplan XML

If you examine Showplan XML, you may find an entry like  <MemoryFractions Input=”1″ Output=”1″ /> on some operators .  They are internal parameters used by query memory grant to save overall memory usage, and “fraction” means how much this operator is allowed to use from “total additional memory” (please see previous post for definition on additional memory)….


Understanding SQL server memory grant

This article describes how query memory grant works in Microsoft SQL Server. It applies to both SQL2005 and SQL2008. Written by Jay Choe, Microsoft SQL Server Engine. ——————————————————————————– Query memory grant (a.k.a. query work buffer) is a part of server memory used to store temporary row data while sorting and joining rows. It is called…


Understanding SQL Server Fast_Forward Server Cursors

SQL Server’s server cursor model is a critical tool to many application writers.  Fast_forward cursors are very popular as an alternative to read_only forward_only cursors, but their inner workings are not well-publicized.  So I thought I’d give it a go. Background A server cursor is a cursor managed by SQL Engine.  It consists of a…


Distinct Aggregation Considered Harmful

Distinct aggregation (e.g. select count(distinct key) …) is a SQL language feature that results in some very slow queries.  It’s particularly frustrating that you can take a perfectly efficient query with multiple aggregates, and make that query take forever just by adding a distinct keyword to one of the aggregates.  For instance, it often makes…


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 format. Also, SQL Server 2005 has XQuery support to directly query XML document. By combining these two new features, users can analyze the query plans using queries.   However, in SQL Server, there is no easy way to capture the…


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 when building the new index. For example, by using the same partition function and scheme, the new index can be partitioned on different columns than the original table. Create table t (c1 int, c2 int) on ps(c2) ……. Create…


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 and in-build index use the same partition schema, and “non-aligned” to be the case when heap and index use different partition schemes, or the case when heap is not partitioned. In this post, we will talk about…


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 scenarios to make optimal use of non-clustered indexes on the huge fact tables. The general idea is to use the non-clustered indexes on the fact table to limit the number of rows scanned from it. More details of index based star…


Hash Warning SQL Profiler Event

One of the less well-known warning events that is logged to SQL Profiler trace is the Hash Warning event.  Hash Warning events are fired when a hash recursion or hash bailout has occurred during a hashing operation.  Both of these situations are less than desirable, as they mean that a Hash Join or Hash Aggregate…


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 parallel and the actual number of sort tables existing at the same time will depends on the actual number of concurrent workers. Partitions are being chosen by workers one by one and when one worker completes with one…