Why does a simple loop result in ASYNC_NETWORK_IO waits?
PTIJ: Why is Haman obsessed with Bose?
Did the UK lift the requirement for registering SIM cards?
What is going on with gets(stdin) on the site coderbyte?
How to convince somebody that he is fit for something else, but not this job?
What is Cash Advance APR?
Is there a way to have vectors outlined in a Vector Plot?
How do you make your own symbol when Detexify fails?
"It doesn't matter" or "it won't matter"?
Taxes on Dividends in a Roth IRA
Why do ¬, ∀ and ∃ have the same precedence?
How to draw a matrix with arrows in limited space
awk assign to multiple variables at once
Giving feedback to someone without sounding prejudiced
Is this toilet slogan correct usage of the English language?
Can I cause damage to electrical appliances by unplugging them when they are turned on?
Mimic lecturing on blackboard, facing audience
Why should universal income be universal?
How would you translate "more" for use as an interface button?
How does electrical safety system work on ISS?
Shouldn’t conservatives embrace universal basic income?
What does "Scientists rise up against statistical significance" mean? (Comment in Nature)
15% tax on $7.5k earnings. Is that right?
Why is it that I can sometimes guess the next note?
A variation to the phrase "hanging over my shoulders"
Why does a simple loop result in ASYNC_NETWORK_IO waits?
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_IOwaits 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
asked 1 min ago
Joe ObbishJoe Obbish
21.3k43187
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_IOwaits 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
asked 1 min ago
Joe ObbishJoe Obbish
21.3k43187
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_IOwaits 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
asked 1 min ago
Joe ObbishJoe Obbish
21.3k43187
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_IOwaits 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 1 min ago
Joe ObbishJoe Obbish
21.3k43187
asked 1 min ago
Joe ObbishJoe Obbish
21.3k43187
asked 1 min ago
Joe ObbishJoe Obbish
21.3k43187
asked 1 min ago
Joe ObbishJoe Obbish
21.3k43187
asked 1 min ago
Joe ObbishJoe Obbish
21.3k43187
21.3k43187
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
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
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
