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!

Advertisements