SQL Server部分有趣的整理（6） 分区的数据清理和合并

上一篇：SQL Server部分有趣的整理（2）分表和表分区，按日分区和分表的结合使用

前文有介绍如何对表按日分区

目前有四个分区表，随着时间推移，数据量会变的很大，这时就需要对分区进行管理，清理时间更久之前的数据

1.查询分区情况：

 select $partition.分区函数名称(分区字段) as partitionNum,count(*) as recordCount
from 分区表
group by  $partition.分区函数名称(分区字段)

效果：

另外也可在sysfilegroups表中，根据当初文件组命名规则进行模糊查询（不同情况不同做法，这里就随便先贴一个结果）

除此之外，也可以通过这种方式进行查询：

select 
convert(varchar(100), ds2.name ) as filegroup,
convert(varchar(19), isnull(v.value, CONVERT(dateTime,'1900',101)), 120) as range_boundary
from sys.indexes i
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id
join sys.partition_functions pf on ps.function_id = pf.function_id
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right
WHERE i.object_id = object_id('AccelData')--这里填分区的表名
and i.index_id in (0, 1)
order by p.partition_number

2.分区合并/删除

当分区数量较少时，可以将两个相邻的分区进行合并

对比合并前，少了一个分区，（4号的分区并入了3号的分区）

3.对历史数据进行减半清理

目前的策略是对3个月以前的历史数据进行减半清理，比如第四个月初时，找3个月前的数据，此时无任何操作
第五个月初（第四个月末），删除第一个月的一半数据
第六个月初（第五个月末），删除第一个月的一半数据，第二个月的一半数据（此时第一个月为初始的25%）
。。。。以此类推
假设每个月存储的数据量大小是恒定的，那么这个模型相当于一个等比队列
1+1+1+1/2+1/4+1/8…<4
存储的数据量无限接近于4个月
当某天的分区行数少于100时，对分区进行合并（删除）
先直接贴代码（由于调试需要，暂时改成了保留3天）：

select name as fileGroup,CONVERT(dateTime,RIGHT(name,8),101) as range_boundary 
into #tmpGroup
from sys.filegroups where name like 'AccelData2%' order by range_boundary asc
DECLARE
    @Day AS DateTime,
    @LeftD as DateTime,
    @SaveDay as DateTime,
    @RowCnt as int;    
set @SaveDay=CAST( CONVERT(varchar(100),dateadd(Month,-3,getdate()) , 112) as dateTime)

DECLARE #tmpGroup CURSOR FAST_FORWARD FOR
    SELECT range_boundary FROM #tmpGroup ORDER BY range_boundary Asc;
OPEN #tmpGroup;
FETCH NEXT FROM #tmpGroup INTO @Day;

WHILE @@FETCH_STATUS=0
BEGIN
    -- 操作
    if(@SaveDay<=@Day)
	begin
		break;
	end
	if(@LeftD is null)
	begin
		set @LeftD=CONVERT(dateTime,'1900',101);
		FETCH NEXT FROM #tmpGroup INTO @Day;
		continue;
    end
    
	select @RowCnt=COUNT(OccTime) from AccelData  where OccTime <@Day and occTime>=@LeftD	
	insert into PlanRecord values('Clear AccelData before '+CONVERT(varchar(100), @Day, 111)+ ' Before Clear Count:'+CAST(@RowCnt as varChar(50)),GETDATE())
	
