I'm a little worried about the default retention period in SQL Server 2008 Change Tracking (which is 2 days).
Is it a good idea to set this period to eg. 100 years and turn auto cleanup off or will it bite me back in the future with excessive storage usage and/or performance degradation? Anyone has experience in that matter?
If you set auto cleanup off, it's best to periodically go through and remove the change tracking information yourself, by disabling and then re-enabling change tracking for each table. Otherwise, yes, the tracking data will continue to grow and grow.
You can't query the underlying tables directly, but you can poke at their metadata. The following query shows relative row counts:
select
s.name as schema_name
, t.name as table_name
, (select sum(rows) from sys.partitions x where o.parent_object_id = x.object_id) as rows_in_base_table
, o.name as tracking_table
, p.rows as rows_in_tracking_table
from sys.objects o
join sys.tables t on o.parent_object_id = t.object_id
join sys.schemas s on t.schema_id = s.schema_id
join sys.partitions p on o.object_id = p.object_id
where o.name like 'change[_]tracking%'
and o.schema_id = schema_id('sys')
order by schema_name, table_name
Run that in your database, and you should get a rough sense of current overhead.
The change tracking tables all follow a standard schema. For example:
select
c.name, c.column_id
, type_name(user_type_id) as type_name
, c.max_length, c.precision, c.scale
, c.is_nullable, c.is_identity
from sys.columns c
where object_id = (
select top 1 object_id from sys.objects o
where o.name like 'change[_]tracking%'
and o.schema_id = schema_id('sys')
)
The k_% columns vary by table and correspond to the primary keys of the tracked table. You are looking at a base minimum overhead of 18 bytes + (primary key length) per row. That adds up!
For example, I'm tracking some skinny base tables that are only 15 bytes wide, with a 7-byte composite key. That makes the tracking tables 18+7=25 bytes wide!