• Question

  • Hi,
    I have a cluster with 3 nodes: one Primary and two Secondaries. Two nodes have the same configuration (cpu, memory, sql server configurations) and they are used as primary (SQL1) and the first secondary (SQL2). When SQL2 is primary the server responds very good, instead when SQL1 is primary the response times are 2-10 times greater. I checked the wait times. In top, on the first position is a wait type named XDESTSVERMGR. The query associated with this wait type is bellow. 
    Could I have some documentation (link, pdf) or could you explain me what means: XDESTSVERMGR, which kind of operations are the cause of this wait type?

    Thank you

    --(@_msparam_0 nvarchar(4000))  
    SELECT *  
    INTO #tmpag_availability_groups  
    FROM master.sys.availability_groups  
    SELECT group_id,  
    INTO #tmpar_availability_replicas  
    FROM master.sys.availability_replicas 
    /* BEGIN ACTIVE SECTION (inserted by Ignite) */  
    SELECT group_id,  
    INTO #tmpar_availability_replica_states  
    FROM master.sys.dm_hadr_availability_replica_states 
    /* END ACTIVE SECTION (inserted by Ignite) */  
    SELECT replica_id, 
    INTO #tmpar_availability_replica_cluster_states  
    FROM master.sys.dm_hadr_availability_replica_cluster_states  
    SELECT arrc.replica_server_name,  
       COUNT(cm.member_name) AS node_count,  
       SUM(cm.member_state) AS member_state_sum,  
       SUM(cm.number_of_quorum_votes) AS quorum_vote_sum  
    INTO #tmpar_availability_replica_cluster_info  
       SELECT DISTINCT replica_server_name,  
       FROM master.sys.dm_hadr_availability_replica_cluster_nodes 
       ) AS arrc  
    LEFT OUTER JOIN master.sys.dm_hadr_cluster_members AS cm  
    ON UPPER(arrc.node_name) = UPPER(cm.member_name)  
    GROUP BY arrc.replica_server_name  
    SELECT *  
    INTO #tmpar_ags  
    FROM master.sys.dm_hadr_availability_group_states  
    SELECT ar.group_id,  
       ar.availability_mode, ( 
          WHEN UPPER(ags.primary_replica) = UPPER(ar.replica_server_name) THEN 1  
          ELSE 0  
       END) as role,  
    INTO #tmpar_availabilty_mode  
    FROM #tmpar_availability_replicas as ar  
    LEFT JOIN #tmpar_ags as ags  
    ON ags.group_id = ar.group_id  
    LEFT JOIN #tmpar_availability_replica_states as ars  
    ON ar.group_id = ars.group_id  
    AND ar.replica_id = ars.replica_id  
    SELECT am1.replica_id,  
       am1.role, ( 
          WHEN (am1.synchronization_health is null) THEN 3  
          ELSE am1.synchronization_health  
       END) as sync_state, ( 
          WHEN (am1.availability_mode is NULL)  
          OR (am3.availability_mode is NULL) THEN null  
          WHEN (am1.role = 1)                THEN 1  
          WHEN (am1.availability_mode = 0  
          OR am3.availability_mode = 0) THEN 0  
          ELSE 1  
       END) as effective_availability_mode  
    INTO #tmpar_replica_rollupstate  
    FROM #tmpar_availabilty_mode as am1  
       SELECT group_id,  
       FROM #tmpar_availabilty_mode as am2  
       WHERE am2.role = 1 
       ) as am3  
    ON am1.group_id = am3.group_id  
    DROP table #tmpar_availabilty_mode  
    DROP table #tmpar_ags  
    SELECT AR.replica_server_name AS [Name],  
       ISNULL(AR.availability_mode, 2) AS [AvailabilityMode],  
       ISNULL(AR.backup_priority, -1) AS [BackupPriority],  
       ISNULL(AR.primary_role_allow_connections, 4) AS [ConnectionModeInPrimaryRole],  
       ISNULL(AR.secondary_role_allow_connections, 3) AS [ConnectionModeInSecondaryRole],  
       ISNULL(arstates.connected_state, 2) AS [ConnectionState],  
       ISNULL(AR.create_date, 0) AS [CreateDate],  
       ISNULL(AR.modify_date, 0) AS [DateLastModified],  
       ISNULL(AR.endpoint_url, N'') AS [EndpointUrl],  
       ISNULL(AR.failover_mode, 2) AS [FailoverMode],  
       ISNULL(arcs.join_state, 99) AS [JoinState],  
       ISNULL(arstates.last_connect_error_description, N'') AS [LastConnectErrorDescription],  
       ISNULL(arstates.last_connect_error_number,-1) AS [LastConnectErrorNumber],  
       ISNULL(arstates.last_connect_error_timestamp, 0) AS [LastConnectErrorTimestamp],   
          WHEN arci.member_state_sum IS NULL  
          OR arci.node_count = 0                       THEN 3  
          WHEN arci.member_state_sum = 0               THEN 0  
          WHEN arci.member_state_sum < arci.node_count THEN 2  
          WHEN arci.member_state_sum = arci.node_count THEN 1  
          ELSE 3  
       END AS [MemberState],  
       ISNULL(arstates.operational_state, 6) AS [OperationalState],  
       suser_sname(AR.owner_sid) AS [Owner],  
       ISNULL(arci.quorum_vote_sum, -1) AS [QuorumVoteCount],  
       ISNULL(AR.read_only_routing_url, '') AS [ReadonlyRoutingConnectionUrl],  
       ISNULL(arstates.role, 3) AS [Role],  
       ISNULL(arstates.recovery_health, 2) AS [RollupRecoveryState], ( 
          WHEN arrollupstates.sync_state = 3 THEN 3  
          WHEN (arrollupstates.effective_availability_mode = 1  
          OR arrollupstates.role = 1)        THEN arrollupstates.sync_state  
          WHEN arrollupstates.sync_state = 2 THEN 1  
          ELSE 0  
       END) AS [RollupSynchronizationState],  
       ISNULL(AR.session_timeout, -1) AS [SessionTimeout],  
       AR.replica_id AS [UniqueId]  
    FROM #tmpag_availability_groups AS AG  
    INNER JOIN #tmpar_availability_replicas AS AR  
    ON (AR.replica_server_name IS NOT NULL)  
    AND (AR.group_id=AG.group_id)  
    LEFT OUTER JOIN #tmpar_availability_replica_states AS arstates  
    ON AR.replica_id = arstates.replica_id  
    LEFT OUTER JOIN #tmpar_availability_replica_cluster_states AS arcs  
    ON AR.replica_id = arcs.replica_id  
    LEFT OUTER JOIN #tmpar_availability_replica_cluster_info AS arci  
    ON UPPER(AR.replica_server_name) = UPPER(arci.replica_server_name)  
    LEFT OUTER JOIN #tmpar_replica_rollupstate AS arrollupstates  
    ON AR.replica_id = arrollupstates.replica_id  
    --WHERE (AG.name=@_msparam_0)  
    ORDER BY [Name] ASC   
    DROP TABLE #tmpar_availability_replicas  
    DROP TABLE #tmpar_availability_replica_states  
    DROP TABLE #tmpar_availability_replica_cluster_states  
    DROP TABLE #tmpar_availability_replica_cluster_info  
    DROP TABLE #tmpar_replica_rollupstate  
    DROP table #tmpag_availability_groups 

    Monday, September 8, 2014 2:55 PM

All replies

  • Hi,

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Sofiya Li

    Sofiya Li
    TechNet Community Support

    Wednesday, September 10, 2014 8:05 AM
  • Hi,

    I have done some research on this waittype. It was new in SQL Server 2012. However, it has not been documented officially yet and we will have it documented.

    For the specific issue you encountered, if the SPID waiting for this is below 50, it should not be related to the issue you encountered.

    It worthy to check profiler trace on both sides to see whether the same query was executed. If yes, you can compare the execution plan on both sides to see if they differ a lot.

    Best regards

    Thursday, September 11, 2014 2:14 AM
  • Thank you!

    For 4 times this waittype occurred/arrived in 'Top Wait'(on primary) when a backup was done on primary. 
    I obtained also an error in the backup process: Msg 0, Sev 0, State 1: Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000]

    Can be a connection between backup (full) and this waittype?

    Thank you!

    Thursday, September 11, 2014 2:17 PM
  • Hi,

    The error message does not provide much information. Moreover, I cannot link the backup process and waittype together since the waittype's meaning is unknown so far.

    For the provided error, you can check the system event log and application event log to see if there are some other related events.

    Best regards,

    Friday, September 12, 2014 8:54 AM
  • Thank you!
    Thursday, October 2, 2014 9:12 AM