ASYNC_NETWORK_IO wait type

We had an issue yesterday where a mess of async_network_io wait types started
showing up in activity monitor (sql 2005). It turns out the optimizer was
picking a bad execution plan for a certain report stored procedure and it was
taking much longer than normal. We've fixed that issue, but my question was
more of why we were seeing that wait type. My understanding of
async_network_io is it indicates that SQL Server is waiting on sending data
back to the client. It looks like stored procedure was taking long enough for
the client app to time out. Eventually when the procedure finished, the
connection just hangs with a wait type of async_network_io until I explicitly
issue a Kill. Is this behavior by design? It used up quite a few connections.
Thanks in advance.Perhaps try adding WITH RECOMPILE to the sproc to ensure it gets appropriate
plan for all parameter combinations. Perhaps update statistics too, and
check indexes for fragmentation.
--
TheSQLGuru
President
Indicium Resources, Inc.
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:352EC2C2-67DD-4C14-A9AB-58C4BE18FC80@.microsoft.com...
> We had an issue yesterday where a mess of async_network_io wait types
> started
> showing up in activity monitor (sql 2005). It turns out the optimizer was
> picking a bad execution plan for a certain report stored procedure and it
> was
> taking much longer than normal. We've fixed that issue, but my question
> was
> more of why we were seeing that wait type. My understanding of
> async_network_io is it indicates that SQL Server is waiting on sending
> data
> back to the client. It looks like stored procedure was taking long enough
> for
> the client app to time out. Eventually when the procedure finished, the
> connection just hangs with a wait type of async_network_io until I
> explicitly
> issue a Kill. Is this behavior by design? It used up quite a few
> connections.
> Thanks in advance.

async_network_io wait during replication

I have re-initialize some subscription. After generated snapshot successfully, the synchronization view showing 'The process is running and is waiting for a response from the server.' and I found the replication process is under 'async_network_io wait'. The job has run for more than 8 hrs. Any idea to improve it? Thanks in advaise.

Are you running the sync agent (like merge agent/distribution agent) in continuous mode? If yes, it will show running forever, although the submitted transactions/commands have been replicated, agent job is just keeping waiting for next actions.

Thanks

-Yunjing

|||Yes, in continuous mode. But the replication hadn't completed in 8 hrs. Any idea? Thanks|||

If an continuous sync complete for a session, its current history log usually shows something like "No replicated transactions are available" and sync job is still showing as running.

As for your case, could you open sqlmonitor to check the current status for the job, is it still running or fail already? If it's running, what's the current history shown? Does the history get updated every a few minutes?

-Yunjing

Asterix not showing in report details

I have a report that shows a header, details and a footer

The detail line never prints lines with asterixes in them, e.g.

"**blah blah**"

As soon as I delete the asterix from the database, the line appears on the report

Any ideas anyone?

to clarify

are you saying:

you have a value in the database that contains the text "** blah blah **"

when you run the report, only "blah blah" is rendered in the row and not "** blah blah **"

?|||

It does not print the line at all, also additionally the problem seems to be caused only by the asterix at the start of the line

so when I would expect the report to show:

Header

detail1

detail2

*blah blah

detai*l4

Footer

The report actually omits the line "*blah blah" and prints this instead:

Header

detail1

detail2

detai*l4

Footer

|||

Interesting... but I am not able to repro this in a simple case (ie an expression such as ="****" prints fine).

So here are some questions:

* -- does the disappearing line occur in all output targets? Or only some (such as HTML versus Excel)?

* -- what does the expression for your detail column look like?

>L<

|||

To reproduce this you will have to query an sql database

The detail row of the table contains:

=Last(Fields!Case_SolutionNote.Value)

|||

no, I still can't repro it. I tried a simple version (without the Last) and also one with a Last() function. I could see it fine.

What else do I have to know about your Case_SolutionNote field? What type is it? How long are the typical values? Can you post some example values (with and without * character)?

>L<

|||

Interestingly, I also get a problem with the report's group footer section, it sometimes does not print at all either, I have not worked out why yet, but I suspect it is the presence of non alphanumeric characters in the relevant database field (which is an ntext data type)

I copied the contents of the field to notepad, deleted the contents of the field from the database, then used the 'update' statement (due to bug that prevents editing of ntext cells sometimes in microsoft sql server management studio) to repopulate it with a copy of the original text from notepad, it then shows on the report

