how to clean buffers - 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

General SQL Server Forums

New to SQL Server Programming

how to clean buffers

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-28 : 00:17:57

Dear All,
i'm trying increase the performance of one select statement.

after trying first time, at the second time it is giving results fastly.because the data is there already in the buffers.

how can i clean the buffers everytime after run the query?

i'm using

--dbcc dropcleanbuffers
--dbcc freeproccache

are these enough or need some more....
please guide me

Vinod
Even you learn 1%, Learn it with 100% confidence.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-28 : 01:43:25

Why clean buffer every time run the query? That hurts overall performance.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-28 : 01:49:00

then how can i expect the result correctly?
actually i'm changing some part of the query and testing it. if i test that on same query analyzer, it gives some different time. is there any way doing my requirement?

Vinod
Even you learn 1%, Learn it with 100% confidence.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-28 : 01:52:39

Fine for testing, but don't do it on prod server. etter to get execution plan to see if you can optimize the query.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-28 : 02:48:30

thank you ...i'm not doing the thing on prod server

Vinod
Even you learn 1%, Learn it with 100% confidence.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-28 : 17:10:18

What version of SQL are you running?

Try this:

SET STATISTICS PROFILE ON
GO
SET STATISTICS IO ON
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL') 
GO
DBCC FREESESSIONCACHE WITH NO_INFOMSGS 
GO
DBCC FREEPROCCACHE WITH NO_INFOMSGS 
GO
CHECKPOINT
GO

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-28 : 23:42:35

i'm running on both sql server 2000 and 2005

Vinod
Even you learn 1%, Learn it with 100% confidence.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-29 : 02:07:24

if you are testing a proc, you can drop/recreate the proc each time. Dropping the proc drops any query plans for the proc. so you dont have to clear entire buffer just for the proc.

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

pootle_flump

1064 Posts

Posted - 2007-11-29 : 06:39:42

quote:
Originally posted by sunsanvin

then how can i expect the result correctly?

You will get correct data whether or not the pages are cached or read from the disk (assuming you don't do something silly like use NOLOCK). To optimise your code you should not be measuring time. Use SET STATISTICS IO ON and pay attention to the logical reads and\ or use the query plans as others have mentioned.

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources