Changes between Version 2 and Version 3 of DataStoringOrganization

Timestamp:

02/07/26 17:26:14 (21 hours ago)

Author:

185022

Comment:

--

DataStoringOrganization

@@ -1 +1 @@
 = Организација на складирање на податоци во Датотечни системи
+
+== Цел
+
+Целта овде е оптимизација на складирањето и перформансите преку:
+* распределба на податоците во `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 локалност и одржување (архивирање на стари партиции)
+
+{{{#!sql
+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 по датум
+
+За табелата `encounters` е креирана партициска функција - `pf_Encounters_Year (datetime2)` со `boundaries: 2000, 2010, 2020, 2030` и партициска шема `ps_Encounters_Year` која ги мапира партициите на горните filegroups.
+
+{{{#!sql
+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
+}}}
+
+=== Clustered индекс на `encounters` со партиционирање
+
+Партиционирањето реално се применува ког clustered индексот е креиран врз партициската шема.
+
+{{{#!sql
+create clustered index CI_Encounters_StartDate_Id
+on encounters(start, Id)
+on ps_Encounters_Year(start);
+}}}
+
+=== Верификација
+
+==== Проверка дека датотечните групи се креирани исправно
+
+{{{#!sql
+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
+}}}
+
+==== Проверка дека партициската шема и функцијата работат исправно
+
+{{{#!sql
+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
+}}}
+
+==== Дистрибуција по партиции
+
+{{{#!sql
+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
+
+{{{#!sql
+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
+
+{{{#!sql
+set statistics io on
+select count(*) from encounters where [start] >= '2022-01-01';
+set statistics io off;
+}}}