We have recreate our 4TB clustered index in ouor SQL Server. Even thought we have 1.5TB of memory we still run into the following error message:
"Msg 802, Level 17, State 2, Line 30 There is insufficient memory available in the buffer pool"
Nothing else was done on the SQL Server as it is dedicated to this database, which was run in Single User mode.
Does anyone has a good idea, what might have filled up the buffer pool - as far as I know, a recreated index will not be kept in memory, but rather on disk.
Some further information:
max degree of parallism: 4
MaxMemory: a little below 1,5TB
Lock Pages in Memory is been activated
the index was created with drop existing and MAXDOP-Option set to 1
The purpose was to stop a wild running UNIQUIFIER, which almost reached 2^31-1 due to a limited number of values for the clustered key (0,1,2,3,4,5,6,7,8 and 9 to be precise) - thus the clustered index key was simply on the partitionKey TINYINT and the internal column UNIQUIFIER.
To repeat a question: Who has some clue as to what might has filled up the buffer pool? Or does an index indeed store the complete recreate in the buffer pool?
One idea was, the LPiM-setting might has caused SQL Server not to page at all - I haven't evaluated that idea yet.#
Here the database schema:
CREATE PARTITION SCHEME [PCBScheme] AS PARTITION [PBCKey] TO ( [P0], [P1], [P2], [P3], [P4], [P5], [P6], [P7], [P8], [P9], [PRIMARY] ) -- each Partition holds approximatly 177 million entries, PRIMMARY is empty and doesn't really belongs in here CREATE PARTITION FUNCTION [PBCKey](TINYINT) AS RANGE LEFT FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9); CREATE TABLE [dbo].[Table]( [TableID] BIGINT NOT NULL IDENTITY(1,1) , [Other_Columns_1] INT NULL , [Other_Columns_2] DATETIME2(7) NULL , [Other_Columns_3] BINARY(32) NULL , [Other_Columns_4] VARBINARY(max) NULL -- regularly 1,5KB in size , [...] , [PartitionKey] AS (CONVERT(TINYINT,[TableID]%(10),0)) PERSISTED ) ON [PCBScheme] ([PartitionKey]); CREATE CLUSTERED INDEX [CL_Table__PartitionKey] ON [dbo].[Table] ( [PartitionKey] -- This is a dumb key to use for a clustered, when the values are only 0-9 ) WITH ( FILLFACTOR = 100 , ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON , PAD_INDEX = OFF , DROP_EXISTING = OFF , IGNORE_DUP_KEY = OFF ) ON [PCBScheme] ([PartitionKey]); -- this is the CL as it is in Prod right now
And here is our recreate-command:
CREATE CLUSTERED INDEX [CL_Table__PartitionKey] ON [dbo].[Table] ( [PartitionKey] ) WITH ( FILLFACTOR = 100 , ALLOW_PAGE_LOCKS = ON , ALLOW_ROW_LOCKS = ON , PAD_INDEX = OFF , SORT_IN_TEMPDB = OFF -- our TempDB isn't big enough to support this (limited due to disk size) , DROP_EXISTING = ON , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF -- we will set this to ON next time , ONLINE = OFF -- OFF is our only option due to the other columsn in the table , MAXDOP = 1 -- Default is 4, we will try either that or 20 or 19, which will be cores/NUMA node or one less ) ON [PCBScheme] ([PartitionKey]);