Prev: VS_ISBROKEN
Next: Linked server - from 2008 to a 2005 box.
From: Erland Sommarskog on 6 Jan 2010 17:46
RG (RG(a)discussions.microsoft.com) writes:
> The output of a query gets loaded to a network buffer, I assume on the
> server. If query output doesn't fit into the network buffer, sql server
> has to wait until remote client fetches this data to refill the network
> buffer. The wait between the refills is of type ASYNC_NETWORK_IO.

So far you are right.

> If my isolation level is read commited and not read committed snapshot,
> the select statement against the same tables is partially, if not
> completely, blocked until client fetch completes.

This is incorrect. The other process only has a shared lock, and another
process can also read the rows.

However, since there are locks, another process cannot update the rows.




--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: RG on 6 Jan 2010 18:42
Thanks for your help.

You are right. I suppose I meant to say that if a table is in the middle
of being updated by process a, process b is going to be blocked on select.

On the other hand, if process a is selecting, when process b wants update
the same table it will be blocked by process a.

If so, all of this is starting to make sense. After running waitstat, I
found that ASYNC_NETWORK_IO wait was 15% of the time which is pretty
substantial.
I couldn't figure out where it was coming from. We have quite a few queries
which return large result sets. Also, quite a few clients are in branch
offices connected over vpn. What kind of bandwidth one could get over wan?
dismal at best, probably 1mb/sec. My guess it isolation level of read
committed snapshot should help the situation significantly.

Tell me what you think.


"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CF8F1CC28376Yazorman(a)127.0.0.1...
> RG (RG(a)discussions.microsoft.com) writes:
>> The output of a query gets loaded to a network buffer, I assume on the
>> server. If query output doesn't fit into the network buffer, sql server
>> has to wait until remote client fetches this data to refill the network
>> buffer. The wait between the refills is of type ASYNC_NETWORK_IO.
>
> So far you are right.
>
>> If my isolation level is read commited and not read committed snapshot,
>> the select statement against the same tables is partially, if not
>> completely, blocked until client fetch completes.
>
> This is incorrect. The other process only has a shared lock, and another
> process can also read the rows.
You are right. I suppose I meant to say that if a table is in the middle
of being updated
>
> However, since there are locks, another process cannot update the rows.
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

From: RG on 6 Jan 2010 19:47
Correction. This doesn't apply we are discussing ASYNC_NETWORK_IO.

> I suppose I meant to say that if a table is in the middle of being updated
> by process a, process b is going to be blocked on select.

Sorry about that.


"RG" <nobody(a)nowhere.com> wrote in message
news:%23CrahnyjKHA.4912(a)TK2MSFTNGP02.phx.gbl...
> Thanks for your help.
>
> You are right. I suppose I meant to say that if a table is in the middle
> of being updated by process a, process b is going to be blocked on select.
>
> On the other hand, if process a is selecting, when process b wants update
> the same table it will be blocked by process a.
>
> If so, all of this is starting to make sense. After running waitstat, I
> found that ASYNC_NETWORK_IO wait was 15% of the time which is pretty
> substantial.
> I couldn't figure out where it was coming from. We have quite a few
> queries which return large result sets. Also, quite a few clients are in
> branch offices connected over vpn. What kind of bandwidth one could get
> over wan? dismal at best, probably 1mb/sec. My guess it isolation level
> of read committed snapshot should help the situation significantly.
>
> Tell me what you think.
>
>
> "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
> news:Xns9CF8F1CC28376Yazorman(a)127.0.0.1...
>> RG (RG(a)discussions.microsoft.com) writes:
>>> The output of a query gets loaded to a network buffer, I assume on the
>>> server. If query output doesn't fit into the network buffer, sql server
>>> has to wait until remote client fetches this data to refill the network
>>> buffer. The wait between the refills is of type ASYNC_NETWORK_IO.
>>
>> So far you are right.
>>
>>> If my isolation level is read commited and not read committed snapshot,
>>> the select statement against the same tables is partially, if not
>>> completely, blocked until client fetch completes.
>>
>> This is incorrect. The other process only has a shared lock, and another
>> process can also read the rows.
> You are right. I suppose I meant to say that if a table is in the middle
> of being updated
>>
>> However, since there are locks, another process cannot update the rows.
>>
>>
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000:
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>

From: Dan Guzman on 7 Jan 2010 08:03
> My guess it isolation level of read committed snapshot should help the
> situation significantly.

I agree. Excessive wait times (regardless of the type), will cause locks to
be held longer than otherwise needed. If you can't mitigate the root cause
(slow WAN in your case), READ_COMMITTED_SNAPSHOT is a viable way to avoid
long-running SELECT queries from blocking UPDATEs.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


