<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
Runnable query - waiting for what?
Runnable query - waiting for what?
Rate Topic
Display Mode
Topic Options
Author
Message
virgilrucsandescu
virgilrucsandescu
Posted Wednesday, February 20, 2013 9:37 AM
Old Hand
Group: General Forum Members
Last Login: Thursday, March 14, 2013 1:34 PM
Points: 348,
Visits: 703
I have a query (basically a SELECT ... INTO tmp_table FROM .... (more than 100 joins in here).
Its status is "runnable" - CPUTime returned by sp_who2 keeps increasing, but DiskIO is locked to a specific value.
SELECT * FROM sys.dm_os_waiting_tasks returns nothing for that spid.
Adam Machanic's sp_whoisactive returns basically the same info: CPU keeps growing fast, but reads and writes keep showing the same (very low) values (0 writes and 445 reads). wait_info for this query = NULL in sp_whoisactive
Is there any way of detecting what this query is waiting for?
The result table is not supposed to be that big (~ 500 rows), but it just uses a lot of tables to join...
Post #1422156
GilaMonster
GilaMonster
Posted Wednesday, February 20, 2013 10:18 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671,
Visits: 29,925
If the query is runnable, it's in the queue for scheduling back onto the processor and is not waiting for anything (other than some time on the CPU). If the state was suspended, then it would be waiting for something.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1422191
sturner
sturner
Posted Wednesday, February 20, 2013 11:42 AM
Ten Centuries
Group: General Forum Members
Last Login: Friday, May 17, 2013 11:27 AM
Points: 1,314,
Visits: 2,882
The chances of you seeing other processes in "runnable" state is quite high since your process (sp_who2 or something) is currently consuming CPU time.
Remember the old adage: "A watched pot never boils". lol
The probability of survival is inversely proportional to the angle of arrival.
Post #1422231
brad.blackburn
brad.blackburn
Posted Wednesday, February 20, 2013 12:17 PM
Forum Newbie
Group: General Forum Members
Last Login: Yesterday @ 2:19 PM
Points: 5,
Visits: 53
I would like to add to this question. I have something similar (except for all the joins). A program on this one computer shows that a single select statement is RUNNABLE and the ElapsedMS keeps increasing. As long as the app is open on that computer, it keeps growing. I can not replicate this problem with that app on any other computer. What is the best way to try and troubleshoot this? I havent noticed any performance issues but it is skewing my Server Activity reports showing extremely high Network I/O waits because of that one process.
Thanks
Post #1422242
sturner
sturner
Posted Wednesday, February 20, 2013 1:15 PM
Ten Centuries
Group: General Forum Members
Last Login: Friday, May 17, 2013 11:27 AM
Points: 1,314,
Visits: 2,882
brad.blackburn (2/20/2013)
I would like to add to this question. I have something similar (except for all the joins). A program on this one computer shows that a single select statement is RUNNABLE and the ElapsedMS keeps increasing. As long as the app is open on that computer, it keeps growing. I can not replicate this problem with that app on any other computer. What is the best way to try and troubleshoot this? I havent noticed any performance issues but it is skewing my Server Activity reports showing extremely high Network I/O waits because of that one process.
Thanks
Sounds like the app is just running a query over and over again on a nailed up database connection to the server. If that is the case the lastBatch time would also keep increasing.
The probability of survival is inversely proportional to the angle of arrival.
Post #1422273
virgilrucsandescu
virgilrucsandescu
Posted Wednesday, February 20, 2013 2:37 PM
Old Hand
Group: General Forum Members
Last Login: Thursday, March 14, 2013 1:34 PM
Points: 348,
Visits: 703
I tried the same query on a testing machine, it was the only query reported by sp_whoisactive, with the same 'runnable' status, but there was nothing else 'running'.
I always thought there should be some 'runnable' queueing mechanism, with queries moving into the 'running' group, but seeing just one 'runnable' query (consuming a lot of CPU while "waiting", but no reads/writes) and no 'running' ones is making me wondering if I understood everything properly...
Post #1422311
GilaMonster
GilaMonster
Posted Wednesday, February 20, 2013 2:56 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671,
Visits: 29,925
You're correct.
sp_whoisactive must exclude itself from the report, because it would obviously be running while it's running.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1422326
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
