February 20, 2013 at 9:37 am
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...
February 20, 2013 at 10:18 am
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
February 20, 2013 at 11:42 am
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.
February 20, 2013 at 12:17 pm
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
February 20, 2013 at 1:15 pm
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.
February 20, 2013 at 2:37 pm
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...
February 20, 2013 at 2:56 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply