在 T-SQL 存储过程中声明变量时，它是保存在内存中还是 tempdb 中? [英] When a variable is declared in a T-SQL stored procedure, is it kept in memory or tempdb?

问题描述

我们正在尝试优化我们的一些 T-SQL 存储过程以减少 tempdb 争用，但我无法弄清楚 SQL 服务器如何存储非表变量:

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:

• 像 INT 和 DATETIME 这样的简单数据类型呢?感觉就像他们活在记忆中一样.
• VARCHARs/VARCHAR(MAX) 怎么样?常规 VARCHAR 可以存在于内存中，但 VARCHAR(MAX) 可能需要使用 tempdb 进行存储.
• 表变量存储在 tempdb 中.不过，我对这些并不感兴趣.

关于 tempdb 的 MSDN 文章 没有解释常规变量.

推荐答案

容量tempdb 的规划文章回答了您的问题:

大对象数据类型有varchar(max)、nvarchar(max)、varbinary(max) 文本、ntext、图像和 xml.这些类型最多可以有 2 个大小为 GB，可用作存储中的变量或参数过程、用户定义的函数、批处理或查询.参数定义为 LOB 数据类型的变量使用主内存作为如果值很小，则存储.但是，较大的值存储在临时数据库.当 LOB 变量和参数存储在 tempdb 中时，它们被视为内部对象.您可以查询sys.dm_db_session_space_usage 动态管理视图报告分配给给定会话的内部对象的页面.

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.

这篇文章值得一读，因为它还涵盖了 tempdb 的许多其他用途.

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

如果您想知道特定会话在 tempdb 中使用了多少内存，您可以运行以下查询:

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

使用这个，它看起来不像我的 VARCHAR(MAX) 变量存储在 tempdb 中，直到它的大小达到 1000 KB 左右......您的服务器可用的内存.

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.

这篇关于在 T-SQL 存储过程中声明变量时，它是保存在内存中还是 tempdb 中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！