July 15, 2016 at 11:15 am
Hello all
My env is a sql server 2012 SP3, 12 cores, 196gb of ram, using a dedicated SAN for the database drives
I have a environment with 41 databases with CDC enabled, + several other databases, the sizes of the dbs are of around 100gb to 400gb, with a transaction rate of ~5 million transactions per day in total
what we have is a huge latency for cdc, some up to 10 hours and we need this to be as much up to date as possible since that is feeding a data warehouse.
The current settings for the capture jobs are like this:
pollinginterval=1
maxscans=10
maxtrans=5000
The waitime that we are experiencing is : repl_schema_access
Could someone provide some light on what can I try as to improve the performance?
Thanks in advance
PD: we are only monitoring the columns that we need at each database
July 15, 2016 at 12:15 pm
Do a differential file IO and wait stats analyses to see how overwhelmed your server is. CDC can be an enormous overhead, especially on the IO subsystem. My guess is that is where your problem resides.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply