1

Topic: How to define number of flows for Network I/O

Good afternoon! There Is a possibility to define how many SQL Server uses flows for network input/conclusion?
I have such sensation that one. At any loading on 10Gb  12,5 % there are more it is not used.
The same I watch if to simulate one flow c two asynchronous buffers through NTttcp.
NTttcp with 8 flows and 2 asynchronous buffers loads a network almost on 100 %.
In numbers it approximately Packets is accepted in  ~15000 for SQL and NTttcp with 1 flow and ~150000 for NTttcp with 8 flows.
The size of a packet 32 and for SQL too, in adjustments  dzhambo-frames = 9008.
Connection a point-point without routers. IPv4 and IPv6 without a difference. Autoselection of a window of reception is disconnected, etc.

2

Re: How to define number of flows for Network I/O

And backup in some flows?

BACKUP DATABASE [AdventureWorks]
TO
DISK = ' \\SERVER\BKP\AdventureWorks1.bak ',
DISK = ' \\SERVER\BKP\AdventureWorks2.bak ',
DISK = ' \\SERVER\BKP\AdventureWorks3.bak'
WITH FORMAT

3

Re: How to define number of flows for Network I/O

piy8f;
1 file - ~13000 packets/sek 258Mb/sek
2 files - ~15000 packets/sek 320Mb/sek
3 files - ~17000 packets/sek 380Mb/sek
4 files - ~19000 packets/sek 443Mb/sek
...
8 files - ~21000 packets/sek 480Mb/sek
But, the situation - the size of a packet 8 a little changed
20000 packets in  are the same of 12,5 %.
At a packet 32
18 files on 1 disk of ~22000 packets/sek 530Mb/sek
18 files on 3 disks of ~30000 packets/sek 660Mb/sek
25 % loading on a network saw only during the moment when went  on 3 disks and parallely there was a copying big .
All together gave nearby 40  packets in second

4

Re: How to define number of flows for Network I/O

ShIgor;
BACKUP DATABASE test TO
DISK = ' C:\Backups\test.bak'
WITH BUFFERCOUNT = 1024, MAXTRANSFERSIZE = 2097152;
BUFFERCOUNT, MAXTRANSFERSIZE try not by default if I will find the reference I will write there a script does  and then automatically draws the chart in excel depending on these parameters

5

Re: How to define number of flows for Network I/O

wrote:

ShIgor;
BACKUP DATABASE test TO
DISK = ' C:\Backups\test.bak'
WITH BUFFERCOUNT = 1024, MAXTRANSFERSIZE = 2097152;
BUFFERCOUNT, MAXTRANSFERSIZE try not by default if I will find the reference I will write there a script does  and then automatically draws the chart in excel depending on these parameters

BUFFERCOUNT, MAXTRANSFERSIZE, an amount of files

6

Re: How to define number of flows for Network I/O

The registrar;
I do not have purpose to accelerate

7

Re: How to define number of flows for Network I/O

ShIgor wrote:

the registrar;
I do not have purpose to accelerate

If  on LUN-> RAID that  an array with most is maximum  value

8

Re: How to define number of flows for Network I/O

ShIgor wrote:

the registrar;
I do not have purpose to accelerate

I in course but if  it will be accelerated it will use the big band of throughput LAN of that you and want to achieve?!

9

Re: How to define number of flows for Network I/O

Here a script for prorolling 100MB with SQL servers on itself.
It is possible to look as the intermediate layer of a software affects.

/*
EXEC master.dbo.sp_addlinkedserver @server = N'LOCALSERVER ', @srvproduct=N ", @provider=N'SQLNCLI ', @datasrc=N'lpc:(local) \SQL2017DEV ', @catalog=N'master'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LOCALSERVER ', @useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'LOCALSERVER ', @optname=N'rpc ', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LOCALSERVER ', @optname=N'rpc out ', @optvalue=N'true'
*/
SET NOCOUNT ON;
declare @dt datetime, @dif1 int, @dif2 int, @dif3 int;
if object_id (' tempdb..#netinfo ') is not null drop table #netinfo;
create table #netinfo ([val] varchar (max));
delete from #netinfo;
set @dt = getdate ();
-- Directly INSERT SELECT
INSERT INTO #netinfo ([val]) SELECT [val] = REPLICATE (cast (REPLICATE (cast (CHAR (55) as varchar (max)), 1024) as varchar (max)), 1024*100);
set @dif1 = DATEDIFF (ms,@dt,getdate ());
delete from #netinfo;
set @dt = getdate ();
-- Through INSERT EXEC AT [LOCALSERVER]
INSERT INTO #netinfo ([val]) EXEC (N'SET NOCOUNT ON; SELECT [val] = REPLICATE (cast (REPLICATE (cast (CHAR (55) as varchar (max)), 1024) as varchar (max)), 1024*100) ') AT [LOCALSERVER];
set @dif2 = DATEDIFF (ms,@dt,getdate ());
delete from #netinfo;
set @dt = getdate ();
-- Through INSERT SELECT FROM OpenQuery ([LOCALSERVER]...)
INSERT INTO #netinfo ([val]) SELECT [val] FROM OpenQuery ([LOCALSERVER], N'SET NOCOUNT ON; SELECT [val] = REPLICATE (cast (REPLICATE (cast (CHAR (55) as varchar (max)), 1024) as varchar (max)), 1024*100); ');
set @dif3 = DATEDIFF (ms,@dt,getdate ());
select [local ms] = @dif1, [AT ms] = @dif2, [OpenQuery ms] = @dif3;

