Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using DBCC DROPCLEANBUFFERS When Testing SQL Server Performance


By:   |   Read Comments (3)   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

Over the years I've had many discussions with other SQL Server Database Administrators as to whether or not you should have the database buffer pool loaded with data pages or empty when testing queries for performance. This tip will take a look at the effects on query performance for both cases by using the DBCC DROPCLEANBUFFERS statement to empty the buffer pool.

Solution

In my opinion the answer to this question, as strange as it may sound, is it really depends on your situation. Let me explain this in a little more detail. If you are administering a database system that is say refreshed daily or one that does not have much memory then you would probably want the database buffer pool empty when testing your queries. If on the other hand you have a system with lots of memory and it's rarely restarted then you would want to test your queries with the database buffer pool loaded with at least some of the data pages. Basically you want to test the query performance with the environment in as close a state as possible as it would be in your production environment.

Test Scenario Setup

Now let's get into the details of our test. We'll use the following T-SQL to load some test data into a table that we will use to test the query performance.

CREATE TABLE [dbo].[test](
 [col1] [bigint] NOT NULL,
 [col2] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS,
 [col3] [varchar](5) COLLATE SQL_Latin1_General_CP1_CS_AS,
PRIMARY KEY CLUSTERED ([col1] ASC) )
GO

CREATE NONCLUSTERED INDEX [IX_test_col2] ON [dbo].[test] ([col2] ASC)
CREATE NONCLUSTERED INDEX [IX_test_col3] ON [dbo].[test] ([col3] ASC)
GO

DECLARE @i INTEGER
DECLARE @x CHAR(1),@y CHAR(1),@z CHAR(1)
DECLARE @u INT,@v INT,@w INT
SELECT @i=1
WHILE @i < 500000
BEGIN

 SELECT @u=ROUND(RAND()+1,0),@v=ROUND(RAND()+1,0),@w=ROUND(RAND()+1,0)
 IF @u=1 
    SELECT @x=CHAR((RAND()*24)+65)
 ELSE
    SELECT @x=CHAR((RAND()*24)+97)
 IF @v=1 
    SELECT @y=CHAR((RAND()*24)+65)
 ELSE
    SELECT @y=CHAR((RAND()*24)+97)
 IF @w=1 
    SELECT @z=CHAR((RAND()*24)+65)
 ELSE
    SELECT @z=CHAR((RAND()*24)+97)

 INSERT INTO [dbo].[test] VALUES (@i,@x+@y+@z,@x+@y+@z)
 SELECT @i=@i+1
END

The other thing we need in order to perform this test is a way to measure the query performance. To do this we are going to use the SET STATISTICS IO command to measure the amount of disk activity as well as the SQL Profiler utility to measure things like total duration and CPU. The main reason for using SET STATISTICS IO versus just using SQL Profiler is it also provides a break down between logical and physical IO which is important for this test scenario.

Test Scenario Execution

Now let's get into the actual test. For our first test we will gather the statistics when running queries with the buffer pool fully loaded. The first 3 statements below will load all the data and index pages into the buffer pool. Once this completes, we can run a few simple SELECT statements and measure their performance. Here is the T-SQL to perform this test scenario.

SELECT * FROM test
SELECT DISTINCT col2 FROM test
SELECT DISTINCT col3 FROM test

SET STATISTICS IO ON
SELECT * FROM test WHERE col2='aNH'
GO
SELECT * FROM test
GO
SELECT * FROM test WHERE col3='ROb'
GO

For the next scenario we will gather the statistics when running the same queries with the buffer pool empty. In order to ensure the buffer pool is empty we'll use the DBCC DROPCLEANBUFFERS command. For those that are not familiar with using this command the following tip provides a good explanation on performing this task, Clearing the Cache for SQL Server Performance Testing. We'll use the same 3 SELECT statements as before with the only difference for this scenario being before each execution we will execute a DBCC DROPCLEANBUFFERS command to empty the buffer pool. Here is the T-SQL to perform this test scenario.

 
SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS
GO
SELECT * FROM test WHERE col2='aNH'
GO
DBCC DROPCLEANBUFFERS
GO
SELECT * FROM test
GO
DBCC DROPCLEANBUFFERS
GO
SELECT * FROM test WHERE col3='ROb'
GO

Test Scenario Results

Let's first take a look at the SQL Profiler results and see what effect if any the DBCC DROPCLEANBUFFERS command had. From the chart below we see that as far as CPU usage goes both scenarios used the same amount. Even the number of reads and writes is close. The only real difference is in the query duration, but these results don't really explain why that's the case.

Buffer Pool State Full Empty
  CPU Reads Writes Duration CPU Reads Writes Duration
Query 1 0 160 0 1 0 423 0 37
Query 2 171 2674 0 219 171 2674 0 271
Query 3 0 21 0 0 0 21 0 45

When we look a little deeper into the SET STATISTICS IO results we can see why we have the longer duration for the queries when the buffer pool is empty. When the buffer pool is full we are only performing logical reads, that is reads from the buffer pool, and when the buffer pool is empty we have to read everything from disk. Also note that even though SQL Server does perform quite a few read ahead reads this still effects the duration of the query.

Buffer Pool State Full Empty
  Scan Count Logical Reads Physical Reads Read-Ahead Reads Scan Count Logical Reads Physical Reads Read-Ahead Reads
Query 1 1 154 0 0 1 286 3 429
Query 2 1 2674 0 0 1 2674 8 2656
Query 3 1 21 0 0 1 21 14 0

Conclusion

The results from this test show how using DBCC DROPCLEANBUFFERS effects your query performance and emphasizes the fact I mentioned earlier, that you should always test your query performance with the test environment in as close a state as possible to your production environment.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Saturday, September 27, 2014 - 9:56:06 PM - Bill Back To Top

A system's state may not be stable. An inspection of Page Life Expectancy, Free Pages, and/or  sqlservr.exe's Working Set over a period of days to weeks (or even quarters) might reveal the instability. If a query is not run often, or if pages used to satisfy certain predicates have not recently been used, they may be flushed out of the bpool. At yet, my experience is that (at least OLTP) queries are submitted with the client setting the exact same command timeout (30 seconds by default). Some web-based apps/clients will impose an even shorter tolerance than 30 seconds.

Whether dropcleanbuffers (or for similar reasons, maxdop and procedure cache) should be tweaked during testing ultimately boils down not only to system stability concerns, but also a person's aversion to risk, or even whether a person is an optimist or a pessimist. My personal preference is to freeproccache, dropcleanbuffers, set maxdop to 1, and use the slowest available machine. That way I am closer to knowing the worst-case scenario for a query's duration (outside of blocking). I think it would be reasonable to call me a pessimist who wants to avoid risks, at all costs :).

Herbert - what you are describing is commonly attributed to SQL Server's cache (not RAID cache) becoming "warm". For example, nightly index rebuilds, statistical maintenance, and/or CHECKDBs can easily flush SQL Server's cache. Please let us know what you observe when you try your test under STATISTICS IO (the night before, the morning for the first run, and the morning for the 2nd run).


Monday, June 23, 2014 - 4:54:18 AM - Herbert Tobisch Back To Top

DBCC DROPCELANBUFFERS establishes a fresh database, but not a fresh system: If you come in the morning, drop buffers, re-execute query you will find the 2nd excution much faster than the first. I think this is due to the cache in our raid system which clearly is not affected by drpping database buffers.


Friday, June 20, 2014 - 3:04:09 AM - Arathy Vangala Back To Top

Thanks Ben.

As most of the testers are not aware of this, they often end up with wrong performance stats.


Learn more about SQL Server tools