SQL Server 2016: 1 GB memory grant to create an *empty* CCI?

**Update 22 October 2017**
Joe Obbish reaches the same conclusions in his thoroughly documented Stackexchange treatment.  Check it out - I usually like his stuff more than mine :-)

Why does CCI creation on an empty table request a huge memory grant?
https://dba.stackexchange.com/questions/172434/why-does-cci-creation-on-an-empty-table-request-a-huge-memory-grant

*****

CREATE TABLE dbo.MyFact_heap 
( [Key] bigint NOT NULL, 
[Type] nvarchar(50) NOT NULL, 
Id nvarchar(50) NOT NULL, 
AltKey_1 bigint NOT NULL DEFAULT -1, 
AltKey_2 bigint NOT NULL DEFAULT -1, 
AltKey_3 bigint NOT NULL DEFAULT -1, 
AltKey_4 bigint NOT NULL DEFAULT -1, 
AltKey_5 bigint NOT NULL DEFAULT -1, 
AltKey_6 bigint NOT NULL DEFAULT -1, 
AltKey_7 bigint NOT NULL DEFAULT -1, 
AltKey_8 bigint NOT NULL DEFAULT -1, 
AltKey_9 bigint NOT NULL DEFAULT -1, 
AltKey_10 bigint NOT NULL DEFAULT -1, 
String_1 nvarchar(300) NOT NULL DEFAULT N'*Default', 
Numeric_1 numeric(18,2) NULL, 
Date_1 date NULL,

TimeValue time NULL, 
Int_1 int NULL, 
String_2 nvarchar(300) NOT NULL DEFAULT N'*Default', 
Tiny_1 tinyint NULL DEFAULT 0, 
Tiny_2 tinyint NULL DEFAULT 0, 
Tiny_3 tinyint NULL, 
Tiny_4 tinyint NULL, 
Tiny_5 tinyint NULL, 
Tiny_6 tinyint NOT NULL DEFAULT 1 ) 

CREATE CLUSTERED COLUMNSTORE INDEX CCI__MyFact 
ON dbo.MyFact_heap

Requesting the actual plan for the CREATE, I find this in the plan XML.  1197064 as the SerialDesiredMemory, the RequiredMemory, the RequestedMemory.

That's a big grant for something that operates on 0 rows.  When creating many CCIs concurrently, that's a problem - queries can timeout.

Might have to use Resource Governor creatively to allow high concurrency CCI creation.

Grant request is smaller if only one column in the CCI.  So I guess could create a smaller CCI and add columns.  That's also kinda painful.  I'll ask on #sqlhelp, see if anyone has something clever...

*****
So far the only things to come up on Twitter were the min_grant_percent and max_grant_percent query hints introduced in SQL Server 2012 SP3.  But...

"Note These two new query memory grant options aren't available for index creation or rebuild."

https://support.microsoft.com/en-us/help/3107401/new-query-memory-grant-options-are-available-min-grant-percent-and-max-grant-percent-in-sql-server-2012

Later I found this while looking around.

"Starting with SQL Server 2016, you can create the table as a clustered columnstore index. It is no longer necessary to first create a rowstore table and then convert it to a clustered columnstore index."

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-columnstore-index-transact-sql

Hmmm. Here's a great post from Pedro Lopes (Twitter @sqlpto), which includes some details for using Extended Events in memory grant investigation.

https://blogs.msdn.microsoft.com/sql_server_team/memory-grant-related-diagnostics/

