Remove from Cache

I want to remove all the procedures from the cache... I used DBCC
FREEPROCCACHE but doesn't effect.
Any idea?
Thanks in advance.
Joh wrote:
> I want to remove all the procedures from the cache... I used DBCC
> FREEPROCCACHE but doesn't effect.
> Any idea?
> Thanks in advance.
How did you determine that the statement is not working as expected?
Maybe you are looking for "DBCC DROPCLEANBUFFERS" to clear data from the
cache.
David Gugick
Imceda Software
www.imceda.com
|||I ran stored procedure first time which took around 2 mins to retrieve the
data and at the second time it tooks 1 min and third time it took 30
seconds... when I used DBCC DROPCLEANBUFFERS command the stored procedure
took same 30 seconds why not 2 mins if it remove from the buffer?
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:#1uJWbvWFHA.3540@.TK2MSFTNGP15.phx.gbl...
> Joh wrote:
> How did you determine that the statement is not working as expected?
> Maybe you are looking for "DBCC DROPCLEANBUFFERS" to clear data from the
> cache.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Joh wrote:
> I ran stored procedure first time which took around 2 mins to
> retrieve the data and at the second time it tooks 1 min and third
> time it took 30 seconds... when I used DBCC DROPCLEANBUFFERS command
> the stored procedure took same 30 seconds why not 2 mins if it remove
> from the buffer?
>
Maybe there's a cache outside SQL Server that's at play or maybe there
was another process keeping the disks/CPUs busy the first execution. In
any case, you're much better off using Profiler to see the performance
of a query. The duration is important, but CPU is more telling as high
CPU always translates into longer durations. Also have a look at your
execution plans and make sure the queries are full-optimized.
David Gugick
Imceda Software
www.imceda.com
|||Yes but my question is that at the first time when procedure take 2 mins
then how come it take 1 min in the next execution .... and when I restart
my system then again it take the same process ... I want that when I execute
that procedure so it should take 2 mins. in every execution ... that 's why
I used DBCC DROPCLEANBUFFERS...
Thanks
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uwMRjGzWFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Joh wrote:
> Maybe there's a cache outside SQL Server that's at play or maybe there
> was another process keeping the disks/CPUs busy the first execution. In
> any case, you're much better off using Profiler to see the performance
> of a query. The duration is important, but CPU is more telling as high
> CPU always translates into longer durations. Also have a look at your
> execution plans and make sure the queries are full-optimized.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
sql