Home > Uncategorized > Change Tracking Internals (SQL2008R2) Part 4

Change Tracking Internals (SQL2008R2) Part 4

Change Tracking Overhead

When change tracking is enabled for a table, some administration operations are affected. The following table lists the operations and the effects you should consider.

Operation

When change tracking is enabled

DROP TABLE

All change tracking information for the dropped table is removed.

ALTER TABLE DROP CONSTRAINT

An attempt to drop the PRIMARY KEY constraint will fail. Change tracking must be disabled before a PRIMARY KEY constraint can be dropped.

ALTER TABLE DROP COLUMN

If a column that is being dropped is part of the primary key, dropping the column is not allowed, regardless of change tracking.

If the column that is being dropped is not part of the primary key, dropping the column succeeds. However, the effect on any application that is synchronizing this data should be understood first. If column change tracking is enabled for the table, the dropped column might still be returned as part of the change tracking information. It is the responsibility of the application to handle the dropped column.

ALTER TABLE ADD COLUMN

If a new column is added to the change tracked table, the addition of the column is not tracked. Only the updates and changes that are made to the new column are tracked.

ALTER TABLE ALTER COLUMN

Data type changes of non-primary key columns are not tracked.

ALTER TABLE SWITCH

Switching a partition fails if one or both of the tables has change tracking enabled.

DROP INDEX, or ALTER INDEX DISABLE

The index that enforces the primary key cannot be dropped or disabled.

TRUNCATE TABLE

Truncating a table can be performed on a table that has change tracking enabled. However, the rows that are deleted by the operation are not tracked, and the minimum valid version is updated. When an application checks its version, the check indicates that the version is too old and a re-initialization is required. This is the same as change tracking being disabled, and then re-enabled for the table.

Effects on DML

Change tracking has been optimized to minimize the performance overhead on DML operations. The incremental performance overhead that is associated with using change tracking on a table is similar to the overhead incurred when an index is created for a table and needs to be maintained.

For each row that is changed by a DML operation, a row is added to the internal change tracking table. The effect of this relative to the DML operation depends on various factors, such as the following:

  • The number of primary key columns
  • The amount of data that is being changed in the user table row
  • The number of operations that are being performed in a transaction

Snapshot isolation, if used, also has an effect on performance for all DML operations, whether change tracking is enabled or not.

 

 


 

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: