SQL Server 列存储索引性能总结（10）——行组的大小影响

接上文SQL Server 列存储索引性能总结（9）——重建和重组聚集列存储索引所需的内存我们知道，为了更好的性能，行组（row group)的大小最好是1045678行，或者小于10万行。如果没有办法达到最佳大小，在读取大量数据的时候，就很难用到列存储的优点。

前言

   在列存储索引中，最重要的概念就是行组和片段，它们分别代表了数据存储在行存储和列存储中。在片段中，不管你存了1行还是100万行数，读取的时候都是每个页或者区来读取，所以如果行数太少，是挺浪费的。
   如果对未排序的列使用筛选条件，那可能会调用很多额外的片段，因为片段最好还好是已排序。未排序的数据可能会分不到多个片段中。我们知道页越多，最终的性能就越差。对于小型表（百万/千万级别），当然很难只读一个片段，也不可能只读一个页/区，不过这种规模并不是非常影响。但是如果是数十亿行的表，不必要的片段将会成为性能杀手。

环境搭建

   接下来继续用ContosoRetailDW来做演示，并把兼容级别设置到150也就是使用SQL Server 2019的特性。：

USE [master]
GO
ALTER DATABASE [ContosoRetailDW] SET COMPATIBILITY_LEVEL = 150
GO

-- 创建聚集列存储索引:
create clustered columnstore Index CCI  on dbo.FactOnlineSales;


select * into dbo.FactOnlineSales_SmallGroups_Test from dbo.FactOnlineSales;

   接下来的技巧要注意了，我把SQL Server的Max Server Memory降低，比如300MB（只能在你自己的实验环境下测试，毕竟300MB内存在任何企业环境下都会导致系统缓慢甚至无法响应），用来强制只用少量的行创建行组：

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'300'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

   接下来创建聚集列存储索引到测试表上，由于内存原因，需要跑一段时间，大概3分钟左右：

create clustered columnstore index CCI on dbo.FactOnlineSales_SmallGroups_test;

   然后对比一下空间大小：

exec sp_spaceused '[dbo].[FactOnlineSales]';
exec sp_spaceused '[dbo].[FactOnlineSales_SmallGroups_test]';

   两者有所差距，但是大小不是非常明显，源表占了163MB的空间，测试表有189MB。但是一旦行组的数量非常多的时候，这个差异将会非常明显。我们来细化一下两个表的行组信息：

SELECT object_name(i.object_id) as TableName, count(*) as RowGroupsCount
	FROM sys.indexes AS i
	INNEr JOIN sys.column_store_row_groups AS rg with(nolock)
		ON i.object_id = rg.object_id
	AND i.index_id = rg.index_id 
	WHERE object_name(i.object_id) in ( 'FactOnlineSales','FactOnlineSales_SmallGroups_test')
	group by object_name(i.object_id)
	ORDER BY object_name(i.object_id);

   可以看出行组的数量差异很大。测试表有79个行组但是源表只有15个，差了快6倍。接下来看看查询（打开实际执行计划）的效果：

dbcc freeproccache;
dbcc dropcleanbuffers;
set statistics io on
set statistics time on

select prod.ProductName, sum(sales.SalesAmount)
	from dbo.FactOnlineSales sales
		inner join dbo.DimProduct prod
			on sales.ProductKey = prod.ProductKey
		inner join dbo.DimCurrency cur
			on sales.CurrencyKey = cur.CurrencyKey
		inner join dbo.DimPromotion prom
			on sales.PromotionKey = prom.PromotionKey
	where cur.CurrencyName = 'USD' and prom.EndDate >= '2004-01-01' 
	group by prod.ProductName;
--清空缓存以免受影响
dbcc freeproccache;
dbcc dropcleanbuffers;

select prod.ProductName, sum(sales.SalesAmount)
	from dbo.FactOnlineSales_SmallGroups_t