Hi,
I have a job running for more than expected time ( exceeds by 2 hrs ) and still running. When I looked at sp who is active 'is blocked' column it shows NULL. So, when I further look into it, It was in suspended state. When I look at more information using Sp who is active @get_task_Info=2, it showed there was another query doing select on table A and this job was trying to update the same Table A ( Wait_info was 3X cspacket 6X cspacket) So, blocking, the user stopped the query doing select on this table in hopes that the job completes however the session is still in suspended state and how can I check it is self blocking. Sp who is active shows NULL in the blocking column. Please let me know...thanks.
Sincerely
January 25, 2022 at 8:42 pm
A cte query which joins couple of tables and then updates is in suspended state. Is it possible it is blocking itself?
SP who is active shows NULL in the blocking column however further looking into it looks like CXPACKET wait. So there was another select running on the same table which user stopped the session. But why this query is still in the suspended state?
Does the scheduler moves the query to runnable since now the blocking select is not running anymore. If yes, does it wait for sometime to do it? or in this case, manual script execution or action is needed? please advise. Thanks
If a query was in a wait state, as soon as the blocking process clears, it will start to execute again, no manual intervention required. If the query in question is still sitting there, something else is going on. It's still blocked, or it has some kind of pause or wait or something built right into the code. Without seeing your code, structure, anything at all really, it's hard to say what's going on specifically.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 26, 2022 at 9:39 pm
A cte query which joins couple of tables and then updates is in suspended state. Is it possible it is blocking itself?
SP who is active shows NULL in the blocking column however further looking into it looks like CXPACKET wait. So there was another select running on the same table which user stopped the session. But why this query is still in the suspended state?
Does the scheduler moves the query to runnable since now the blocking select is not running anymore. If yes, does it wait for sometime to do it? or in this case, manual script execution or action is needed? please advise. Thanks
You really need to post the code. There is this "thing" that I simply refer to as an "illegal update" that can make an UPDATE that should only take a minute or two , quite literally take hours and drive several CPUs to the wall in the process.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2022 at 5:25 pm
I looked at it again it seems query was the issue. The CTE was selecting DISTINCT values and joining on TABLE A
In the same query we were doing UPDATE TABLE A and joining on a predicate. So, looks like this has to be rewritten. Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply