Monday, February 4, 2013

Improve query performance with RAM disk

Improve query performance with RAM disk:


-- create database on physical drive c:\ with default setting
CREATE DATABASE [TestPerformance] ON  PRIMARY 
( NAME = N'TestPerformance', FILENAME = N'TestPerformance.mdf' , SIZE = 4352KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestPerformance_log', FILENAME = N'TestPerformance_log.LDF' , SIZE = 576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
-- create database on ramdisk (drive L:\) with default setting
CREATE DATABASE [TestPerformanceRamdisk] ON  PRIMARY 
( NAME = N'TestPerformanceRamdisk', FILENAME = N'TestPerformanceRamdisk.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestPerformanceRamdisk_log', FILENAME = N'TestPerformanceRamdisk_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

-- create simple test tables in both databases
CREATE TABLE TestPerformance..TestInsert (testColumn UNIQUEIDENTIFIER)
GO
CREATE TABLE TestPerformanceRamDisk..TestInsert (testColumn UNIQUEIDENTIFIER)
GO

-- run this script on both databases and compare time
DECLARE @i INT = 100000
DECLARE @start DATETIME = GETDATE()
DECLARE @end DATETIME 

WHILE @i > 0
BEGIN

    INSERT INTO TestInsert
        VALUES (NEWID())

    SET @i -= 1
END

SET @end = GETDATE()
SELECT CONVERT (TIME, @end - @start)

No comments: