CDC Growth and poor performance issues

  • Hello Friends,

    In our Database we have enabled the CDC initially from the past 2 years it has grown huge,

    5Tables i have enabled now each tables having 16Laks rows...

    Now the performance of the retrieving the history queries are too slow... its all predefined MS functions only..

    How can we optimize and come up with better performance 🙂

    Note: But we cannot archiving the Tables of Data... next 3 years...

  • Or how can we purge the CDC data without any issues... anyone tried before? if yes please let me know your experience's

  • your settings for CDC were probably set to keep data for 100 years or something,a nd now you are seeing some performance inpact;

    I setup CDC on a few tables, but have not had to fiddle with them since, so i'm a little rusty.

    As i remember it, you need to change your setting on how long you want to keep the data:

    http://msdn.microsoft.com/en-us/library/bb510748.aspx

    exec sp_cdc_change_job @job_type='cleanup', @retention=integerminutes

    so instead of the max value of 52494800(100 years? 36454 days actually), change it to say, 2 years:

    select 365 * 2 * 24 * 60 --1051200 minutes? check my math!

    exec sp_cdc_change_job @job_type='cleanup', @retention=1051200

    now you should be able to call the parameterless cleanup procedure, and it will start cleaning the cdc tables of older data:

    EXEC sp_MScdc_cleanup_job

    I think that's what you need to do, but some peer review would help, of course; see what some other folks say too!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hey thanks for the valid suggestions,

    But I can say here the Data statistics here...

    In 2011 -----> 1.5Crores rows for each 5 tables

    In 2012 -----> 2.5Crores rows for each 5 tables

    In 2013 -----> 0.5Crores rows for each 5 tables

    just three years data only... we committed with customer that we should not clean the history for the corresponding periods... Coz our audit starts only from the 2011 onwards...

  • ok, if you cannot remove records due to your audit requirements, i think it comes down to query tuning;

    can you grab some execution plans, and see if any of the inserted into teh cdc tables might benefit from indexes, either on the main table, or maybe even the CDC tables themselves?(i've not bothered putting indexes on any of the automatically built cdc tables...they only receive inserts anyway).

    I'm sure you are already looking at the usual suspects... SARGabile arguments, catch all queries, indexing.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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