Why does a simple loop result in ASYNC_NETWORK_IO waits? The Next CEO of Stack OverflowSlow temp table drops in sql 2005Why is there ASYNC_NETWORK_IO wait type on Shared Memory connections?SPIDs stuck indefinitely in suspended stateGet specific result without using loopWhy do async_network_io wait types occur?Large Variation in Bulk Insert timeDoes dm_exec_procedure_stats.total_worker_time include signal waits?Why does a plan with FULL optimization show simple parameterization?Increased waits during checkpoint after upgrading to better storageSimple recursive CTE stuck in infinite loop
How can I prove that a state of equilibrium is unstable?
Find a path from s to t using as few red nodes as possible
Planeswalker Ability and Death Timing
My ex-girlfriend uses my Apple ID to login to her iPad, do I have to give her my Apple ID password to reset it?
What difference does it make matching a word with/without a trailing whitespace?
What is a typical Mizrachi Seder like?
Shortening a title without changing its meaning
How exploitable/balanced is this homebrew spell: Spell Permanency?
What does this strange code stamp on my passport mean?
Why was Sir Cadogan fired?
Why do we say “un seul M” and not “une seule M” even though M is a “consonne”?
My boss doesn't want me to have a side project
Gödel's incompleteness theorems - what are the religious implications?
Is it OK to decorate a log book cover?
Do I need to write [sic] when including a quotation with a number less than 10 that isn't written out?
Is it a bad idea to plug the other end of ESD strap to wall ground?
How can a day be of 24 hours?
Does Germany produce more waste than the US?
How does a dynamic QR code work?
Can Sri Krishna be called 'a person'?
Compensation for working overtime on Saturdays
Gauss' Posthumous Publications?
Prodigo = pro + ago?
Why did early computer designers eschew integers?
Why does a simple loop result in ASYNC_NETWORK_IO waits?
The Next CEO of Stack OverflowSlow temp table drops in sql 2005Why is there ASYNC_NETWORK_IO wait type on Shared Memory connections?SPIDs stuck indefinitely in suspended stateGet specific result without using loopWhy do async_network_io wait types occur?Large Variation in Bulk Insert timeDoes dm_exec_procedure_stats.total_worker_time include signal waits?Why does a plan with FULL optimization show simple parameterization?Increased waits during checkpoint after upgrading to better storageSimple recursive CTE stuck in infinite loop
The following T-SQL takes about 25 seconds on my machine with SSMS v17.9:
DECLARE @outer_loop INT = 0,
@big_string_for_u VARCHAR(8000);
SET NOCOUNT ON;
WHILE @outer_loop < 50000000
BEGIN
SET @big_string_for_u = 'ZZZZZZZZZZ';
SET @outer_loop = @outer_loop + 1;
END;
It accumulates 532 ms of ASYNC_NETWORK_IO
waits according to both sys.dm_exec_session_wait_stats
and sys.dm_os_wait_stats
. The total wait time increases as the number of loop iterations increases. Using the wait_completed
extended event I can see that the wait happens roughly every 43 ms with a few exceptions:
In addition, I can get the call stacks that occur right before the ASYNC_NETWORK_IO
wait:
sqldk.dll!SOS_DispatcherBase::GetTrack+0x7f6c
sqldk.dll!SOS_Scheduler::PromotePendingTask+0x204
sqldk.dll!SOS_Task::PostWait+0x5f
sqldk.dll!SOS_Scheduler::Suspend+0xb15
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf6af
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf44c
sqllang.dll!SNIPacketRelease+0xd63
sqllang.dll!SNIPacketRelease+0x2097
sqllang.dll!SNIPacketRelease+0x1f99
sqllang.dll!SNIPacketRelease+0x18fe
sqllang.dll!CAutoExecuteAsContext::Restore+0x52d
sqllang.dll!CSQLSource::Execute+0x151b
sqllang.dll!CSQLSource::Execute+0xe13
sqllang.dll!CSQLSource::Execute+0x474
sqllang.dll!SNIPacketRelease+0x165d
sqllang.dll!CValOdsRow::CValOdsRow+0xa92
sqllang.dll!CValOdsRow::CValOdsRow+0x883
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x15d
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x638
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x2ad
sqldk.dll!SystemThread::MakeMiniSOSThread+0xdf8
sqldk.dll!SystemThread::MakeMiniSOSThread+0xf00
sqldk.dll!SystemThread::MakeMiniSOSThread+0x667
sqldk.dll!SystemThread::MakeMiniSOSThread+0xbb9
Finally, I noticed that SSMS uses a surprising amount of CPU during the loop (about half a core on average). I'm unable to figure out what SSMS is doing during that time.
Why does a simple loop cause ASYNC_NETWORK_IO
waits when executed through SSMS? The only output that I appear to get from the client from this query execution is the "Commands completed successfully." message.
sql-server ssms
add a comment |
The following T-SQL takes about 25 seconds on my machine with SSMS v17.9:
DECLARE @outer_loop INT = 0,
@big_string_for_u VARCHAR(8000);
SET NOCOUNT ON;
WHILE @outer_loop < 50000000
BEGIN
SET @big_string_for_u = 'ZZZZZZZZZZ';
SET @outer_loop = @outer_loop + 1;
END;
It accumulates 532 ms of ASYNC_NETWORK_IO
waits according to both sys.dm_exec_session_wait_stats
and sys.dm_os_wait_stats
. The total wait time increases as the number of loop iterations increases. Using the wait_completed
extended event I can see that the wait happens roughly every 43 ms with a few exceptions:
In addition, I can get the call stacks that occur right before the ASYNC_NETWORK_IO
wait:
sqldk.dll!SOS_DispatcherBase::GetTrack+0x7f6c
sqldk.dll!SOS_Scheduler::PromotePendingTask+0x204
sqldk.dll!SOS_Task::PostWait+0x5f
sqldk.dll!SOS_Scheduler::Suspend+0xb15
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf6af
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf44c
sqllang.dll!SNIPacketRelease+0xd63
sqllang.dll!SNIPacketRelease+0x2097
sqllang.dll!SNIPacketRelease+0x1f99
sqllang.dll!SNIPacketRelease+0x18fe
sqllang.dll!CAutoExecuteAsContext::Restore+0x52d
sqllang.dll!CSQLSource::Execute+0x151b
sqllang.dll!CSQLSource::Execute+0xe13
sqllang.dll!CSQLSource::Execute+0x474
sqllang.dll!SNIPacketRelease+0x165d
sqllang.dll!CValOdsRow::CValOdsRow+0xa92
sqllang.dll!CValOdsRow::CValOdsRow+0x883
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x15d
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x638
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x2ad
sqldk.dll!SystemThread::MakeMiniSOSThread+0xdf8
sqldk.dll!SystemThread::MakeMiniSOSThread+0xf00
sqldk.dll!SystemThread::MakeMiniSOSThread+0x667
sqldk.dll!SystemThread::MakeMiniSOSThread+0xbb9
Finally, I noticed that SSMS uses a surprising amount of CPU during the loop (about half a core on average). I'm unable to figure out what SSMS is doing during that time.
Why does a simple loop cause ASYNC_NETWORK_IO
waits when executed through SSMS? The only output that I appear to get from the client from this query execution is the "Commands completed successfully." message.
sql-server ssms
add a comment |
The following T-SQL takes about 25 seconds on my machine with SSMS v17.9:
DECLARE @outer_loop INT = 0,
@big_string_for_u VARCHAR(8000);
SET NOCOUNT ON;
WHILE @outer_loop < 50000000
BEGIN
SET @big_string_for_u = 'ZZZZZZZZZZ';
SET @outer_loop = @outer_loop + 1;
END;
It accumulates 532 ms of ASYNC_NETWORK_IO
waits according to both sys.dm_exec_session_wait_stats
and sys.dm_os_wait_stats
. The total wait time increases as the number of loop iterations increases. Using the wait_completed
extended event I can see that the wait happens roughly every 43 ms with a few exceptions:
In addition, I can get the call stacks that occur right before the ASYNC_NETWORK_IO
wait:
sqldk.dll!SOS_DispatcherBase::GetTrack+0x7f6c
sqldk.dll!SOS_Scheduler::PromotePendingTask+0x204
sqldk.dll!SOS_Task::PostWait+0x5f
sqldk.dll!SOS_Scheduler::Suspend+0xb15
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf6af
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf44c
sqllang.dll!SNIPacketRelease+0xd63
sqllang.dll!SNIPacketRelease+0x2097
sqllang.dll!SNIPacketRelease+0x1f99
sqllang.dll!SNIPacketRelease+0x18fe
sqllang.dll!CAutoExecuteAsContext::Restore+0x52d
sqllang.dll!CSQLSource::Execute+0x151b
sqllang.dll!CSQLSource::Execute+0xe13
sqllang.dll!CSQLSource::Execute+0x474
sqllang.dll!SNIPacketRelease+0x165d
sqllang.dll!CValOdsRow::CValOdsRow+0xa92
sqllang.dll!CValOdsRow::CValOdsRow+0x883
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x15d
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x638
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x2ad
sqldk.dll!SystemThread::MakeMiniSOSThread+0xdf8
sqldk.dll!SystemThread::MakeMiniSOSThread+0xf00
sqldk.dll!SystemThread::MakeMiniSOSThread+0x667
sqldk.dll!SystemThread::MakeMiniSOSThread+0xbb9
Finally, I noticed that SSMS uses a surprising amount of CPU during the loop (about half a core on average). I'm unable to figure out what SSMS is doing during that time.
Why does a simple loop cause ASYNC_NETWORK_IO
waits when executed through SSMS? The only output that I appear to get from the client from this query execution is the "Commands completed successfully." message.
sql-server ssms
The following T-SQL takes about 25 seconds on my machine with SSMS v17.9:
DECLARE @outer_loop INT = 0,
@big_string_for_u VARCHAR(8000);
SET NOCOUNT ON;
WHILE @outer_loop < 50000000
BEGIN
SET @big_string_for_u = 'ZZZZZZZZZZ';
SET @outer_loop = @outer_loop + 1;
END;
It accumulates 532 ms of ASYNC_NETWORK_IO
waits according to both sys.dm_exec_session_wait_stats
and sys.dm_os_wait_stats
. The total wait time increases as the number of loop iterations increases. Using the wait_completed
extended event I can see that the wait happens roughly every 43 ms with a few exceptions:
In addition, I can get the call stacks that occur right before the ASYNC_NETWORK_IO
wait:
sqldk.dll!SOS_DispatcherBase::GetTrack+0x7f6c
sqldk.dll!SOS_Scheduler::PromotePendingTask+0x204
sqldk.dll!SOS_Task::PostWait+0x5f
sqldk.dll!SOS_Scheduler::Suspend+0xb15
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf6af
sqllang.dll!CSECCNGProvider::GetBCryptHandleFromAlgID+0xf44c
sqllang.dll!SNIPacketRelease+0xd63
sqllang.dll!SNIPacketRelease+0x2097
sqllang.dll!SNIPacketRelease+0x1f99
sqllang.dll!SNIPacketRelease+0x18fe
sqllang.dll!CAutoExecuteAsContext::Restore+0x52d
sqllang.dll!CSQLSource::Execute+0x151b
sqllang.dll!CSQLSource::Execute+0xe13
sqllang.dll!CSQLSource::Execute+0x474
sqllang.dll!SNIPacketRelease+0x165d
sqllang.dll!CValOdsRow::CValOdsRow+0xa92
sqllang.dll!CValOdsRow::CValOdsRow+0x883
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x15d
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x638
sqldk.dll!ClockHand::Statistic::RecordClockHandStats+0x2ad
sqldk.dll!SystemThread::MakeMiniSOSThread+0xdf8
sqldk.dll!SystemThread::MakeMiniSOSThread+0xf00
sqldk.dll!SystemThread::MakeMiniSOSThread+0x667
sqldk.dll!SystemThread::MakeMiniSOSThread+0xbb9
Finally, I noticed that SSMS uses a surprising amount of CPU during the loop (about half a core on average). I'm unable to figure out what SSMS is doing during that time.
Why does a simple loop cause ASYNC_NETWORK_IO
waits when executed through SSMS? The only output that I appear to get from the client from this query execution is the "Commands completed successfully." message.
sql-server ssms
sql-server ssms
asked Mar 22 at 4:21
Joe ObbishJoe Obbish
21.6k43190
21.6k43190
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The documentation for SET NOCOUNT
says:
SET NOCOUNT ON
prevents the sending ofDONE_IN_PROC
messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, settingSET NOCOUNT
toON
can provide a significant performance boost, because network traffic is greatly reduced.
You are not running the statements in a stored procedure, so SQL Server sends DONE
tokens (code 0xFD
) to indicate the completion status of each SQL statement. These messages are deferred, and sent asynchronously when the network packet is full. When the client does not consume network packets quickly enough, eventually the buffers fill up, and the operation becomes blocking for SQL Server, generating the ASYNC_NETWORK_IO
waits.
Note the DONE
tokens are different from DONEINPROC
(code 0xFF
) as the documentation notes:
A
DONE
token is returned for each SQL statement in the SQL batch except variable declarations.
For execution of SQL statements within stored procedures,
DONEPROC
andDONEINPROC
tokens are used in place ofDONE
tokens.
You will see a dramatic reduction in ASYNC_NETWORK_IO
waits using:
CREATE PROCEDURE #P AS
SET NOCOUNT ON;
DECLARE
@outer_loop integer = 0,
@big_string_for_u varchar(8000);
WHILE @outer_loop < 5000000
BEGIN
SET @big_string_for_u = 'ZZZZZZZZZZ';
SET @outer_loop = @outer_loop + 1;
END;
GO
EXECUTE dbo.#P;
You could also use sys.sp_executesql
to achieve the same result.
Example stack trace captured just as an ASYNC_NETWORK_IO
wait begins:
An example TDS packet as seen in the inline function sqllang!srv_completioncode_ex<1>
had the following 13 bytes:
fd 01 00 c1 00 01 00 00 00 00 00 00 00
Which decodes to:
- TokenType = 0xfd
DONE_TOKEN
- Status = 0x0001
DONE_MORE
- CurCmd = 0x00c1 (193)
- DoneRowCount = 0x00000001 (1)
Ultimately, the number of ASYNC_NETWORK_IO
waits depends on the client and driver, and what it does, if anything, with all the DONE
messages. Testing with a loop 1/10th of the size given in the question (5,000,000 loop iterations) I found SSMS ran for about 4 seconds with 200-300 ms of waits. sqlcmd
ran for 2-3 seconds with single digit ms waits; osql
around the same run time with around 10 ms of waits.
The worst client by far for this test was Azure Data Studio. It ran for almost 6 hours:
add a comment |
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232816%2fwhy-does-a-simple-loop-result-in-async-network-io-waits%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The documentation for SET NOCOUNT
says:
SET NOCOUNT ON
prevents the sending ofDONE_IN_PROC
messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, settingSET NOCOUNT
toON
can provide a significant performance boost, because network traffic is greatly reduced.
You are not running the statements in a stored procedure, so SQL Server sends DONE
tokens (code 0xFD
) to indicate the completion status of each SQL statement. These messages are deferred, and sent asynchronously when the network packet is full. When the client does not consume network packets quickly enough, eventually the buffers fill up, and the operation becomes blocking for SQL Server, generating the ASYNC_NETWORK_IO
waits.
Note the DONE
tokens are different from DONEINPROC
(code 0xFF
) as the documentation notes:
A
DONE
token is returned for each SQL statement in the SQL batch except variable declarations.
For execution of SQL statements within stored procedures,
DONEPROC
andDONEINPROC
tokens are used in place ofDONE
tokens.
You will see a dramatic reduction in ASYNC_NETWORK_IO
waits using:
CREATE PROCEDURE #P AS
SET NOCOUNT ON;
DECLARE
@outer_loop integer = 0,
@big_string_for_u varchar(8000);
WHILE @outer_loop < 5000000
BEGIN
SET @big_string_for_u = 'ZZZZZZZZZZ';
SET @outer_loop = @outer_loop + 1;
END;
GO
EXECUTE dbo.#P;
You could also use sys.sp_executesql
to achieve the same result.
Example stack trace captured just as an ASYNC_NETWORK_IO
wait begins:
An example TDS packet as seen in the inline function sqllang!srv_completioncode_ex<1>
had the following 13 bytes:
fd 01 00 c1 00 01 00 00 00 00 00 00 00
Which decodes to:
- TokenType = 0xfd
DONE_TOKEN
- Status = 0x0001
DONE_MORE
- CurCmd = 0x00c1 (193)
- DoneRowCount = 0x00000001 (1)
Ultimately, the number of ASYNC_NETWORK_IO
waits depends on the client and driver, and what it does, if anything, with all the DONE
messages. Testing with a loop 1/10th of the size given in the question (5,000,000 loop iterations) I found SSMS ran for about 4 seconds with 200-300 ms of waits. sqlcmd
ran for 2-3 seconds with single digit ms waits; osql
around the same run time with around 10 ms of waits.
The worst client by far for this test was Azure Data Studio. It ran for almost 6 hours:
add a comment |
The documentation for SET NOCOUNT
says:
SET NOCOUNT ON
prevents the sending ofDONE_IN_PROC
messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, settingSET NOCOUNT
toON
can provide a significant performance boost, because network traffic is greatly reduced.
You are not running the statements in a stored procedure, so SQL Server sends DONE
tokens (code 0xFD
) to indicate the completion status of each SQL statement. These messages are deferred, and sent asynchronously when the network packet is full. When the client does not consume network packets quickly enough, eventually the buffers fill up, and the operation becomes blocking for SQL Server, generating the ASYNC_NETWORK_IO
waits.
Note the DONE
tokens are different from DONEINPROC
(code 0xFF
) as the documentation notes:
A
DONE
token is returned for each SQL statement in the SQL batch except variable declarations.
For execution of SQL statements within stored procedures,
DONEPROC
andDONEINPROC
tokens are used in place ofDONE
tokens.
You will see a dramatic reduction in ASYNC_NETWORK_IO
waits using:
CREATE PROCEDURE #P AS
SET NOCOUNT ON;
DECLARE
@outer_loop integer = 0,
@big_string_for_u varchar(8000);
WHILE @outer_loop < 5000000
BEGIN
SET @big_string_for_u = 'ZZZZZZZZZZ';
SET @outer_loop = @outer_loop + 1;
END;
GO
EXECUTE dbo.#P;
You could also use sys.sp_executesql
to achieve the same result.
Example stack trace captured just as an ASYNC_NETWORK_IO
wait begins:
An example TDS packet as seen in the inline function sqllang!srv_completioncode_ex<1>
had the following 13 bytes:
fd 01 00 c1 00 01 00 00 00 00 00 00 00
Which decodes to:
- TokenType = 0xfd
DONE_TOKEN
- Status = 0x0001
DONE_MORE
- CurCmd = 0x00c1 (193)
- DoneRowCount = 0x00000001 (1)
Ultimately, the number of ASYNC_NETWORK_IO
waits depends on the client and driver, and what it does, if anything, with all the DONE
messages. Testing with a loop 1/10th of the size given in the question (5,000,000 loop iterations) I found SSMS ran for about 4 seconds with 200-300 ms of waits. sqlcmd
ran for 2-3 seconds with single digit ms waits; osql
around the same run time with around 10 ms of waits.
The worst client by far for this test was Azure Data Studio. It ran for almost 6 hours:
add a comment |
The documentation for SET NOCOUNT
says:
SET NOCOUNT ON
prevents the sending ofDONE_IN_PROC
messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, settingSET NOCOUNT
toON
can provide a significant performance boost, because network traffic is greatly reduced.
You are not running the statements in a stored procedure, so SQL Server sends DONE
tokens (code 0xFD
) to indicate the completion status of each SQL statement. These messages are deferred, and sent asynchronously when the network packet is full. When the client does not consume network packets quickly enough, eventually the buffers fill up, and the operation becomes blocking for SQL Server, generating the ASYNC_NETWORK_IO
waits.
Note the DONE
tokens are different from DONEINPROC
(code 0xFF
) as the documentation notes:
A
DONE
token is returned for each SQL statement in the SQL batch except variable declarations.
For execution of SQL statements within stored procedures,
DONEPROC
andDONEINPROC
tokens are used in place ofDONE
tokens.
You will see a dramatic reduction in ASYNC_NETWORK_IO
waits using:
CREATE PROCEDURE #P AS
SET NOCOUNT ON;
DECLARE
@outer_loop integer = 0,
@big_string_for_u varchar(8000);
WHILE @outer_loop < 5000000
BEGIN
SET @big_string_for_u = 'ZZZZZZZZZZ';
SET @outer_loop = @outer_loop + 1;
END;
GO
EXECUTE dbo.#P;
You could also use sys.sp_executesql
to achieve the same result.
Example stack trace captured just as an ASYNC_NETWORK_IO
wait begins:
An example TDS packet as seen in the inline function sqllang!srv_completioncode_ex<1>
had the following 13 bytes:
fd 01 00 c1 00 01 00 00 00 00 00 00 00
Which decodes to:
- TokenType = 0xfd
DONE_TOKEN
- Status = 0x0001
DONE_MORE
- CurCmd = 0x00c1 (193)
- DoneRowCount = 0x00000001 (1)
Ultimately, the number of ASYNC_NETWORK_IO
waits depends on the client and driver, and what it does, if anything, with all the DONE
messages. Testing with a loop 1/10th of the size given in the question (5,000,000 loop iterations) I found SSMS ran for about 4 seconds with 200-300 ms of waits. sqlcmd
ran for 2-3 seconds with single digit ms waits; osql
around the same run time with around 10 ms of waits.
The worst client by far for this test was Azure Data Studio. It ran for almost 6 hours:
The documentation for SET NOCOUNT
says:
SET NOCOUNT ON
prevents the sending ofDONE_IN_PROC
messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, settingSET NOCOUNT
toON
can provide a significant performance boost, because network traffic is greatly reduced.
You are not running the statements in a stored procedure, so SQL Server sends DONE
tokens (code 0xFD
) to indicate the completion status of each SQL statement. These messages are deferred, and sent asynchronously when the network packet is full. When the client does not consume network packets quickly enough, eventually the buffers fill up, and the operation becomes blocking for SQL Server, generating the ASYNC_NETWORK_IO
waits.
Note the DONE
tokens are different from DONEINPROC
(code 0xFF
) as the documentation notes:
A
DONE
token is returned for each SQL statement in the SQL batch except variable declarations.
For execution of SQL statements within stored procedures,
DONEPROC
andDONEINPROC
tokens are used in place ofDONE
tokens.
You will see a dramatic reduction in ASYNC_NETWORK_IO
waits using:
CREATE PROCEDURE #P AS
SET NOCOUNT ON;
DECLARE
@outer_loop integer = 0,
@big_string_for_u varchar(8000);
WHILE @outer_loop < 5000000
BEGIN
SET @big_string_for_u = 'ZZZZZZZZZZ';
SET @outer_loop = @outer_loop + 1;
END;
GO
EXECUTE dbo.#P;
You could also use sys.sp_executesql
to achieve the same result.
Example stack trace captured just as an ASYNC_NETWORK_IO
wait begins:
An example TDS packet as seen in the inline function sqllang!srv_completioncode_ex<1>
had the following 13 bytes:
fd 01 00 c1 00 01 00 00 00 00 00 00 00
Which decodes to:
- TokenType = 0xfd
DONE_TOKEN
- Status = 0x0001
DONE_MORE
- CurCmd = 0x00c1 (193)
- DoneRowCount = 0x00000001 (1)
Ultimately, the number of ASYNC_NETWORK_IO
waits depends on the client and driver, and what it does, if anything, with all the DONE
messages. Testing with a loop 1/10th of the size given in the question (5,000,000 loop iterations) I found SSMS ran for about 4 seconds with 200-300 ms of waits. sqlcmd
ran for 2-3 seconds with single digit ms waits; osql
around the same run time with around 10 ms of waits.
The worst client by far for this test was Azure Data Studio. It ran for almost 6 hours:
edited Mar 22 at 22:31
answered Mar 22 at 6:42
Paul White♦Paul White
54k14287459
54k14287459
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232816%2fwhy-does-a-simple-loop-result-in-async-network-io-waits%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown