Memory Allocation while creating database in SQL Server

156 views Asked by At

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?

0

There are 0 answers