I think there is a weakness somewhere in ntext data types in sql or reporting services relating to non alphanumeric characters

|||

Oh, also...

When I delete the report's group header/footer, leaving just a basic report with detail lines, the original problem dissappears - lines with asterixes at the beginning of them appear!!

ie the report prints correctly, e.g:

detail1

detail2

*blah blah

detai*l4

also...

if i have a group header/footer present, the group footer will print lines starting with asterixes but the detail lines do not

|||

When you copy the value of the field to Notepad and back, and then using the UPDATE statement you may not be preserving the Unicode characteristics of the string. It's hard to tell, because the clipboard buffer, the way you've stated the UPDATE, and the way the interactive query-handling facility in Studio would all be involved in interpreting that data <s>.

Point is, it is not a fair test...

>L<

|||

I wish I could be absolutely sure that your report logic isn't doing something to influence is -- forgive me, I don't mean to doubt you -- it's just that you have the Last() function in there....

IOW, I can't tell whether the lines you're expecting are missing because of what you've pinpointed (the asterisk) or some other factor, such as the sort/group/whatever changing what Last() decides should come up.

Without repro'ing more of your exact scenario I can't debug, reproduce your error.. or otherwise offer advice... sorry

>L<

|||

Hi thanks for you help

Regarding the second problem where the group footer was not displaying sometimes, I have worked around this by adding a space to the beginning of the data on the fly in the select statement in the report - note this only works in testing on sql server 2005 (unfortunately varchar(max) does not work in sql server 2000 where my database lives! )

ie i changed this:

select....., CaseProgress.Case_SolutionNote, .......

to

select....., (cast(' ' as varchar(max)) + cast(CaseProgress.Case_SolutionNote as varchar(max))) as Case_SolutionNote, .......

What this does is to populate the field if it was null in any rows - bear in mind i am using Last!(CaseProgress.Case_SolutionNote) and the last row is usually not empty but was not printing if prior rows had a 'null' in the field - wierd

The original problem with the aterix still stands

My hunch about alphanumeric characters was a red-herring i think

|||

Actually a slightly better workaround for the second problem is:

select..,isnull(CaseProgress.Case_SolutionNote, ' ') Case_SolutionNote,...

|||

>>The original problem with the aterix still stands

Unfortunately I think I can't go any further to help without being able to repro.

I created a table with an ntext column. I replaced the data in each even-numbered row with "*" + the data. I used the Last() function. I tried it on a details level and a group level. Still worked.

I'd be willing to look at your report, if you thought it would do any good... but I still think it may be something data related, even though I don't know why or how...

>> My hunch about alphanumeric characters was a red-herring i think

Yes, I'm sure of that...

>L<

|||

Fixed:

The filter on the detail line of the report was causing the problem, I changed the filter on the detail line to this:

=len(trim(Fields!Case_ProgressNote.Value)) > =0

(i.e. ignore blank entries)

note Case_ProgressNote.Value is ntext in the database, but by the time it gets to the layout it has been converted to text (the fact that it was ntext is irrelevant at this point)

Asterix not showing in report details

I have a report that shows a header, details and a footer

The detail line never prints lines with asterixes in them, e.g.

"**blah blah**"

As soon as I delete the asterix from the database, the line appears on the report

Any ideas anyone?

to clarify

are you saying:

you have a value in the database that contains the text "** blah blah **"

when you run the report, only "blah blah" is rendered in the row and not "** blah blah **"

?|||

It does not print the line at all, also additionally the problem seems to be caused only by the asterix at the start of the line

so when I would expect the report to show:

Header

detail1

detail2

*blah blah

detai*l4

Footer

The report actually omits the line "*blah blah" and prints this instead:

Header

detail1

detail2

detai*l4

Footer

|||

Interesting... but I am not able to repro this in a simple case (ie an expression such as ="****" prints fine).

So here are some questions:

* -- does the disappearing line occur in all output targets? Or only some (such as HTML versus Excel)?

* -- what does the expression for your detail column look like?

>L<

|||

To reproduce this you will have to query an sql database

The detail row of the table contains:

=Last(Fields!Case_SolutionNote.Value)

|||

