SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CDC Growth and poor performance issues


CDC Growth and poor performance issues

Author
Message
Saravanan_tvr
Saravanan_tvr
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1349
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...

Many Thanks!
S.saravanan
“I am a slow walker, but I never walk backwards-
Abraham Lincoln”
Saravanan_tvr
Saravanan_tvr
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1349
Or how can we purge the CDC data without any issues... anyone tried before? if yes please let me know your experience's

Many Thanks!
S.saravanan
“I am a slow walker, but I never walk backwards-
Abraham Lincoln”
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28193 Visits: 39954
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
Saravanan_tvr
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 1349
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...

Many Thanks!
S.saravanan
“I am a slow walker, but I never walk backwards-
Abraham Lincoln”
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28193 Visits: 39954
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search