sql server 为千万级数据量的表建立分区效果如何

1.数据库所在服务器信息

2.表数据量截图

declare @table_spaceused table (name nvarchar(100) ,rows int ,reserved nvarchar(100) ,data nvarchar(100) ,index_size nvarchar(100) ,unused nvarchar(100) ) 
insert into @table_spaceused (name,rows,reserved,data,index_size,unused ) exec sp_MSforeachtable @command1='exec sp_spaceused ''?''' 
select * from @table_spaceused order  by  rows desc

3.表索引截图

4.建立分区前查询数据所用时间

 4.1准备查询语句

查询3月26日至4月1日前的数据：查询3月份内的数据

select * from t_dd_qm_quality_collection_final where collection_time >='2022-03-26' and collection_time<'2022-04-01'

查询3月26日至4月11日前的数据：查询3、4月份内的数据

select * from t_dd_qm_quality_collection_final where collection_time >='2022-03-26' and collection_time<'2022-04-11'

查询3月26日至5月11日前的数据：查询3、4、5月份内的数据

select * from t_dd_qm_quality_collection_final where collection_time >='2022-03-26' and collection_time<'2022-05-11'

查询3月26日至6月11日前的数据：查询3、4、5、6月份内的数据

select * from t_dd_qm_quality_collection_final where collection_time >='2022-03-26' and collection_time<'2022-06-11'

4.2清除所有缓存

DBCC DROPCLEANBUFFERS

4.3重启sqlserver服务

4.4执行查询语句记录时间(毫秒)，每次查询前清除缓存，重启服务器

　　初期清除缓存重启服务后的CPU30%，内存47%。

	查询3月份内的数据				查询3、4月份内的数据				查询3、4、5月份内的数据(未测试)				查询3、4、5、6月份内的数据(未测试)
	查询结果数据量	时间	峰值CPU	峰值内存	查询结果数据量	时间	峰值CPU	峰值内存	查询结果数据量	时间	峰值CPU	峰值内存	查询结果数据量	时间	峰值CPU	峰值内存
第一次	856150	209190	67	77	2160486	249617	65	78
第二次	856150	229190	67	77	2160486	250946	89	78
第三次	856150	228963	68	77	2160486	250230	77	77
平均值	856150	222448	67.5	77	2160486	250264	77	77.7
		22653	67	51		56160	80	59
		22664	73	52		56230	89	59
		22910	73	53		55890	71	58
平均值

 

5.为表创建分区

5.1经查询表中最早的时间是2021-09月，最晚的时间是2022-06月。本表只留近90天内的数据，其余符合历史化条件的均已历史化。2022-03月前的数据有10万+条，故可以分4个区，3月份前一个区，3月份数据一个区，4月份数据一个区，5月份数据一个区，6月份及以后一个区。

5.2创建数据库文件组：选择数据库进入属性页面，选择文件组点击添加文件组，依次录入5个文件组名称，点击确定。

 

 5.3创建数据库文件：选择数据库进入属性页面，选择文件点击添加文件，依次录入5个文件名称并选择相应的文件组，点击确定。

 5.4创建分区：选择要创建分区的表，右键选择存储-->创建分区，进入创建分区向导。

5.4.1选择分区列：选择采集时间collection_time

 

5.4.2创建分区函数

5.4.3创建分区方案

 

 5.4.4映射分区范围选择左边界，文件组中依次选择原先创建的文件组，点击设置边界，开始日期输入20220301，结束日期输入20220601，日期范围选择每月，确定后会按月依次填充到边界一列中。之后点击下一步

预计存储控件按钮不要随便点，若表内数据量小还可以，计算出来不耗时，若表内数据量大会执行很长时间。

 

 5.4.5选择输出模板，表内数据量小可以立即执行，表内数据量大选择创建脚本，之后再编辑器内执行。我这里选择创建脚本。之后点击完成。

 

 5.4.6编辑器内点击执行，创建分区。1387万的数据，脚本方式执行创建分区耗时40分钟。

 6.建立分区后查询数据所用时间