SQLServerCentral.com / SQL Server 2008 / SQL Server 2008 Performance Tuning / CDC Growth and poor performance issues / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 07:05:34 GMT20RE: CDC Growth and poor performance issueshttp://www.sqlservercentral.com/Forums/Topic1466608-3387-1.aspxok, 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.Mon, 01 Jul 2013 06:20:45 GMTLowellRE: CDC Growth and poor performance issueshttp://www.sqlservercentral.com/Forums/Topic1466608-3387-1.aspxHey thanks for the valid suggestions, But I can say here the Data statistics here... In 2011 -----&gt; 1.5Crores rows for each 5 tables In 2012 -----&gt; 2.5Crores rows for each 5 tables In 2013 -----&gt; 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... Mon, 01 Jul 2013 06:15:48 GMTSaravanan_tvrRE: CDC Growth and poor performance issueshttp://www.sqlservercentral.com/Forums/Topic1466608-3387-1.aspxyour 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:[b][url]http://msdn.microsoft.com/en-us/library/bb510748.aspx[/url][/b][code]exec sp_cdc_change_job @job_type='cleanup', @retention=integerminutes[/code]so instead of the max value of 52494800(100 years? 36454 days actually), change it to say, 2 years:[code]select 365 * 2 * 24 * 60 --1051200 minutes? check my math!exec sp_cdc_change_job @job_type='cleanup', @retention=1051200 [/code]now you should be able to call the parameterless cleanup procedure, and it will start cleaning the cdc tables of older data:[code]EXEC sp_MScdc_cleanup_job[/code]I think that's what you need to do, but some peer review would help, of course; see what some other folks say too!Mon, 01 Jul 2013 06:09:26 GMTLowellRE: CDC Growth and poor performance issueshttp://www.sqlservercentral.com/Forums/Topic1466608-3387-1.aspxOr how can we purge the CDC data without any issues... anyone tried before? if yes please let me know your experience'sMon, 01 Jul 2013 04:15:03 GMTSaravanan_tvrCDC Growth and poor performance issueshttp://www.sqlservercentral.com/Forums/Topic1466608-3387-1.aspxHello 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...Mon, 24 Jun 2013 01:29:13 GMTSaravanan_tvr