For a bench-marking project I am currently working on, requires creating multiple databases on SQL Server. I am using Enterprise Edition instance on a VM, that has 8GB RAM and 4 core processor. I wanted to create dummy databases, with no data in it. I have the below script to generate the create database script.
create table createdb
(
dbname varchar(100),
createdbscript varchar(max)
)
DECLARE
@query as varchar(max), @NUM AS INT
SET @NUM = 1
CREATE TABLE #db_names(dbname varchar(250))
WHILE(@NUM <1001)
BEGIN
INSERT INTO #db_names values('NUM'+cast(@NUM as varchar))
SET @NUM = @NUM+1
END
SET @query = ''
insert into createdb
SELECT dbname, @query + 'CREATE DATABASE [' + dbname + ']
CONTAINMENT = NONE
ON PRIMARY
( NAME = N''' + dbname + ''',
FILENAME = N''c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\' + dbname +'.mdf'' ,
SIZE = 10240KB ,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N''' + dbname + '_log''' +',
FILENAME = N''c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\' + dbname + '_log' +'.ldf'' ,
SIZE = 512KB ,
FILEGROWTH = 256KB )
' FROM #db_names
select * from createdb
drop table #db_names
I started to run create database commands in a batch of 100. It swamped my server's memory. After sometime, a batch of 2 create database commands was difficult.
I wanted to understand how the memory allocation happens for this kind of operations? What is the best way to create multiple databases? What hardware changes can impact this performance?