Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
You should add more detail about how you tested this assumption. For instance, did you time a single INSERT each way? Or did you time 10 million INSERTs each way? What kind of data are you dealing with? Is the data you are testing representative of the data in production? –  Max Vernon Oct 26 '12 at 20:19
1  
You may find this link useful: databasejournal.com/features/mssql/article.php/3806001/… –  8kb Oct 27 '12 at 22:27
    
Great link, @8kb - thanks! –  JHFB Oct 29 '12 at 10:31
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.