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 type

We had an issue yesterday where a mess of async_network_io wait types starte
d
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 wa
s
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 fo
r
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 explicitl
y
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 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.

Assigning column types in select into

Group,

Is there a way to assign nullability on a column when using a select into?
I've tried some of the usual things like coalsce, isnull, and cast. Since
the new table gets definition from the source table or can be somewhat
adjusted with cast is there a way to cast a not null? In the example below
how can I select into causing tableone_new..col2 to be not null. We
typically must use an alter statement after the select into but this seems
inefficient.

Thanks,
Ray

create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
col2
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
goRay (someone@.nowhere.com) writes:
> Is there a way to assign nullability on a column when using a select
> into? I've tried some of the usual things like coalsce, isnull, and
> cast. Since the new table gets definition from the source table or can
> be somewhat adjusted with cast is there a way to cast a not null? In
> the example below how can I select into causing tableone_new..col2 to be
> not null. We typically must use an alter statement after the select
> into but this seems inefficient.

As far as I know there isn't. You might be better off with CREATE TABLE
instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Ray" <someone@.nowhere.com> wrote in message
news:t7tTd.8720$rK1.8198@.newssvr31.news.prodigy.co m...
> Group,
> Is there a way to assign nullability on a column when using a select into?
> I've tried some of the usual things like coalsce, isnull, and cast. Since
> the new table gets definition from the source table or can be somewhat
> adjusted with cast is there a way to cast a not null? In the example
> below how can I select into causing tableone_new..col2 to be not null. We
> typically must use an alter statement after the select into but this seems
> inefficient.

<snip
ISNULL() appears to do what you want (8.00.760 Enterprise Edition), although
COALESCE() does not (don't ask me why). Personally, I agree with Erland - it
would be much better to use CREATE TABLE and therefore make your intentions
clear and your data model explicit.

Simon

create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
isnull(col2, 1) as col2 -- tableone_new.col2 is NOT NULL
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
go

create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
coalesce(col2, 1) as col2 -- tableone_new.col2 IS NULL
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
go|||Simon Hayes (sql@.hayes.ch) writes:
> ISNULL() appears to do what you want (8.00.760 Enterprise Edition),
> although COALESCE() does not (don't ask me why).

There is a similar case with indexed views where you can use isnull()
to make a view indexable but where coalesce() does not cut it. And,
no, I can't really make any sense of it.

(And in the particular case where I looked at it, the view does not
index at all in SQL 2000, but does so in SQL 2005 when you use isnull.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp