Page splitting with clustered composite primary key. Performance and database size

Here are the performance obtained on unique-incrementing+clustered-composite-primary-key + vs clustered-incrementing-primary-key+unique-composite.

Non-increasing are those composites that received random/semi-random numbers, increasing are those composites that received monotonically increasing values. Units are in seconds.

                                                        non-increasing  increasing 
                                        
unique incrementing + clustered composite primary key   23              19      

clustered incrementing primary key + unique composite   20              19

And here are the database sizes, units are in megabytes:

                                                        non-increasing  increasing 
                                        
unique incrementing + clustered composite primary key   56              46

clustered incrementing primary key + unique composite   46              46

We can infer that the increase in database size and slow performance stems from page splitting. Clustered primary key(be it one or more columns) arranges the records based on the primary key value(s)' order, so if the values inserted on a clustered primary key(s) is random/non-increasing, the re-arranging of rows happens, which causes slow performance. Physically re-arranging rows causes page splits, which in turn causes larger database size



Following are the scripts that test the impact of different database designs to your app's performance and its database size.



Unique Incrementing + Clustered Composite Primary Key. Has non-increasing value on clustered composite primary key, hence page-splitting happens. Slow and larger database size, 23 seconds and 56 MB

use master;
go

-- drop database testC;
-- go

create database testC;
go


use testC;


create table tbl
(
 i int not null identity(1,1) unique,
 a int not null,
 b int not null,
 x varchar(2000) not null,  
 constraint pk_tbl primary key clustered(a,b) 
);


set nocount on;

declare @i int = 0;
while @i < 8 begin
 
 declare @j int = 0;
 while @j < 2000 begin
  
  insert into tbl(a,b, x) values(@j,@i, replicate('x',2000));

  set @j = @j + 1;
 end;

 set @i = @i + 1;
end;

set nocount off;

-- 23 seconds. 56 MB

Clustered Incrementing Primary key + Unique composite. Has non-increasing value on unique composite, yet increasing on clustered primary key, hence no page-splitting happens. Fast and small database size, 20 seconds and 46 MB

use master;
go

-- drop database testD;
-- go

create database testD;
go


use testD;

create table tbl
(
 i int identity(1,1) primary key clustered,
 a int not null,
 b int not null,
 x varchar(2000) not null,
 constraint uk_tbl unique(a,b) 
);


set nocount on;
declare @i int = 0;
while @i < 8 begin
 
 declare @j int = 0;
 while @j < 2000 begin 
  
  insert into tbl(a,b, x) values(@j,@i, replicate('x',2000));

  set @j = @j + 1;
 end;
  
 set @i = @i + 1;
end;
set nocount off;

-- 20 seconds. 46 MB

Unique Incrementing + Clustered Composite Primary key. Has increasing value on clustered composite primary key, hence no page-splitting happens. Fast and small database size. 19 seconds and 46 MB

use master
go

-- drop database testE;
-- go

create database testE;

go

use testE;

create table tbl
(
 i int not null identity(1,1) unique,
 a int not null,
 b int not null,
 x varchar(2000) not null,
 constraint pk_tbl primary key clustered(a,b) 
);


set nocount on;

declare @i int = 0;
while @i < 8 begin

 declare @j int = 0;
 while @j < 2000 begin
  
  insert into tbl(a,b, x) values(@i,@j, replicate('x',2000));

  set @j = @j + 1;
 end;

 set @i = @i + 1;
end;

set nocount off;

-- 19 seconds. 46 MB

Clustered Incrementing Primary key + Unique Composite. Using increasing value on clustered primary key, hence no page-splitting happens. Fast and small database size, 19 seconds and 46 MB

use master
go

-- drop database testF;
-- go

create database testF;

go

use testF;

create table tbl
(
 i int identity(1,1) primary key clustered,
 a int not null,
 b int not null,
 x varchar(2000) not null,
 constraint uk_tbl unique(a,b) 
);


set nocount on;

declare @i int = 0;
while @i < 8 begin

 declare @j int = 0;
 while @j < 2000 begin
  
  insert into tbl(a,b, x) values(@i,@j, replicate('x',2000));
  set @j = @j + 1;

 end;

 set @i = @i + 1;
end;
  
set nocount off;

-- 19 seconds. 46 MB