Suspended Queries

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Suspended Queries

Suspended Queries

Rate Topic

Display Mode

Topic Options

Author

Message

mssqlsrv

Posted Tuesday, October 30, 2012 12:30 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:32 PM
Points: 72, Visits: 244

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?

Post #1378581

sanket kokane

Posted Tuesday, October 30, 2012 12:35 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 5:04 AM
Points: 323, Visits: 966

wait till query get completed.

verify whether data getting insert or not by using

sp_spaceused 'tablename'

look at the rows.

-----------------------------------------------------------------------------
संकेत कोकणे

Post #1378583

mssqlsrv

Posted Tuesday, October 30, 2012 1:28 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:32 PM
Points: 72, Visits: 244

There are other quereis like select which are getting suspended.

No idea why this is happening

Post #1378592

Vegard Hagen

Posted Tuesday, October 30, 2012 2:46 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:18 AM
Points: 494, Visits: 326

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, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)

Post #1378614

Gail Wanabee

Posted Wednesday, October 31, 2012 11:36 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 06, 2013 2:26 PM
Points: 254, Visits: 1,029

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.

Post #1379463

Vegard Hagen

Posted Thursday, November 01, 2012 3:31 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:18 AM
Points: 494, Visits: 326

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.

"It is better to light a candle than to curse the darkness."
(Chinese proverb)

Post #1379686

« Prev Topic | Next Topic »

Permissions