Saturday, October 21, 2006 5:29 PM tonyrogerson

Server Commissioning Part #1 - Transaction Log Performance

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.



( NAME = N'IOMeter', FILENAME = N'e:\IOMeter.mdf' , SIZE = 1048576KB , FILEGROWTH = 1024KB )


( NAME = N'IOMeter_log', FILENAME = N'g:\IOMeter_log.ldf' , SIZE = 1048576KB , FILEGROWTH = 10%)


EXEC dbo.sp_dbcmptlevel @dbname=N'IOMeter', @new_cmptlevel=90








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…





    id  int not null identity primary key clustered,

    somedata char(4000) not null



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





    IF @@TRANCOUNT > 0

        ROLLBACK            --  Make sure no transaction open


    WAITFOR DELAY '00:00:05'    --  System settle, helps seperate stuff in PERFMON




    CHECKPOINT                  --  Write any dirty pages to disk


    DBCC DROPCLEANBUFFERS       --  Empty data cache

    DBCC FREEPROCCACHE          --  Empty execution cache






    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 )




    WHILE @i <= 100000


        INSERT mytest ( somedata ) VALUES( CAST( @i AS char(4000) ) )


        IF @i % @tran_batch_size = 0


            SET @c = @c + 1

            COMMIT TRAN

            IF @i < 100000

                BEGIN TRAN



        SET @i = @i + 1


    IF @@TRANCOUNT > 0



    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 (











        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'





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


exec test_perf 'test 1', 10


exec test_perf 'test 1', 100


exec test_perf 'test 1', 1000


exec test_perf 'test 1', 10000


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 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....



Filed under:


# EMC CX300 SAN performance - 12MBytes/sec and 80% SAN utilisation on writing for single INSERT's...!

Monday, October 23, 2006 8:23 AM by Tony Rogerson's ramblings on SQL Server

This thing has been doing mine head in so I’m going to vent; some of you who know me know my opinion...

# UK SQL User Group Newsletter for October

Tuesday, October 24, 2006 1:07 PM by Tony Rogerson's ramblings on SQL Server

UK SQL Server User Community

Providing the Database Professional with the tools...

# Database Mirroring (Part 1) - Performance Base Line and Network stuff.

Thursday, April 5, 2007 1:23 PM by Tony Rogerson's ramblings on SQL Server

Introduction Database mirroring is a really cool technology introduced in SQL Server 2005 to simplify

# Twitter Trackbacks for Server Commissioning Part #1 - Transaction Log Performance - Tony Rogerson's ramblings on SQL Server [] on

Pingback from  Twitter Trackbacks for                 Server Commissioning Part #1 - Transaction Log Performance - Tony Rogerson's ramblings on SQL Server         []        on

# Archive | Pearltrees

Wednesday, July 5, 2017 12:51 PM by Archive | Pearltrees

Pingback from  Archive | Pearltrees