SQL SERVER定期转移海量数据方案收藏 查看分区在哪个文件组

SQL SERVER定期转移海量数据方案

【关键词】
1、 分区切换
2、 BCP
3、 BULK INSERT
4、 Insert

【背景】
有个表比较巨大，每天新增约500万条记录。只保留最新7天数据，每天定期移走过期记录，归并到历史库。采用INSERT，DELETE的方法，消耗时间越来越长，平均达到45分钟，期间该表和历史库根本无法访问。

【方案】
1、 采用分区切换，快速清除旧数据
将表按日进行分区，每日一个区。
比如今天是2010年8月8日，有
--分区函数
CREATE PARTITION FUNCTION [CrPFN] (smalldatetime) AS RANGE LEFT FOR VALUES (N'2010-08-01T00:00:00.000', N'2010-08-02T00:00:00.000', N'2010-08-03T00:00:00.000', N'2010-08-04T00:00:00.000', N'2010-08-05T00:00:00.000', N'2010-08-06T00:00:00.000', N'2010-08-07T00:00:00.000', N'2010-08-08T00:00:00.000 ', N'2010-08-09T00:00:00.000')
GO
--分区方案
CREATE PARTITION SCHEME [crSCM] AS PARTITION [CrPFN] TO ([cr0], [cr1], [cr2], [cr3], [cr4], [cr5], [cr6], [cr7], [cr8], [cr9] )
GO

巨大表应用此分区方案，数据按天划分，分布于[cr0]……[cr9] 这10个文件组。另外，按照巨大表相同的结构，分别在这10个文件组中建立一个不分区的表： [Temp_0]……[Temp_9] ，每个文件组上有一个。

分区切换步骤：
1） 分区切换，将最老分区数据切换到同一文件组的单表（秒杀，几百万条记录瞬时完成，不用1秒）
@day7ago SMALLDATETIME --当前日期的7天前
@day2later SMALLDATETIME --当前日期的2天后
ALTER TABLE [BigTable] SWITCH PARTITION $PARTITION.CrPFN (@day7ago) TO [Temp_0] ;

2） 将最老分区与次老分区合并，也就是8天前分区与7天前分区合并。由于8天前分区的数据已经切换掉，为空，所以合并也瞬时完成。
ALTER PARTITION FUNCTION CrPFN() MERGE RANGE (CONVERT(VARCHAR(10),@day7ago,120));

3） 修改分区方案，将原先最老分区对应的文件组插入到分区队列末尾，以重新使用。
ALTER PARTITION SCHEME [CrSCM] NEXT USED [cr0] ;

4） 拆分最新日期的分区，边界值推进到当前日期的2天后
ALTER PARTITION FUNCTION CrPFN() SPLIT RANGE (CONVERT(VARCHAR(10),@day2later,120));
因为最新日期所在分区还没有数据（因为是未来日期），所以拆分起来也是瞬间完成。这也正是我不只设置7个分区，而是10个分区，永远将边界值往未来移两天的原因。

5） 将移到单表的数据归并入历史库
如何归并，是下面一个话题。

6） 清空单表
TRUNCATE TABLE [Temp_0] ;
总结：
1） 永远只有10个分区，循环使用。最老的分区被合并出局后，马上又插到分区队列末尾。
2） 关键是如何找出分区位于哪个文件组，然后才能知道应该切换给哪个单表，才能放在作业里自动执行。这个方法详见拙作：

查看分区在哪个文件组

3） 源表与单表的结构一定要一致。比如字段类型、是否可以为null，索引是否一致，等等。分区表的索引要与表对齐。是否压缩倒不用一致。

2、 bcp + BULK INSERT，将数据导入历史库
用bcp将数据库导出到文本文件，然后用BULK INSERT。
DECLARE @shell VARCHAR(500);
SET @shell = 'bcp [myDb].dbo.[' + @Table + '] out c:\data.txt -T -n';
EXEC xp_cmdshell @shell;
BULK INSERT [History].dbo.[Table1] FROM 'c:\data.txt';
EXEC xp_cmdshell 'del c:\data.txt';

总结：
1） bcp导出很快。400百万条记录20秒内完成。
2） BULK INSERT号称导入数据很快，但在我这里却没有见到效果。如果目标表是空表，400百万条数据可在1分半钟内导入成功。但我的历史表超过5亿条记录，里面两个索引，超过20分钟不见反应，比直接INSERT还要慢（INSERT大约17分钟），我失去了耐心，直接按取消了。
3） 其实BULK INSERT运行的这20分钟里，已经插入了100多万条，但这样的速度比直接用INSERT还要慢，让人无法接受。我估计是索引的缘故，如果将索引全部删掉，应该会快吧？但重建的时间会更长。究竟这个BULK INSERT是个什么机制，在这里会比INSERT更慢？有个网友说它会在tempDB里处理，然后再插进源表，但我在运行期间，观察了一下系统的tempDB，发现它的文件没有变化。
4） 失败。请高手赐教。