no, I still can't repro it. I tried a simple version (without the Last) and also one with a Last() function. I could see it fine.

What else do I have to know about your Case_SolutionNote field? What type is it? How long are the typical values? Can you post some example values (with and without * character)?

>L<

|||

Interestingly, I also get a problem with the report's group footer section, it sometimes does not print at all either, I have not worked out why yet, but I suspect it is the presence of non alphanumeric characters in the relevant database field (which is an ntext data type)

I copied the contents of the field to notepad, deleted the contents of the field from the database, then used the 'update' statement (due to bug that prevents editing of ntext cells sometimes in microsoft sql server management studio) to repopulate it with a copy of the original text from notepad, it then shows on the report

I think there is a weakness somewhere in ntext data types in sql or reporting services relating to non alphanumeric characters

|||

Oh, also...

When I delete the report's group header/footer, leaving just a basic report with detail lines, the original problem dissappears - lines with asterixes at the beginning of them appear!!

ie the report prints correctly, e.g:

detail1

detail2

*blah blah

detai*l4

also...

if i have a group header/footer present, the group footer will print lines starting with asterixes but the detail lines do not

|||

When you copy the value of the field to Notepad and back, and then using the UPDATE statement you may not be preserving the Unicode characteristics of the string. It's hard to tell, because the clipboard buffer, the way you've stated the UPDATE, and the way the interactive query-handling facility in Studio would all be involved in interpreting that data <s>.

Point is, it is not a fair test...

>L<

|||

I wish I could be absolutely sure that your report logic isn't doing something to influence is -- forgive me, I don't mean to doubt you -- it's just that you have the Last() function in there....

IOW, I can't tell whether the lines you're expecting are missing because of what you've pinpointed (the asterisk) or some other factor, such as the sort/group/whatever changing what Last() decides should come up.

Without repro'ing more of your exact scenario I can't debug, reproduce your error.. or otherwise offer advice... sorry

>L<

|||

Hi thanks for you help

Regarding the second problem where the group footer was not displaying sometimes, I have worked around this by adding a space to the beginning of the data on the fly in the select statement in the report - note this only works in testing on sql server 2005 (unfortunately varchar(max) does not work in sql server 2000 where my database lives! )

ie i changed this:

select....., CaseProgress.Case_SolutionNote, .......

to

select....., (cast(' ' as varchar(max)) + cast(CaseProgress.Case_SolutionNote as varchar(max))) as Case_SolutionNote, .......

What this does is to populate the field if it was null in any rows - bear in mind i am using Last!(CaseProgress.Case_SolutionNote) and the last row is usually not empty but was not printing if prior rows had a 'null' in the field - wierd

The original problem with the aterix still stands

My hunch about alphanumeric characters was a red-herring i think

|||

Actually a slightly better workaround for the second problem is:

select..,isnull(CaseProgress.Case_SolutionNote,' ') Case_SolutionNote,...

|||

>>The original problem with the aterix still stands

Unfortunately I think I can't go any further to help without being able to repro.

I created a table with an ntext column. I replaced the data in each even-numbered row with "*" + the data. I used the Last() function. I tried it on a details level and a group level. Still worked.

I'd be willing to look at your report, if you thought it would do any good... but I still think it may be something data related, even though I don't know why or how...

>> My hunch about alphanumeric characters was a red-herring i think

Yes, I'm sure of that...

>L<

|||

Fixed:

The filter on the detail line of the report was causing the problem, I changed the filter on the detail line to this:

=len(trim(Fields!Case_ProgressNote.Value)) > =0

(i.e. ignore blank entries)

note Case_ProgressNote.Value is ntext in the database, but by the time it gets to the layout it has been converted to text (the fact that it was ntext is irrelevant at this point)

Asterix not showing in report details

I have a report that shows a header, details and a footer

The detail line never prints lines with asterixes in them, e.g.

"**blah blah**"

As soon as I delete the asterix from the database, the line appears on the report

Any ideas anyone?

to clarify

are you saying:

you have a value in the database that contains the text "** blah blah **"

when you run the report, only "blah blah" is rendered in the row and not "** blah blah **"

?|||

It does not print the line at all, also additionally the problem seems to be caused only by the asterix at the start of the line

so when I would expect the report to show:

Header

detail1

detail2

*blah blah

