Configuring and implementing customized steps in snapshot replication lets you boost performance that might be compromised when you try to replicate huge volumes of data. In the customized drop step described in "Manual Transmission," InstantDoc ID 25538, the main article for this sidebar, the remote stored procedure uses a COM object. This object calls the SQL Distributed Management Objects (SQL-DMO) API to drop indexes and key constraints safely and optimally on the destination tables and save their recreation scripts. Let’s put this drop-and-save step under a magnifying glass so you can see how the custom COM object method named Drop_Save_Table_Indexes works when it calls the SQL-DMO API; then, you’ll be ready to apply the techniques elsewhere.
The COM object method adopts a specific order for dropping indexes and key constraints on each table. Note that this order is the only way to drop key constraints and the optimal way to drop indexes. First, the method drops all foreign key constraints that reference the current table. Next, it drops the nonclustered primary key, all nonclustered unique keys, and all nonclustered indexes. Then, it drops the clustered index, clustered primary key, or clustered unique key.
The order for saving creation scripts is exactly the opposite. First, the method saves the creation scripts of the clustered index, clustered primary key, or clustered unique key. Next, it saves the creation scripts of the nonclustered primary key, all nonclustered unique keys, and all nonclustered indexes. Then, it saves the creation scripts of all foreign key constraints referencing the current table.
The logic behind the order of steps in these two instances is easy to follow. First, you can’t drop a primary key without dropping all the referencing foreign keys first; conversely, you can’t create a foreign key without creating the referenced primary key first. Therefore, you must drop foreign keys first but create them last. Second, if a clustered index is on the same table, the leaf level of a nonclustered index B-tree stores clustered index keys instead of pointers to data pages. That is, a nonclustered index search uses the clustered index if one exists. If you drop the clustered index first, the leaf nodes of all nonclustered index B-trees have to be rebuilt to store pointers instead. In contrast, if you create all nonclustered indexes first, their leaf nodes have to be rebuilt when you create a clustered index later. Therefore, the optimal order is to drop nonclustered indexes and keys before dropping the clustered one, but create them after creating the clustered index. The Visual Basic (VB) code snippet in Web Listing A highlights the Drop_Save_Table_Indexes method of the DMO_drop_save_indexes_listing.zip executable script that you can download by entering InstantDoc ID 25694, then clicking Download the code. The code snippet shows how to drop indexes and key constraints and save their creation scripts by using SQL-DMO. Executing this method results not only in dropped indexes and key constraints, but also in a concatenated string of index and key creation scripts that will form the body of the programmatically created (and altered) stored procedure that the Distribution Agent job uses at the recreate step.
Note that the Indexes collection of a Table object in the SQL-DMO API includes statistical (hypothetical) indexes. These indexes are part of the usage statistics that SQL Server gathers and uses to improve queries on nonindexed—but frequently used—columns. These column statistics are stored in the sysindexes system table and are created automatically when the auto create statistics option is set on a database. SQL-DMO assigns them to the SQLDMOIndex_Hypothetical index type. The Index Tuning Wizard can create a hypothetical index during its analysis of indexes for optimization.