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:

    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

    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]);

    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:

    ON [dbo].[Table] (
    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]);
  • What are your MIN/MAX memory settings for the SQL Server? And how much memory is available to Windows and other processes (SSIS, etc) that also need memory? And index rebuilds may use tempdb space, which is also limited, so how much tempdb space do you have and how big is the index?
    – RLF
    Commented Oct 20, 2015 at 1:00
  • I have added our schema above in my initial question. About your other questions: Commented Oct 20, 2015 at 8:53
  • I have added our schema above in my initial question. About your other questions: MinMemory=0 MaxMemory=1TB (it was supposed to be 1.5TB-50GB) phy. Memory= 1.5TB The available space for TempDB is 700GB - thought in a worst case scenario, we could crete a TempDB-data file on the data file-disk (after shrinking the DB, we would have 7TB of free disk space there as well) Commented Oct 20, 2015 at 9:01
  • I don't think this has anything to do with DROP_EXISTING. This settings does not change anything about what the index build does physically. The strange thing about this message is that SQL Server can run almost any query with limited memory (such as 1GB).
    – usr
    Commented Oct 20, 2015 at 11:12
  • Btw, it might be a good idea to build into a fresh filegroup. Otherwise you will have 2x temporary space usage in the existing FG and end up with a 4TB space at the beginning of the file where the old index was.
    – usr
    Commented Oct 20, 2015 at 11:13


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.