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

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 nightlyPossible 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|||I would investigate whether it has to do with statistics. Perhaps auto-updat
e 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|||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.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|||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...
>

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

Optimization issue

hi All,
I have a 2.5 GIG SQL2K SP3 database with an Access XP
front end application. As the database grew, the responce
time has degraded dramatically. It is now taking minutes
to sell a service, when it should only take seconds. all
together I have 20 different databases all using the same
front end. they do not seem to be having this issue.
their DB's are closer to 700 Megs.
I ran a DBCC CheckDb. there were no errors found. Are
there any ways to optimize the database? Would update
statistics, and sp_recompile help?run profiler with the default template to find the slow
query or procedure,
then paste that procedure into query analyzer
and look at the execution plan for that query
>--Original Message--
>hi All,
>I have a 2.5 GIG SQL2K SP3 database with an Access XP
>front end application. As the database grew, the
responce
>time has degraded dramatically. It is now taking minutes
>to sell a service, when it should only take seconds. all
>together I have 20 different databases all using the same
>front end. they do not seem to be having this issue.
>their DB's are closer to 700 Megs.
>I ran a DBCC CheckDb. there were no errors found. Are
>there any ways to optimize the database? Would update
>statistics, and sp_recompile help?
>
>.
>