19 July 2011 07:10
tonyrogerson
Use GUID’s (uniqueidentifier) for keys when storing data on SSD’s (Part 2 - Initial Database Schema)
In relation to my blog post on guids and ssds (part 1) the script below forms the basis of the permanent tables in the database.
TestGuid_INSERT forms a table that is 50 million rows and around 85.5GBytes in size.
The tests I will be doing as part of the benchmarking are real world tests for instance an INNER JOIN between the TestGuid_INSERT and a lookup table with 50K guids in it.
If you can think of any real world tests you want me to run then email me tonyrogerson@torver.net and I'll include in the benchmarks, better still - create the test yourself and be ready to run the benchmarks to better understand the game changer I'm talking about.
I've created the contiguous table on its own file group so I know the data is as contiguous as I can get it.
The 1GB start size and increment on the original test was deliberate to make it even more on-drive fragmented (it is already on SSD because of the mapping that goes on).
CREATE DATABASE [GUIDTest_SSD] ON PRIMARY
( NAME
= N'GUIDTest_SSD', FILENAME = N'E:\S2008R2\GUIDTest_SSD.mdf'
, SIZE = 1GB , FILEGROWTH = 1GB )
LOG ON
( NAME
= N'GUIDTest_SSD_log', FILENAME = N'E:\S2008R2\GUIDTest_SSD_log.ldf'
, SIZE = 1GB , FILEGROWTH = 1GB)
GO
alter database GUIDTest_SSD set recovery simple
go
USE GUIDTest_SSD
go
select * from sys.dm_io_virtual_file_stats(
DB_ID(), null )
go
-- population
script (2:55)
create table TestGuid_INSERT (
guidcol uniqueidentifier not null default( newid() ) primary key clustered,
expandrow char(1024) not null
);
declare @i int =
1;
set nocount on;
while @i <= 50000000
begin
insert TestGuid_INSERT ( guidcol, expandrow ) values( NEWID(), cast( @i as CHAR(1024) ) )
set @i = @i + 1
end
go
select * from sys.dm_io_virtual_file_stats(
DB_ID(), null )
go
USE [master]
GO
ALTER DATABASE [GUIDTest_SSD] ADD FILEGROUP [CONTIG_TBL]
GO
ALTER DATABASE [GUIDTest_SSD] ADD FILE ( NAME = N'GUIDTest_SSD_CONTIG',
FILENAME = N'E:\S2008R2\GUIDTest_SSD_CONTIG.ndf' , SIZE = 100GB , FILEGROWTH = 1024KB
) TO FILEGROUP
[CONTIG_TBL]
GO
USE GUIDTest_SSD
go
create table
TestGuid_SingleINSERT (
guidcol uniqueidentifier not null default( newid() ) primary key clustered,
expandrow char(1024) not null
) ON [CONTIG_TBL];
select * from sys.dm_io_virtual_file_stats(
DB_ID(), null )
go
insert TestGuid_SingleINSERT ( guidcol, expandrow )
select guidcol,
expandrow
from TestGuid_INSERT
order by guidcol, expandrow
go
select * from sys.dm_io_virtual_file_stats(
DB_ID(), null )
go
USE [master]
GO
ALTER DATABASE [GUIDTest_SSD] ADD FILEGROUP [HEAP_TBL]
GO
ALTER DATABASE [GUIDTest_SSD] ADD FILE ( NAME = N'GUIDTest_SSD_HEAP_TBL',
FILENAME = N'E:\S2008R2\GUIDTest_SSD_HEAP_TBL.ndf' , SIZE = 100GB , FILEGROWTH = 1024KB
) TO FILEGROUP
[HEAP_TBL]
GO
USE GUIDTest_SSD
go
-- population
script (2:55)
create table TestGuid_HeapINSERT (
guidcol uniqueidentifier not null default( newid() ) primary key nonclustered,
expandrow char(1024) not null
) on [heap_tbl];
declare @i int =
1;
set nocount on;
select * from sys.dm_io_virtual_file_stats(
DB_ID(), null )
while @i <= 50000000
begin
insert TestGuid_HeapINSERT ( guidcol, expandrow ) values( NEWID(), cast( @i as CHAR(1024) ) )
set @i = @i + 1
end
go
select * from sys.dm_io_virtual_file_stats(
DB_ID(), null )
go