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

Question

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.

Answer 1

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.

Answer 2

"Table variables are stored in tempdb. These I'm not really interested in though."

In general yes, table variables are stored in tempdb, but this could be changed with memory-optimized table variables.

Faster temp table and table variable by using memory optimization

D. Scenario: Table variable can be MEMORY_OPTIMIZED=ON

A traditional table variable represents a table in the tempdb database. For much faster performance you can memory-optimize your table variable.

The inline syntax does not support memory-optimization. So let us convert the inline syntax to the explicit syntax for the TYPE.

CREATE TYPE dbo.typeTableD AS TABLE  
(  
    Column1  INT   NOT NULL INDEX ix1,  
    Column2  CHAR(10)  
) WITH (MEMORY_OPTIMIZED = ON);

DECLARE @tvTableD dbo.typeTableD;
INSERT INTO @tvTableD (Column1) values (1), (2);  

SELECT * FROM @tbTableD;

A memory-optimized table variable does not reside in tempdb. Memory-optimization results in speed increases that are often 10 times faster or more.