Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Секционирование со скользящим окном и архивной секцией.  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
Друг попрсил скинуть макетик по секционированию.
Может еще кому будет полезен.Поправки и предложения приветствуются

+ Модель секционирования
1. Создаём 5 секций с открытой 1-ой секцией (RIGHT-функция секционирования)
1.[архивная секция c открытой левой границей и смещающейся правой границей] (компрессированная)
2.[квартал]
3.[квартал]
4.[квартал]
5.[текущий квартал с открытой правой границей]
2. под каждую секцию создаём файловую группу
в 1-ой файловой группе [TEST_FG1] всегда лежит 1 секция ( объём данных растёт по мере сдвига окна и переноса в неё данных)
4 остальных группы [TEST_FG2],[TEST_FG3],[TEST_FG4],[TEST_FG5] ходят по кругу по алгоритму скользящего окна
3. скользящее окно
1. при завершении текущего квартала делаем слияние 1 и 2 секции удаляя 1-ую граничнуюю точку
и происходит автоматическое переливание данных из 2 секции в 1 секцию
(первоначально из фаловой группы [TEST_FG2] -> [TEST_FG1]) и сеций становиться 4
2. Назначаем освободившуюся файловую группу [TEST_FG2] следующей используемой (NEXT USED)
3. Создаём новую граничную точку начала следующего квартала.
При этом 4-ая секция ограничивается новой точкой и создаётся новая 5 -ая секция открытая справа
которая размещается в файловой группе [TEST_FG2].
Таким образом данные из самого старого квартала переносятся в архивную секцию
а окно из 4 -х кварталов смещается на 1 квартал вперёд.


+ Скрипт создания,тестирования,удаления макета

/****************************************************************************************************************/
-- Создание файловых групп и файлов
-- (размер и рассположение на дисках по вкусу)
--!!!!!! ПОПРАВИТЬ ПУТЬ N'D:\TEMP\TEST_F1.ndf
/****************************************************************************************************************/
CREATE DATABASE  TestP
GO
USE [TestP]
ALTER DATABASE [TestP] ADD FILEGROUP [TEST_FG1] 
ALTER DATABASE [TestP] ADD FILEGROUP [TEST_FG2] 
ALTER DATABASE [TestP] ADD FILEGROUP [TEST_FG3] 
ALTER DATABASE [TestP] ADD FILEGROUP [TEST_FG4] 
ALTER DATABASE [TestP] ADD FILEGROUP [TEST_FG5] 

ALTER DATABASE TestP	ADD FILE 
(NAME = N'TEST_F1', FILENAME = N'D:\TEMP\TEST_F1.ndf',	SIZE = 1000MB,	FILEGROWTH = 1000MB) TO FILEGROUP [TEST_FG1]
ALTER DATABASE TestP	ADD FILE 
(NAME = N'TEST_F2', FILENAME = N'D:\TEMP\TEST_F2.ndf',	SIZE = 1000MB,	FILEGROWTH = 1000MB) TO FILEGROUP [TEST_FG2]
ALTER DATABASE TestP	ADD FILE 
(NAME = N'TEST_F3', FILENAME = N'D:\TEMP\TEST_F3.ndf',	SIZE = 1000MB,	FILEGROWTH = 1000MB) TO FILEGROUP [TEST_FG3]
ALTER DATABASE TestP	ADD FILE 
(NAME = N'TEST_F4', FILENAME = N'D:\TEMP\TEST_F4.ndf',	SIZE = 1000MB,	FILEGROWTH = 1000MB) TO FILEGROUP [TEST_FG4]
ALTER DATABASE TestP	ADD FILE 
(NAME = N'TEST_F5', FILENAME = N'D:\TEMP\TEST_F5.ndf',	SIZE = 1000MB,	FILEGROWTH = 1000MB) TO FILEGROUP [TEST_FG5]


/****************************************************************************************************************/
-- Создание функции секционирования
/****************************************************************************************************************/
CREATE PARTITION FUNCTION PFN_YearByQuarter(datetime)
AS 
RANGE RIGHT FOR VALUES ('20121001',     
                                        '20130101',     
                                        '20130401',     
                                        '20130701'    -- начало текущего квартала
                                        )
GO                                        

/****************************************************************************************************************/
-- Создание схемы секционирования
/****************************************************************************************************************/
CREATE PARTITION SCHEME [PScheme_YearByQuarter]
AS 
PARTITION PFN_YearByQuarter TO 
( [TEST_FG1],[TEST_FG2],[TEST_FG3],[TEST_FG4],[TEST_FG5])
GO

