Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CDC Growth and poor performance issues Expand / Collapse
Author
Message
Posted Monday, June 24, 2013 1:29 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, March 30, 2014 9:34 PM
Points: 277, Visits: 1,179
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”
Post #1466608
Posted Monday, July 01, 2013 4:15 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, March 30, 2014 9:34 PM
Points: 277, Visits: 1,179
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”
Post #1468992
Posted Monday, July 01, 2013 6:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 12,744, Visits: 31,065
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1469013
Posted Monday, July 01, 2013 6:15 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, March 30, 2014 9:34 PM
Points: 277, Visits: 1,179
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”
Post #1469017
Posted Monday, July 01, 2013 6:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 12,744, Visits: 31,065
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1469019
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse