SQL server performance over long distance

Question

Is it reasonable to expect decent performance from a SQL server connected via fiber private WAN - 5000 km's away. The carrier is telling us that this network is the 'best' possible, and will introduce less than 50ms latency guaranteed.

Our database is currently connected via gigabit ethernet to our other machines within the same datacenter. Getting a 5000km 'LAN Extension' between the two DC's is appealing to us. But when we connect via the Internet and VPN, we find the performance very slow doing large volumes of small transactions. We don't have any WAN experience, so not sure what most companies do in these cases. Can we simulate 5000kms of 'latency'?

Answer 1

5000Kms at the speed of light would be ~17ms latency. Down optical fibre, more like 25ms. And that's a best case server-to-server fibre with no delays for media conversion, switching, routing, server response, etc.

Minimum is 25ms, guaranteed maximum 50ms...

yes there are tools to simulate a slow link, e.g.

http://jagt.github.io/clumsy/ - a Windows program for network traffic changing

http://wanem.sourceforge.net/ - a Linux LiveCD for WAN emulation

and lots of suggestions https://stackoverflow.com/questions/130354/how-do-i-simulate-a-low-bandwidth-high-latency-environment and https://stackoverflow.com/questions/1094760/network-tools-that-simulate-slow-network-connection

Answer 2