detai*l4

Footer

The report actually omits the line "*blah blah" and prints this instead:

Header

detail1

detail2

detai*l4

Footer

|||

Interesting... but I am not able to repro this in a simple case (ie an expression such as ="****" prints fine).

So here are some questions:

* -- does the disappearing line occur in all output targets? Or only some (such as HTML versus Excel)?

* -- what does the expression for your detail column look like?

>L<

|||

To reproduce this you will have to query an sql database

The detail row of the table contains:

=Last(Fields!Case_SolutionNote.Value)

|||

no, I still can't repro it. I tried a simple version (without the Last) and also one with a Last() function. I could see it fine.

What else do I have to know about your Case_SolutionNote field? What type is it? How long are the typical values? Can you post some example values (with and without * character)?

>L<

|||

Interestingly, I also get a problem with the report's group footer section, it sometimes does not print at all either, I have not worked out why yet, but I suspect it is the presence of non alphanumeric characters in the relevant database field (which is an ntext data type)

I copied the contents of the field to notepad, deleted the contents of the field from the database, then used the 'update' statement (due to bug that prevents editing of ntext cells sometimes in microsoft sql server management studio) to repopulate it with a copy of the original text from notepad, it then shows on the report

I think there is a weakness somewhere in ntext data types in sql or reporting services relating to non alphanumeric characters

|||

Oh, also...

When I delete the report's group header/footer, leaving just a basic report with detail lines, the original problem dissappears - lines with asterixes at the beginning of them appear!!

ie the report prints correctly, e.g:

detail1

detail2

*blah blah

detai*l4

also...

if i have a group header/footer present, the group footer will print lines starting with asterixes but the detail lines do not

|||

When you copy the value of the field to Notepad and back, and then using the UPDATE statement you may not be preserving the Unicode characteristics of the string. It's hard to tell, because the clipboard buffer, the way you've stated the UPDATE, and the way the interactive query-handling facility in Studio would all be involved in interpreting that data <s>.

Point is, it is not a fair test...

>L<

|||

I wish I could be absolutely sure that your report logic isn't doing something to influence is -- forgive me, I don't mean to doubt you -- it's just that you have the Last() function in there....

IOW, I can't tell whether the lines you're expecting are missing because of what you've pinpointed (the asterisk) or some other factor, such as the sort/group/whatever changing what Last() decides should come up.

Without repro'ing more of your exact scenario I can't debug, reproduce your error.. or otherwise offer advice... sorry

>L<

|||

Hi thanks for you help

Regarding the second problem where the group footer was not displaying sometimes, I have worked around this by adding a space to the beginning of the data on the fly in the select statement in the report - note this only works in testing on sql server 2005 (unfortunately varchar(max) does not work in sql server 2000 where my database lives! )

ie i changed this:

select....., CaseProgress.Case_SolutionNote, .......

to

select....., (cast(' ' as varchar(max)) + cast(CaseProgress.Case_SolutionNote as varchar(max))) as Case_SolutionNote, .......

What this does is to populate the field if it was null in any rows - bear in mind i am using Last!(CaseProgress.Case_SolutionNote) and the last row is usually not empty but was not printing if prior rows had a 'null' in the field - wierd

The original problem with the aterix still stands

My hunch about alphanumeric characters was a red-herring i think

|||

Actually a slightly better workaround for the second problem is:

select..,isnull(CaseProgress.Case_SolutionNote,' ') Case_SolutionNote,...

|||

>>The original problem with the aterix still stands

Unfortunately I think I can't go any further to help without being able to repro.

I created a table with an ntext column. I replaced the data in each even-numbered row with "*" + the data. I used the Last() function. I tried it on a details level and a group level. Still worked.

I'd be willing to look at your report, if you thought it would do any good... but I still think it may be something data related, even though I don't know why or how...

>> My hunch about alphanumeric characters was a red-herring i think

Yes, I'm sure of that...

>L<

|||

Fixed:

The filter on the detail line of the report was causing the problem, I changed the filter on the detail line to this:

=len(trim(Fields!Case_ProgressNote.Value)) > =0

(i.e. ignore blank entries)

note Case_ProgressNote.Value is ntext in the database, but by the time it gets to the layout it has been converted to text (the fact that it was ntext is irrelevant at this point)

sql