Sql-server – weight of replication on SQL Server 2008 R2 instance


How can I see what impact replication is having on a server/instance, replication vs all other things.

Best Answer

I assume replication is already running here. My general go to is to use the sp_whoisactive script. I will have the procedure run every few minutes and insert into a history table. After a few days you'll get an idea of what's running on your server. Ideally, I'd run that script for a few days with replication off and then turn it back on again to see the impact.

The table collects information based on your DMV's which is where I look for performance tuning. You need to get an idea though of what's normal or what regularly runs in your environment. If a query is using a lot of CPU, how do you know if that's the issue? Has it always used a lot of CPU or has something changed?

Paul Randal has a great article that talks about using DMV's to tune and it can help you here as well.

Keep in mind replication is using the Transaction logs here, so CDC or CDT can have a huge impact pending configuration.

There are a few warning signs to your DB if it's under pressure. Is the page life expectancy frequently dropping? Do you see your Buffer Cache Ratio dropping? What do your IO reads and writes look like during high transaction times? Do you have latency alerts configured on replication?