Sql-server – Multi-insert batch executed remotely 5x slower than locally

Networkperformancesql server

I am inserting 30.000 rows into a table in one batch using INSERT .. VALUES statement for each row.

See the testing environment:

Table creation:

CREATE TABLE [dbo].[TestInsert](
    [Col1] [int] NOT NULL,
    [Col2] [varchar](16) NOT NULL,
    [Col3] [varchar](15) NOT NULL,
    [Col4] [int] NULL,
    [Col5] [datetime] NOT NULL,
    [Col6] [nvarchar](128) NOT NULL
)

Batch inserting rows:

INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491073,N'D0058A79AE',N'OCIP',51849,'20100823 10:02:04.683',N'TUVWXYZabcdefgh')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491074,N'D00559B4C4',N'OCIP',62488,'20100823 10:02:04.710',N'CDEFGHIJKLMNOPQ')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491075,N'D005AB75B6',N'OCIP',52836,'20100823 10:05:17.070',N'BCDEFGHIJKLMNOP')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491076,N'D0070B9F25',N'OCIP',62554,'20100825 08:03:08.260',N'BCDEFGHIJKLMNOP')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491077,N'D00753F2D7',N'OCIP',62554,'20100825 08:03:58.733',N'UVWXYZabcdefghi')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491078,N'D0070B979A',N'OCIP',62554,'20100825 08:04:09.917',N'STUVWXYZabcdefg')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491079,N'D0070B6F37',N'OCIP',62554,'20100825 08:04:21.043',N'CDEFGHIJKLMNOPQ')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491080,N'D0070B86F3',N'OCIP',62554,'20100825 08:05:28.460',N'GHIJKLMNOPQRSTU')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491081,N'D00708D1E1',N'OCIP',62554,'20100825 08:06:50.030',N'CDEFGHIJKLMNOPQ')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491082,N'D0070B7DFA',N'OCIP',62554,'20100825 08:11:13.507',N'VWXYZabcdefghij')
INSERT dbo.TestInsert(Col1,Col2,Col3,Col4,Col5,Col6)VALUES(146491083,N'D0070B7FCE',N'PRON_OCIP',62555,'20100825 09:13:26.563',N'XYZabcdefghijka')
...
more (30.000 rows)

You can download the whole batch here: https://filebin.net/zg499h4iv1m44z6v

The table has no indexes, constraints, foreign keys, triggers…nothing.

When I run the batch in SSMS locally, it takes 20 seconds and produces these wait stats:

enter image description here

But when I run it in SSMS from a remote computer over LAN, it takes 100 seconds (5x slower) with almost the same wait stats:

enter image description here

Please, mention there is no ASYNC_NETWORK_IO wait type.

SET NOCOUNT ON is not set intentionally!

Testing in other environment it works just fine with no big differences in time.

What could be the real source of the slowness? Why it's not captured by wait statistics?
Please be more specific than just saying „network infrastructure“.

Best Answer

So there's only one batch (SqlBatchCompleted) involved here, as I understand. Assuming that:

My guess is that the difference is because of (the lack of) the network stack.

The client network library default to try Shared Memory first. This doesn't "dip down" in the network stack. This is likely the one you end up with from the local SSMS.

But Shared Memory will of course not work from the remote machine. The next tried is TCP, which is likely the one you end up with from the remote SSMS (unless you either re-configured that client or re-configured SQL server - both pretty unlikely).

So you compare using a netlib which uses a shared memory area to one that travels the code path of the network stack in both directions and also has bandwidth and latency because of the network in between.

Things you can play with includes:

  • Force the TCP netlib when running locally (option in SSMS client connect dialog).
  • Play with various network packet sizes (also available in SSMS connection dialog).
  • Talk to your network people and try with jumbo frames and such things.
Related Question