sql-server – 存档除当前年份之外的所有内容并同时对表进行分区

从一组大表中归档除了滚动的13个月之外的所有时间段.归档数据必须存储在另一个数据库中.

>数据库处于简单恢复模式
>这些表是50密耳到几十亿,在某些情况下每个占用数百GB.
>表当前未分区
>每个表在不断增加的日期列上都有一个聚簇索引
>每个表还有一个非聚集索引
>对表的所有数据更改都是插入
>目标是最大限度地减少主数据库的停机时间.
>服务器是2008 R2 Enterprise

“存档”表将有大约11亿行,“实时”表大约有4亿行.显然,存档表会随着时间的推移而增加,但我希望实时表也能够快速增加.至少在未来几年说50％.

我曾考虑过Azure拉伸数据库,但不幸的是我们是在2008 R2,可能会在那里停留一段时间.

目前的计划

>创建一个新数据库
>在新数据库中创建按月分区的新表(使用修改日期).
>将最近12-13个月的数据移动到分区表中.
>重命名两个数据库的交换
>从现在的“存档”数据库中删除移动的数据.
>对“归档”数据库中的每个表进行分区.
>使用分区交换将来存档数据.

>我确实意识到我必须交换要存档的数据,将该表复制到存档数据库,然后将其交换到存档表中.这是可以接受的.

问题：我正在尝试将数据移动到初始分区表中(实际上我仍在对其进行概念验证).我正在尝试使用TF 610(根据Data Loading Performance Guide)和INSERT … SELECT语句最初移动数据,认为它将被最小化记录.不幸的是,每次我尝试它都完全记录.

在这一点上,我认为我最好的选择可能是使用SSIS包移动数据.我正在努力避免这种情况,因为我正在处理200个表以及我可以通过脚本执行的任何操作,我可以轻松生成并运行.

在我的总体规划中是否有任何我遗漏的东西,SSIS是我最好的选择,可以快速移动数据并且最少使用日志(空间问题)？

没有数据的演示代码

-- Existing structure
USE [Audit]
GO

CREATE TABLE [dbo].[AuditTable](
    [Col1] [bigint] NULL,[Col2] [int] NULL,[Col3] [int] NULL,[Col4] [int] NULL,[Col5] [int] NULL,[Col6] [money] NULL,[Modified] [datetime] NULL,[ModifiedBy] [varchar](50) NULL,[ModifiedType] [char](1) NULL
); 
-- ~1.4 bill rows,~20% in the last year

CREATE CLUSTERED INDEX [AuditTable_Modified] ON [dbo].[AuditTable]
(   [Modified] ASC   )
GO


-- New DB & Code
USE Audit_New
GO

CREATE PARTITION FUNCTION ThirteenMonthPartFunction (datetime)
AS RANGE RIGHT FOR VALUES ('20150701','20150801','20150901','20151001','20151101','20151201','20160101','20160201','20160301','20160401','20160501','20160601','20160701') 

CREATE PARTITION SCHEME ThirteenMonthPartScheme AS PARTITION ThirteenMonthPartFunction
ALL TO ( [PRIMARY] );

CREATE TABLE [dbo].[AuditTable](
    [Col1] [bigint] NULL,[ModifiedType] [char](1) NULL
) ON ThirteenMonthPartScheme (Modified)
GO

CREATE CLUSTERED INDEX [AuditTable_Modified] ON [dbo].[AuditTable]
(
    [Modified] ASC
)WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,ONLINE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON ThirteenMonthPartScheme (Modified)
GO

CREATE NONCLUSTERED INDEX [AuditTable_Col1_Col2_Col3_Col4_Modified] ON [dbo].[AuditTable]
(
    [Col1] ASC,[Col2] ASC,[Col3] ASC,[Col4] ASC,[Modified] ASC
)WITH (PAD_INDEX = OFF,ALLOW_PAGE_LOCKS = ON) ON ThirteenMonthPartScheme (Modified)
GO

移动代码

USE Audit_New
GO
DBCC TRACEON(610);

INSERT INTO AuditTable
SELECT * FROM Audit.dbo.AuditTable
WHERE Modified >= '6/1/2015'
ORDER BY Modified

解决方法

我发现你提到的Data Loading Performance Guide是一个非常有价值的资源.但是,它也不是100％全面,我怀疑网格已经足够复杂,作者没有添加列分区表来分解行为的差异取决于接收插入的表是否被分区.正如我们稍后将看到的,表已经被分区的事实似乎抑制了最小的日志记录.

推荐的方法

根据“数据加载性能指南”中的建议(包括“批量加载分区表”部分)以及加载具有数百亿行的分区表的丰富经验,我建议采用以下方法：

>创建一个新数据库.
>在新数据库中创建按月分区的新表.
>以下列方式移动最近一年的数据：

>对于每个月,创建一个新的堆表;
>使用TABLOCK提示将该月份的数据插入堆中;
>将聚集索引添加到包含该月份数据的堆中;
>添加检查约束,强制该表仅包含本月的数据;
>将表切换到新的整体分区表的相应分区.

>重命名两个数据库的交换.
>截断现在“存档”数据库中的数据.
>对“归档”数据库中的每个表进行分区.
>使用分区交换将来存档数据.

与原始方法相比时的差异：

>如果使用TABLOCK一次装入一个堆中,使用分区切换将数据放入分区表中,那么移动最近12-13个月数据的方法将会更加高效.
>将完全记录清除旧表的DELETE.也许你可以TRUNCATE或删除表并创建一个新的存档表.

移动最近一年数据的方法比较

为了在我的机器上在合理的时间内比较方法,我使用了我生成的100MM行测试数据集,它遵循您的模式.

从下面的结果中可以看出,通过使用TABLOCK提示将数据加载到堆中,可以大大提高性能并减少日志写入.如果一次只进行一个分区,还有一个额外的好处.值得注意的是,如果一次运行多个分区,则可以轻松地进一步并行化一次一分区方法.根据您的硬件,这可能会产生很好的提升;我们通常在服务器级硬件上一次加载至少四个分区.

这是full test script.

最后的笔记

所有这些结果在某种程度上取决于您的硬件.但是,我的测试是在带有旋转磁盘驱动器的标准四核笔记本电脑上进行的.如果您正在使用一个在执行此过程时没有很多其他负载的体面服务器,那么数据加载可能会快得多.

例如,我在实际的开发服务器(Dell R720)上运行了推荐的方法,并且减少到76秒(从我的笔记本电脑上的156秒).有趣的是,插入分区表的原始方法没有经历相同的改进,并且仍然在开发服务器上花费了超过12分钟.据推测这是因为这种模式产生了一个串行执行计划,我的笔记本电脑上的单个处理器可以匹配开发服务器上的单个处理器.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!