It’s quite difficult to work out if your kit will support the load that you want to put on it. Through my consultancy years I’ve often been in the position of trying to determine whether an existing system will be capable of scaling up to more users and more data – this is a difficult task, but there are methods.
In this article I suggest a method for determining the performance and ability of your hardware in servicing the write load for your system, specifically determining how many updates/inserts/deletes can be done with the performance of the transaction log being the only factor, so discounting concurrency (for the moment!).
We need to consider a couple of things before going any further, firstly an INSERT / UPDATE / DELETE is not complete and SQL Server will not allow the process to continue until it has been told that the data physically resides on in the transaction log on the disk (the LDF file), the MDF is written in a different matter, the throughput of INSERT’s, UPDATE’s and DELETE’s is directly related to how fast you can get data into the LDF on disk whereas the MDF is written in a different manner – dirty pages (changes) are batched up and written as a big burst of data around checkpoint time or when the SQL Server decides it needs the pages for some other data so has to write them out to disk.
Straight to the test, create a test database, change G: to where you want to test your transaction log performance and E: to your MDF file; try and keep them separate especially for this test but in practice writing to the transaction log is almost purely sequential and the MDF random so you want the transaction log on its own disks where nothing will be changing the disk head position.
CREATE DATABASE [IOMeter] ON PRIMARY
( NAME = N'IOMeter', FILENAME = N'e:\IOMeter.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'IOMeter_log', FILENAME = N'g:\IOMeter_log.ldf' , SIZE = 1048576KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'IOMeter', @new_cmptlevel=90
GO
GO
ALTER DATABASE [IOMeter] SET RECOVERY FULL
GO
ALTER DATABASE [IOMeter] SET PAGE_VERIFY CHECKSUM
GO
Let’s create our test table, this can be one of your own tables, in fact if you have the time you could actually use your own schema and test a specific stored procedure performance etc. but for this example I just want to illustrate some log behaviour so I’ll use a simple table…
USE IOMeter
GO
CREATE TABLE mytest (
id int not null identity primary key clustered,
somedata char(4000) not null
)
GO
CREATE TABLE io_results (
test_name varchar(100) not null,
entry_date datetime not null default( getdate() ),
write_ios int not null,
write_bytes bigint not null,
transaction_size int not null,
ios_per_row decimal( 10, 3 ) not null,
ios_per_xact decimal( 10, 3 ) not null,
bytes_per_io decimal( 10, 3 ) not null,
mbytes_per_sec decimal( 10, 3 ) not null,
rows int not null,
commits int not null,
duration_seconds int not null
)
Now for the test, the is your test harness, if you are going to use your own stuff then I would suggest putting it in another stored procedure and replace the INSERT mytest with a call to your stored procedure.
CREATE PROC test_perf
@test_name varchar(100),
@tran_batch_size int
AS
BEGIN
SET NOCOUNT ON
IF @@TRANCOUNT > 0
ROLLBACK -- Make sure no transaction open
WAITFOR DELAY '00:00:05' -- System settle, helps seperate stuff in PERFMON
TRUNCATE TABLE mytest
CHECKPOINT -- Write any dirty pages to disk
DBCC DROPCLEANBUFFERS -- Empty data cache
DBCC FREEPROCCACHE -- Empty execution cache
ALTER DATABASE IOMeter SET RECOVERY SIMPLE
CHECKPOINT
ALTER DATABASE IOMeter SET RECOVERY FULL
WAITFOR DELAY '00:00:05' -- System settle, helps seperate stuff in PERFMON
DECLARE @i int
DECLARE @c int
SET @i = 1
SET @c = 0
SELECT 'START' AS op, GETDATE() AS op_time, *
INTO #filestats
FROM ::fn_virtualfilestats( db_id(), 2 )
BEGIN TRAN
WHILE @i <= 100000
BEGIN
INSERT mytest ( somedata ) VALUES( CAST( @i AS char(4000) ) )
IF @i % @tran_batch_size = 0
BEGIN
SET @c = @c + 1
COMMIT TRAN
IF @i < 100000
BEGIN TRAN
END
SET @i = @i + 1
END
IF @@TRANCOUNT > 0
COMMIT TRAN
INSERT #filestats (
op, op_time, DbId, FileId, TimeStamp, NumberReads, BytesRead,
IoStallReadMS, NumberWrites, BytesWritten,
IoStallWriteMS, IoStallMS, BytesOnDisk, FileHandle )
SELECT 'END', getdate(), DbId, FileId, TimeStamp, NumberReads, BytesRead,
IoStallReadMS, NumberWrites, BytesWritten,
IoStallWriteMS, IoStallMS, BytesOnDisk, FileHandle
FROM ::fn_virtualfilestats( db_id(), 2 )
INSERT io_results (
test_name,
write_ios,
write_bytes,
transaction_size,
ios_per_row,
ios_per_xact,
bytes_per_io,
mbytes_per_sec,
rows,
commits,
duration_seconds )
SELECT @test_name,
Write_IOs = e.NumberWrites - s.NumberWrites,
Write_Bytes = e.BytesWritten - s.BytesWritten,
transaction_size = @tran_batch_size,
IOs_Per_Row = CAST( (e.NumberWrites - s.NumberWrites) AS decimal( 10, 3 ) ) / 100000,
IOs_Per_Xact= CAST( (e.NumberWrites - s.NumberWrites) AS decimal( 10, 3 ) ) / @c,
Bytes_Per_IO= CAST( ( e.BytesWritten - s.BytesWritten ) AS decimal( 20, 3 ) ) / (e.NumberWrites - s.NumberWrites),
MBytes_Per_Sec = ( CAST( ( e.BytesWritten - s.BytesWritten ) AS decimal( 20, 3 ) ) / DATEDIFF( second, s.op_time, e.op_time ) ) / 1048576,
Rows = (SELECT COUNT(*) FROM mytest),
Commits = @c,
duration_seconds = datediff( second, s.op_time, e.op_time )
FROM #filestats s
CROSS JOIN #filestats e
WHERE s.op = 'START'
AND e.op = 'END'
END
GO
If you are doing this using SQL Server 2000 you will need to modify the call to ::fn_virtual_filestats slightly – instead you need to hardcode the database id because db_id() won’t work.
Now our test, the point is to try different sizes of transaction so that you get a range of blocksizes being written to disk – this is what kills the SAN performance at a client where I was testing the CX300.
exec test_perf 'test 1', 1
go
exec test_perf 'test 1', 10
go
exec test_perf 'test 1', 100
go
exec test_perf 'test 1', 1000
go
exec test_perf 'test 1', 10000
go
exec test_perf 'test 1', 100000
And the results….
select commits, write_ios, write_bytes, transaction_size, ios_per_row, ios_per_xact, bytes_per_io, mbytes_per_sec, duration_seconds
from io_results
order by entry_date
As can be clearly seen the larger the transaction size the better throughput you get to the disk, note – the above is done against a SATA disk with DASD on my development box with nothing else using the disk (that is very important for these tests!!).
Look at the difference between a transaction size of single row to that of 1000 rows, the total amount written to the log with 1 row in the transaction is 467Mbytes with 1000 rows its 427Mbytes but more importantly look at the Mbytes per second we get and the duration difference, single row transaction is only managing 24Mbytes per second and a duration of 19 seconds, the 1000 row is a healthy 53Mbytes per second and 8 seconds duration!
Let’s look at the performance monitor graph to see what it’s telling us.
Reading from the left, 1 row transaction, 10, 100, 1000, 10000 and 100000.
There is considerably more log flushes/sec for smaller transaction sizes, also the log flush waits/sec is considerably higher the smaller the transaction size.
The graph also tells me that my optimum transaction blocksize is the one around 100 rows (equates to an IOP of around 56Kbytes). The log bytes flushed/sec levels out at that level and the Avg. Disk Write Queue Length starts to grow faster.
You can try this without even installing SQL Server, SQL Server does not allow outstanding IO when writing to the log, writes are done asynchronously yes but directly through the cache and too disk. SQL Server doesn’t make use of the Windows system cache. The way to try this performance test without installing SQL Server is to use IOMeter; download this free tool from http://sourceforge.net/projects/iometer/ and set up your tests using the following :-
On [Disk Targets] select the drive you want to test, add 1024 for Maximum Disk Size in sectors and make # Outstanding I/O’s 1; on [Access Specifications] choose 4K; 0% Read; 0% random and click Edit Copy, now change Transfer Request Size to the size of the transaction you want to simulate (4K, 64K etc…) and leave everything else.
You can play – try changing the Outstanding I/O’s to a higher number say 64 and you’ll see performance get significantly better for example with 1 Outstanding I/O my 64K test I’m getting 43Mbytes per second with 64 Outstanding I/O’s I get 82Mbytes per second. This is where the SAN people bastardise the tests – SAN’s need Outstanding I/O to bulk the data up so it can use larger IOP sizes, well – that’s my experience so far and I know its limited – EMC CX300 but there you go – people – please try this at home and feed me the results back for different SAN’s….
To summarise, what’s the point in doing all this? Well, SQL Server when writing data is absolutely dependant on the speed it can write stuff out to the transaction log, if you have an application (OLTP) with lots of recurrent small transactions (trading system for instance) then you’ll see big differences in system performance and scalability by getting the IO subsystem to support the load and the method above can be used to help prove that.
As with everything it’s not that simple, but, it goes a way towards helping you prove your performance and give a better guestimate as to scaling.
There is so much more to say on this and I'll try and a Part 2 soon, I'll also be doing other entries on this subject to cover the other stuff - concurrency, MDF writes etc... It will be there when time permits....