In a previous post about non-clustered columnstore indexes, I mentioned the creation of an index is a very memory intensive operation. Sometimes the memory grant needed exceeds what is currently available on your server. So what do you do about it?
SQL Server requires a minimal amount of memory in order to create a columnstore index. This can be calculated as Memory Grant Request in MB = ((4.2 * number of columns in the columnstore index) + 68) * Degree of Parallelism + (number of string columns * 34). If there is not enough physical memory available to create the columnstore index, SQL Server will throw an error.
The test server I’m using for the examples below has 2 CPUs and 4GB of memory.
The Max Degree of Parllelism is set to 0 and Max Server Memory is set to 4095MB.
EXEC sp_configure 'max degree of parallelism';
GO
EXEC sp_configure 'max server memory (MB)';
GO
I have enlarged a table, FactInternetSales, in the AdventureWorksDW2012 database using Kalen Delaney’s script. The table has 247 million rows and contains 26 columns; three of which are string columns. If we want to create a non-clustered columnstore index on the entire table, then using the formula above we could estimate a memory grant of 456MB would be needed to build the index.
To create the index we’ll use this query.
USE AdventureWorksDW2012;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csi_FactInternetSalesBig ON dbo.FactInternetSalesBig
(
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
Once we execute that query, we can view the memory grant using this query.
SELECT
dop
,requested_memory_kb
,required_memory_kb
,ideal_memory_kb
,granted_memory_kb
FROM sys.dm_exec_query_memory_grants
WHERE session_id = 55;
GO
The requested memory grant was actually 525MB, but it still wasn’t too far from our estimation. What would happen to the create index query if we change the max server memory to 1024MB? Let’s find out.
EXEC sp_configure 'max server memory (MB)',1024;
GO
RECONFIGURE;
GO
Oops, we got an error.
The statement has been terminated.
Msg 8658, Level 17, State 1, Line 1
Cannot start the columnstore index build because it requires at least 341424 KB, while the maximum memory grant is limited to 189696 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 is telling us we need an absolute minimum of 341MB of memory to create the colunstore index. That seems weird since we have 1024MB of memory configured for the server. Well not really. The Resource Governor is always running in the background for all SQL Servers, and every query executes inside the default workgroup pool. That default workload group has a limit of granting no more than 25% of available memory to one single query. This can be verified by running the following query.
SELECT
name
,request_max_memory_grant_percent
FROM sys.dm_resource_governor_workload_groups
WHERE name = 'default';
GO
There are a couple of workarounds to this problem.
First, we can try to reduce the max degree of parallelism when creating the index. Using the formula above, that would have an estimated memory grant of 279MB. That’s still more than our minimum allowable grant size, but let’s try it anyway. All we have to do is add the WITH (MAXDOP=1) hint to the end of the create index statement.
USE AdventureWorksDW2012;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csi_FactInternetSalesBig ON dbo.FactInternetSalesBig
(
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
) WITH (MAXDOP=1);
GO
Oops, we got the same error again.
The statement has been terminated.
Msg 8658, Level 17, State 1, Line 1
Cannot start the columnstore index build because it requires at least 341424 KB, while the maximum memory grant is limited to 189696 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.
Now it’s on to our next workaround; changing the Resource Governor settings. As I mentioned above, the Resource Governor limits each query to have a memory grant of 25% of the total available. We can easily be changed by using the following query.
ALTER WORKLOAD GROUP [default] WITH (request_max_memory_grant_percent = 50);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
The setting change is dynamic, but let’s double check it.
SELECT
name
,request_max_memory_grant_percent
FROM sys.dm_resource_governor_workload_groups
WHERE name = 'default';
GO
Now let’s try to create the columnstore index again.
Ahh success! We can check the memory grant while it’s running.
SELECT
dop
,requested_memory_kb
,required_memory_kb
,ideal_memory_kb
,granted_memory_kb
FROM sys.dm_exec_query_memory_grants
WHERE session_id = 55;
GO
Once the columnstore index has been created, you should change the default workload group back to its default of 25. That way it doesn’t adversely affect other queries running on your server.
ALTER WORKLOAD GROUP [default] WITH (request_max_memory_grant_percent = 25);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Another workaround is to exclude some of the 26 columns from our create index statement. Using the formula above, if we removed the last six columns of the table, we’d only need a 186MB memory grant. However, that may not be an option, because our user queries may need those columns to be part of the colunstore index to get the maximum performance for their queries.
Finally, as last workaround, you could add more memory to the server, but unless your server is VM that might be bit impossible for most situations.
For more info on columnstore indexes, check out the Columnstore Index FAQ on Technet, Books Online, and my other blog posts.
