July 27, 2006 at 6:31 am
We have a script that uses cursor with many joins that run fairly quickly in SQL2000 with SP3 (30secs), but runs extremely long in SQL2000 SP4 (1hr 30 mins). After running an explain, we have determined with SP3 it uses a worktable and with SP4 it does not. There no difference that we can tell in the configurations between the two servers or database. When we add TOP 200000 to the select statement, it started using worktables under SP4.
Has anyone else run into this? Is there anything else, I may have missed?
July 28, 2006 at 8:33 am
well the usual list to start:-
Is the harware the same, are the indexes the same. Have you updated stats? Have you compared the query plans to spot the differences.
Is the actual data the same. How have you diagnosed the creation of a worktable?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply