deleting query cache command - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

deleting query cache command

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2007-04-26 : 12:08:06

When I am testing for performance between two queries, it is obvious sql server is cacheing the results or execution plan etc. becuase after I run a query once, subsequent calls the to the same query are much faster.

Is there a way for me to delete the query/execution plan cache so I can see how fast a query would be on the first run?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-26 : 12:14:30

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-26 : 12:15:54

You can do a DBCC FREEPROCCACHE but it clears entire cache not just for one proc.
Alternatively, if you dont want to do that, you can drop/recreate the proc each time so the plan is also deleted. you can make it part of your script as:


IF EXISTS (SELECT * FROM Sys.objects WHERE name = '...' AND Type = 'p')
   DROP PROC ...
GO
CREATE PROC ...

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2007-04-26 : 12:39:49

actually its not even a stored procedure, I'm just firing the query from Query Analyzer.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-26 : 12:42:56

Either you can use DBCC FREEPROCCACHE or wrap the query in a proc and do the drop/create each time.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-26 : 13:39:10

quote:
Originally posted by sql777

When I am testing for performance between two queries, it is obvious sql server is cacheing the results or execution plan etc. becuase after I run a query once, subsequent calls the to the same query are much faster.

Is there a way for me to delete the query/execution plan cache so I can see how fast a query would be on the first run?

I doubt that is why the query runs fast the second time.

More than likely is is because the data required by the query is in the data cache, and it does not need to get it from disk the second time.

You use this to clear the data cache:

DBCC DROPCLEANBUFFERS

CODO ERGO SUM

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-26 : 13:47:53

MVJ

SQL Server could have cached the data for the second run and also has the plan ready to use. During the first run there is an additional overhead of figuring out the best indexes to use and create a plan. As much as it is kinda hard to wipe out everything for each run, perhaps a combination of DROPCLEANBUFFERS and FREEPROCCACHE can help.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources