Columnstore index rebuild cannot start

A couple of weeks ago, I was charged to migrate an SQL Server datawarehouse environment to a more recent version of SQL Server (from 2005 to 2012 in my case). After discussing with my customer we decided to benefit from the nonclustered columnstore index feature to improve reporting queries performance directly executed against the concerned database.

So the implementation was successful and we included an additional job that consists in disabling as well as rebuilding the columnstore indexes (they are in read-only mode with SQL Server 2012) after bulk loading data into the concerned tables. The job was encapsulated into an SSIS process.

The first two days, everything run successfully but the third one, we experienced an overall performance issue on the same server. In fact, we didn’t get sufficient memory on the server side and the different instances installed on it were struggling to get enough room to satisfy the executed queries

More specifically, we experienced obvious symptoms as low PLE, abnormal buffer cache ratio drops and lazy writer activities as well. But the most interesting point here, one of another resulting edge effect was the following error message from the SSIS catalog when SQL Server tries to rebuild one of the columnstore indexes:

SQL Server seems to miss some memory from the default resource pool (pool_id = 2) when it tries to rebuild a columnstore index. As a reminder, the Resource Governor limits each query to have a memory grant of 25% of the total available even if it is not enabled (by default).  Well, it sounds very interesting because this is not definitely the first kind issue we have to deal with. So the first question that came to my mind was: how SQL Server evaluates the amount of memory to start the rebuild step of the columnstore index?  Microsoft provides a formula to estimate the amount of memory required to create a columnstore index that is the following:

Memory grant request in MB = [(4.2 *Number of columns in the CS index) + 68]*DOP + (Number of string cols * 34)

Well, referring to my customer context I was able to estimate the amount of memory required in my case:

Memory grant request (MB) = (4.2 * 8 + 68) * 4 + (1 * 34) = 440 MB

• The columnstore index included all the columns of the underlying table (8 columns).
• Assuming SQL Server uses all the processors available from SQL Server, the DOP value is equal to 4
• Finally, the underlying table contained only one string column

But, I was surprised to notice my estimation was a quite bit higher than the value from the error message (133 MB). What I have missed out here?

Let’s investigate further by testing the same database against on my lab environment that includes the same SQL Server version (2012 SP2) and the same configuration. I limited voluntary my SQL Server instance to 300MB with a total of 4 processors available for my instance in order to be as close as possible to my customer environment.

select 
	parent_node_id,
	cpu_id,
	scheduler_id
from sys.dm_os_schedulers
where [status] = 'VISIBLE ONLINE';
GO

After starting the rebuild index operation, I effectively faced the following error message:

Msg 8658, Level 17, State 1, Line 1

Cannot start the columnstore index build because it requires at least 158744 KB, while the maximum memory grant is limited to 56640 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.

This time, the estimated amount of memory was not exactly the same. Now, let’s increase the max memory limit to 2048MB in order to get rid out of this error message and leave enough memory to rebuild the columnstore index without any issues. At the same time, I will get memory requested by the rebuild operation from the sys.dm_exec_memory_grants DMV.

select 
	session_id,
	request_id,
	scheduler_id,
	dop,
	group_id,
	pool_id,
	requested_memory_kb,
	granted_memory_kb,
	ideal_memory_kb
from sys.dm_exec_query_memory_grants

One interesting thing from this output is the dop value because we may noticed that the query is using only 2 processors. This changes a little bit the equation.

Memory grant request (MB) = (4.2 * 8 + 68) * 2 + (1 * 34) = 237 MB

Let’s do the same math with only one processor (135.6 MB) and this time we may find a value pretty close to what SQL Server estimates on the first error message. Here the output from the sys.dm_exec_query_memory_grants with maxdop = 1:

I found the same value either by using the maxdop hint or by changing the max degree of parallelism at the server level.

My guess is that SQL Server estimates the minimum of memory with one processor and this does make sense here. We want to get the minimum amount of memory required for the rebuild operation and according to the Microsoft formula, increasing the maxdop value will also increase the memory in the same order of magnitude.

Finally the billion dollar question: how I solved this problem? In my case, the solution was pretty simple and we decided to increase the overall memory on the server because we suffered from a general memory pressure on it. Then we reconfigured the max memory setting on each SQL Server instance regarding the new available memory size.  But of course there are other alternatives: you may decrease either the maxdop value or the number of columns included to the columnstore index. You may also either change the request_max_memory_grant of the default workload group (be careful to the potential side effects) or perform the rebuild index operation on a specific resource pool for example. It is up to you to find out the right solution that will address your memory issue.

Happy columnstore stuff!

By David Barbarin