CDC Growth and poor performance issues

Question

Post reply

CDC Growth and poor performance issues

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
June 24, 2013 at 1:29 am

#275472

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...

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

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

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 1

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 3

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...

Lowell SSC Guru Points: 323518 More actions · Answer 4

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!