1

I am doing some tests to quantify the performance/reliability of the Change Tracking feature of SQL Server. I have a single table t1 in which I insert 1 million rows, once with Change Tracking OFF and once with Change Tracking On. I am monitoring the sizes of syscommittab, the size of the change tracking table and the I/Os recorded against the database. As is to be expected, the change tracking table and syscommitab only get populated when Change Tracking is ON. And I expect the IOs recorded against the database to be "proportional" to the sizes of these tables. But to my surprise, these are way off. The IOs recorded against the database are many orders more than the sizes of these 2 tables. Wondering if anyone knows why or can give me pointers to figuring it out. I am using sys.dm_io_virtual_file_stats() to determine the IO activity on the database and the following query to determine the sizes of the tracked table and syscommittab.

SELECT   sct1.name as CT_schema,
    sot1.name as CT_table,
    ps1.row_count as CT_rows,
    ps1.reserved_page_count*8./1024. as CT_reserved_MB,
    sct2.name as tracked_schema,
    sot2.name as tracked_name,
    ps2.row_count as tracked_rows,
    ps2.reserved_page_count*8./1024. as tracked_base_table_MB,
    change_tracking_min_valid_version(sot2.object_id) as min_valid_version
FROM sys.internal_tables it
JOIN sys.objects sot1 on it.object_id=sot1.object_id
JOIN sys.schemas AS sct1 on
sot1.schema_id=sct1.schema_id
JOIN sys.dm_db_partition_stats ps1 on
it.object_id = ps1. object_id
and ps1.index_id in (0,1)
LEFT JOIN sys.objects sot2 on it.parent_object_id=sot2.object_id
LEFT JOIN sys.schemas AS sct2 on
sot2.schema_id=sct2.schema_id
LEFT JOIN sys.dm_db_partition_stats ps2 on
sot2.object_id = ps2. object_id
and ps2.index_id in (0,1)
WHERE it.internal_type IN (209, 210)
and (sot2.name='t1' or sot1.name='syscommittab')

I am checkpointing before running the queries.

Any tip or pointer appreciated.

Acknowledgements to https://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/ for the SQL above.

1

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.