Compare sys.dm_os_wait_stats polls between a period of simulated high network latency and an identical period of normal network latency (for the same client load and the same database statistics). For example, the following script can be used to compare [wait_rate (ms/Sec)] for the 'ASYNC_NETWORK_IO' [wait_type]. Differences measured in X to low XX % are likely to under the influence of background noise (i.e. they're meaningless). Differences measured in XXX% are likely to be relevant. To give you a qualitative understanding about the relative importance of the wait_rates you will see: If your SQL Server were to have 500 active worker threads, and if all 500 of those threads were waiting upon just one wait_type (for some given period of time), then the [wait_rate (ms/Sec)] for that one [wait_type] would be 500,000 ms/sec. I typically ignore [wait_rate (ms/Sec)]s that are less than 1000 ms/Sec.

The following script was written to collect data for a 20 second period of time. This can be adjusted (per your testing needs). The script does not aggregate wait_types into categories (for example, this script does not lump all PAGEIO% waits together).

DECLARE @hhmmssDelay CHAR(8) = '00:00:20'

SET NOCOUNT ON

CREATE TABLE 
    #ignored
    (
    wait_type NVARCHAR(60) PRIMARY KEY
    )
INSERT
    #ignored
SELECT 'BAD_PAGE_PROCESS'
UNION SELECT 'BROKER_EVENTHANDLER'
UNION SELECT 'BROKER_RECEIVE_WAITFOR'
UNION SELECT 'BROKER_TASK_STOP'
UNION SELECT 'BROKER_TO_FLUSH'
UNION SELECT 'BROKER_TRANSMITTER'
UNION SELECT 'CHECKPOINT_QUEUE'
UNION SELECT 'CLR_AUTO_EVENT'
UNION SELECT 'CLR_MANUAL_EVENT'
UNION SELECT 'DBMIRROR_EVENTS_QUEUE'
UNION SELECT 'DISPATCHER_QUEUE_SEMAPHORE'
UNION SELECT 'FT_IFTS_SCHEDULER_IDLE_WAIT'
UNION SELECT 'FT_IFTSHC_MUTEX'
UNION SELECT 'KSOURCE_WAKEUP'
UNION SELECT 'LAZYWRITER_SLEEP'
UNION SELECT 'LOGMGR_QUEUE'
UNION SELECT 'ONDEMAND_TASK_QUEUE'
UNION SELECT 'PREEMPTIVE_OS_AUTHENTICATIONOPS'
UNION SELECT 'PREEMPTIVE_OS_GETPROCADDRESS'
UNION SELECT 'REQUEST_FOR_DEADLOCK_SEARCH'
UNION SELECT 'RESOURCE_QUEUE'
UNION SELECT 'SLEEP_BPOOL_FLUSH'
UNION SELECT 'SLEEP_SYSTEMTASK'
UNION SELECT 'SLEEP_TASK' 
UNION SELECT 'SQLTRACE_BUFFER_FLUSH'
UNION SELECT 'WAITFOR'
UNION SELECT 'XE_DISPATCHER_JOIN'
UNION SELECT 'XE_DISPATCHER_WAIT'
UNION SELECT 'XE_TIMER_EVENT'
UNION SELECT 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
UNION SELECT 'DIRTY_PAGE_POLL'
UNION SELECT 'HADR_FILESTREAM_IOMGR_IOCOMPLETION'  -- May be useful for  AlwaysON (AKA Hadron)

SELECT 
    GETDATE() 'Poll'
    , ows.*
INTO 
    #os_wait_stats  
FROM 
    sys.dm_os_wait_stats ows
LEFT JOIN
    #ignored i
ON
    i.wait_type = ows.wait_type
WHERE
    i.wait_type IS NULL


WAITFOR DELAY @hhmmssDelay

INSERT
    #os_wait_stats 
SELECT 
    GETDATE() 'Poll', ows.*
FROM 
    sys.dm_os_wait_stats ows
LEFT JOIN
    #ignored i
ON
    i.wait_type = ows.wait_type
WHERE
    i.wait_type IS NULL

DECLARE @SecondsBetweenPolls INT
SELECT 
    @SecondsBetweenPolls=DATEDIFF(SECOND,MIN(Poll),MAX(Poll))
FROM
    #os_wait_stats

DECLARE @SecondsSinceSQLServerStarted BIGINT
DECLARE @LastPoll DATETIME
SELECT 
    @LastPoll=MAX(Poll)
FROM
    #os_wait_stats

SELECT 
    @SecondsSinceSQLServerStarted =DATEDIFF(SECOND, sqlserver_start_time, @LastPoll)
FROM sys.dm_os_sys_info


SELECT
    CONVERT(NUMERIC(38,0),(Later.wait_time_ms-Earlier.wait_time_ms))/CONVERT(NUMERIC(38,0),@SecondsBetweenPolls) 'wait_rate (ms/Sec)'
    ,Later.wait_type
    ,Later.waiting_tasks_count-Earlier.waiting_tasks_count 'waiting_tasks_count'
    ,Later.signal_wait_time_ms-Earlier.signal_wait_time_ms 'signal_wait_time_ms' 
    ,Later.wait_time_ms-Earlier.wait_time_ms 'wait_time_ms'
    ,CONVERT(NUMERIC(38,0),(Later.wait_time_ms))/CONVERT(NUMERIC(38,0),@SecondsSinceSQLServerStarted)'cumulative_wait_rate (ms/Sec)'
    ,Later.wait_time_ms 'cumulative_wait_time_ms'
    ,Earlier.Poll 'earlier_poll'
    ,Later.Poll 'later_poll'
    ,Earlier.max_wait_time_ms 'earlier_max_wait_time_ms'
    ,Later.max_wait_time_ms 'later_max_wait_time_ms'

    FROM    
    #os_wait_stats Later
JOIN
    (
    SELECT
        wait_type
        ,wait_time_ms
        ,waiting_tasks_count
        ,signal_wait_time_ms
        ,max_wait_time_ms
        ,Poll
    FROM
        #os_wait_stats
    WHERE
        Poll = 
            (
            SELECT 
                MIN(Poll) 
            FROM 
                #os_wait_stats
            )
    ) as Earlier
ON
    Earlier.wait_type = Later.wait_type
WHERE
    Later.wait_time_ms-Earlier.wait_time_ms > 0
ORDER BY
    CONVERT(NUMERIC(38,0),(Later.wait_time_ms-Earlier.wait_time_ms))/CONVERT(NUMERIC(38,0),@SecondsBetweenPolls) -- 'wait_rate (ms/Sec)'
DESC

GO
DROP TABLE #os_wait_stats
GO
DROP TABLE #ignored