Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 344
codingguy3000 Avatar asked Aug 17 '09 15:08

codingguy3000


2 Answers

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)
like image 157
Andrew Avatar answered Oct 20 '22 12:10

Andrew


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.

like image 40
Gavin Avatar answered Oct 20 '22 12:10

Gavin