October 28, 2008 at 9:58 am
First off let me explain I am more of a system administrator put into a DBA role so I'm still pretty green to a lot of the intricacies of SQL Server.
Let me explain the problem that's occurring.
One of my developers has written a query that when runs seems to be spawning some sub-queries (I say this because when I look in activity monitor I see multiple lines all with the same SPID) When this query is run it gets suspended, however CPU Usage is maxed until I manually go in and kill that process. The wait type is CXPACKET. In my brief searching I've found this may have to do with parallelism? The other problem is when trying to troubleshoot this I can't see what queries are actually being executed under this SPID. When I try to view the details of the process it is blank.
So a couple questions.
#1 is there a good way to figure out which processes are currently using the most CPU? The CPU stat shown in activity monitor is cumulative for a process. I want to know how much CPU each process is using right now.
#2 Can anyone point me in the direction of a good resource for understanding the "Suspended" status?
#3 Is there a way that I'm missing to see what SQL it was that was executed? Why is that "Last TSQL command batch" window blank?
Hopefully someone will be able to shed some light for me.
October 28, 2008 at 10:21 am
One of my developers has written a query that when runs seems to be spawning some sub-queries (I say this because when I look in activity monitor I see multiple lines all with the same SPID)
It's not spawning subqueries. It's that the query optimiser has decided that the query is best run in parallel and so the processing or the query has been spread out over multiple threads in order to complete faster.
If it's running badly, toss it back at the developer and ask him to optimise it.
#1 is there a good way to figure out which processes are currently using the most CPU? The CPU stat shown in activity monitor is cumulative for a process. I want to know how much CPU each process is using right now.
Query sys.dm_exec_requests. There's a CPU_time column there. It's total cpu that this request has used since it started.
#2 Can anyone point me in the direction of a good resource for understanding the "Suspended" status?
Waiting for a resource or an event. If you're seeing CXPacket waits, it means that the thread is waiting for other threads or this request to complete in order to go on to the next operation.
#3 Is there a way that I'm missing to see what SQL it was that was executed? Why is that "Last TSQL command batch" window blank?
Where are you viewing the "Last TSQL command batch"? You can use sys.dm_exec_requests and sys.dm_exed_sql_text to see the statements that teh request is 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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply