主页 / dba / 问题 / 119179
Accepted
Ozzie
Asked: 2015-10-27 02:55:20 +0800 CST

如何在 SQL Server 2012 上使用分区实现表清除？

  • 772

我读过这个问题： 如何删除表分区

但这不是我想要的。

我想设置一个表，在日期/时间列上使用分区来进行 1 个月的分区。然后，配置一个执行 montlhy 的作业并删除记录超过 9 个月的分区。

我可以使用 SQL Server 2012 Enterprise 进行设置吗？

sql-server sql-server-2012

1 个回答

  1. Best Answer

    目标是仅通过更新元数据来减少事务日志的数量。

    当数据从分区中移动或删除时，它会插入和删除行，这将导致（吨​​）LOB_INSERT_ROWS和LOB_DELETE_ROW事务日志。

    唯一的选择是截断分区，但此选项不存在。我们可以通过仅在空分区上使用Mergeand来避免它。Split

    在下面的示例中，我将缩短内容并仅创建过去 3 个月（即 8 月、9 月和 10 月）的数据，但您可以轻松地将其扩展到 9 个月。一旦数据开始添加到 11 月，8 月将被删除，以此类推，9 月和 12 月......

    创建文件和文件组：

    我首先创建 6 个文件和文件组 [Part_0] 到 [Part_5]：

    Alter Database [Test] Add Filegroup [Part_0];
...
Alter Database [Test] Add Filegroup [Part_5];
Alter Database [Test] Add File( NAME = N'Part_0', FILENAME = N'...\Part_0.ndf' , SIZE = 100MB , FILEGROWTH = 100MB ) TO Filegroup [Part_0];
...
Alter Database [Test] Add File( NAME = N'Part_5', FILENAME = N'...\Part_5.ndf' , SIZE = 100MB , FILEGROWTH = 100MB ) TO Filegroup [Part_5];

    创建函数和方案：

    Create Partition Function [DateKeyPartFunction] (datetime2)
as Range Right For Values ('20150801', '20150901', '20151001', '20151101', '20151201');
Create Partition Scheme [DateKeyPartScheme] as Partition [DateKeyPartFunction]
To ([Part_0], [Part_1], [Part_2], [Part_3], [Part_4], [Part_5]);

    同样有 6 个分区。这将在稍后解释，但这主要是由于需要有空分区。

    创建表和聚集索引：

    因为我不知道您的桌子的确切设计，所以我将使用这张桌子：

    Create Table dbo.DataPart(id int identity(0, 1), name char(1000), name_date datetime2);

    这Clustered Index：

    Create Clustered Index IDX_Part On dbo.DataPart(name_date) On DateKeyPartScheme(name_date);

    虚拟数据：

    从 10 月到 8 月（现在），此代码每 6 秒在一系列虚拟日期中创建超过一百万条记录：

    With inc(n) as(
    Select ROW_NUMBER() over(order by (select 1))-1 From (
        Select 1 From (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x1(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x2(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x3(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x4(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x5(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x6(n)
    ) as x(n)
)
Insert into dbo.DataPart(name, name_date) 
Select TOP(1000000) '', DATEADD(second, -n*6, getdate()) From inc;

    分区数据：

    这将分区如下：

    Id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150801'    0
2  [Part_1] >= '20150801'   < '20150901'    184042
3  [Part_2] >= '20150901'   < '20151001'    432000
4  [Part_3] >= '20151001'   < '20151101'    383958
5  [Part_4] >= '20151101'   < '20151201'    0
6  [Part_5] >= '20151201'                   0
    • [Part_1]、[Part_2]、[Part_3] 包含 8 月、9 月和 10 月的数据。
    • [Part_0]（即<'20150801'）仅存在因为它需要一个空分区才能在11月清空[Part_1]（8月）一次。这将是 7 月及之前的数据，但必须保持空白。
    • 尽管在 11 月之前它会一直是空的，但下个月已经有一个空分区会让事情变得更容易。这是 11 月 [Part_4] 的目的。
    • 分区方案需要为右边界之外的所有内容额外分区。这是 12 月及以后的 [Part_5]，它也必须保持空白。

    添加十一月：

    一旦在 11 月，新行将转到 [Part_4]，并且可以从 [Part_1] 中删除 8 月的数据。在不必删除数十万行的情况下删除它的唯一方法是将 [Part_1] 从表中移开：

    Create Table dbo.DataPart_Temp(id int identity(0, 1), name char(1000), name_date datetime2);
Create Clustered Index IDX_Part_temp On dbo.DataPart_temp(name_date) On [Part_1];
Alter Table DataPart Switch Partition 2 to DataPart_temp Partition 1;
    • DataPart_Temp必须与DataPart（列、索引）相同
    • 因为 [Part_1] 是从 移动DataPart到，所以必须在同一个文件组上创建DataPart_temp聚簇索引：[Part_1]DataPart_temp
    • [Part_1] 是 的第二个分区，DataPart是切换到 的第一个也是唯一一个分区DataPart_temp。现在所有 August 行都在DataPart_temp.

    该表现在分区如下：

    id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150801'    0
2  [Part_1] >= '20150801'   < '20150901'    0
3  [Part_2] >= '20150901'   < '20151001'    432000
4  [Part_3] >= '20151001'   < '20151101'    383958
5  [Part_4] >= '20151101'   < '20151201'    0
6  [Part_5] >= '20151201'                   0

    合并分区：

    [Part_0] 和 [Part_1] 现在为空，可以合并：

    Alter Partition Function [DateKeyPartFunction]() Merge Range ('20150801');

    [Part_1] 已被删除：

    id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150901'    0
2  [Part_2] >= '20150901'   < '20151001'    432000
3  [Part_3] >= '20151001'   < '20151101'    383958
4  [Part_4] >= '20151101'   < '20151201'    0
5  [Part_5] >= '20151201'                   0

    下个月添加：

    现在 [Part_1] 不再使用，可以将其添加到分区方案中作为下一个可用分区：

    Alter Partition Scheme [DateKeyPartScheme] Next Used [Part_1];

    然后 [Part_5] (December, >= '20151201') 可以拆分：

    Alter Partition Function [DateKeyPartFunction]() Split Range ('20160101');

    由于 [Part_5] 为空，因此无需移动任何内容。[Part_5] 的另一半将转到下一个可用分区，即 [Part_1]：

    Id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150901'    0
2  [Part_2] >= '20150901'   < '20151001'    432000
3  [Part_3] >= '20151001'   < '20151101'    383958
4  [Part_4] >= '20151101'   < '20151201'    x rows in November
5  [Part_5] >= '20151201'   < '20160101'    0
6  [Part_1] >= '20160101'                   0

    清理：

    DataPart_temp现在可以截断然后删除（或至少删除其聚集索引）。

    添加以下月份：

    在 12 月，必须将分区 ID 2 移走 ([Part_2])，与分区 1 合并，然后在 1 月拆分之前添加回来。

    为了自动执行此过程，您必须查找分区 2 的文件组名称：

    然后创建动态 SQL 以：

    • 将聚集索引添加到 [Part_X] 上的 DataPart_temp（类似于 DataPart）
    • 将 [Part_X] 从 DataPart 切换到 DataPart_temp
    • 合并分区 1 和 2
    • 将 [Part_X] 添加到 DataPart
    • 拆分最后一个分区
    • 截断 DataPart_temp
    • 删除 DataPart_temp 或其聚集索引

    如果您使用 fn_dblog，您应该会看到事务日志很少。

    • 5

相关问题