Когда переменная объявляется в хранимой процедуре T-SQL, хранится ли она в памяти или в базе данных tempdb?

Мы пытаемся оптимизировать некоторые из наших хранимых процедур T-SQL, чтобы уменьшить конкуренцию за tempdb, но я не могу понять, как нетабличные переменные хранятся на сервере SQL:

  • Как насчет простых типов данных, таких как INT и DATETIME? Такое ощущение, что они будут жить в памяти.
  • А как насчет VARCHAR/VARCHAR(MAX)? Обычный VARCHAR может храниться в памяти, но VARCHAR(MAX) может потребовать использовать tempdb для хранения.
  • Табличные переменные хранятся в tempdb. Хотя эти меня не особо интересуют.

В статье MSDN о tempdb не объясняются обычные переменные.


sql-server stored-procedures tsql tempdb
person Evan M    schedule 15.03.2012    source источник

Ответы (2)


arrow_upward
11
arrow_downward

Статья о планировании емкости для tempdb отвечает на ваши вопросы:

Типы данных больших объектов: varchar(max), nvarchar(max), varbinary(max) text, ntext, image и xml. Эти типы могут иметь размер до 2 ГБ и могут использоваться в качестве переменных или параметров в хранимых процедурах, определяемых пользователем функциях, пакетах или запросах. Параметры и переменные, определенные как тип данных LOB, используют оперативную память в качестве хранилища, если значения малы. Однако большие значения хранятся в базе данных tempdb. Когда переменные и параметры больших объектов хранятся в базе данных tempdb, они рассматриваются как внутренние объекты. Вы можете запросить представление динамического управления sys.dm_db_session_space_usage, чтобы сообщить о страницах, выделенных внутренним объектам для данного сеанса.

Статью стоит прочитать полностью, потому что она также охватывает множество других способов использования tempdb.

РЕДАКТИРОВАТЬ: Если вам интересно, сколько памяти в базе данных tempdb использует конкретный сеанс, вы можете выполнить следующий запрос:

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

Используя это, не было похоже, что моя переменная VARCHAR(MAX) хранилась в tempdb, пока ее размер не достиг около 1000 КБ ... но я уверен, что это зависит от памяти, доступной на вашем сервере.

person Michael Fredrickson    schedule 15.03.2012
comment
Спасибо, что нашли это! Я предполагаю, что если значения малы, это означает менее 8000 байт? Несмотря на это, в статье, похоже, подразумевается, что не-MAX (N) VARCHAR и небольшие переменные хранятся в памяти. - person Evan M; 16.03.2012

arrow_upward
2
arrow_downward

Переменные таблицы хранятся в базе данных tempdb. Хотя они меня не особо интересуют.

В целом да, табличные переменные хранятся в базе данных tempdb, но это можно изменить с помощью табличных переменных, оптимизированных для памяти.

Ускорение временной таблицы и табличной переменной за счет оптимизации памяти

D. Сценарий: переменная таблицы может быть MEMORY_OPTIMIZED=ON

Традиционная табличная переменная представляет собой таблицу в базе данных tempdb. Для гораздо более высокой производительности вы можете оптимизировать свою табличную переменную для памяти.

Встроенный синтаксис не поддерживает оптимизацию памяти. Итак, давайте преобразуем встроенный синтаксис в явный синтаксис для 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;

Табличная переменная, оптимизированная для памяти, не находится в базе данных tempdb. Оптимизация памяти приводит к увеличению скорости в 10 и более раз.

person Lukasz Szozda    schedule 30.03.2019