We are going to be implementing a mechanism to track changes in our SQL Server 2008 application database. The programmers are concerned about INSERT speed being negatively impacted, so I have set out to test three cases: no mechanism to track changes, change tracking, and Change Data Capture (CDC).
In all of my tests, there appears to be no significant difference in the speed of INSERTs. Does this make sense? I want to make sure I am not overlooking anything before I remove this concern from our list of requirements.
(Note: my testing has shown differences with UPDATES, but we are only concerned about INSERTS)
EDIT: My tests were relatively simple INSERTS into a production-like table, but 250K of them. The processes ran over 5 minutes, so I'm comfortable it was enough for our environment.
Best Answer
Yes it makes sense, CDC is maintained via a Log Reader, which other than the overhead of the reads on the log drive, and the writes to the capture tables, should not affect the insert rate. They're thinking about (old school :)) tracking via triggers, which can easily impact the insert rate.
But to be safe, make sure your tests run long enough for the overhead of the reader to be actively capturing the inserts.