December 9, 2007 at 6:53 pm
Hello all,
I have a SQL statement containing a UNION clause that is giving me some problems. When I run the statement as a Stored Procedure it takes an average of 28seconds to respond and causes my processors to Spike to over 70%, virtually shutting down the server. When I run the exact same code outside of a Stored Procedure it runs in under a second and everything is fine. I have isolated the issue to the UNION statement because when I remove the union and run it as a SP the problem goes away. So, it appears to be the UNION statement and only when run as a SP? I have other SPs containing unions that don't have this problems.
The kicker is that the SP has been in production for a month with no problems and just started throwing the error this last monday. After one day of problems it just started working again. Then, today it started hanging my server once more.
I am running SQL Server 2000 (SP4) on a Win2k3 box.
Has anyone heard of this? Does it make sense to anyone why it would only have a problem as an SP?
Thanks for any suggestions.
December 9, 2007 at 8:34 pm
Have you rebooted? I've seen this where the plan gets booted for some reason and then dies. I haven't seen a good way to fix this. Hints can help at times. You could look for an boot the stored proc plan from sysproccache and see if it builds a better plan.
RECOMPILE has helped, but not always.
December 10, 2007 at 8:52 am
1) You state "started throwing this error" but don't specify an actual error.
2) Sprocs will often run differently than hard-coded statements. With manual executions the query optimizer has explicit values to use to build a query plan. Sprocs do not. Options include using local variables in the sproc, WITH RECOMPILE and dynamic sql. The latter is probably your best bet in SQL2000. Upgrade to SQL 2005 and you have several better options exposed to you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 11, 2007 at 1:11 am
If UNION ALL is applicable use that instead of UNION.
It goes a lot faster.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply