Sql-server – How to make a test Database grow in size by running transactions to test autogrowth factor

auto-growthperformanceperformance-testingsql-server-2012ssms

I have created a test Database instance which is currently having zero activity on it. I have set the auto growth parameter for this DB similar to our actual production DB which is 600 MB per day , max size unlimited, initial size 888329 MB. I want to automate or run transactions on this test db instance so that it triggers the auto growth everyday for bench marking purpose before applying the same to our actual PROD DB.
Can anyone suggest how to do it in the best possible way?

Best Answer

In order to grow a DB you do not need to produce "real" data. Anything that uses space on disk will suffice.

SQL Server does IO in 8kB chunks - a page. By making a table with a row size over half this (i.e. > 4kB) we ensure a single INSERT uses 8kB of disk. I'd propose adding a new table to your test instance. Define it thus:

create table dbo.SpaceFiller
(
    spacefiller_id  int  identity(1, 1) primary key clustered,
    data            char(4100) not null
);

Column data is what consumes disk. Note it is char not varchar. This ensures the whole 4100 bytes is allocated. The size of 4100 is somewhat arbitrary. It just needs to be over half a page in size. The primary key column is there to make post hoc analysis a little easier, and because adding a surrogate key is a hard habit to break!

Now add rows:

insert dbo.SpaceFiller(data)
values ('');

The column data is defined as fixed length. Therefore, no matter what is actually written to it, it will occupy the defined 4100 bytes. So, to save typing, I just write an empty string. It may seem odd but, because it is a fixed length column, the same amount of space will be used. This is just how SQL Server stores fixed length columns.

Wrap this in a WHILE, or whatever your preferred mechanism is. As each iteration will consume 8k you can do the math to find how many cycles to perform. If the DB already has allocated but unused space that space will be used first. You can force it to use new, dedicated space by putting dbo.SpaceFiller in its own filegroup.

You can see how things are going:

exec sp_spaceused 'dbo.SpaceFiller';

If you want to capture the actual moment of growth you could start an Extended Event session, or use Profiler, or monitor the OS action.

To re-test, drop the table and shrink the DB. Better still, restore a backup.

Related Question