Network Waits and Read Committed Snapshot
in [Server Configuration]
|
From: RG on 7 Jan 2010 11:53 Just to confirm ... when you are saying, "extra overhead for keeping these versions on tempdb", are you refering to extra space or are there other things I need to worry about? BTW... what happens to the snapshot data after the transaction committed? If tempdb is not cleaned automatically after commit, how should you maintain it? Thanks in advance "Andrew J. Kelly" wrote: > 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 > >> >> > >> >> . > >> >> > >> . > >> > . > |