Re: Optimizer goes bad but then recovers

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 12:12:36 +0200

I would investigate whether it has to do with statistics. Perhaps auto-update of statistics kicks in
,does a not so good job, then kicks in after two hours and then does a good job again. Perhaps you
can see a trend whether this is happening in conjunction with some mass load, removal or update? 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Calvin Slater" <CalvinNSlater@Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@posting.google.com...
> I have a 10 gig database that is pretty consistent on performance.
> But every few days some of the main stored procedures get a bad
> execution plan which kills performance.  It has happened while we have
> had many users hitting the database and also when only a single person
> is running.
>
> The interesting part is that SQL Server has always corrected itself
> after about 2 hours.  We have not been able to repeat this in the test
> lab.  It only happens in production - lucky me.
>
> Sometimes just doing a sp_updatestats will cure it for a while.  One
> time we changed a stored procedure to set ARITHABORT ON and the system
> recovered immediately.
>
> Any clue as to what might cause the optimizer to go a miss and
> likewise why does it always recover in 2 hours?
>
> Thanks
>
> Hardware / Software being used
>   ASP.NET C# application using SqlClient
>   SQL Server 2000 Standard Edition, SP3a
>   Full rebuild of indexes is performed nightly