Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Runnable query - waiting for what? Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 9:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:25 AM
Points: 348, Visits: 710
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
Posted Wednesday, February 20, 2013 10:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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
Posted Wednesday, February 20, 2013 11:42 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, December 11, 2014 12:22 PM
Points: 1,446, Visits: 3,232
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
Posted Wednesday, February 20, 2013 12:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 12:53 PM
Points: 5, 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
Post #1422242
Posted Wednesday, February 20, 2013 1:15 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, December 11, 2014 12:22 PM
Points: 1,446, Visits: 3,232
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
Posted Wednesday, February 20, 2013 2:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:25 AM
Points: 348, Visits: 710
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
Posted Wednesday, February 20, 2013 2:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 »

Add to briefcase

Permissions Expand / Collapse