Posted by Erland Sommarskog on 04/04/06 00:09

traceable1 (tracykc@gmail.com) writes:
> I am trying to improve the performance of a query. No matter how bad
> it runs the first time, it runs really fast the second time.
>
> So how can I tell if I've done anything to improve the query if it
> always comes back quickly after the first run? I assume the
> query/data/plan is in cache - how can I clean it out for my session?

As pointed out by others you can use DBCC FREEPROCCACHE to clear the
plan cache, and DBCC DROP CLEANBUFFERS to clean the data cache. But
don't do this on a production machine, as it affects the entire server.

Another reason you get better performance the second time, could be that
SQL Server has auto-statistics and improved statistics gives you a
better query plan.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]