Re: Optimizer goes bad but then recovers

From: Brian Moran (brian_at_solidqualitylearning.com)
Date: 10/21/04 

Date: Thu, 21 Oct 2004 09:58:00 -0400

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.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
>
>

Relevant Pages

  • Re: Business Rules & Referential Integrity
    ... With regards to my stored procedures and the inherent "White Elephant", ... fully appreciate your comments and will use Declared Referential Integrity ... in SQL Server and capture any named constraint errors/exceptions this way. ... >> about application design and implementation using DotNet. ...
    (microsoft.public.dotnet.distributed_apps)
  • Re: Working with task durations of minutes
    ... Just tell me I'm responsible for updating the server, everything will be "go" for me to do it starting Tuesday afternoon, and then get out of my way and let me do my job. ... From a project managment perspective it's usually enough for the PM to say "Update Server with Module Package X, 5 hours duration, starting Tuesday 1pm" and leave it up to Joe how he organizaes the details. ... If I'm painting a room and the painter and his assistant will box up the computers in it, move the furniture out, remove fixtures from the wall, mix and apply the paint I'll just list it as one task "Paint the Room." ... On the plan there are about 250 tasks that need ...
    (microsoft.public.project)
  • Re: Querying on dates in dd/mm/yyyy hh/mm/ss format using Access2002 And SQL Server 7
    ... server compares the parameter values to the date of the creation of the SP, ... > 1) you can set the record source to the name of a SP, ... > Sylvain Lafontaine, ing. ... >> I don't understand how I can use parameterized stored procedures to solve ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Backing up SBS 2003 for Disaster Recovery
    ... You will also need a backup with some disaster recover plan on how to use ... Modern backup software gives you specific DR capabilities. ... backup job that backs up the whole server, and then it will provide a CD ... you should test your plan. ...
    (microsoft.public.windows.server.general)
  • Re: [9fans] 9vx and local file systems
    ... For about seven years I had the luxury of running Plan 9 ... I have a second Plan 9 server with a bigger, ... I've used drawterm to connect to it, edit and compile venti, ... doesn't require a cpu server. ...
    (comp.os.plan9)