sql-servert-sqlstored-procedurestempdb

When a variable is declared in a T-SQL stored procedure, is it kept in memory or tempdb?


We're trying to optimize some of our T-SQL stored procedures to reduce tempdb contention, but I can't figure out how non-table variables are stored by SQL server:

  • What about simple data types like INT and DATETIME? It feels like they'd live in memory.
  • What about VARCHARs/VARCHAR(MAX)? A regular VARCHAR could live in memory, but a VARCHAR(MAX) might need to use tempdb for its storage.
  • Table variables are stored in tempdb. These I'm not really interested in though.

The MSDN article on tempdb doesn't explain regular variables.


Solution

  • The Capacity Planning article for tempdb answers your questions:

    The large object data types are varchar(max), nvarchar(max), varbinary(max) text, ntext, image, and xml. These types can be up to 2 GB in size and can be used as variables or parameters in stored procedures, user-defined functions, batches, or queries. Parameters and variables that are defined as a LOB data type use main memory as storage if the values are small. However, large values are stored in tempdb. When LOB variables and parameters are stored in tempdb, they are treated as internal objects. You can query the sys.dm_db_session_space_usage dynamic management view to report the pages allocated to internal objects for a given session.

    The article is worth reading in its entirety, because it also covers a lot of the other uses for tempdb.

    EDIT: If you're curious how much memory in tempdb a specific session is using, you can run the following query:

    select * 
    from sys.dm_db_session_space_usage 
    where session_id = @@SPID
    

    Using this, it didn't look like my VARCHAR(MAX) variable was stored in tempdb until it reached around 1000 KB in size... but I'm sure that varies based on the memory that your server has available.