9

When I use a variable of a large object (LOB) data type in SQL Server, is the whole thing kept in memory at all times? Even if it is 2GB in size?

1 Answer 1

15

The 2GB LOB limit only applies to persistent storage; variables have no limit.

SQL Server starts with an pure in-memory implementation.

It changes to a tempdb-based backup scheme if the size of the LOB data grows beyond 512KB (524,288 bytes).

The backup scheme stores LOB data in a tree of internal tempdb LOB pages.

These tempdb pages are cached in the buffer pool as usual, but may be flushed to disk by the lazy writer as needed.

There are extra overheads in using the tempdb-backed scheme even if all pages remain in memory.

When the variable goes out of scope, the pages are synchronously deallocated. This can take time for very large LOBs.

DECLARE 
    -- 512KB: In-memory
    @V1 varchar(max) = REPLICATE(CONVERT(varchar(max), 'X'), 524288),
    -- 512KB + 1: Buffered, tempdb-backed
    @V2 varchar(max) = REPLICATE(CONVERT(varchar(max), 'X'), 524289);

The limit is in bytes, so the number of characters stored depends on the encoding.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.