SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Runnable query - waiting for what?


Runnable query - waiting for what?

Author
Message
virgilrucsandescu
virgilrucsandescu
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 712
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86376 Visits: 45232
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, MVP, M.Sc (Comp Sci)
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


sturner
sturner
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2214 Visits: 3259
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.
brad.blackburn
brad.blackburn
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 66
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
sturner
sturner
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2214 Visits: 3259
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.
virgilrucsandescu
virgilrucsandescu
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 712
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...
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86376 Visits: 45232
You're correct.

sp_whoisactive must exclude itself from the report, because it would obviously be running while it's running. :-D

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search