0

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?

Adarsh
  • 52
  • 7
  • 2
    I flagged this, doesn't look like a programming question to me. Try dba stackexchange? – crimson589 Jan 05 '18 at 03:21
  • A hundred databases in only 8GB of RAM is silly. SQL Server Books Online talks about memory requirements. Do some research. And what in the world are you trying to *benchmark* that has you batch-creating hundreds of databases? How fast you can bring down the server? – Ken White Jan 05 '18 at 03:26
  • This is to check how many AGs could be possible with 100 databases in each. I did enough research. If you knew how different the allocation for database creation, I wanted to know that please. I know the server isn't powerful, but am asking to see if someone has already some some research on this and they could answer. – Adarsh Jan 05 '18 at 03:36

0 Answers0