Организација на складирање на податоци во Датотечни системи

Цел

Целта овде е оптимизација на складирањето и перформансите преку:

• распределба на податоците во filegroups по временски период
• партиционирање по датум (година/деценија)
• clustered индекси поставени на partition scheme за да се активира partitioning

Filegroups по временски период

Креирани се filegroups за логичка/физичка сегментација на податоците:

• FG_Old (пред 2000)
• FG_2000s (2000-2009)
• FG_2010s (2010-2019)
• FG_2020s (2020-2029)
• FG_Future (2030+)

Причина:

• подобро управување со големи податочни сетови (VLDB пристап)
• можност за backup/restore по filegroup
• подобра I/O локалност и одржување (архивирање на стари партиции)

alter database HospitalSyntheaDB add filegroup FG_Old;
go
alter database HospitalSyntheaDB add file (name='FG_Old', filename='C:\Users\User\Documents\DBA\FG_Old.ndf') to filegroup FG_Old;
go

alter database HospitalSyntheaDB add filegroup FG_2000s;
go
alter database HospitalSyntheaDB add file (name='FG_2000s', filename='C:\Users\User\Documents\DBA\FG_2000s.ndf') to filegroup FG_2000s;
go

alter database HospitalSyntheaDB add filegroup FG_2010s;
go
alter database HospitalSyntheaDB add file (name='FG_2010s', filename='C:\Users\User\Documents\DBA\FG_2010s.ndf') to filegroup FG_2010s;
go

alter database HospitalSyntheaDB add filegroup FG_2020s;
go
alter database HospitalSyntheaDB add file (name='FG_2020s', filename='C:\Users\User\Documents\DBA\FG_2020s.ndf') to filegroup FG_2020s;
go

alter database HospitalSyntheaDB add filegroup FG_Future;
go
alter database HospitalSyntheaDB add file (name='FG_Future', filename='C:\Users\User\Documents\DBA\FG_Future.ndf') to filegroup FG_Future;
go

Партиционирање

Партициски функции и шема

Поради тоа што encounters, medications, observations и procedures користат ист тип за колоната за датум - datetime2, тие може да користат иста функција - pf_Encounters_Year и иста партициска шема - ps_Encounters_Year.

Табелата conditions користи различен тип за датум - date, па за неа ќе се направи посебна партициска функција и шема.

pf_Encounters_Year и ps_Encounters_Year

Партициската функција - pf_Encounters_Year (datetime2) е креирана со boundaries: 2000, 2010, 2020, 2030.

Партициската шема ps_Encounters_Year ги мапира партициите на горните filegroups.

create partition function pf_Encounters_Year (datetime2)
as range right for values
(
    '2000-01-01',
    '2010-01-01',
    '2020-01-01',
    '2030-01-01'
);
go

create partition scheme ps_Encounters_Year
as partition pf_Encounters_Year to
(
    FG_Old,
    FG_2000s,
    FG_2010s,
    FG_2020s,
    FG_Future
);
go

• range right значи дека граничната вредност припаѓа на десната (повисока) партиција.

pf_Conditions_Year и ps_Conditions_Year

create partition function pf_Conditions_Year (date)
as range right for values 
(
    '2000-01-01',
    '2010-01-01',
    '2020-01-01',
    '2030-01-01'
);
go

create partition scheme ps_Conditions_Year
as partition pf_Conditions_Year to
(
    FG_Old,
    FG_2000s,
    FG_2010s,
    FG_2020s,
    FG_Future
);
go

Clustered индекси со партиционирање

Партиционирањето реално се применува кога clustered индексот е креиран врз партициската шема.

Помалите табели како allergies и immunizations не се партиционирани затоа што се помали и не зависат од временски период.

encounters

create clustered index CI_Encounters_StartDate_Id
on encounters(start, Id)
with (drop_existing = off)
on ps_Encounters_Year(start);
go

observations

create clustered index CI_Observations_Date_Patient
on observations([date], patient)
with (drop_existing = off)
on ps_Encounters_Year([date]);
go

medications

create clustered index CI_Medications_Start
on medications([start], patient)
with (drop_existing = off)
on ps_Encounters_Year([start]);
go

procedures

create clustered index CI_Procedures_Start
on procedures([start], patient)
with(drop_existing = off)
on ps_Encounters_Year([start]);
go

conditions

create clustered index CI_Conditions_Start
on conditions([start], patient)
with (drop_existing = off)
on ps_Conditions_Year([start]);
go

Верификација

Проверката е слична за сите табели.

Подолу е прикажан пример само за encounters табелата.

Проверка дека датотечните групи се креирани исправно

select 
    fg.name as FilegroupName,
    df.name as LogicalFileName,
    df.type_desc as FileType,
    df.physical_name as PhysicalPath,
    df.size / 128.0 as SizeMB
from sys.filegroups fg
left join sys.database_files df ON fg.data_space_id = df.data_space_id
order by fg.name;
go

Проверка дека партициската шема и функцијата работат исправно

select 
    ps.name as PartitionScheme,
    pf.name as PartitionFunction,
    dds.destination_id as PartitionNumber,
    fg.name as FilegroupName,
    prv.value as UpperBoundaryValue
from sys.partition_schemes ps
join sys.partition_functions pf on ps.function_id = pf.function_id
join sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id
join sys.filegroups fg on dds.data_space_id = fg.data_space_id
left join sys.partition_range_values prv 
    on pf.function_id = prv.function_id 
    and dds.destination_id = case when pf.boundary_value_on_right = 1 then prv.boundary_id + 1 else prv.boundary_id end
where ps.name = 'ps_Encounters_Year'
order by dds.destination_id;
go
-----------------------------
select
    fg.name as FilegroupName,
    df.name as LogicalFileName,
    df.physical_name,
    df.size / 128.0 as SizeMB
from sys.filegroups fg
    left join sys.database_files df on fg.data_space_id = df.data_space_id;
go

Дистрибуција по партиции

select
    $partition.pf_Encounters_Year([start]) as PartitionNumber,
    count(*) as 'RowCount',
    min([start]) as MinDate,
    max([start]) as MaxDate
from dbo.encounters
group by $partition.pf_Encounters_Year([start])
order by PartitionNumber;
go

Filegroup usage

select 
    fg.name as FilegroupName,
    count(*) as PartitionCount
from sys.partitions p
join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
join sys.data_spaces ds on i.data_space_id = ds.data_space_id
join sys.filegroups fg on ds.data_space_id = fg.data_space_id
where object_name(p.object_id) = 'Encounters'
group by fg.name;
go

Partition elimination

set statistics io on
select count(*) from encounters where [start] >= '2022-01-01';
set statistics io off;