cesardbaLeave a Comment on Movimentar particionamento existente de tabelas

Movimentar particionamento existente de tabelas

Esta semana um cliente solicitou que o particionamento atual dele com apenas um filegroup com range ate 2019-12-31 00:00:00.000 fosse modificado para o particionamento por ano, abaixo seguem os passos usados para atendar esta solicitação.

Cenário atual encontrado.

SELECT distinct * FROM (
SELECT SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(i.object_id) AS [object]
     , p.partition_number AS [p#]
     , fg.name AS [filegroup]
     , p.rows
     , CASE boundary_value_on_right
       WHEN 1 THEN 'Less than'
       ELSE 'less than or equal to' END as comparison
     , rv.value
FROM sys.partitions p
INNER JOIN sys.indexes i
     ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.objects o
     ON p.object_id = o.object_id
INNER JOIN sys.system_internals_allocation_units au
     ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
     ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions f
     ON f.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
     ON dds.partition_scheme_id = ps.data_space_id
     AND dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg
     ON dds.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.partition_range_values rv
     ON f.function_id = rv.function_id
     AND p.partition_number = rv.boundary_id
WHERE i.index_id < 2
) AS T WHERE T.object LIKE '%Partitioned_Table%'
SQL

Modificando o particionamento para o período solicitado de 2016 ate 2027.

use db_01
go
 
alter database db_01 add filegroup FG_2016
alter database db_01 add filegroup FG_2017
alter database db_01 add filegroup FG_2018
alter database db_01 add filegroup FG_2019
alter database db_01 add filegroup FG_2020
alter database db_01 add filegroup FG_2021
alter database db_01 add filegroup FG_2022
alter database db_01 add filegroup FG_2023
alter database db_01 add filegroup FG_2024
alter database db_01 add filegroup FG_2025
alter database db_01 add filegroup FG_2026
alter database db_01 add filegroup FG_2027
 
 
alter database db_01
add file (
name='Reserva_FG_2016',
filename='D:\apagar\db_01_FG_2016.ndf',
size=128Mb,
MAXSIZE = UNLIMITED, 
filegrowth=512MB
)to filegroup [FG_2016]
 
alter database db_01
add file (
name='Reserva_FG_2017',
filename='D:\apagar\db_01_FG_2017.ndf',
size=128Mb,
MAXSIZE = UNLIMITED, 
filegrowth=512MB
)to filegroup [FG_2017]
 
alter database db_01
add file (
name='Reserva_FG_2018',
filename='D:\apagar\db_01_FG_2018.ndf',
size=128Mb,
MAXSIZE = UNLIMITED, 
filegrowth=512MB
)to filegroup [FG_2018]
 
alter database db_01
add file (
name='Reserva_FG_2019',
filename='D:\apagar\db_01_FG_2019.ndf',
size=128Mb,
MAXSIZE = UNLIMITED, 
filegrowth=512MB
)to filegroup [FG_2019]
 
alter database db_01
add file (
name='Reserva_FG_2020',
filename='D:\apagar\db_01_FG_2020.ndf',
size=128Mb,
MAXSIZE = UNLIMITED, 
filegrowth=512MB
)to filegroup [FG_2020]
 
 
alter database db_01
add file (
name='Reserva_FG_2021',
filename='D:\apagar\db_01_FG_2021.ndf',
size=128Mb,
MAXSIZE = UNLIMITED, 
filegrowth=512MB
)to filegroup [FG_2021]
 
alter database db_01
add file (
name='Reserva_FG_2022',
filename='D:\apagar\db_01_FG_2022.ndf',
size=128Mb,
MAXSIZE = UNLIMITED, 
filegrowth=512MB
)to filegroup [FG_2022]
 
alter database db_01
add file (
name='Reserva_FG_2023',
filename='D:\apagar\db_01_FG_2023.ndf',
size=128Mb,
MAXSIZE = UNLIMITED, 
filegrowth=512MB
)to filegroup [FG_2023]
 
alter database db_01
add file (
name='Reserva_FG_2024',
filename='D:\apagar\db_01_FG_2024.ndf',
size=128Mb,
MAXSIZE = UNLIMITED, 
filegrowth=512MB
)to filegroup [FG_2024]
 
alter database db_01
add file (
name='Reserva_FG_2025',
filename='D:\apagar\db_01_FG_2025.ndf',
size=128Mb,
MAXSIZE = UNLIMITED, 
filegrowth=512MB
)to filegroup [FG_2025]
 
alter database db_01
add file (
name='Reserva_FG_2026',
filename='D:\apagar\db_01_FG_2026.ndf',
size=128Mb,
MAXSIZE = UNLIMITED, 
filegrowth=512MB
)to filegroup [FG_2026]
 
 
alter database db_01
add file (
name='Reserva_FG_2027',
filename='D:\apagar\db_01_FG_2027.ndf',
size=128Mb,
MAXSIZE = UNLIMITED, 
filegrowth=512MB
)to filegroup [FG_2027]
SQL

O segredo esta aqui, iremos fazer o merge dos dados e em seguida movimentar os dados por ano sem necessidare de modificar a função ou schema atual do particionamento existente.

SELECT      s.[name]                        AS      [schema_name]
,           t.[name]                        AS      [table_name]
,           i.[name]                        AS      [index_name]
,           p.[partition_number]            AS      [partition_number]
,           SUM(a.[used_pages]*8.0)         AS      [partition_size_kb]
,           SUM(a.[used_pages]*8.0)/1024    AS      [partition_size_mb]
,           CONVERT(bigint,SUM(a.[used_pages]*8.0)/1048576) AS      [partition_size_gb]
,           p.[rows]                        AS      [partition_row_count]
,           rv.[value]                      AS      [partition_boundary_value]  -- COLUNA USADA PARA O MERGE
,           p.[data_compression_desc]       AS      [partition_compression_desc]
,ps.name AS PartitionSchemeName
,ds.name AS PartitionFilegroupName
,pf.name AS PartitionFunctionName
FROM        sys.schemas s
JOIN        sys.tables t                    ON      t.[schema_id]         = s.[schema_id]
JOIN        sys.partitions p                ON      p.[object_id]         = t.[object_id]
JOIN        sys.allocation_units a          ON      a.[container_id]        = p.[partition_id]
JOIN        sys.indexes i                   ON      i.[object_id]         = p.[object_id]
                                            AND     i.[index_id]          = p.[index_id]
JOIN        sys.data_spaces ds              ON      ds.[data_space_id]    = i.[data_space_id]
LEFT JOIN   sys.partition_schemes ps        ON      ps.[data_space_id]    = ds.[data_space_id]
LEFT JOIN   sys.partition_functions pf      ON      pf.[function_id]      = ps.[function_id]
LEFT JOIN   sys.partition_range_values rv   ON      rv.[function_id]      = pf.[function_id]
                                            AND     rv.[boundary_id]      = p.[partition_number]
WHERE       p.[index_id] <=1
AND t.[name]  IN ('Partitioned_Table')
GROUP BY    s.[name]
,           t.[name]
,           i.[name]
,           p.[partition_number]
,           p.[rows]
,           rv.[value]
,           p.[data_compression_desc]
,ps.name 
,ds.name 
,pf.name 
order by partition_size_gb desc
SQL

Realizando o merge e movimentação por ano.

use db_01
go
-- ###################### 2016 ######################
ALTER PARTITION SCHEME spt_rsv_atg_ecommerce_order_info NEXT USED [FG_2016];
 
ALTER PARTITION FUNCTION fpt_rsv_atg_ecommerce_order_info()
SPLIT RANGE(N'2016-01-01T00:00:00.000');
GO
 
-- ###################### 2017 ######################
ALTER PARTITION SCHEME spt_rsv_atg_ecommerce_order_info NEXT USED [FG_2017];
 
ALTER PARTITION FUNCTION fpt_rsv_atg_ecommerce_order_info()
SPLIT RANGE(N'2017-01-01T00:00:00.000');
GO
 
-- ###################### 2018 ######################
ALTER PARTITION SCHEME spt_rsv_atg_ecommerce_order_info NEXT USED [FG_2018];
 
ALTER PARTITION FUNCTION fpt_rsv_atg_ecommerce_order_info()
SPLIT RANGE(N'2018-01-01T00:00:00.000');
GO
 
-- ###################### 2019 ######################
ALTER PARTITION SCHEME spt_rsv_atg_ecommerce_order_info NEXT USED [FG_2019];
 
ALTER PARTITION FUNCTION fpt_rsv_atg_ecommerce_order_info()
SPLIT RANGE(N'2019-01-01T00:00:00.000');
GO
 
-- ###################### 2020 ######################
 
ALTER PARTITION SCHEME spt_rsv_atg_ecommerce_order_info NEXT USED [FG_2020];
 
ALTER PARTITION FUNCTION fpt_rsv_atg_ecommerce_order_info()
SPLIT RANGE(N'2020-01-01T00:00:00.000');
GO
 
-- ###################### 2021 ######################
ALTER PARTITION SCHEME spt_rsv_atg_ecommerce_order_info NEXT USED [FG_2021];
 
ALTER PARTITION FUNCTION fpt_rsv_atg_ecommerce_order_info()
SPLIT RANGE(N'2021-01-01T00:00:00.000');
GO
 
-- ###################### 2022 ######################
ALTER PARTITION SCHEME spt_rsv_atg_ecommerce_order_info NEXT USED [FG_2022];
 
ALTER PARTITION FUNCTION fpt_rsv_atg_ecommerce_order_info()
SPLIT RANGE(N'2022-01-01T00:00:00.000');
GO
 
-- ###################### 2023 ######################
ALTER PARTITION SCHEME spt_rsv_atg_ecommerce_order_info NEXT USED [FG_2023];
 
ALTER PARTITION FUNCTION fpt_rsv_atg_ecommerce_order_info()
SPLIT RANGE(N'2023-01-01T00:00:00.000');
GO
 
-- ###################### 2024 ######################
 
ALTER PARTITION SCHEME spt_rsv_atg_ecommerce_order_info NEXT USED [FG_2024];
 
ALTER PARTITION FUNCTION fpt_rsv_atg_ecommerce_order_info()
SPLIT RANGE(N'2024-01-01T00:00:00.000');
GO
 
-- ###################### 2025 ######################
ALTER PARTITION SCHEME spt_rsv_atg_ecommerce_order_info NEXT USED [FG_2025];
 
ALTER PARTITION FUNCTION fpt_rsv_atg_ecommerce_order_info()
SPLIT RANGE(N'2025-01-01T00:00:00.000');
GO
 
-- ###################### 2026 ######################
ALTER PARTITION SCHEME spt_rsv_atg_ecommerce_order_info NEXT USED [FG_2026];
 
ALTER PARTITION FUNCTION fpt_rsv_atg_ecommerce_order_info()
SPLIT RANGE(N'2026-01-01T00:00:00.000');
GO
 
 
-- ###################### 2027 ######################
ALTER PARTITION SCHEME spt_rsv_atg_ecommerce_order_info NEXT USED [FG_2027];
 
ALTER PARTITION FUNCTION fpt_rsv_atg_ecommerce_order_info()
SPLIT RANGE(N'2027-01-01T00:00:00.000');
GO
SQL

Resultado final das mudanças nos particionamentos.

SELECT 
    OBJECT_NAME(P.object_id)   AS ObjectName
,   P.partition_number         AS PartitionNumber
,   FG.name                    AS FGName
,   P.rows                     AS [Rows]
,   (f.size * 8) / 1024        AS SizeMB
,   AU.used_pages              AS UsedPages
,   F.filename                 AS [FileName]
,   P.data_compression_desc    AS [Compression]
,   AU.type_desc               AS TypeDesc
,   S.name                     AS PartitionSchemeName
,   PF.name                    AS PartitionFunctionName
,   PF.boundary_value_on_right AS IsRightBoundary
,   R.value                    AS BoundaryValue
FROM 
            sys.tables AS T  
INNER JOIN  sys.indexes AS i  
                ON T.object_id = I.object_id  
INNER JOIN  sys.partitions AS P 
                ON i.object_id = p.object_id AND i.index_id = p.index_id   
INNER JOIN  sys.system_internals_allocation_units AS AU
                ON P.partition_id = AU.container_id
INNER JOIN  sys.filegroups AS FG
                ON AU.filegroup_id = FG.data_space_id 
INNER JOIN  sys.sysfiles AS F
                ON FG.data_space_id = F.groupid
INNER JOIN  sys.partition_schemes AS S   
                ON I.data_space_id = S.data_space_id  
INNER JOIN  sys.partition_functions AS PF   
                ON S.function_id = PF.function_id  
LEFT JOIN   sys.partition_range_values AS R
                ON PF.function_id = R.function_id 
                and R.boundary_id = P.partition_number  
WHERE 
    P.object_id = object_id('Partitioned_Table')
AND I.type <= 1 
;
SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

search previous next tag category expand menu location phone mail time cart zoom edit close