Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted March 2, 2017

Clean Your Buffers for Accurate Performance Testing

By Greg Larsen

In order to do accurate performance testing between multiple runs of a SQL Server command or scripts you need to remember to clean the buffer, procedure and system cache between each test run.  Without cleaning the buffer and caches between runs, your test query may run faster because some or all of the data needed is found in memory.  To accomplish cleaning/clearing the buffers and caches you will need to run a number of different commands.

The first thing you want to do is to flush all the dirty pages from the buffer cache.  Dirty pages are those pages that have been updated in the buffer pool but have not been written to disk.  To get these dirty pages out of the buffer pool you just need to run a CHECKPOINT command.  By running the CHECKPOINT command all dirty pages for the current database will be written to disk. 

The second thing is to remove all the pages from the buffer pool.  You want to do this so that each test run will not find any existing pages in the buffer pool.  By cleaning the buffer pool before each test run SQL Server will have to re-read the data it needs from disk.  To clean the buffer pool you execute the command: DBCC DROPCLEANBUFFERS. 

Next you should remove your execution plans from the procedure cache.  To remove all the plans from the procedure cache you can run the “DBCC FREEPROCCACHE” statement.  Or you can pass parameters, like a plan handle to remove just a single cached plan.  I find it easiest to remove all cached plans, by just running the “DBCC FREEPROCCACHE” statement. Of course, I only do this in an isolated testing environment, because this removes all plan and cache information for all batches that are running, and not just my test queries.

Lastly you should consider clearing the system caches as well.  To accomplish this, you can run the “DBCC FREESYSTEMCACHE('ALL')” statement.   Cleaning this cache, as well as all the other items discussed, will allow you to compare your execution performance between multiple runs of your TSQL command or scripts.

See all articles by Greg Larsen

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM