May 24, 2012 at 1:41 am
HI All,
I am facing a weird issue ,during the processing of the certain tasks from Application,some part of time we are observing blocks in our database.
Processing ,load are quite normal at that time of blocks and what we observe is last query which is holding locks and making the other processes to wait is sp_cursorclose;1 it is taking a very long time just say 2hrs.
As the blocks are making the other activities to be on hold,application performance went down.
Why sp_cursorclose is taking a very long time to process and one more thing what we observed is this process is blocked by spid 0.
What is the significance of the process 0?why and when this process blocks the others?
Is there a way to fix this issue or it should definitely fixed from the application?
May 24, 2012 at 3:50 am
rkumar590 (5/24/2012)
HI All,I am facing a weird issue ,during the processing of the certain tasks from Application,some part of time we are observing blocks in our database.
Processing ,load are quite normal at that time of blocks and what we observe is last query which is holding locks and making the other processes to wait is sp_cursorclose;1 it is taking a very long time just say 2hrs.
As the blocks are making the other activities to be on hold,application performance went down.
Why sp_cursorclose is taking a very long time to process and one more thing what we observed is this process is blocked by spid 0.
What is the significance of the process 0?why and when this process blocks the others?
Is there a way to fix this issue or it should definitely fixed from the application?
It's hard to know for sure since I can't see the code or your system, but I suspect you have a cursor that you're using for data updates within a transaction. The cursor is complete and is updating it's data as it closes and that's why you're seeing that as the statement. Again, I'm speculating on very little information.
"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
May 25, 2012 at 3:47 am
Hi Grant,
Thanks for your reply.
Is the sp_cursorclose;1 depends on the Number of records it has been processed by the update which was run before it or it depends on the application which is trying to run this statement in Database.
What are the possible scenarios of cursor close statement taking very long time to process?
May 25, 2012 at 4:37 am
rkumar590 (5/25/2012)
Hi Grant,Thanks for your reply.
Is the sp_cursorclose;1 depends on the Number of records it has been processed by the update which was run before it or it depends on the application which is trying to run this statement in Database.
What are the possible scenarios of cursor close statement taking very long time to process?
It's probably one of two things, and possibly in combination. You're either seeing lots of rows getting processed, so it's taking time, or you're getting contention on resources, waits, while the processing is taking place. Possibly both at the same time. Without a lot more information though, I'm still just speculating.
"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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply