{"id":319,"date":"2014-10-23T12:12:59","date_gmt":"2014-10-23T11:12:59","guid":{"rendered":"http:\/\/www.codereview.co\/?p=319"},"modified":"2014-10-23T09:23:40","modified_gmt":"2014-10-23T08:23:40","slug":"how-can-i-clear-the-sql-server-query-cache","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/how-can-i-clear-the-sql-server-query-cache\/","title":{"rendered":"How can I clear the SQL Server query cache?"},"content":{"rendered":"<p><span style=\"color: #000000;\"><strong>Scenario<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">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.\u00a0 To determine the best overall solution, each option is tested and the results are recorded.\u00a0 As lessons are learned options may be combine for a better end result and often as data is cached the overall query performance improves.\u00a0 Unfortunately, with the data in cache testing each subsequent option may lend itself to an apples to oranges comparison.\u00a0 How can I ensure during each execution of a new set of code that the data is not cached?<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>\u00a0<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>Solution<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">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.\u00a0 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.\u00a0 Then the DBCC DROPCLEANBUFFERS command can be executed to remove all buffers from the buffer pool.\u00a0 Here is a quick code snippet to serve as an example:<\/span><\/p>\n<table width=\"250\">\n<tbody>\n<tr>\n<td><span style=\"color: #000000;\">USE &lt;YOURDATABASENAME&gt;; GO CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"color: #000000;\">Although the CHECKPOINT and\u00a0 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.\u00a0 If you are testing via a batch file (or similar) then you could issue &#8216;net stop mssqlserver&#8217; and &#8216;net start mssqlserver&#8217; DOS commands.\u00a0 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 &#8216;net start&#8217; command or via one of the GUI tools.\u00a0 Although these options are possible, they are not recommended.\u00a0 These last set of commands will shut down your SQL Server instance or machine, which is probably unneeded.<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong><em>A few words of caution&#8230;<\/em><\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">It is not recommended to issue the CHECKPOINT\\DBCC DROPCLEANBUFFERS, the &#8216;net stop mssqlserver&#8217;, T-SQL SHUTDOWN command or restarting Windows on production systems just for the sake of testing.\u00a0 These commands could have detrimental results to your environment.\u00a0 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.\u00a0 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.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #000000;\"><strong>Thanks for reading this article,<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>Next steps :<\/strong><\/span><\/p>\n<ol>\n<li><span style=\"color: #000000;\"><b><strong>Add this article to your database toolkit<\/strong><\/b><\/span><\/li>\n<li><span style=\"color: #000000;\"><strong>Share this with your colleagues because Sharing is Learning<\/strong><\/span><\/li>\n<li><span style=\"color: #000000;\"><strong>Comment below if you need any assistance<\/strong><\/span><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Scenario 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.\u00a0 To determine the best overall solution, each option is tested and the results are recorded.\u00a0 As lessons are learned options may be combine for a better end result [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[32,3],"tags":[178,176,90,91,179,13,12,177],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/319"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=319"}],"version-history":[{"count":3,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/319\/revisions"}],"predecessor-version":[{"id":385,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/319\/revisions\/385"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=319"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=319"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=319"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}