Rendimiento de SQLSQL performance

En una jerarquía de varios sitios, Configuration Manager usa la replicación de SQL para transferir datos entre sitios.In a multi-site hierarchy, Configuration Manager uses SQL replication to transfer data between sites. Para más información, consulte Replicación de la base de datos.For more information, see Database replication.

Use el siguiente diagrama para iniciar la solución de problemas de rendimiento de SQL que puede afectar al estado de la replicación:Use the following diagram to start troubleshooting SQL performance that can impact replication status:

Diagrama para solucionar problemas de rendimiento de SQL

ConsultasQueries

En este diagrama se usan las siguientes consultas:This diagram uses the following queries:

Asegurarse de que la tabla de seguimiento de cambios de SQL se ha limpiadoMake sure SQL 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'

Cambiar las sesiones actuales que controlan que los mensajes de SQL Service Broker están bloqueadosChange current sessions that handle SQL 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'

Comprobar las sesiones que solicitan demasiada memoriaCheck sessions asking too much memory

SELECT * FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC

Comprobar las sesiones que se bloquean demasiadoCheck 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

Vea tambiénSee also

Configuración de SQLSQL configuration