So far, it looks like creating the clustered columnstore inline with table creation, as below, avoids a memory grant.  (That makes sense.)  Whereas creating a clustered columnstore index on an empty rowstore table requires a memory grant - at the same size as up to 1 million rows for the table! (That's a surprise.)

If you'll be creating CCIs at fairly high concurrency, you may also want to consider creating the CCI inline with table create, as below, in order to avoid a potential bottleneck in memory grants.

CREATE TABLE dbo.MyFact 
( [Key] bigint NOT NULL, 
[Type] nvarchar(50) NOT NULL, 
Id nvarchar(50) NOT NULL, 
AltKey_1 bigint NOT NULL DEFAULT -1, 
AltKey_2 bigint NOT NULL DEFAULT -1, 
AltKey_3 bigint NOT NULL DEFAULT -1, 
AltKey_4 bigint NOT NULL DEFAULT -1, 
AltKey_5 bigint NOT NULL DEFAULT -1, 
AltKey_6 bigint NOT NULL DEFAULT -1, 
AltKey_7 bigint NOT NULL DEFAULT -1, 
AltKey_8 bigint NOT NULL DEFAULT -1, 
AltKey_9 bigint NOT NULL DEFAULT -1, 
AltKey_10 bigint NOT NULL DEFAULT -1, 
String_1 nvarchar(300) NOT NULL DEFAULT N'*Default', 
Numeric_1 numeric(18,2) NULL, 
Date_1 date NULL,

TimeValue time NULL, 
Int_1 int NULL, 
String_2 nvarchar(300) NOT NULL DEFAULT N'*Default', 
Tiny_1 tinyint NULL DEFAULT 0, 
Tiny_2 tinyint NULL DEFAULT 0, 
Tiny_3 tinyint NULL, 
Tiny_4 tinyint NULL, 
Tiny_5 tinyint NULL, 
Tiny_6 tinyint NOT NULL DEFAULT 1,
INDEX CCI_MyFact CLUSTERED COLUMNSTORE ) 

#SQLServer: How much of that free memory came from checkdb completing and releasing its memory grant? And what about tempdb?

Arun Sirpal (@blobeater1 on Twitter) asked a great question today (11 April 2017) about the impact of memory grant release on free memory after checkdb completes.

I love questions!  It means someone was really paying attention!

Here's the relevant blog post about the appearance of a lot of free memory when a checkdb (without tablock) completes.

Where'd all this #SQLServer Free Memory come from??!?
http://sql-sasquatch.blogspot.com/2017/04/whered-all-this-sqlserver-free-memory.html

So - what contribution to free memory on completion of checkdb is made by releasing the memory grant?  YMMV, but for my 3.35 TB database... almost none.  Here's the thing: checkdb doesn't get a single memory grant, rather at multiple times throughout its execution it gets memory grants.  And with each of those memory grants, like other queries, it may or may not use the entire memory grant.  So, the contribution of releasing the memory grant to free memory is dependent on how much used memory grant checkdb releases on completion.  That's kind of confusing to describe, maybe some graphs will help!

This is the same system, same checkdb as previous post.  The CPU utilization in blue clearly delineates when checkdb was active, since it was run in isolation.

As checkdb progressed, it requested varying memory grants - granted memory in the dark brown/orange below.  In order for this not to be too long of a blog post, I layered "reserved memory" in a lighter orange in front of granted memory.  So - the exposed dark brown granted memory is the workspace memory at any given time that is actually used.  Recall that when memory is granted its just a promise - the query "steals" memory against the grant over time as needed. 

 

So if we subtract reserved from granted... we'll be left with query memory used by checkdb.  Here's what that looks like.

 

This is fun!  Let's keep going! I said that over time, as the query needs more of its grant for sort/hash, it "steals" against the grant.  Hmmm... am I implying that used query memory is a subset of "stolen memory", and that only used query memory (excluding reserved memory from the grant) is within "stolen memory"?  Yessss!!  Yes, I am.

Check out used query memory layered in front of stolen memory in the graph below.  Stolen memory is greater than used query memory - but by a relatively fixed amount, and the greater stolen memory is during this isolated checkdb, the greater the share used query memory is.

Interesting, no?  Here's the relevance to the initial question: look how small the used query memory is as checkdb nears completion and CPU utilization drops to near zero.  Its pretty doggone low.  That was the contribution to free memory when the last grant of checkdb was released.  Not much.  But, as I said, YMMV.  Maybe the allocation order of HoBTs in your database is different, and a peak of used query memory like that between 20:50 and 21:50 occurs right before completion!  In that case, releasing the grant would lead to a pretty big contribution to free memory.

OK.  Now for a lagniappe :-)

See that dip in CPU utilization at 16:50, and at 17:50?  Let's tie that in to checkdb work via tempdb.

In perfmon, we've got "SQLServer:Databases(tempdb)\Data File(s) Size (KB)" and "SQLServer:Transactions\Free Space in tempdb (KB)". Let's subtract them to get tempdb used!

And - here's my trick that I think you'll see first from me: let's stack tempdb used on top of query memory used!!  Conceptually, its easiest to understand that when a single session is running in isolation, and when its a session that isn't explicitly using temp tables, row versioning, etc.  In that case, tempdb use comes from spill: sort/hash has consumed the memory grant, so tempdb is leveraged to complete remaining sort/hash work.

Here's what that looks like.  How nice for me; it just happened to work out that there were nice peaks in tempdb use at 16:50 and 17:50 - the same times that CPU utilization dipped.  So tempdb-related waits are prime suspects. :-)

 Ciao for now!!