tags:

views:

83

answers:

1
Q: 

SQL2008: Is there a way to throttle Change Data Capture (CDC) throughput?

I'm trying to find a way to 'throttle' CDC on SQL2008.

The reason being that under normal circumstances, CDC performs brilliantly, but as soon as it needs to deal with a 'large' number of rows, it starts tanking.

Typical throughput is between 1000 and 3000 rows a second. It starts to die at about 5000 rows per second.

Usually, this is not an issue, since we're using CDC to keep two databases in sync as a near real-time ETL process for statistical modelling. In the past, for bulk data moves we've had to come up with dodgy manual methods. I'm wondering if I can through a huge amount of data at it, but find a way to tell it to only do 5 transactions at a time, or otherwise force it to work through bite-sized chunks (however long that takes), rather than try and do them all at once and suffer poorly.

A: 

Please see: Tuning the Performance of Change Data Capture in SQL Server 2008

Are you sure that CDC is the right solution for what you are trying to achieve? I'm just wondering if SQL Server Change Tracking using ADO.NET Sync services might be more appropriate?

Mitch Wheat  2010-02-22 05:46:10
I've read through that. Changing the values didn't affect the 'tank' point.I think this is because those values control the flow of data into the change tables. My issue is more on the flow of data out of those same tables.I'm thinking about using the __$start_lsn column, and then some kind of rowcount operation to determine how big those transactions are (each __$start_lsn should be analogous to a 'transaction' for all intents and purposes). I'll then know how many transactions, or lsn's I can process in a batch.But I'm kinda struggling with the concepts!
divv  2010-02-22 05:51:27
I kinda made it a bit simplistic in my question. We do a little more than merely sync. We're using CDC so we get the historical view of how the data changed over time (which is important to our statistics modelling process).
divv  2010-02-22 06:06:33
You might want to ask a more detailed question, and someone might have ideas.
Mitch Wheat  2010-02-22 06:25:50

related questions

*= in Sybase SQL
sql missing rows when grouped by DAY, MONTH, YEAR
Adapt Replace all strings in all tables to work with text
Can you call a webservice from TSQL code?
How to tell the data types after executing a stored procedure?
C# - SQLClient - Simplest INSERT
SQL Server: Get data for only the past year
Get last item in a table - SQL
Is there a way to make a TSQL variable constant?
Create a database from another database?
SQL Server PIVOT examples?
What are some references, lessons and or best practices for SQL optimization training.
What's a good way to check if two datetimes are on the same calendar day in TSQL?
Trigger without a transaction?
How do I do an Upsert Into Table?
What is the easiest way using T-SQL / MS-SQL to append a string to existing table cells?
How do I avoid using cursors in Sybase (T-SQL)?
Best .NET Solution for Frequently Changed Database
T-Sql date format for seconds since last epoch / formatting for sqlite input
HOWTO - Compare a date string to datetime in SQL Server?
Differences Between MySql and MS SQL
Best method for varchar date validation in Sybase (T-SQL)?
interrogating table lock schemes in T-SQL
Testing for inequality in T-SQL
T-Sql Remove Decimal Point From Money Data Type