SQL2K -> SQL 2008, EXTREMELY slow cursor fetch

Question

SQL2K -> SQL 2008, EXTREMELY slow cursor fetch

Viewing 13 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

bkuhnke SSC Eights! Points: 883 More actions · Answer 1

This is interesting....the original more involved cursor fetches the 450 rows in less than one second if I force it to build in tempdb with insensitive. Without insensitive, it is still running after 20 minutes and the CPU is at 100%. Of course I could find all cursors and make them insensitive (provided that makes logical sense in each case), but I think this is probably a symptom of something that will crop up in other areas. Also interesting, is that this same thing happens on my sql server express instance on my laptop. Instant with insensitive, basically never finishes otherwise. That would seem to rule out hardware, but I'm wondering if there is an installation parameter that I did incorrectly for both installs....

LutzM SSC Guru Points: 107049 More actions · Answer 2

I just tested two versions on my SQL2008R2 EXPRESS version:

DECLARE @CustNum AS INT

DECLARE myCrsr CURSOR LOCAL STATIC

FOR

SELECT number

FROM master..spt_values

WHERE TYPE='P' AND number <20

OPEN myCrsr

FETCH NEXT FROM myCrsr INTO @CustNum

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM myCrsr INTO @CustNum

END

Without LOCAL STATIC I get an index seek on master..spt_values for each and every fetch.

With LOCAL STATIC added the OPEN myCrsr will create a table in tempdb and fetch the values from there. Pretty much what I expected.

You might get a table scan because the index you created is not a covering index (column txt_cust_num

is missing) and since there are not that many rows in the table query optimizer decide that it's more efficient to do a table scan rather than using the [ixcustsalesman] index followed by a key lookup.

You could try to add the txt_cust_num to the index (maybe just as an included column). This index will most probably be "more attractive".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

bkuhnke SSC Eights! Points: 883 More actions · Answer 3

Wow, thanks for your efforts in helping me out with this. Greatly appreciated!

Was there a huge time difference in fetching the rows either with the local static or without? Did it seem excessively slow, as compared to just executing the select statement on it's own?

bkuhnke SSC Eights! Points: 883 More actions · Answer 4

Definitely something different in the way my 2008 servers are handling insensitive/temp db vs not. In SQL2K, the larger cursor that runs for 20 min+ when not making a tempdb table in 2008 runs slightly faster by not copying to tempdb. Either way, sql2k fetches all of the rows in < 5 seconds.

LutzM SSC Guru Points: 107049 More actions · Answer 5

bkuhnke (12/15/2010)
Wow, thanks for your efforts in helping me out with this. Greatly appreciated!
Was there a huge time difference in fetching the rows either with the local static or without? Did it seem excessively slow, as compared to just executing the select statement on it's own?

The one without local static took three times longer than the one with local static (for 2000 rows it took 130msec vs. 40msec). If you repeat the test on your system (based on 2000 numbers from spt_values), how long does each one take? Furthermore, repeat the test with 20 numbers. What do you see with both versions, a table scan or an index seek?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

bkuhnke SSC Eights! Points: 883 More actions · Answer 6

The larger cursor from the original stored procedure fetches the 450 rows in 171ms with local static. Without local static, I've let it run for as long as half an hour and it doesn't come back.

I'd be willing to find all of my cursors and change them to local static, then go ahead and roll this out, but it definitely seems like something is wrong and it might come back to bite me after we go live.

LutzM SSC Guru Points: 107049 More actions · Answer 7

Did you alter the nonclustered index like I recommended earlier?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Johan Bijnens SSC Guru Points: 135591 More actions · Answer 8

Another thing that may be in the game is ...

What version of SQL2008 are you on:

Select @@version

Are you on SP2 yet ?

or is it SQL2008R2 without a cumulative update ... ( if i'm correct CU4 is current.)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

bkuhnke SSC Eights! Points: 883 More actions · Answer 9

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

Just as I thought, it is overall performance, not just with the fetch. I changed the cursor from the "real" proc where this problem first surfaced to do local static. In the explain, the fetch now looks ok (and actually flips through fast if i comment out the code) but to do the logic for just "top 10" records (of 450) it took over 20 seconds. On the "old" 2k server, all 450 records are processed, including the cursor, in 3 seconds.

Probly something to do with memory? It likes static cursor cause stores in tempdb rather than buffer? But the query plans for everything else are crazy cause it thinks it doesn't have memory maybe?

Ugh!

bkuhnke SSC Eights! Points: 883 More actions · Answer 10

Top wait time is:

REQUEST_FOR_DEADLOCK_SEARCH980404905326617312490532661

Johan Bijnens SSC Guru Points: 135591 More actions · Answer 11

If you can, apply the CU4 package.

-Is this on Windows 2008 R2 ?

If that is also the RTM version, you be hitting a known I/O bug:

my ref:

Hotfix Alert: Identifying poor I/O
http://blogs.msdn.com/benjones/
We have just released a hotfix to help identify poor I/O issues on Windows Server 2008 and Windows Server 2008 R2.
This is particularly relevant for SQL Server environments as it will the administrator to
determine if I/O performance issues are caused by the SAN.
Whilst the output is not particularly easy to consume, it is a good step towards identifying I/O issues.
The knowledge base articles:
Add a fix to improve the logging capabilities of the Storport.sys driver to troubleshoot poor disk I/O performance
in Windows Server 2008 R2 http://support.microsoft.com/kb/978000
A hotfix is available that improves the logging capabilities of the Storport.sys driver to troubleshoot
poor performance issues for the disk I/O in Windows Server 2008 http://support.microsoft.com/kb/978000

Please confirm if this is the case.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data and code to get the best help

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me