By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Testing Query Performance - clear out cache

P: n/a

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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a

That did it! Thank you very much!!!

Apr 6 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.