The case of the columnstore index and the memory grant

This post covers examples from Denali CTP3, version 11.0.1440.  

I was working with Denali’s columnstore index feature this last week and was testing it on a virtual machine when I encountered the following error message when trying to create a new index:

The statement has been terminated.
Msg 8657, Level 17, State 5, Line 2
Could not get the memory grant of 91152 KB because it exceeds the maximum configuration limit in workload group 'default' (2) and resource pool 'default' (2). Contact the server administrator to increase the memory usage limit.'

Now at the time the VM I was using was constrained for resources. It was configured to use 1 GB of RAM.

So I shut down the VM and added in 7 GB more of RAM. Given that the “max server memory (MB)” wasn’t capped, I was then able to create the columnstore index successfully. Regarding the memory requirements, BOL states that we need [approximately] 8 MBs times the # of columns in the index times the DOP.  Also, the more string data type columns involved, the higher the memory needed for creating the index.

So I thought I would test out these requirements in a more controlled fashion and see how close the estimates were to the reality.

In this test, I used a VM with 4 processors and 8 GB of RAM, but unlike with my previous experience I capped the “max server memory (MB)” to a much lower value to reproduce the issue I had earlier.  I was adding the columnstore index to the dbo.FactInternetSales table from the AdventureWorksDWDenali database (which can be downloaded here).

For the first step, I capped the max server memory for my test Denali SQL Server instance to a very low value (300 MBs did the trick in this case):

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'max server memory (MB)', 300

RECONFIGURE

 

Next I attempted to create the following columnstore index:

USE [AdventureWorksDWDenali]

GO

 

CREATE NONCLUSTERED COLUMNSTORE

INDEX [NCSI_FactInternetSales]

ON [dbo].[FactInternetSales]

(

          [ProductKey],

          [OrderDateKey],

          [DueDateKey],

          [ShipDateKey],

          [CustomerKey],

          [PromotionKey],

          [CurrencyKey],

          [SalesTerritoryKey],

          [SalesOrderNumber],

          [SalesOrderLineNumber],

          [RevisionNumber],

          [OrderQuantity],

          [UnitPrice],

          [ExtendedAmount],

          [UnitPriceDiscountPct],

          [DiscountAmount],

          [ProductStandardCost],

          [TotalProductCost],

          [SalesAmount],

          [TaxAmt],

          [Freight],

          [CarrierTrackingNumber],

          [CustomerPONumber],

          [OrderDate],

          [DueDate],

          [ShipDate])

GO

 

This failed with the following error:

 

The statement has been terminated.

Msg 8657, Level 17, State 5, Line 2

Could not get the memory grant of 143560 KB because it exceeds the maximum configuration limit in workload group 'default' (2) and resource pool 'default' (2).  Contact the server administrator to increase the memory usage limit.

 

So the memory grant request was just over 140 MB.  Looking at the requirements of the columnstore index request and taking into account the equation in BOL, we have 8 MBs x 25 columns x 4 available processors = 800 MB.  Quite a bit higher than the 140 MB it needed at runtime.  But this was assuming 4 processors in the equation.  If we factor in just 1 proc being used – we have 200 MB which is closer to what was being requested and factors in the varying data types and sizes for columns defined in FactInternetSales.

What if I capped the DOP but keep the memory capped at the low value?  I gave it a try:

 

EXEC sp_configure 'max degree of parallelism', 1

RECONFIGURE

 

Attempting a creation of the same index gave the following error message and same memory grant value:

 

The statement has been terminated.

Msg 8657, Level 17, State 5, Line 1

Could not get the memory grant of 143560 KB because it exceeds the maximum configuration limit in workload group 'default' (2) and resource pool 'default' (2).  Contact the server administrator to increase the memory usage limit.

 

So in this case, capping the DOP didn’t reduce the memory requirements.

 

What about using the MAXDOP hint with the CREATE statement (WITH (MAXDOP = 1))?  Again this returned the same memory grant requirement of 143560 KB.

 

What about capping the default workload group itself (not recommending this as standard practice – but rather to further explore memory grant requirements for columnstore)?

 

ALTER WORKLOAD GROUP [default]

WITH(max_dop=1)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

 

And even after this change, the memory grant requirements remained the same. So I reverted the DOP options to get back to my previous state:

 

EXEC sp_configure 'max degree of parallelism', 0

RECONFIGURE

 

ALTER WORKLOAD GROUP [default]

WITH(max_dop=0)

GO

 

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

 

Regarding the maximum memory grant request itself, the error message gives us enough of a hint on where to look (workload group 'default' (2) and resource pool 'default' (2)).   So I ran the following query to return the request_max_memory_grant_percent value for the default workload group:

 

SELECT request_max_memory_grant_percent

FROM sys.resource_governor_workload_groups

WHERE name = 'default'

 

The value returned was 25%.  So with my 300 MB cap, we’re talking 75 MB.  Not the 140 MB we need. 

I then bumped up the max memory grant percent (but left the max server memory at the low value) just to see what would happen:

 

ALTER WORKLOAD GROUP [default] WITH(request_max_memory_grant_percent=70)

GO

 

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

 

Sure enough – my CREATE NONCLUSTERED COLUMNSTORE INDEX was allowed to execute – but while it executed, it didn't complete.  Instead it ran for 2 minutes and 59 seconds before getting the following error message:

 

The statement has been terminated.

Msg 8645, Level 17, State 1, Line 1

A timeout occurred while waiting for memory resources to execute the query in resource pool 'default' (2). Rerun the query.

I tried creating the index a second time so that I could see what was going on in sys.dm_exec_query_memory_grants – but it executed immediately the second time around. So I set back the max memory grant to the default 25 % – dropped the index and tried to recreate and got the error again.  I then set the max memory grant back to 70% and had the timeout again – but this time I was ready for it and I executed a query against sys.dm_exec_query_memory_grants:

 

SELECT scheduler_id, dop, requested_memory_kb, required_memory_kb, ideal_memory_kb

FROM sys.dm_exec_query_memory_grants

 

The results were as follows:

 

 

What jumped out at me was the DOP value of “1” (even though my SQL Server instance’s “max degree of parallelism” was set to “0” and I had 4 available prcs and the “default” pool dop was also set to 0).

 

Now it seems that the requested memory was remaining the same because the plan was assuming to be maxdop of “1” all along.  Which made me wonder if the requested memory would increase if I added a hint for the creation of the columnstore index to more than one processor (I was reaching, I know, but I'm a fiddler by nature)?  Before testing this, I set back the max memory grant percent to 25%:

 

ALTER WORKLOAD GROUP [default] WITH(request_max_memory_grant_percent=25)

GO

 

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

 

Then I used WITH (MAXDOP = 4) for the query.  The result?  Still asking for a memory grant of 143560 KB.  And repeating the test of bumping up the max memory grant and checking the dop value in sys.dm_exec_query_memory_grants – it remained at “1”. 

So this post was more of an exploration and if I saw this case in the wild I would ask more directly about available memory for the SQL Server instance and/or increasing the max server memory if there was sufficient availability already. 

I would also ask about the necessity of each column being included in the columnstore index definition.  For example – if I needed only half of the columns from FactInternetSales, we’re talking about a 60MB memory grant requirement versus a 140 MB one.

 

During my work last week with columnstore indexes, there were also some interesting findings related to parallelism.  I’ll save this for another post.