/****************************************************************************************************************/
-- Создание секционированой таблицы
/****************************************************************************************************************/
CREATE TABLE [dbo].[TestPartitionTable](
	[RecID] [bigint] IDENTITY(1,1) NOT NULL,
	[Date] [datetime] NOT NULL,
	--..........any columns
	[Value] [nvarchar](128) NULL,
 CONSTRAINT [PK_TestPartitionTable] PRIMARY KEY CLUSTERED 
(
	[Date] ASC,
	[RecID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PScheme_YearByQuarter]([Date])
) ON [PScheme_YearByQuarter]([Date])
WITH
(
DATA_COMPRESSION = PAGE ON PARTITIONS (1)  -- КОПРЕССИРУЕМ ПЕРВУЮ СЕКЦИЮ (архивные данные)
)
GO
CREATE NONCLUSTERED INDEX [XIE1_TestPartitionTable] ON [dbo].[TestPartitionTable] 
([Value] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)  
ON [PScheme_YearByQuarter]([Date])   ---!!!! ИНДЕКСЫ ВЫРАВНИВАЕМ ПО СХЕМЕ СЕКЦИОНИРОВАНИЯ
GO
ALTER INDEX [XIE1_TestPartitionTable]  ON [dbo].[TestPartitionTable]
REBUILD PARTITION = ALL 
WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;  ---!!! И КОПРЕССИРУЕМ ДЛЯ ПЕРВОЙ СЕКЦИИ
GO

/****************************************************************************************************************/
-- заполним тестовыми данными
/****************************************************************************************************************/
TRUNCATE TABLE [dbo].[TestPartitionTable]
DECLARE @d datetime
SET @d = '20120101'
WHILE @d < '20131101'
 BEGIN
	SET @d = DATEADD(hh,1,@d)
	INSERT INTO  [dbo].[TestPartitionTable]([Date],Value)
	SELECT @d,convert(varchar(8),@d,112) 
 END 

/****************************************************************************************************************/
-- Проверяем распределение данных по секциям
/****************************************************************************************************************/
SELECT 
 fg.name as FileGroupName
,p.partition_number
,p.data_compression_desc 
,case when f.type = 'R' THEN '>= ' ELSE '<= ' END+CAST(r.value as varchar(24)) AS [range]
,p.rows
--,a.DataUsed/128 as [DataUsed_MB]
,a.DataUsed as [DataUsed_MB]
FROM sys.indexes i 
JOIN sys.partition_schemes ps ON (i.data_space_id = ps.data_space_id)
JOIN sys.partition_functions f ON f.function_id = ps.function_id
JOIN sys.destination_data_spaces dds ON (ps.data_space_id = dds.partition_scheme_id)
JOIN sys.partitions p ON p.object_id = i.object_id and p.index_id = i.index_id  and p.partition_number = dds.destination_id
JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
LEFT JOIN sys.partition_range_values r on r.function_id = f.function_id and r.boundary_id + cast(f.boundary_value_on_right as int) = dds.destination_id
OUTER APPLY(SELECT SUM(u.used_pages)  AS DataUsed
					FROM sys.allocation_units u 
					LEFT OUTER JOIN sys.data_spaces AS ds ON ds.data_space_id = u.data_space_id and ds.type = 'FG'					
					WHERE  u.container_id =
					CASE u.[type]
						WHEN 2 THEN p.partition_id
						ELSE p.hobt_id
					END) a					
WHERE 1=1
AND i.object_id = Object_ID('[dbo].[TestPartitionTable]')
AND (i.index_id IN (0,1))
ORDER BY p.partition_number

/****************************************************************************************************************/
-- моделируем сдвиг окна  (в рабочей версии оформить процедурой и запускать job-ом в конце квартала)
/****************************************************************************************************************/
--		Проверка необходимости сдвига окна 
--		если период от начала 5-ой открытой  секции( 4-ая граница ) до текущей даты не  превысил квартал 
--		выходим
--		иначе делаем сдвиг

	/*  при тестировании макета проверку отключаем
	DECLARE @NOW datetime
	SET @NOW =getdate() 

	IF EXISTS(SELECT 1
				  FROM sys.partition_functions f 
				  JOIN   sys.partition_range_values r on r.function_id = f.function_id  and r.boundary_id=4
				  WHERE f.name = 'PFN_YearByQuarter'
				  AND DATEADD(qq,1,CAST(r.value as datetime)) >= @NOW)
		RETURN
	*/	

--		находим файловую группу 2-ой секции (1-ый квартал после архивной секции)
--		находим первое и  последнеe значение границ секций (partition_range_value)

	DECLARE @FG_P2 varchar(100)
	DECLARE @FirstRange DATETIME
	DECLARE @LastRange DATETIME

	SELECT 
		@FG_P2			=MAX(CASE WHEN p.partition_number	=2 THEN fg.name ELSE '' END)
	,	@FirstRange	=MAX(CASE WHEN r.boundary_id			=1 THEN cast([r].value as datetime) ELSE '19000101' END)
	,	@LastRange	=MAX(CASE WHEN r.boundary_id			=4 THEN cast([r].value as datetime) ELSE '19000101' END)
	FROM sys.indexes i 
	JOIN sys.partition_schemes ps ON (i.data_space_id = ps.data_space_id)
	JOIN sys.partition_functions f ON f.function_id = ps.function_id
	JOIN sys.destination_data_spaces dds ON (ps.data_space_id = dds.partition_scheme_id)
	JOIN sys.partitions p ON p.object_id = i.object_id and p.index_id = i.index_id  and p.partition_number = dds.destination_id
	JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
	LEFT JOIN sys.partition_range_values r on r.function_id = f.function_id and r.boundary_id + cast(f.boundary_value_on_right as int) = dds.destination_id
	WHERE 1=1
	AND i.object_id = Object_ID('[dbo].[TestPartitionTable]')
	AND (i.index_id IN (0,1))

	--SELECT @FG_P2,@FirstRange,@LastRange

-------------------------------------------------------------------------------------------------------
--		сливаем  данные из второй секции в первую удаляя первую границу
-------------------------------------------------------------------------------------------------------
	ALTER PARTITION FUNCTION PFN_YearByQuarter()
	MERGE RANGE (@FirstRange)
-------------------------------------------------------------------------------------------------------
--		инициируем  NEXT USED
-------------------------------------------------------------------------------------------------------
	DECLARE @SQL2 NVARCHAR(500)
	SET @SQL2 = 'ALTER PARTITION SCHEME [PScheme_YearByQuarter] NEXT USED '+@FG_P2
	EXEC (@SQL2)
-------------------------------------------------------------------------------------------------------
--		добавляем новую граничную точку (+ квартал к последней существующей граничной точке)
-------------------------------------------------------------------------------------------------------
	SET @LastRange = DATEADD(qq,1,@LastRange)
	ALTER PARTITION FUNCTION PFN_YearByQuarter() 
	SPLIT RANGE (@LastRange)

/****************************************************************************************************************/
-- Проверяем распределение данных по секциям после сдвига
/****************************************************************************************************************/
SELECT 
 fg.name as FileGroupName
,p.partition_number
,p.data_compression_desc 
,case when f.type = 'R' THEN '>= ' ELSE '<= ' END+CAST(r.value as varchar(24)) AS [range]
,p.rows
--,a.DataUsed/128 as [DataUsed_MB]
,a.DataUsed as [DataUsed]
FROM sys.indexes i 
JOIN sys.partition_schemes ps ON (i.data_space_id = ps.data_space_id)
JOIN sys.partition_functions f ON f.function_id = ps.function_id
JOIN sys.destination_data_spaces dds ON (ps.data_space_id = dds.partition_scheme_id)
JOIN sys.partitions p ON p.object_id = i.object_id and p.index_id = i.index_id  and p.partition_number = dds.destination_id
JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id
LEFT JOIN sys.partition_range_values r on r.function_id = f.function_id and r.boundary_id + cast(f.boundary_value_on_right as int) = dds.destination_id
OUTER APPLY(SELECT SUM(u.used_pages)  AS DataUsed
					FROM sys.allocation_units u 
					LEFT OUTER JOIN sys.data_spaces AS ds ON ds.data_space_id = u.data_space_id and ds.type = 'FG'					
					WHERE  u.container_id =
					CASE u.[type]
						WHEN 2 THEN p.partition_id
						ELSE p.hobt_id
					END) a					
WHERE 1=1
AND i.object_id = Object_ID('[dbo].[TestPartitionTable]')
AND (i.index_id IN (0,1))
ORDER BY p.partition_number

--данные из 2 секции перенеслись в сжатый архив
--границы секций сдвинулись на квартал
--!!! можно заметить что данные которые входили в последнюю секцию но имели дату из следующего квартала  
-- перенеслись в новую секцию в соответствии с новой границей

/****************************************************************************************************************/
-- очистка тестовых данных
/****************************************************************************************************************/
IF OBJECT_ID('[dbo].[TestPartitionTable]') IS NOT NULL DROP TABLE [dbo].[TestPartitionTable]
DROP PARTITION SCHEME [PScheme_YearByQuarter]
DROP PARTITION FUNCTION PFN_YearByQuarter
ALTER DATABASE TestP REMOVE FILE TEST_F1
ALTER DATABASE TestP REMOVE FILE TEST_F2
ALTER DATABASE TestP REMOVE FILE TEST_F3
ALTER DATABASE TestP REMOVE FILE TEST_F4
ALTER DATABASE TestP REMOVE FILE TEST_F5

ALTER DATABASE TestP REMOVE FILEGROUP [TEST_FG1]
ALTER DATABASE TestP REMOVE FILEGROUP [TEST_FG2]
ALTER DATABASE TestP REMOVE FILEGROUP [TEST_FG3]
ALTER DATABASE TestP REMOVE FILEGROUP [TEST_FG4]
ALTER DATABASE TestP REMOVE FILEGROUP [TEST_FG5]

USE master
GO
DROP DATABASE TestP

27 сен 13, 10:58    [14891443]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование со скользящим окном и архивной секцией.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
LexusR
Поправки и предложения приветствуются

У вас SPLIT RANGE происходит "по живому", т.е. по диапазону, наполненному данными. А поскольку эта операция реализована как комбинация удаления из таблицы и вставки в таблицу (вы можете убедиться в этом, посмотрев в профайлере план выполнения этой команды), это слишком накладно. Лучше иметь одну пустую секцию "про запас", тогда SPLIT будет проходить максимально быстро.
27 сен 13, 11:34    [14891642]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование со скользящим окном и архивной секцией.  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
переносятся только данные которые попадают в следующий квартал а это происходит только если данные ошибочны или сдвиг окна происходит с опозданием что является исключительной ситуацией. В штатном режиме новая секция нарезается заблоговременно(за 1-2) дня настроенным JOB-ом и переноса данных никакого нет. В примере я специально накидал тестовых данных за предел чтобы проилюстрировать что даже в этом случае ничего криминального не произойдет кроме переноса незначительного числа записей из одного файла в другой
27 сен 13, 11:42    [14891686]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование со скользящим окном и архивной секцией.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31444
LexusR,

Ещё одно замечание для практического применения.

Секционирование применяют обычно для хранилищь данных для аналитики, и не в последнюю очередь для того, что бы легко удалять старые данные, так что типично вообще не держат архивные секции.

А вообще отличный материал, понятное простое описание и скрипт с примером, спасибо!

Предлагаю поместить в FAQ
27 сен 13, 12:13    [14891872]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование со скользящим окном и архивной секцией.  [new]
split partition
Guest
LexusR
переносятся только данные которые попадают в следующий квартал

для того чтобы это сделать необходимо "посмотреть", нет ли таких данных, даже при наличии кластерного индекса с первым полем дата это все равно seek с несколькими чтениями, плюс не всегда в скользящем окне необходимо дату держать первым полем в кластерном индексе.
вообщем я согласен с Гость333, лучше split-ить пустую секцию.
27 сен 13, 12:23    [14891960]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование со скользящим окном и архивной секцией.  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
[quot split partition]
LexusR
вообщем я согласен с Гость333, лучше split-ить пустую секцию.


Ну в принципе для страховки можно добавить еще один квартал с переди про запас. Принципиально ничего не меняется.
Хотя по сравнению с переносом данных в архивную секцию затраты на SPLIT даже если там будет какое то количество записей не значительны
27 сен 13, 13:45    [14892584]     Ответить | Цитировать Сообщить модератору
 Re: Секционирование со скользящим окном и архивной секцией.  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
alexeyvg
LexusR,

Ещё одно замечание для практического применения.
Секционирование применяют обычно для хранилищь данных для аналитики, и не в последнюю очередь для того, что бы легко удалять старые данные, так что типично вообще не держат архивные секции.


С этим полностью согласен - но было требование от бизнеса оставить архивные данные в той же таблице только закомпрессить .
27 сен 13, 13:52    [14892640]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Секционирование со скользящим окном и архивной секцией.  [new]
dab2
Member

Откуда: Воронеж
Сообщений: 781
alexeyvg
LexusR,

Ещё одно замечание для практического применения.

Секционирование применяют обычно для хранилищь данных для аналитики, и не в последнюю очередь для того, что бы легко удалять старые данные, так что типично вообще не держат архивные секции.

А вообще отличный материал, понятное простое описание и скрипт с примером, спасибо!

Предлагаю поместить в FAQ

Поместили? Я не нашёл. Модератор, ау!
30 дек 19, 18:04    [22051827]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить