Geek Answers Handbook

Partitioning Large Tables - Indexes

I was entrusted with the task of improving data management processes (data archiving) into pairs of table tables similar to 200gb

Now I'm reading about table splitting and best practices, and as far as I know, now the process is going on as

  • create filegroups and files
  • create partition function
  • partition scheme - (display intervals for apppriate filegroups)
  • recreate a clustered index - this is the moment when the table is physically moved to other files.
  • profit:)

but cannot find one information what is happening with existing non-clustered indexes at the moment? from here: http://technet.microsoft.com/en-us/library/ms187526(v=sql.105).aspx I found

Although partitioned indexes can be implemented independently of their underlying tables, it usually makes sense to create a partitioned table and then create an index on the table. When you do this, SQL Server automatically partitions the index using the same partition scheme and partition column as the table. As a result, the index is split essentially the same as the table. This makes the index aligned with the table.

and one more

When splitting a unique non-clustered index, the index key must contain a split column. When splitting a non-key non-clustered index, SQL Server adds the default partition column as the keyless column of the (included) index to ensure that the index is aligned with the base table. SQL Server does not add a partition column to the index if it is already in the index.

but none of these links is my problem I need to explicitly create a partition function for existing non-clustered indexes that / do not have a separation column in their definition?

lets say that we have a table like

table A - col1 col2 col3

with a clustered index on col1 and not grouped on col 3 in the PRIMARY section

what will happen to the non-clustered index on col3 after partitioning, will it be aligned with the table or will still be in the PRIMARY section

+4
source share
2 answers

You have to align your indices. There are two main forces that pull you in this direction:

For both aligned and unrecognized indexes, the memory requirement may be greater if SQL Server applies degrees of parallelism to the assembly work on a multiprocessor computer. This is because the greater the degree of parallelism, the greater the need for memory. For example, if SQL Server sets the degrees of parallelism to 4, a partitioned index with 100 partitions requires enough memory for four processors to sort 4,000 pages at a time, or 16,000 pages. If the partitioned index is aligned, the memory requirement is reduced to four processors sorting 40 pages or 160 (4 * 40) pages.

In your case, this implies adding a partitioning column explicitly to each nonclustered index and declaring each nonclustered index on the same partition scheme as the base table (cluster index). Do not try to create another function / partition scheme for a non-clustered index. Adding a partition column to each index has profound implications in your data model, for example. you will no longer be able to declare a primary key constraint that does not contain a partition column (and this ruffles into all foreign key definitions that reference a partitioned table!), but this is the price that you already bought when you took the partition as a solution, see How decide whether to use table splitting .

+3
source

Typically, you usually discard all non-clustered indexes and recreate them on the new schema. This will separate them compared to table Paris in the same way as the clustered index (and row data).

If you do not, they will remain seated on the original filegroup (main or everywhere)

+2
source

More articles:


All Articles