Working around “Cannot start the columnstore index build because…”

 

Recently I run into the issue trying to improve query performance by building the column store index on the one of the tables. The message I got building column store index:

Cannot start the columnstore index build because it requires at least 2458240 KB, while the maximum memory grant is limited to 854208 KB per query in workload group ‘default’ (2) and resource pool ‘default’ (2). Retry after modifying columnstore index to contain fewer columns, or after increasing the maximum memory grant limit with Resource Governor.

And this is very strange: I have 8 GB of RAM on the box, 7 GB dedicated to the SQL Server. Nothing else is running on this computer. If we look at the error message, we see that the query is asking for 2,4GB. So, there is enough of memory to successfully build the index.

Well, the error message tells very much directly to check the Resource Governor settings on the server. You may want to use SQL Server Management Studio for this, as well as the script like this:

sp_configure ‘show advanced options’, 1

go

reconfigure

go

sp_configure

go

select * from sys.resource_governor_resource_pools

select * from sys.resource_governor_workload_groups

Running those queries, I got following output

Query Pool configuration :

pool_id	name	min_cpu_percent	max_cpu_percent	min_memory_percent	max_memory_percent	cap_cpu_percent
1	internal	0	100	0	100	100
2	default	0	100	0	100	100

Workload groups configuration:

group_id	name	importance	request_max_memory_grant_percent	request_max_cpu_time_sec	pool_id
1	internal	Medium	25	0	1
2	default	Medium	100	0	2

So, according to the output, default query pool may hope for all available for SQL Server memory, but for whatever reason not getting it. What is really going on on the box you can check with the dbcc memorystatus.

Alternatively, and this is that I did, it is possible to force server not releasing memory by setting up minimum memory settings (I set it to the 45% of the memory size dedicated to the server) . And voila! Column store index was built successfully!