2 added 62 characters in body
source | link

We have sql server 2014 alwayson availability group set up for a database. Synchronous-commit availability mode is used. We ran a few PAL (Performance Analyzer Log) reports to obtain performance data on the primary replica and they showed IO alerts where read and writes were >25ms. The IO alerts occurred for both logical and physical IOs and on drives C:, F: (that has db files) and L: (that has transaction logs). I ran this statement:

select wait_type, waiting_tasks_count, wait_time_ms, wait_time_ms/waiting_tasks_count as 'time_per_wait' from sys.dm_os_wait_stats where waiting_tasks_count >0 and wait_type = 'HADR_SYNC_COMMIT';

SELECT 
    wait_type, waiting_tasks_count, wait_time_ms,
    wait_time_ms/waiting_tasks_count as 'time_per_wait'
FROM 
    sys.dm_os_wait_stats 
WHERE 
    waiting_tasks_count > 0
    and wait_type = 'HADR_SYNC_COMMIT';

and got 12ms for the time_per_waittime_per_wait value. This means the latency between the primary and secondary replicas were 12 ms.

My question: does the IO response time reported by PAL include this 12ms latency between the primary and the secondary replicas?

Thanks

We have sql server 2014 alwayson availability group set up for a database. Synchronous-commit availability mode is used. We ran a few PAL (Performance Analyzer Log) reports to obtain performance data on the primary replica and they showed IO alerts where read and writes were >25ms. The IO alerts occurred for both logical and physical IOs and on drives C:, F: (that has db files) and L: (that has transaction logs). I ran this statement:

select wait_type, waiting_tasks_count, wait_time_ms, wait_time_ms/waiting_tasks_count as 'time_per_wait' from sys.dm_os_wait_stats where waiting_tasks_count >0 and wait_type = 'HADR_SYNC_COMMIT';

and got 12ms for the time_per_wait value. This means the latency between the primary and secondary replicas were 12 ms.

My question: does the IO response time reported by PAL include this 12ms latency between the primary and the secondary replicas?

Thanks

We have sql server 2014 alwayson availability group set up for a database. Synchronous-commit availability mode is used. We ran a few PAL (Performance Analyzer Log) reports to obtain performance data on the primary replica and they showed IO alerts where read and writes were >25ms. The IO alerts occurred for both logical and physical IOs and on drives C:, F: (that has db files) and L: (that has transaction logs). I ran this statement:

SELECT 
    wait_type, waiting_tasks_count, wait_time_ms,
    wait_time_ms/waiting_tasks_count as 'time_per_wait'
FROM 
    sys.dm_os_wait_stats 
WHERE 
    waiting_tasks_count > 0
    and wait_type = 'HADR_SYNC_COMMIT';

and got 12ms for the time_per_wait value. This means the latency between the primary and secondary replicas were 12 ms.

My question: does the IO response time reported by PAL include this 12ms latency between the primary and the secondary replicas?

Thanks

    Bumped by Community user
    Bumped by Community user
1
source | link

SQL Alwayson availabity group latency

We have sql server 2014 alwayson availability group set up for a database. Synchronous-commit availability mode is used. We ran a few PAL (Performance Analyzer Log) reports to obtain performance data on the primary replica and they showed IO alerts where read and writes were >25ms. The IO alerts occurred for both logical and physical IOs and on drives C:, F: (that has db files) and L: (that has transaction logs). I ran this statement:

select wait_type, waiting_tasks_count, wait_time_ms, wait_time_ms/waiting_tasks_count as 'time_per_wait' from sys.dm_os_wait_stats where waiting_tasks_count >0 and wait_type = 'HADR_SYNC_COMMIT';

and got 12ms for the time_per_wait value. This means the latency between the primary and secondary replicas were 12 ms.

My question: does the IO response time reported by PAL include this 12ms latency between the primary and the secondary replicas?

Thanks