October 30, 2012 at 12:30 am
Hi Team,
I am getting blocking in one of my sql server.
I can query which is suspened causing the block.
It is an insert query waiting on async_network_io but waittime is
icreasing and decreasing.
Query status is suspended contineously.
How should I troubleshoot this?
October 30, 2012 at 12:35 am
wait till query get completed.
verify whether data getting insert or not by using
sp_spaceused 'tablename'
look at the rows.
-----------------------------------------------------------------------------
संकेत कोकणे
October 30, 2012 at 1:28 am
There are other quereis like select which are getting suspended.
No idea why this is happening
October 30, 2012 at 2:46 am
Suspended is not necessarily bad, it simply mens we're waiting for some resource, which practically all queries will at some point and for some time.
The questions we need to be asking are
1) How long are we waiting?
2) What are we waiting for?
If the wait times are very short, then there's no problem here at all and you can just smile and go find something else to tinker with for a while. If there are constantly processes waiting for stuff and they're actually being delayed significantly by these waits, then there's probably some sort of performance bottleneck somewhere.
The good news is that your SQL server keeps track of all waits and keeps statistics on them, so your server actually knows who is spending the most time waiting for what and you can just ask it for this information and get it presented to you. Actually, studying these wait statistics is an excellent way of identifying bottlenecks.
Paul Randal has an excellent blog post on this over at his SQL Skills site:
http://www.sqlskills.com/blogs/paul/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
October 31, 2012 at 11:36 am
From Paul Randal's blog:
"275: ASYNC_NETWORK_IO - This is commonly where SQL Server is waiting for a client to finish consuming data. It could be that the client has asked for a very large amount of data or just that it's consuming it reeeeeally slowly because of poor programming."
Question: Could your production environment have application code written that is dependent upon a human being having to do something in order to consume data results extracted from a query? Human beings are very slow and, for instance, if a human being were required to download a screen at a time to see the results of a query, this might account for the long wait, and at least some of the blocking, your server is experiencing.
November 1, 2012 at 3:31 am
Lee Crain (10/31/2012)
Question: Could your production environment have application code written that is dependent upon a human being having to do something in order to consume data results extracted from a query? Human beings are very slow and, for instance, if a human being were required to download a screen at a time to see the results of a query, this might account for the long wait, and at least some of the blocking, your server is experiencing.
Yes, you are quite correct. With poorly written application code, this is entirely possible. I have actually seen at least one real live example of exactly what you describe here: An application was retrieving and displaying a maximum of 50 rows at a time and requiring the end user to click on a "get more" button to retrieve the next 50 - and so on and so forth...
-And yes, this did cause the query to be suspended on the SQL Server and all locks were held until the end user on the client side had manually retrieved all rows of the result set.
The solution in this case was that the application vendor had to rewrite their code so that the client app would initially retrieve the entire result set and not just the first 50 rows. That way, the transaction could be closed and all locks released, and the user could take 5 hours digesting the results without potentially locking down the entire system.
As a DBA, encountering these kinds of situations can be a frustrating experience because everybody willl be pointing fingers at you and your database, when in fact - unless you are also the application vendor, the real problem has nothing to do with you at all. But that just goes with the territory of a production DBA - quite often you will have to prove that it's not your DB that's messed up before anybody else will seriously check out their stuff. Your job here will be to document that it's actually the client application that's doing something stupid. Period.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply