October 4, 2012 at 8:49 am
Hi all,
Im trying to understand locking and had a few questions.
If i have a query that takes 10 mins to run. and 9 mins of that is network_io waits.. do all the locks on the rows/tables stay till the query is completely finished. Or till sql has found the data and is sending it (ie when the network_io waits start)?
With that. If SQL works in sets. Does network_io waits only start when sql has worked out all the data to send (Ie if you saw the waits to a query happen form start to finish. Does it always finish with the network_io) - With that is it possible to see waits as they happen and in what order!
Does it deal with cursor type queries differently? ie as it gets the data it sends it and repeats the circles though waits.
Are there any WAIT events for XML? is it possible to track how long it takes to read data out of a xml column?
Hope someone can help with any of the queries!
Regards
S
October 4, 2012 at 10:24 am
n00bDBA (10/4/2012)
If i have a query that takes 10 mins to run. and 9 mins of that is network_io waits.. do all the locks on the rows/tables stay till the query is completely finished. Or till sql has found the data and is sending it (ie when the network_io waits start)?
Depends on the isolation level. In the default, the locks (shared locks only) are released as soon as the data is read. Higher isolation levels hold shared locks until the query finishes
With that. If SQL works in sets. Does network_io waits only start when sql has worked out all the data to send (Ie if you saw the waits to a query happen form start to finish. Does it always finish with the network_io)
Not necesssarily. SQL will start sending data to the client as soon as it has some available to send. So unless your query has blocking operators in it (sorts, hash joins, other things that require the entire resultset to be available before processing), some rows may already have been sent to the client before others are even read off disk.
p.s. If you have lots of async network io waits, you may want to check out the application, see why it's slow at accepting results.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2012 at 10:51 am
Hi Gail,
Thanks for the reply!
Is there a list of operators that course blocking for the whole query (Ive never heard of it.. which isn't difficult to be honest, but that's another story)
October 4, 2012 at 11:06 am
Not blocking as in locks, just query operators that require the entire result set before they can process. It's easy to figure out, what operations (within a query) require the entire set. Things like sorts, hash aggregates and matches that have to build a hash table. Things like that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2012 at 11:51 am
Thanks Gail 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply