SQL Server performance
In a multi-site hierarchy, Configuration Manager uses SQL Server replication to transfer data between sites. For more information, see Database replication.
Use the following diagram to start troubleshooting SQL Server performance that can impact replication status:
Queries
This diagram uses the following queries:
Make sure SQL Server change tracking table is cleaned up
DECLARE @RetentionUnit INT = 0;
DECLARE @RetentionPeriod INT = 0;
DECLARE @CTCutOffTime DATETIME;
DECLARE @CTMinTime DATETIME;
SELECT @RetentionPeriod=retention_period,
@RetentionUnit=retention_period_units
FROM sys.change_tracking_databases
WHERE database_id = DB_ID();
IF @RetentionUnit = 1
SET @CTCutOffTime = DATEADD(MINUTE,-@RetentionPeriod,GETUTCDATE())
ELSE IF @RetentionUnit = 2
SET @CTCutOffTime = DATEADD(HOUR,-@RetentionPeriod,GETUTCDATE())
ELSE IF @RetentionUnit = 3
SET @CTCutOffTime = DATEADD(DAY,-@RetentionPeriod,GETUTCDATE())
-- give a buffer of two days
SET @CTCutOffTime = DATEADD(DAY, -2, @CTCutOffTime)
select top 1 @CTMinTime=commit_time from sys.dm_tran_commit_table order by commit_ts asc
IF @CTMinTime < @CTCutOffTime
PRINT 'there is change tracking backlog, please contact Microsoft support'
Change current sessions that handle SQL Server service broker messages are blocked
select
req.session_id
,req.blocking_session_id
,req.last_wait_type
,req.wait_type
,req.wait_resource
,t.text
from sys.dm_exec_sessions s
inner join sys.dm_exec_requests req on s.Session_id=req.session_id
cross apply sys.dm_exec_sql_text(sql_handle) t
where program_name='SMS_data_replication_service'
Check sessions asking too much memory
SELECT * FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC
Check sessions taking too many locks
SELECT TOP 10 request_session_id,
program_name = (SELECT program_name FROM sys.dm_exec_sessions WHERE session_id=request_session_id),
COUNT (*) num_locks
FROM sys.dm_tran_locks
GROUP BY request_session_id
ORDER BY count (*) DESC