Testing Query Performance - clear out cache

[traceable1]

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?

Thanks in advance for any help you can provide -

[Jack Vamvas]

Use" dbcc freeproccache" to clear the proc cache

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"traceable1" <tr*****@gmail.com> wrote in message
news:11*********************@t31g2000cwb.googlegro ups.com...

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?

Thanks in advance for any help you can provide -

[Tom Moreau]

Try:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"traceable1" <tr*****@gmail.com> wrote in message
news:11*********************@t31g2000cwb.googlegro ups.com...

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?

Thanks in advance for any help you can provide -

[Erland Sommarskog]

traceable1 (tr*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[traceable1]

That did it! Thank you very much!!!