Partition Management Utility v 2.0
February, 2008
Provided AS-IS, with no warranties expressed or implied
Executable name: ManagePartition.exe
This
release supports both SQL Server 2008 and SQL Server 2005. It requires .Net 2.0, along with SQL Server
2008 System CLR Types and SQL Server 2008 Management Objects (SMO), both downloadable
from:
Description:
The general problems
addressed by this utility are:
1.
Guaranteeing
that the staging table required to SWITCH in or out a partition is always
created correctly just in time, with the right indexes, columns, foreign
keys, indexed views, and partition-specific check constraint and filegroup corresponding to the target partition of a
partitioned table.
2. Ensuring that any partition management
scripts stay in synch with the possible index changes, column changes or
foreign key constraints of partition tables over time. Without this utility, any change to a
partition tables DDL would require an equivalent change in a TSQL-based
partition management script, along with associated testing, etc.
3. Providing an easy mechanism to quickly empty
a selected partition from a partitioned table with a single command-line
4. Allowing fine-tuned partition data loading
including scenarios in which you want to create a staging table and populate
it, and create indexes later prior to a SWITCH in operation.
The utility must be
run from the command line or within an SSIS package. Command line parameters determine
behavior. You may perform one of five different
functions depending on the COMMAND parameter.
Additional parameters define the connection (server, integrated
security, etc); database, schema, partitioned table name, etc. You have the option of identifying a
partition to manage either by explicit partition number (/p:) OR by specifying
a string representation of a value that can be input to the partition range
function to determine a partition number (/v:).
COMMAND Description:
5 mutually-exclusive
commands are supported as the /C: parameter to the executable.
ClearPartition Empties a partition of all rows by
creating a staging table and calling SWITCH.
There is the option to keep or drop the staging table (/K), and the name
of the staging table is optional (/A)
CreateStagingFull Creates a staging table to match a
selected partition of a partition table, including all indexes, fkeys, check constraint, indexed views, etc. This can then be populated for a later SWITCH
in to the partition table.
CreateStagingNoindex Same as CreateStagingFull except it does not
create the indexes or indexed views. Use
this command if you want to populate the staging table through a bulk-load,
SSIS or insert-select while indexes are not present for performance reasons. After the table is populated, you must rerun
the program using the IndexStaging command in order for
the table to be capable of a SWITCH operation.
CreateStagingClusteredIndex Same as CreateStagingFull except it only
creates the Clustered Index, deferring all nonclustered
indexes and indexed views until the program is run again with the IndexStaging command.
Use this command if you want to populate the staging table through a
bulk-load, SSIS or insert-select while only the Clustered index is in place.
IndexStaging Builds all indexes
and indexed views on a staging table to match those of the associated partition
table. Use this if you previously used
the CreateStagingNoindex or CreateStagingClusteredIndex command to create the
table. After this command the table will
be ready for a SWITCH operation.
Usage:
ManagePartition
/Command:<string> short form /C command, default = CreateStagingFull
/Server:<string> /S Server name, default = (local)
/User:<string> /U User name, default = null
/Password:<string> /P Password, default = null
/Integrated[+|-] /E Integrated Security, default +
/Database:<string> /d Database name
/Schema:<string> /s Schema name use quotes as delimiter if needed
/PartitionTable:<string> /t Partition
table name - use quotes as delimiter if needed
/PartitionNumber:<int>
/p Partition Number
/PartitionRangeValue:<string> /v Value string to input to partition
function to specify partition number
/StagingTable:<string> /A Staging
Table Name, default = null
/Keep[+|-] /K Keep Staging Table
if ClearPartition, default +
<Command>
= ClearPartition | CreateStagingFull
| CreateStagingNoindex | CreateStagingClusteredIndex
| IndexStaging
Examples:
ManagePartition /C:CreateStagingFull /d:sample /s:dbo
/t:Address /p:4 /A:myStagingTbl
ManagePartition /C:CreateStagingFull /d:sample /s:dbo
/t:Address /v:2009-01-01 /A:myStagingTbl
ManagePartition /C:ClearPartition /d:sample /s:dbo
/t:Address /p:1 /K-
Return
Codes: 0 normal exit; 1 invalid or missing
parameters; 2 exception encountered
Note: In
the source code, the core functionality is all packaged at even finer
granularity into the class PartitionManager (file PartitionManagement.cs).
Indexes, check constraints, fkeys, etc. can
all be cloned from a partition as separate operations by an alternative diver
program if desired.
Discussion:
Switching a partition in or out of a table requires that a staging table
be created in the same filegroup as the partition,
with identical index structures, column characteristics, and constraints as the
partitioned table. When switching data
in to a partitioned table, the staging table must also have and additional
check constraint that restricts the value of the partitioning key to match the
target partition of the partitioned table.
ManagePartition.exe is designed to fully
automate the creation of such a staging table when needed such as during
daily or monthly partition management cycles, as an alternative to maintaining
complex TSQL scripts to generate staging tables matching partitions. ManagePartition
determines the structures to build based on the partitioned table structures in
place and a data value of the partitioning key (or the partition number)
identifying the partition of interest.
So even if indexes or column definitions on partition tables change, management
scripts that rely on the ManagePartition need not be
changed because staging table structure is determined at execution time. Also the ManagePartition
command can be easily integrated with parameter-driven scripts.
ManagePartition offers flexibility
in index building to match the data loading style that you prefer to use when
loading a staging table with new data.
You can choose to create a staging table with or without indexes, or
with only a clustered index, as an alternative to a fully indexed table since
bulk loads may be much faster using that technique. Then, when the data is loaded, you can run ManagePartition again invoking the command to build all
indexes that were skipped initially.
Some users prefer to disable Check
Constraints and Foreign Key constraints during the data load, and re-enable
them later. In some cases this may boost data load performance. While ManagePartition
does not offer this option built-in, it is easy to accommodate this within your
overall data load process: After
creating the staging table using ManagePartition
(with or without indexes), prior to data loading, you may disable all Check and
Foreign Key constraints using:
ALTER TABLE table_name NOCHECK
CONSTRAINT ALL;
Then, after completing data load operations, re-enable all FOREIGN KEY and
CHECK constraints, and validate existing rows using:
ALTER TABLE table_name WITH
CHECK CHECK CONSTRAINT ALL;
Enhancements from v1:
Support for automatically handling partition-aligned indexed views in SQL
Server 2008
Handles sparse columns and filtered indexes
in SQL Server 2008
Accommodates the new date and time data types
in SQL Server 2008
Clustered index is always created first
New command CreateStagingClusteredIndex
supports creating only the Clustered Index to optimize a load of presorted
data; additional indexes can be
automatically built later using IndexStaging
Support for Nullable
partitioning keys
Support for compressed tables and indexes in
SQL Server 2008
Default constraints are inherited from the
partitioned table to assist with data loading into staging tables
Connection Timeout eliminated
For questions or comments, email stuarto@microsoft.com