Friday, April 21, 2017

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."

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




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 ) 

No comments:

Post a Comment