2

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“.

6
  • Use a transaction to reduce the WRITELOG waits and retest. To account for the rest of the time look at the total scheduled time in sys.dm_exec_sessions for the session used, and the client stats in SSMS. – David Browne - Microsoft Nov 28 '20 at 12:11
  • Thanks, but the point is not the reduce WRITELOG waits. In fact I cannot change the way the data is inserted because it's from vendor application that way. Bud vendor says it's working good enough in other environments and he is right. I observe this problem just in certain infrastructure and I am seeking where the root cause is. – jericzech Nov 28 '20 at 12:17
  • There is 30.000 times table insert operator in the execution plan, no matter if executed locally or remotely – jericzech Nov 28 '20 at 15:31
  • 1
    @jericzech but how fast does it work when you set it on in SSMS and run your script remotely? – NikitaSerbskiy Dec 5 '20 at 16:17
  • Do you have a proxy or a WAF between your machine and the remote one? Are you also creating the empty table at start in both test? (I mean not inserting 30'000 records in a million record tables); Is the remote SQL-Server configured exactly the same as the local one? Is the remote DB instance used by other databases / systems. Those details are some aspects that can be a source of being slower. – рüффп Dec 8 '20 at 12:26
1

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.
1

I converted that file into a dynamic SQL batch, and it takes me 34sec just to send the batch to Azure SQL Database, and there's no wait stat that tracks that time. eg

    declare @sql nvarchar(max) = N'
    
    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'')
    . . .

But it only takes 2sec for me to download the batch script from a temp table.

It also could take more CPU time than in the local environment (especially if it's a VM, has TDE on, or has slower CPUs), but it looks like the upload probably accounts for most of the difference.

3
  • You mean uploading 4MB via LAN takes those 80s of difference? Not talking that while profiling, SQLBatch: StatementStarting is beeing invoked almost immediately after the start of the remote execution. – jericzech Nov 28 '20 at 20:56
  • 1
    I don't know exactly what's happening in your environment, but I was able to reproduce that uploading a very large batch over TDS produced significant elapsed time that was not accounted for in the session waits. – David Browne - Microsoft Nov 28 '20 at 21:03
  • Got it, thank you David! Unfortunately it's probably not the explanation for my environment as I said the batch is accepted quite fast by the instance. – jericzech Nov 28 '20 at 21:14
0

Given that you are inserting from app into db, there is no reason for async_io_wait as your db is not waiting for app to signal back to DB.

The question is what the enviroment looks like. I get you have no other option, but sending 30000 insets per row is going to be slow :/

With 80 seconds of overhead, that's 2ms per row extra overhead. Which by itself actually looks ok, but it adds up.

Questions:

  1. is the app sending each insert by itself or the whole file at once, i assume per row
  2. what is the enviroment like is the app on same environment as db or is there distance? Is the same layout for other enviroments

Suggestion: Start profiler, and monitor the commands as they come in. My bet would be delays there

2
  • Forget it's from the App please. The behavior is also from SSMS connected remotely to the MS SQL instance - as described. It is executed as one batch - one round-trip - one SQL: BatchCompleted row in the Profiler – jericzech Nov 28 '20 at 13:29
  • Best guess is to run full profiler, that should show what takes up time... – Vladislav Zalesak Nov 28 '20 at 16:07
0

I don't know about remote problem for this question. but I've other solution for give good feedback

Try this one method:

  1. Save your data by .CSV or .XLM

  2. Use this bulk insert like below

BULK INSERT [TableName]
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT='CSV');
  1. if you don't like number-2 using pipes to import data from a file

both of the document (number 2-3) for use other file type and etc in micro-soft document

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.