469,618 Members | 2,562 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,618 developers. It's quick & easy.

Testing Query Performance - clear out cache


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 -

Apr 3 '06 #1
4 13629
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 -

Apr 3 '06 #2
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 -

Apr 3 '06 #3
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
Apr 3 '06 #4

That did it! Thank you very much!!!

Apr 6 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Dylan Neild | last post: by
11 posts views Thread by Eugenio | last post: by
1 post views Thread by steve | last post: by
4 posts views Thread by laurenq uantrell | last post: by
6 posts views Thread by UnixSlaxer | last post: by
2 posts views Thread by Greg Stark | last post: by
21 posts views Thread by =?ISO-8859-1?Q?Fad=A5?= | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.