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

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...

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. --------------------------------------------------------------------------------...

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...

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...

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...

Source Partitioned While 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...

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...

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...

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...

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...

There are two main categories of partitioned index build: Aligned (when base object and in-build index use the same partition schema) Not- Aligned (when heap and index use different partition schemas (including the case when base object is not...

Build (serial) (write data to the in-build index) | X (Merge exchange) / | \ Sort… Sort… Sort … (order by index key) | | | Scan… Scan… Scan… (read data from source) When histogram is not available (for example when we building...

The type of parallel index build plan in SQL server depends on whether or not we have a histogram available with necessary statistics. Therefore, there are two broad categories of parallel index plans: Histogram available: No histogram ...

Checklist for time out errors Memory pressure : In most cases timeouts are caused by insufficient memory (i.e. memory pressure). There are different types of memory pressures and it is very important to identify the root cause. The following articles...

Builder (write data to the in-build index) | Sort (order by index key) | Scan (read data from source) In order to build the b-tree for the index we have to first sort the data from source. The flow is to scan the source, sort it (if...

This short article provides a checklist for query execution time out errors in Yukon . It does not touch the time out issues on optimization and connection. Before reading this article, you are recommended to read the following post to get familiar with...

ETW stands for “Event Tracing for Windows” and it is used by many Windows applications to provide debug trace functionality. This “wide” availability is a key point of using ETW because it can help to track certain activities from end to end. For example...

- Building Partitioned Index vs. Building non-Partitioned Index: The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups...

Index Build strategy in SQL Server may vary depending on users needs. Each of these Index Build strategies may have different memory and disc space requirement. These different strategies will be discussed in the next several posts. For the beginning...

One of the least understood Query Execution operators is the Bitmap . I'd like to give a fairly brief overview of how Bitmap filters are used, as well as some technical details about their limitations and functionality. Bitmap filters are often mistaken...

Statistics profile output is an important tool when it comes to troubleshooting query plan issues. When enabled, it returns a textual representation of the query plan with a lot of detail about cost and cardinality estimates as well as actual counts....

Besides SSMS, another great tool available to database developers and DBAs to view query plans and troubleshoot query compilation or execution issues is the SQL Server Profiler. In the Profiler, all the showplan events are listed under the Performance...

Oftentimes when people include actual execution plan (Ctrl-M, see previous posts for a good primer on execution plans) while executing a batch in SQL Server Management Studio, and they see this "Query cost (relative to the batch)" thing on top of each query in the batch, they start to ask: What does this mean? But when I run this batch, the first query runs a faster than the second, and yet this crazy SQL Server says the second has a higher cost, what are they talking about? ...

As mentioned in our previous blog posting, SQL Server 2005 supports Showplan generation in XML format. XML-based Showplans provide greater flexibility in viewing query plans and saving them to files as compared to legacy Showplans. In addition to the...

Showplan is a feature in SQL Server to display and read query plans. While some of you may already be very familiar with Showplan, it is one of the most important diagnostic tools that we use in the query processing team to locate and identify problems...