CDC - Impact on Performance

  • We have 
    3 TB database that is serving a busy Web Application.
    If we implement CDC that will track changes on 75 tables, what is performance impact?
    One of the tables is 2TB partitioned table. Daily INSERT in this table is approximately 2 million records.
    Other daily changes are maybe 2-3,000 records in 5 tables and few hundreds in the rest of the tables.
    How to measure CDC impact?

  • use your dev server, and simulate production load on the box before and after CDC to measure the impact.

  • "simulate production load".
    Easy to say.
    Impossible to do in our environment.
    Busy Web Application + hundreds of automated SQL jobs running every 1,10,20 minutes.
    It's a whole project to make UAT "as PROD" but management does not
    want to allocate budget and time to it.
    What i was looking for in general, how big might be the impact, if any?
    What to expect?

  • RVO - Friday, February 10, 2017 12:25 PM

    "simulate production load".
    Easy to say.
    Impossible to do in our environment.
    Busy Web Application + hundreds of automated SQL jobs running every 1,10,20 minutes.
    It's a whole project to make UAT "as PROD" but management does not
    want to allocate budget and time to it.
    What i was looking for in general, how big might be the impact, if any?
    What to expect?

    Well you did ask how to measure the impact. And the poster did you give you the correct answer.

    Outside of that, no one can really tell you as it depends. Will it have an impact? Yes.
    How big an impact? No one can tell you.
    If you have a very busy high transaction system, it likely is not what you want to use and generally is not recommended for those environments. 
    Things to expect would be that you don't just enable it and expect it to just run well and manage itself.
    Clean up jobs can have problems and need to be monitored.
    Most people have somewhere to test a manual, undocumented method for cleanup which you don't have so that introduces some risks.
    The log can take a take a hit in terms of size, load which in turn also can impact your IO subsystem.
    Stats on the internal tables can go stale and affect performance.
    You can get a list of some things you can do for synthetic workloads as well as some ways to guess at the impact in this article which was originally for SQL 2008, but it will at least give you an idea of some things to try:
    Tuning the Performance of Change Data Capture in SQL Server 2008

    Sue

  • RVO - Friday, February 10, 2017 12:25 PM

    "simulate production load".
    Easy to say.
    Impossible to do in our environment.
    Busy Web Application + hundreds of automated SQL jobs running every 1,10,20 minutes.
    It's a whole project to make UAT "as PROD" but management does not
    want to allocate budget and time to it.
    What i was looking for in general, how big might be the impact, if any?
    What to expect?

    may be you can use SQL profile and tuning advisor to simulate production work load.
    if you are looking for spoon feeding I am afraid that's unlikely to happen
    https://msdn.microsoft.com/en-us/library/ms174202.aspx

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply