Optimizer goes bad but then recovers

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
Possible that a SQL Server is using a cached execution plan that was
optimimal for the previous user but not for the other users? Don't know
about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next time.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.c om...
> 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
|||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.c om...
> 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
|||And you can do the checking by running profiler... Trace auto update
statistics, and SP plan reuse..
If you have a particular sp in mind... TEST it using extreme values for
parameters, looking at the execute plan to see if any plan changes between
index seek, and index scan...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
news:ec64e251.0410201939.5b4324a2@.posting.google.c om...
> 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
|||I agree about your assumption re: the 'not optimal for previous user', but I
don't think I woudl recommend dbcc freeproccache on a busy production
server.
You could simple sp_recompile that one procedure in question...
If the procedure is not exectued all that often and the cost of a recompile
is low compared to the cost of a 'bad plan' you might simply create the proc
using the with recompile.
Or even better... yuo might research what set of paramaters require widely
divergent exection plans and then create two new procs. Have the current
proc call each of the child procs based on it's knowledge of which proc will
have the better plan.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23TIpE1ztEHA.1276@.TK2MSFTNGP12.phx.gbl...
> Possible that a SQL Server is using a cached execution plan that was
> optimimal for the previous user but not for the other users? Don't know
> about the 2 hours duration. Might give DBCC FREEPROCCACHE a try next
time.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Calvin Slater" <CalvinNSlater@.Hotmail.com> wrote in message
> news:ec64e251.0410201939.5b4324a2@.posting.google.c om...
>