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…

1

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

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…

1

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 index use the same partition schema) Not- Aligned (when heap and index use different partition schemas (including the case when base object is not partitioned at all and in-build index use partitions)) (see Index Build strategy in SQL Server -…

1

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

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

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