10

Re: How to define number of flows for Network I/O

The registrar;
That I want to achieve:
There are 2 servers connected among themselves a direct lace in 10Gb. At any amount of simultaneously triggered requests from one server on another, from several different applications and-or to different bases, I watch as much as possible 12,5 % of loading of a network and more in any way. Simple copying of one file loads a network to 60-70 % even in one flow, transit velocity thus makes more 600Mb/second In case of SQL (without ) I would not see more 140Mb/sek how many requests (applications, connections, etc.) was not used. The same 140Mb/sek I see at testing of a network by program NTttcp in 8 flows, but on each flow!!!! And loading of a network of 100 %. From here I do an output that SQL or works in one flow, or simply stupidly does not understand that a grid not Gigabit, and 10.

11

Re: How to define number of flows for Network I/O

ShIgor wrote:

the registrar;
That I want to achieve:
There are 2 servers connected among themselves a direct lace in 10Gb. At any amount of simultaneously triggered requests from one server on another, from several different applications and-or to different bases, I watch as much as possible 12,5 % of loading of a network and more in any way. Simple copying of one file loads a network to 60-70 % even in one flow, transit velocity thus makes more 600Mb/second In case of SQL (without ) I would not see more 140Mb/sek how many requests (applications, connections, etc.) was not used. The same 140Mb/sek I see at testing of a network by program NTttcp in 8 flows, but on each flow!!!! And loading of a network of 100 %. From here I do an output that SQL or works in one flow, or simply stupidly does not understand that a grid not Gigabit, and 10.

It is improbable, but suddenly, maxdop=1 on the server by default? Try maxdop to specify in request

SELECT *
FROM dbo.test
OPTION (MAXDOP 2)

12

Re: How to define number of flows for Network I/O

ngrfm;
local ms, AT ms, OpenQuery ms
473,1723,1400
About what it speaks?

13

Re: How to define number of flows for Network I/O

The registrar;
MAXDOP 2 by default and a threshold 120

14

Re: How to define number of flows for Network I/O

ShIgor wrote:

the registrar;
MAXDOP 2 by default and a threshold 120

Try to increase to an amount of kernels of the processor (in reasonable limits if it  the server :-))

15

Re: How to define number of flows for Network I/O

The registrar;
In test request (instead of on the server by default)

16

Re: How to define number of flows for Network I/O

The registrar;
On the server by default.
Tried to increase result 0. And from what suddenly?  is for one specific request, it is rough:  request execution inside, but transmission all on one connection.
I can launch both 1, and 1000 simultaneously requests, with  and without - the network all will be used a maximum on 12,5 %.
Naturally in case of one request - loadings and 0,5 % are not present...
Somebody can show to me that at it not so?

17

Re: How to define number of flows for Network I/O

ShIgor;
If at you 1000 requests for that they wait are simultaneously fulfilled? Permanently ASYNC_NETWORK_IO?
At usage NTttcp with several flows you use at once some ports (Each thread creates a logical processor that connects to (listens) a different port ).
In Sql Server one port and accordingly one listening flow is used. It is possible for  to add still port in a configuration.

18

Re: How to define number of flows for Network I/O

ShIgor wrote:

At any loading on 10Gb  12,5 % there are more it is not used.
The same I watch if to simulate one flow c two asynchronous buffers through NTttcp

By the way, you are assured, what this normal behavior for NTttcp, what at usage of 1 flow \port it loads a network only on 12.5 %?
Came across in other arguing (truth, there there was 1Gb a card) that already 1 flow should load a network almost completely. Remaining flows only approach loading to 100 % more strongly.

19

Re: How to define number of flows for Network I/O

Eleanor;
Besides I am declined.
Adjusted SoftNUMA from 4  (4 on 6  in everyone),
On everyone assigned the port. It is necessary to test.

20

Re: How to define number of flows for Network I/O

ShIgor;
Can be QoS influences a flow or any network ?