Optimising a query / stored procedure

I have a Stored Procedure based on a View which is running very slowly, so
we need to rewrite it. (Need to reduce from 10sec to 1sec)
Using Query Analyser we Executed the Stored Procedure and it takes 10 Sec.
However re-Executing with same parameters it only takes 1 sec, it's like the
Query Optimiser has cached or remembered the Execution Plan or something.
(If I use a new set or parameters then it takes 10 secs again)
Unless we can get a consistent result of how long the current SP takes to
run, there's no way of determining whether any changes are beneficial - is
there some way of clearing the "cache" or "un-remembering" the Execution
Plan so that we can get a consistent result to compare with ?
(This is SQL Server 2000 standard edition)Look at
DBCC DROPCLEANBUFFER
DBCC FREEPROCCACHE
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Richard" <richa@.heidmar.co.uk> wrote in message
news:EHNkb.10$to6.7@.newsr2.u-net.net...
> I have a Stored Procedure based on a View which is running very slowly, so
> we need to rewrite it. (Need to reduce from 10sec to 1sec)
> Using Query Analyser we Executed the Stored Procedure and it takes 10 Sec.
> However re-Executing with same parameters it only takes 1 sec, it's like
the
> Query Optimiser has cached or remembered the Execution Plan or something.
> (If I use a new set or parameters then it takes 10 secs again)
> Unless we can get a consistent result of how long the current SP takes to
> run, there's no way of determining whether any changes are beneficial - is
> there some way of clearing the "cache" or "un-remembering" the Execution
> Plan so that we can get a consistent result to compare with ?
> (This is SQL Server 2000 standard edition)
>|||... and CHECKPOINT in the beginning to get rid of dirty pages.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:ei6GoxulDHA.3316@.tk2msftngp13.phx.gbl...
> Look at
> DBCC DROPCLEANBUFFER
> DBCC FREEPROCCACHE
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Richard" <richa@.heidmar.co.uk> wrote in message
> news:EHNkb.10$to6.7@.newsr2.u-net.net...
> > I have a Stored Procedure based on a View which is running very slowly, so
> > we need to rewrite it. (Need to reduce from 10sec to 1sec)
> > Using Query Analyser we Executed the Stored Procedure and it takes 10 Sec.
> > However re-Executing with same parameters it only takes 1 sec, it's like
> the
> > Query Optimiser has cached or remembered the Execution Plan or something.
> > (If I use a new set or parameters then it takes 10 secs again)
> > Unless we can get a consistent result of how long the current SP takes to
> > run, there's no way of determining whether any changes are beneficial - is
> > there some way of clearing the "cache" or "un-remembering" the Execution
> > Plan so that we can get a consistent result to compare with ?
> >
> > (This is SQL Server 2000 standard edition)
> >
> >
>