Using DBCC DROPCLEANBUFFERS When Testing SQL Server Performance

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.

&nbsp

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.

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.

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

• Use a similar test strategy to test different disk types and measure their performance
• Read this tip on Increasing Buffer Pool in SQL Server 2014

Ben Snaidero

Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.

• MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017