avoid reusing query plan..

Hi,

I'm trying to test some queries in SQL analyser without reusing the query plan (already cached). I know that there is a way to avoid that but I don't remember right now. Another option would be to restart MS SQL service but I don't want to do that.
Any thoughts...?

Thanks,

S.How about...

DBCC FREEPROCCACHE|||Does it also clear the Query cache ??|||Originally posted by Enigma
Does it also clear the Query cache ??

Not sure, never had the need..

Remarks
Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.|||Thanks guys,

Well, I hoped to find a way to clear the procedure cache and the buffer pages as well, but DBCC FREEPROCCACHE does only the first part of it.
Actually I was trying to optimize a stored proc. and examine the impact directly using SQL Analyser. When I execute the SP for the first time, it takes around 3 sec. to complete. Once the data pages buffered and execution plan reused, it takes around 1 sec. (FREEPROCCACHE doesn't alter it much)
So it seems that FREEPROCCACHE has the same effect as CREATE PROCEDURE WITH RECOMPILE but nothing more.
Thanks anyway,

S.|||Look up Buffer manage object in BOL...

Haven't seen a way to do what you're looking for...

but hey ...1 second ain't bad....

1 mississippi...

done|||Originally posted by dbadelphes
Thanks guys,

Well, I hoped to find a way to clear the procedure cache and the buffer pages as well, but DBCC FREEPROCCACHE does only the first part of it.
Actually I was trying to optimize a stored proc. and examine the impact directly using SQL Analyser. When I execute the SP for the first time, it takes around 3 sec. to complete. Once the data pages buffered and execution plan reused, it takes around 1 sec. (FREEPROCCACHE doesn't alter it much)
So it seems that FREEPROCCACHE has the same effect as CREATE PROCEDURE WITH RECOMPILE but nothing more.
Thanks anyway,

S.

Try DBCC DROPCLEANBUFFERS. This removes any cached data. I use this and dbcc freeproccache when doing any comparison testing.|||That's what I was looking for.
Thanks a lot...(go Leafs go.. :) )

S.

Originally posted by homer37
Try DBCC DROPCLEANBUFFERS. This removes any cached data. I use this and dbcc freeproccache when doing any comparison testing.