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