Clean up the cache - 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 2000 Forums

Transact-SQL (2000)

Clean up the cache

Author

Topic

jpham
Starting Member

19 Posts

Posted - 2006-11-06 : 19:50:12

Hi All,
I have two SQL statement to run and want
to find which one is faster. I have ran the first one
and I want to clean up the cache so I can run the
second one. Do you know what command to clean up the cache.
Thanks,
JP

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-06 : 20:15:01

DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS

Kristen
Test

22859 Posts

Posted - 2006-11-07 : 01:26:29

I do:


-- SET SHOWPLAN_TEXT ON
GO
SET STATISTICS IO ON; SET STATISTICS TIME ON
GO

SELECT * FROM MyTable

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SET SHOWPLAN_TEXT OFF
GO

and then compare the two LOGICAL values (and ignore the PHYSICAL) to see the difference - smaller numbers are good!

Physical numbers are a bit hit&miss - data might be in the caching controller of the disk, stuff may be fragmented, other tasks may be launched by the scheduler, windows update may install a newer, faster, version of SQL Server for free

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources