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;
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.
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.
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%.
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%.
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.
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?
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 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:
|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|
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?