"RG" <nobody(a)nowhere.com> wrote in message
news:#CrahnyjKHA.4912(a)TK2MSFTNGP02.phx.gbl...
> Thanks for your help.
>
> You are right. I suppose I meant to say that if a table is in the middle
> of being updated by process a, process b is going to be blocked on select.
>
> On the other hand, if process a is selecting, when process b wants update
> the same table it will be blocked by process a.
>
> If so, all of this is starting to make sense. After running waitstat, I
> found that ASYNC_NETWORK_IO wait was 15% of the time which is pretty
> substantial.
> I couldn't figure out where it was coming from. We have quite a few
> queries which return large result sets. Also, quite a few clients are in
> branch offices connected over vpn. What kind of bandwidth one could get
> over wan? dismal at best, probably 1mb/sec. My guess it isolation level
> of read committed snapshot should help the situation significantly.
>
> Tell me what you think.
>
>
> "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
> news:Xns9CF8F1CC28376Yazorman(a)127.0.0.1...
>> RG (RG(a)discussions.microsoft.com) writes:
>>> The output of a query gets loaded to a network buffer, I assume on the
>>> server. If query output doesn't fit into the network buffer, sql server
>>> has to wait until remote client fetches this data to refill the network
>>> buffer. The wait between the refills is of type ASYNC_NETWORK_IO.
>>
>> So far you are right.
>>
>>> If my isolation level is read commited and not read committed snapshot,
>>> the select statement against the same tables is partially, if not
>>> completely, blocked until client fetch completes.
>>
>> This is incorrect. The other process only has a shared lock, and another
>> process can also read the rows.
> You are right. I suppose I meant to say that if a table is in the middle
> of being updated
>>
>> However, since there are locks, another process cannot update the rows.
>>
>>
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000:
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>
From: Andrew J. Kelly on 7 Jan 2010 09:37
By the 2 isolation levels I meant the 2 snapshot isolations levels. There is
the READ_COMMITTED_SNAPSHOT and the ALLOW_SNAPSHOT_ISOLATION settings in the
database. One is essentially at the statement level where as the other is at
the transaction level. You can read more on these in BOL:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/814e4c22-eaea-4871-8d24-8644f42996d0.htm

And yes either of these will help in a situation in which you are blocking
writers due to selects taking too long or readers blocked do to updates as
well. But be aware of the extra overhead for keeping these versions around
in tempdb. And one more thing to note about network waits is that they are
not always caused by slow network traffic. Most of the time it is caused by
the client itself being slow. A poorly written client app that has too much
processing in between each row retrieved or does not have enough physical
resources on the client machine can also cause excessive ASYNC_NETWORK_IO
waits. In your case it sounds like maybe a bit of both.
--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"RG" <RG(a)discussions.microsoft.com> wrote in message
news:7A66AA60-BCC6-42FA-949A-0054032B0367(a)microsoft.com...
> I apologize, yes, wait type. When you say, the difference between 2
> isolation levels, you mean issue of not getting somewhat obsolete data?
> If
> yes, I am not concerned about that.
>
> Correct me if I am wrong...
>
> The output of a query gets loaded to a network buffer, I assume on the
> server. If query output doesn't fit into the network buffer, sql server
> has
> to wait until remote client fetches this data to refill the network
> buffer.
> The wait between the refills is of type ASYNC_NETWORK_IO. If my isolation
> level is read commited and not read committed snapshot, the select
> statement
> against the same tables is partially, if not completely, blocked until
> client
> fetch completes. With read committed snapshot in the same scenario,
> select
> statement will not be blocked.
>
> "Andrew J. Kelly" wrote:
>
>> ASYNC_NETWORK_IO is not a lock it is a wait type and has nothing directly
>> to
>> do with locking. But the snapshot isolation levels will allow you to read
>> older versions of any data that has changed while you are trying to read
>> it
>> without blocking. But there is a difference in how the two snapshot
>> isolation levels work so make sure you get the one you need.
>>
>> --
>>
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "RG" <RG(a)discussions.microsoft.com> wrote in message
>> news:30C19FA2-1C50-4046-A37D-DBF32A47E1E5(a)microsoft.com...
>> > Are you saying that while you have ASYNC_NETWORK_IO lock, the tables
>> > involved
>> > are available for reading?
>> >
>> > Thanks
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Network traffic doesn't lock rows. The database engine does. Read
>> >> committed
>> >> will cause SQL Server to give you earlier versions of data for rows
>> >> with
>> >> exclusive locks.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >>
>> >> "RG" <RG(a)discussions.microsoft.com> wrote in message
>> >> news:78423B8A-40F0-4875-B2B9-E8ABE0209071(a)microsoft.com...
>> >> > I set my db isolation level to read committed snapshot. Will this
>> >> > allow
>> >> > me
>> >> > to read rows locked by network traffic?
>> >> >
>> >> > Thanks in advance
>> >>
>> >> .
>> >>
>> .
>>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: VS_ISBROKEN
Next: Linked server - from 2008 to a 2005 box.