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…

9

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…

5

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…

4

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…

4

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 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 give a good start point on this issue:   http://blogs.msdn.com/slavao/archive/2005/02/19/376714.aspx http://blogs.msdn.com/slavao/archive/2005/02/01/364523.aspx http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EWIAC (includes a link…

3

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 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 SQL Server memory management architecture: http://blogs.msdn.com/slavao/archive/2005/02/11/371063.aspx   Overview of query processing When a…

3

Using ETW for SQL Server 2005

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, you can literally track a request coming from IIS, passing through…

3

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 overview of how Bitmap filters are used, as well as some technical details about their limitations and functionality.  Bitmap filters are often mistaken as Bitmap indexes.  The two are actually very distinct concepts — Bitmap indexes are physical structures that are…

3

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…

2

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…

2