Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Thursday, March 27, 2014 9:42 AM
Hi all ~ There is a cursor inside a store proc. and at the middle of the loop, became extreamly slow..
and I look into "sysprocesses"
"Fetch Next from cursor_name" SUDDENLY uses huge logical read.~~~~
I am using SQL 2008 R2 SP2 .
Anyone meet this kind of case before ?
Tuesday, April 8, 2014 6:20 AM ✅Answered
its solved after adding another index.
Thursday, March 27, 2014 9:51 AM
Not enough information to understand what exactly is happening. Atleast give us an idea of what cursor is trying to do.
Please Mark This As Answer if it helps to solve the issue Visakh http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
Thursday, March 27, 2014 9:57 AM | 2 votes
Change your cursor declaration to
DECLARE cur CURSOR STATIC LOCAL FOR
The default cursor type is a dynamic cursor which means that SQL Server evaluates the condition for every FETCH which is not good for performance and also can have some confusing effects if you modify data in the cursor loop.
With a static cursor, SQL Server copies the result set into tempdb and then serves the cursor from this temp table which is a lot more efficient. The only time a static cursor is bad is when cursor qualifies lots of rows and you only iterate over a handful. Those situations are not very common.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Thursday, March 27, 2014 5:23 PM
Even not using STATIC local for...
in the Query plan. there is also Cluster index update on the temp table CWT_PrimaryKey~~~
and finally need to create another non-cluster index for each of the "fetch next"
Thursday, March 27, 2014 8:26 PM
Hello,
We honestly can't really help you at this point (as Erland pointed out) as your question is so vague that the amount of possibilities are extremely large.
If you could give us code, execution plans, purposes, values, anything concrete to help us other than it's running slow would be a good start.
Sean Gallardy | Blog | Twitter
Thursday, March 27, 2014 10:27 PM
With a static cursor, FETCH NEXT should not be a problem.
But since we don't see any code, any tables, any indexes or any problems, we can't assist you further.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Tuesday, April 8, 2014 1:34 AM
Hi sakuri_db,
I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps? If you would like to, you can post a reply to share your solution and I will mark it as answer. That way, other community members could benefit from your sharing. If it is not resolved, as other post, please post more information or code for analysis.
Regards,
Sofiya Li
Sofiya Li
TechNet Community Support