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