Microsoft SQL Server forum - Testing Query Performance

Author

Testing Query Performance - clear out cache

traceable1

2006-04-03, 11:27 am

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

2006-04-03, 11:27 am

Use" dbcc freeproccache" to clear the proc cache

--
Jack Vamvas
____________________
_______________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

"traceable1" <tracykc@gmail.com> wrote in message
news:1144078630.796134.94070@t31g2000cwb.googlegroups.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

2006-04-03, 11:27 am

Try:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"traceable1" <tracykc@gmail.com> wrote in message
news:1144078630.796134.94070@t31g2000cwb.googlegroups.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

2006-04-03, 8:26 pm

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

traceable1

2006-04-06, 8:26 pm

That did it! Thank you very much!!!

Sponsored Links