问题:

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

回答1:

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)

回答2:

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.

回答3:

Be warned : sometimes queries run faster the second time because the server has already done the disk IO and still has tables in RAM. Consider looking at IO costs in the execution plan.