SQL Server – Why CCI Creation on Empty Table Requests Huge Memory Grant

columnstorememory-grantsql serversql-server-2016

On a QA server with very low memory, I experienced error 8545 when creating clustered columnstore indexes on new tables in SQL Server 2016:

Error 8645: A time out occurred while waiting for memory resources to execute the query. Re-run the query.

I can easily reproduce a large requested memory grant on my local machine. For the following code:

DROP TABLE IF EXISTS dbo.MY_FIRST_FACT_TABLE;

CREATE TABLE dbo.MY_FIRST_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL,
    COL2 BIGINT NULL,
    COL3 BIGINT NULL,
    COL4 BIGINT NULL,
    COL5 BIGINT NULL,
    COL6 BIGINT NULL,
    COL7 BIGINT NULL,
    COL8 BIGINT NULL,
    COL9 BIGINT NULL,
    COL10 BIGINT NULL,
    STRING1 VARCHAR(100) NULL,
    STRING2 VARCHAR(100) NULL,
    STRING3 VARCHAR(100) NULL,
    STRING4 VARCHAR(100) NULL,
    STRING5 VARCHAR(100) NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX MY_FIRST_CCI ON dbo.MY_FIRST_FACT_TABLE;

I get a memory request of around 512 MB of memory. There is a warning for the excessive memory grant in the actual execution plan:

memory grant

Even though the query uses 0 KB of memeory it still might time out depending on other activity on the server. Why does SQL Server request so much memory? What can I do about it?

Best Answer

There's a hint about this in the documentation for CCI query performance:

Plan for enough memory to create columnstore indexes in parallel

Creating a columnstore index is by default a parallel operation unless memory is constrained. Creating the index in parallel requires more memory than creating the index serially. When there is ample memory, creating a columnstore index takes on the order of 1.5 times as long as building a B-tree on the same columns.

The memory required for creating a columnstore index depends on the number of columns, the number of string columns, the degree of parallelism (DOP), and the characteristics of the data. For example, if your table has fewer than one million rows, SQL Server will use only one thread to create the columnstore index.

That quote suggests that the number of rows in the table matters for DOP but doesn't matter for the memory grant. The memory required also depends on the number of columns so let's test with a table with fewer columns to more easily see the expected difference in memory grant. I'm using this table and index definition:

DROP TABLE IF EXISTS dbo.MY_SECOND_FACT_TABLE;

CREATE TABLE dbo.MY_SECOND_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL
);

INSERT INTO dbo.MY_SECOND_FACT_TABLE WITH (TABLOCK) (ID, COL1)
SELECT TOP (0) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

CREATE CLUSTERED COLUMNSTORE INDEX MY_SECOND_CCI ON dbo.MY_SECOND_FACT_TABLE WITH (MAXDOP = 1);

Below are results from a few tests:

╔══════════════════╦════════════════╦═════════════════╗
║ REQUESTED_MAXDOP ║ NUMBER_OF_ROWS ║ MEMORY_GRANT_KB ║
╠══════════════════╬════════════════╬═════════════════╣
║                1 ║              0 ║           89928 ║
║                2 ║              0 ║           89928 ║
║                1 ║        2000000 ║           89928 ║
║                2 ║        2000000 ║          179896 ║
║                1 ║        5000000 ║           89928 ║
║                2 ║        5000000 ║          179896 ║
╚══════════════════╩════════════════╩═════════════════╝

With 0 rows the DOP is downgraded to 1, but otherwise the number of rows doesn't appear to matter for the memory grant. This is undoubtedly a gross oversimplification, but perhaps SQL Server builds the CCI one rowgroup at a time per DOP and maintains the string dictionaries as it goes along. That sounds fine in practice and means that the memory grant won't scale with the number of rows in the table, but without a check for a very low number of rows SQL Server could request an excessive memory grant. The excessive memory grant wouldn't matter in most cases because the CREATE INDEX is so fast, but it can matter if SQL Server waits on the memory grant.

So now I have an idea of why the memory grant is so large, but what can be done about it? SQL Server 2014 introduced the syntax to define an index definition as part of the table definition. It is possible to write a single statement that creates both the table and the index:

INDEX index_name CLUSTERED COLUMNSTORE

Applies to: SQL Server 2014 through SQL Server 2016 and Azure SQL Database.

Specifies to store the entire table in columnar format with a clustered columnstore index. This always includes all columns in the table. The data is not sorted in alphabetical or numeric order since the rows are organized to gain columnstore compression benefits.

For the original table the syntax would look like this:

CREATE TABLE dbo.MY_FIRST_FACT_TABLE (
    ID BIGINT NOT NULL,
    COL1 BIGINT NULL,
    COL2 BIGINT NULL,
    COL3 BIGINT NULL,
    COL4 BIGINT NULL,
    COL5 BIGINT NULL,
    COL6 BIGINT NULL,
    COL7 BIGINT NULL,
    COL8 BIGINT NULL,
    COL9 BIGINT NULL,
    COL10 BIGINT NULL,
    STRING1 VARCHAR(100) NULL,
    STRING2 VARCHAR(100) NULL,
    STRING3 VARCHAR(100) NULL,
    STRING4 VARCHAR(100) NULL,
    STRING5 VARCHAR(100) NULL,
    INDEX MY_FIRST_CCI CLUSTERED COLUMNSTORE
);

A CREATE TABLE statement does not generate an actual plan so I can't immediately tell if this reduced the memory grant. I tried creating the table 1000 times while hammering the sys.dm_exec_query_memory_grants DMV but never got any results. That suggests that memory was not granted but isn't proof. I also tried extended events with query_memory_grant_usage and didn't get any results for the single CREATE TABLE statement.

Of course, the best way to test is to create the CCI the original workflow conditions with limited available memory. I did that and did not experience any memory timeouts.

I suppose it could be said that defining the CCI in the CREATE TABLE statement for a new table is a best practice. However, if your workload causes this error then changing the CREATE TABLE script probably isn't sufficient to address other problems that you'll run into, such as memory issues when you starting loading data into the CCIs.

Related Question