SQL Server 2005 How Do You Clear Out a Query Execution Plan

Question

Hello fellow programmers. I have a SQL Server 2005 query that is taking a long time to process the first time through. After the first run the query works much faster. It goes from one minute to one second.

I know that SQL Server is caching an execution plan (is that the right term? ). What I want to do is clear out this execution plan so that I can replicate the issue better. I'm trying to tune the query.

Does anyone know if this possible and how to do it?

Thanks in Advance

Answer 1

DBCC FREEPROCCACHE

Answer 2

If you want to clear it then:

DBCC FreeProcCache
DBCC DropCleanbuffers

If you just want to force a query recompilation each time, then add a query hint to the end of the query:

OPTION (RECOMPILE)

Answer 3

This is what I run when ever I want to clear them

DBCC freeproccache
DBCC dropcleanbuffers
go

If I'm performance testing a query I usually just paste that at the top of the query so every time it runs its running with a clear cache.