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 …(order by index key)                            |           |            |                        Scan…    Scan… Scan…(read data from source)      When histogram is not available (for example when we building an index on a…

0

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 available with necessary statistics. Therefore, there are two broad categories of parallel index plans: Histogram available: No histogram   Histogram available (parallel sort and build):                  X (Exchange)    |          \            \          Builder……

1

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

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 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 possible – in…

1

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

Index Build strategy in SQL Server – Introduction (II)

–         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 of rows are mapped into individual partitions. The table or index is treated as a single…

1

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 strategies may have different memory and disc space requirement. These different strategies will be discussed in the next several posts.   For the beginning let’s see what kind of Index Build types exist in SQL Server 2005:  …

1

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

Statistics Profile Output Formatting

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.   When working in SQL Server management Studio (SSMS), it is advised to enable…

0