Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 attend our next webcast













































   Got a SQL tip?
            We want to know!

Clearing Cache for SQL Server Performance Testing

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (7)   |   Related Tips: More > Testing

Problem
When conducting performance testing and tuning on a new system, most of the time a number of options are outlined to potentially correct the performance problem.  To determine the best overall solution, each option is tested and the results are recorded.  As lessons are learned options may be combine for a better end result and often as data is cached the overall query performance improves.  Unfortunately, with the data in cache testing each subsequent option may lend itself to an apples to oranges comparison.  How can I ensure during each execution of a new set of code that the data is not cached?

Solution
If all of the performance testing is conducted in SQL Server the best approach may be to issue a CHECKPOINT and then issue the DBCC DROPCLEANBUFFERS command.  Although the CHECKPOINT process is an automatic internal system process in SQL Server and occurs on a regular basis, it is important to issue this command to write all of the dirty pages for the current database to disk and clean the buffers.  Then the DBCC DROPCLEANBUFFERS command can be executed to remove all buffers from the buffer pool.  Here is a quick code snippet to serve as an example:

USE <YOURDATABASENAME>;
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

Although the CHECKPOINT and  DBCC DROPCLEANBUFFERS commands seem to be the most elegant approach because they can be included in your T-SQL test scripts, you also can achieve the same results by either restarting the SQL Server instance or restarting Windows.  If you are testing via a batch file (or similar) then you could issue 'net stop mssqlserver' and 'net start mssqlserver' DOS commands.  As a side note, you also have the option to shutdown SQL Server via the T-SQL SHUTDOWN command, but would need to restart the services via either the 'net start' command or via one of the GUI tools.  Although these options are possible, they are not recommended.  These last set of commands will shut down your SQL Server instance or machine, which is probably unneeded.

A few words of caution...

It is not recommended to issue the CHECKPOINT\DBCC DROPCLEANBUFFERS, the 'net stop mssqlserver', T-SQL SHUTDOWN command or restarting Windows on production systems just for the sake of testing.  These commands could have detrimental results to your environment.  It is recommended to only issue these types of commands in testing environments with coordination among your team due to the impact to the overall SQL Server.  In addition, keep in mind that if you do issue these commands only in test environments that if multiple tests are being conducted simultaneously issuing the CHECKPOINT and DBCC DROPCLEANBUFFERS commands may skew results for other testers.

Next Steps

  • As you conduct performance testing in the future consider including the CHECKPOINT and DBCC DROPCLEANBUFFERS command in each of your scripts to ensure cached data is not benefiting later executions of your code.
  • As you test, it may be a good idea to capture the query execution results for cold and warm cache.
  • Although, time needed for a query is important, it is also a good idea to review the query plans for the code to determine the best cost among the options.
  • For related information check out the following:

 



Last Update: 10/26/2007


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, August 25, 2014 - 1:20:54 PM - Jeremy Kadlec Read The Tip

Ali,

I would suggest digging into the issue more to determine the root cause of the issue.  I would also determine the steps to duplicate the issue so you can determine the corrective actions. 

This could be a variety of issues, so I would try to pinpoint where the issue is occurring by isolating each portion of your application and eliminate possibilities.

Thank you,
Jeremy Kadlec
Community Co-Leader


Sunday, August 24, 2014 - 2:32:33 AM - ali Read The Tip

Hi Guys,

I like your site verymuch!!

I have a question, please help/suggest me. 

I have a production SQL Server 2008 where there are 4 significant stored procedures running in every 15 seconds(Borwser aurot refresh) from different users(100+ users connect to site from diff locations) 

Sometimes there is no data coming from these stored procedures and application is timing out  

So we are running follwoing queries to resolve the issue in productuion server

DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE

Now we are planing to keep this as part of weekend maintenance activity, so please advise what are the parameters we have to check and what is best practices to clear cache?

Thanks,

Ali


Thursday, June 20, 2013 - 5:27:41 PM - Jeremy Kadlec Read The Tip

Raj,

I would check out these tips (http://www.mssqltips.com/sql_server_business_intelligence_tips.asp) as a starting point.

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

 


Thursday, June 20, 2013 - 12:06:36 PM - Raj Read The Tip

Hello Everyone,

I have a set of cubes in ssrs 2005 which I haven't worked before on these cubes.Now I have to check the performance,design of these cubes and suggest any changes or modifications for these cubes if they have any .I dont know where and how to start,so can anybody suggest me  anything.Thanks I really appreciate your help thanks

 


Thursday, April 15, 2010 - 10:49:47 AM - Repriser2010 Read The Tip

Also, try to differentiate your are testing "recompile" or not.

Never "test" on production environment. "Tune" on production environment after test on QA environment.


Saturday, November 03, 2007 - 10:24:06 AM - admin Read The Tip

Bharathy,

Thank you for the feedback on the tip.  In the scenario where our team used the script (CHECKPOINT and DBCC DROPCLEANBUFFERS) outlined in this tip and found value was in these testing scenarios: 

  • Change database design
  • Change indexes (clustered and non-clustered)
  • Changed partitions
  • Changed full text catalogs

Due to the significance of the changes, we wanted to be able to start each test run at the same point (all data committed with cleared cache) as all of the other tests to ensure we could observed the metrics with both cold and warm cache.

Thank you,
The MSSQLTips.com Team


Tuesday, October 30, 2007 - 1:02:50 AM - Bharathy Read The Tip
 

Generally in the production environment users will access the database concurrently, so the ‘datacache' could holds the data for the repeatedly called SQLs. In the same way, if the load testing run is trying to simulate the production scenario / usage, then the explicit call to clear the data cache could simulate the worst case scenario of the application usage rather than the real time requirement.  Even for the query tuning, better execute the SQL, at least for 3 to 4 times, and get the final statistics like query execution time (elapsed time), physical reads, logical reads, sorting & etc and tune it to reduced the these values. So, clearing the ‘datacahce' is not valid for performance testing.

 

 




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.