Some Initial Insert Test Benchmarks on SQL Server 2008 R2

They say that imitation is the sincerest form of flattery. I have been following along as Paul Randal has been doing a series of blogs illustrating some benchmarks on a collection of actual server hardware he has at his house. Unfortunately, I don’t have any server class hardware to play with at my house, but I do have a pretty modern and capable desktop class machine that I can use to run a similar series of tests. My tests will be much shorter in duration, so I can quickly try different things.

This machine has an ASUS P7P55D motherboard, with an Intel P55 Express chipset (which has six 3.0Gb/s SATA ports). It has an Intel Core i7 860 2.8GHz CPU, which has four cores, plus hyper-threading, along with 6GB of DDR3 RAM. It has (1) Western Digital Caviar Black 1TB 7200rpm SATA 3Gb/s drive, (3) Seagate Barracuda 7200.12 500GB 7200rpm SATA 3Gb/s drives, (1) Western Digital Raptor 74GB 10000rpm SATA 1.5Gb/s drive, and (1) Intel X-25M G2 80GB SSD SATA 3Gb/s drive.  To start with, these drives are setup like this:

C: Drive    1TB   WD1001FALS         Operating system, SQL Server binaries, TempDB data files, SQL data and log files

K: Drive    74GB  WD740ADFD        Alternate location for SQL log file

L: Drive     80GB X-25M G2            Alternate location for SQL log file, TempDB log file

P: Drive     500GB  ST3500418AS     Alternate location for SQL data file

Q: Drive    500GB  ST3500418AS     Alternate location for SQL data file

S: Drive     500GB  ST3500418AS     Alternate location for SQL data file

It is running x64 Windows Server 2008 R2 Enterprise Edition and the November CTP of x64 SQL Server 2008 Enterprise Edition.

The idea here is to start out with a purposely “bad” configuration of SQL Server and of the available hardware. Then, I will try making some changes to the configuration to improve the insert performance. Big disclaimer: You should not be running a production instance of SQL Server on a desktop class machine without any hardware or storage redundancy.  I am going to do some things that make this even less reliable in the interest of attempting to improve performance for this test, so don’t do this in production!

The baseline “bad” configuration puts both the single SQL data file and the SQL log file on the C: drive, along with the (4) TempDB data files. The database is intentionally created with a very small data and log file and very small autogrowth settings. The SQL Server 2008 R2 Service account was not given the “Perform Volume Maintenance” right, so we don’t have Windows Instant File Initialization.

My test calls a simple stored procedure that inserts one million rows into an empty table in a newly created database, using one connection. I clear out the SQL wait stats before calling the SP, then record the cumulative wait stats, along with the elapsed time and rows/sec for each test run. One million rows in this table is a little less than 8GB in size. This is how I created the database for the baseline:

CREATE DATABASE InsertLoadTest ON  PRIMARY 
( NAME = N'InsertLoadTest', FILENAME = N'C:\SQLData\InsertLoadTest.mdf', 
  SIZE = 3MB , FILEGROWTH = 1MB ), 
 FILEGROUP [MAIN] 
( NAME = N'InsertLoadTest1', FILENAME = N'C:\SQLData\InsertLoadTest1.ndf', 
  SIZE = 3MB , FILEGROWTH = 1MB ) 
 LOG ON 
( NAME = N'InsertLoadTest_log', FILENAME = N'C:\SQLLogs\InsertLoadTest_log.ldf', 
  SIZE = 1MB , FILEGROWTH = 1MB )

The table looks like this:

-- Create BigTable
CREATE TABLE [dbo].[BigTable](
    [BigID] [bigint] IDENTITY(1,1) NOT NULL,
    [BigChar] [char](4100) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY CLUSTERED 
(
    [BigID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [MAIN]
) ON [MAIN]
GO

ALTER TABLE [dbo].[BigTable] 
ADD CONSTRAINT [DF_BigTable_BigChar]  DEFAULT ('a') FOR [BigChar]
GO

The insert stored procedure looks like this:

/* AddDataToBigTable ============================= 
 Description : Insert rows into BigTable 
  
 Author: Glenn Berry
 Date: 2/5/2010
 Input: 
 Output: 
 Used By: 
  
 Last Modified          Developer        Description 
 2/5/2010                Glenn Berry        Creation
 ================================================*/ 
CREATE PROCEDURE [dbo].[AddDataToBigTable]
(@NumberOfRows bigint)    
AS
    SET NOCOUNT ON;

    DECLARE @Counter bigint = 0;
    DECLARE @Start   datetime = GETDATE();
    DECLARE @End     datetime;
    DECLARE @ElapsedTime int = 0; 
    DECLARE @RowsPerSecond int = 0;

    WHILE (@Counter < @NumberOfRows)
        BEGIN
           INSERT INTO dbo.BigTable DEFAULT VALUES;
           SELECT @Counter += 1;
        END; 

    -- Calculate elapsed time and rows/second
    SET @End = GETDATE(); 
    SET @ElapsedTime = CONVERT(INTEGER, DATEDIFF (second, @Start, @End));
    SET @RowsPerSecond = @NumberOfRows/@ElapsedTime;
    
    -- Record results in local table
    INSERT INTO dbo.Results
    (StartTime, EndTime, ElapsedTime, NumberOfRows, RowsPerSecond) 
    VALUES (@Start, @End, @ElapsedTime, @NumberOfRows, @RowsPerSecond);
    
    RETURN;

Run 1

As expected, the baseline Run 1 showed very poor performance, with only 835 rows/second being inserted into the table, as the one million row insert took 1197 seconds. The top waits were PREEMPTIVE_OS_FLUSHFILEBUFFERS at 47.2% of waits and WRITELOG at 39.8% of waits. Both the data file and the log file are on the same drive, plus the data file is rapidly growing from 3MB to 7.8GB in 1MB increments. Surprisingly, the log file did not grow at all, which indicates that the Checkpoints were able to complete before the log file filled up.

Run 2

The only change for Run 2 was that I granted the “Perform Volume Maintenance” right to the SQL Server 2008 Service account, so we would have Windows Instant File Initialization for the data file. I restarted the SQL Server Service so the change would take effect, and created a fresh copy of the database with no other changes. Insert performance improved slightly here, with the total elapsed time going down to 1161 seconds and the insert rate going up to 861 rows/second. The top waits were PREEMPTIVE_OS_FLUSHFILEBUFFERS at 49.5% of waits and WRITELOG at 42.2% of waits. Not having to “zero out” the 1MB file growths helped a little bit, but growing the data file so many times in such a small increment really slows us down.

Run 3

For this run, I created a fresh copy of the database with the data file in the default MAIN file group initially set to be 8GB. This would prevent the file having to be grown at all during the test. I restarted the SQL Server Service, and ran the test. Insert performance improved dramatically, with the total elapsed time going down to 382 seconds and the insert rate going up to 2617 rows/second. The top wait was WRITELOG at 99.6%.

Run 4

For this attempt, I created a fresh copy of the database where I moved the transaction log to the K: drive (which is the 10,000rpm WD Raptor 74GB SATA drive). I restarted the SQL Server Service, and ran the test. Insert performance improved again, with the total elapsed time going down to 320 seconds and the insert rate going up to 3125 rows/second. The top wait was WRITELOG at 99.0%.

Run 5

For Run 5, I created a fresh copy of the database where I moved the transaction log to the L: drive (which is the Intel X-25M G2 80GB SSD). I restarted the SQL Server Service, and ran the test. Insert performance improved slightly, with the total elapsed time going down to 288 seconds and the insert rate going up to 3472 rows/second. The top wait was still WRITELOG at 98.3%. I expected the Intel SSD to do a little better than that.

Run 6

This time, I created a fresh copy of the database where I moved the transaction log to the P: drive (which is the 7200rpm 500GB Seagate Barracuda 7200.12 SATA drive). I restarted the SQL Server Service, and ran the test. Insert performance improved again, with the total elapsed time going down to 267 seconds and the insert rate going up to 3745 rows/second. The top wait was WRITELOG at 97.5%. What else can we do to help the log file?

Run 7

For Run 7, I used Windows to create a two drive RAID 0 array, using two of the 500GB Seagate Barracuda 7200.12 drives. This is “bad” for several reasons, so don’t do this on a production server! RAID 0 gives you no redundancy, and actually increases your risk of data loss, since if either drive fails, all of the data is gone. Using Windows to manage the array is also not a good thing to do. At any rate, I created a fresh copy of the database where I moved the transaction log to the P: drive (which is the two drive RAID 0 array). I restarted the SQL Server Service, and ran the test. Insert performance actually decreased, with the total elapsed time going up to 293 seconds and the insert rate going down to 3412 rows/second. The top wait was WRITELOG at 98.8%. I expected the RAID 0 to help here.

Run 8

Run 8 was the same as Run 7, except that I created a three drive RAID 0 array with all three of the Seagate Barracuda drives. I restarted the SQL Server Service, and ran the test. Insert performance improved, with the total elapsed time going down to 240 seconds and the insert rate going up to 4166 rows/second. The top wait was still WRITELOG at 97.5%.

Runs 9 and 10

I tried moving the main data file to the Intel SSD, and saw a decrease in performance, and I tried moving the data file to the 74GB WD Raptor and I also saw a decrease in performance. That 1TB Western Digital Caviar Black drive seems to work very well for this workload. The top wait was still WRITELOG.

Here are all of the test results:

Test	Time	Insert Rate	Configuration
Run 1	1197 seconds	835/second	Baseline
Run 2	1161 seconds	861/second	Granted "Perform Volume Maintenance"
Run 3	382 seconds	2617/second	Pre-allocated data file to 8GB
Run 4	320 seconds	3125/second	Moved log file to K: drive
Run 5	288 seconds	3472/second	Moved log file to L: drive
Run 6	267 seconds	3745/second	Moved log file to P: drive
Run 7	293 seconds	3412/second	Moved log file to two drive software RAID 0
Run 8	240 seconds	4166/second	Moved log file to three drive software RAID 0
Run 9	342 seconds	2923/second	Moved data file to L: drive. Moved log file to three drive software RAID 0
Run 10	297 seconds	3367/second	Moved data file to K: drive. Moved log file to three drive software RAID 0

 

Final Notes

The two biggest gains were from pre-allocating the data file to an appropriate size, and from moving the transaction log file to a dedicated drive separate from the data files. I am still stuck with WRITELOG as the top wait. Possible next steps are adding RAM to get to 8GB (which will allow checkpoints to happen a little less frequently), adding more 500GB Seagate Barracuda 7200.12 drives to the RAID 0 array for the log file, and getting another SATA controller (possibly with hardware RAID and a cache) and placing the log drive(s) there. I could also enable PAGE data compression on the clustered index, (which would sort of be cheating).

As my Dad used to say about auto engines “there’s no substitute for cubic inches”, while here we see there is no substitute for spindles and a capable I/O subsystem. What other “tweaking” or configuration changes should I try, leaving everything else the same?