-----------------AccelData历史数据减半清理
    select SensorNum,GroupSeqNum,COUNT(GroupSeqNum) as Cnt,OccTime 
	into #tmpAccelData_Day from AccelData  where OccTime <@Day and occTime>=@LeftD	
	Group by SensorNum,OccTime,GroupSeqNum
	order by SensorNum,OccTime

	--选择时间段内的数据进行减半删除
		Declare
			@SensorNum as int,
			@GroupSeqNum as int,
			@Cnt as int,
			@occTime as DateTime,
			@isdelete as tinyint;
		-- 声明游标
		DECLARE #tmpAccelData_Day CURSOR FAST_FORWARD FOR
    		SELECT SensorNum,GroupSeqNum,Cnt,OccTime
    		FROM #tmpAccelData_Day
    
		OPEN #tmpAccelData_Day;
		FETCH NEXT FROM #tmpAccelData_Day INTO @SensorNum,@GroupSeqNum,@Cnt,@occTime;
		set @isdelete=1;
		WHILE @@FETCH_STATUS=0
		BEGIN
   		 -- 操作
   			if(@Cnt<23)
				set @isdelete=1;    
    		if(@isdelete=1)		
				delete AccelData where SensorNum=@SensorNum and OccTime=@occTime and GroupSeqNum=@GroupSeqNum
			set @isdelete=(@isdelete+1)%2
		    -- 取下一条记录
    		FETCH NEXT FROM #tmpAccelData_Day INTO @SensorNum,@GroupSeqNum,@Cnt,@occTime;
		END

		-- 关闭，释放游标，释放临时表
		CLOSE #tmpAccelData_Day;
		DEALLOCATE #tmpAccelData_Day;
		Drop Table #tmpAccelData_Day;
-----------------
		
	select @RowCnt=COUNT(OccTime) from AccelData  where OccTime <@Day and occTime>=@LeftD	
	insert into PlanRecord values('Clear AccelData before '+CONVERT(varchar(100), @Day, 111)+ ' After Clear Count:'	+CAST(@RowCnt as varChar(50)),GETDATE())
	if(@RowCnt<100)
	begin
		alter partition function PARTITION_FUNCTION_AccelData()
		merge range( CONVERT(varchar(100), @Day, 120))  
		insert into PlanRecord values('Merge AccelData_Paritition '+CONVERT(varchar(100), @Day, 111),GETDATE())
		declare @Sql varChar(500);
		set @Sql='
		DBCC SHRINKFILE (AccelOriginData'+CONVERT(varchar(100), @Day, 112) +', EMPTYFILE);
		ALTER DATABASE kjtx_TVSensor REMOVE FILE AccelOriginData'+CONVERT(varchar(100), @Day, 112)+';      
		ALTER DATABASE kjtx_TVSensor REMOVE FILEGROUP AccelData'+CONVERT(varchar(100), @Day, 112) 
		Exec (@Sql)
		insert into PlanRecord values('Shrink and Remove File/FileGroup AccelOriginData/AccelData'+CONVERT(varchar(100), @Day, 111),GETDATE())
		set @LeftD=@Day;
		FETCH NEXT FROM #tmpGroup INTO @Day;
		continue;
	end
    set @LeftD=@Day;
    -- 取下一条记录
    FETCH NEXT FROM #tmpGroup INTO @Day;
END
-- 关闭，释放游标，释放临时表
CLOSE #tmpGroup;
DEALLOCATE #tmpGroup;
Drop table #tmpGroup

4.文件组及文件的管理

当分区合并之后，原本的分区所对应的文件组及文件并未被删除
哪怕该文件内已经没有数据了，但是文件的创建会存在初始的默认大小
文件组及文件的清楚分为以下几个步骤：
1.收缩文件：

DBCC SHRINKFILE (文件名, EMPTYFILE);

2.删除文件：

ALTER DATABASE 数据库名称 REMOVE FILE 文件名

3.删除文件组：

ALTER DATABASE 数据库名称 REMOVE FILEGROUP 文件组名称

由于文件名，文件组的名字不是固定的，实际使用中，可以将这三步拼接成一个字符串，再将字符串作为sql语句执行：

Declare @sql varChar(500);
Set @sql='....';
Exec (@sql);

执行结果：
对其他分区表进行同样处理，执行多次后：

后续将此段代码做成存储过程的形式，并且在
上一篇：SQL Server部分有趣的整理（5） 通过代理定期执行维护作业
中提到的维护计划中调用。
通过这种方式，就可以很轻松的处理历史数据了。