Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Табличные переменные и потребление памяти  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Возник вопрос в котором я не могу получить однозначного ответа. Когда создается табличная переменная, данные которые я в нее записываю (при условии того что памяти достаточно) хранятся в tempdb или сразу попадают в BufferPool (т.е. в tempdb создается только пустая структура).

Вот этот пример показывает, что данные физически записываются в tempdb:

DECLARE @t TABLE(i INT)
INSERT INTO @t
VALUES (1), (2)
SELECT sys.fn_PhysLocFormatter(%%physloc%%)
FROM @t

Я прав в своих рассуждения? Или все же нет. Проверял на SQL Server 2012 (особенности InMemory не брались во внимание).

И еще вопрос. При создании табличной переменной память для нее освобождается после того как она выходит из области видимости?

Справка утверждает, что да:

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

В каких еще случаях память может освобождаться (если таблица не вышла из области видимости)? Например, в такой ситуации:

DECLARE @t TABLE(i INT)
INSERT INTO @t
VALUES (1), (2)

DELETE FROM @t --- насколько я понимаю, что нет

....
JOIN @t ...
GO

Всем заранее спасибо за конструктивный пинок в правильном направлении.
10 мар 16, 19:31    [18917089]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
AlanDenton
Когда создается табличная переменная, данные которые я в нее записываю (при условии того что памяти достаточно) хранятся в tempdb или сразу попадают в BufferPool
Физически обычная табличная переменная - таблица, расположенная в tempdb. То, что это какая-то особая структура, расположенная в памяти - миф.
AlanDenton
В каких еще случаях память может освобождаться
О какой памяти речь? Ваша цитата не относится к обычным табличным переменным.
10 мар 16, 20:36    [18917358]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
invm
О какой памяти речь? Ваша цитата не относится к обычным табличным переменным.

Имелось ввиду данная ситуация:

DECLARE @t TABLE(i VARCHAR(8000))
INSERT INTO @t
SELECT TOP(5) REPLICATE('=', 8000)
FROM sys.columns

SELECT a.data_pages -- 5
FROM tempdb.sys.allocation_units a
JOIN tempdb.sys.partitions p ON p.partition_id = a.container_id
JOIN tempdb.sys.columns c ON p.[object_id] = c.[object_id]
	AND c.name = 'i'

DELETE FROM @t -- #1

SELECT a.data_pages -- 0
FROM tempdb.sys.allocation_units a
JOIN tempdb.sys.partitions p ON p.partition_id = a.container_id
JOIN tempdb.sys.columns c ON p.[object_id] = c.[object_id]
	AND c.name = 'i'

GO
-- #2

Понятно, что когда табличная переменная выходит за область видимости (#2) она удаляется из tempdb и освобождаются страницы в буфер-пуле которые она занимала. Но вопрос в другом - будут ли освобождены из буфер-пула страницы @t когда из нее удаляются все записи или их часть (#1)? Насколько я помню - нет.
10 мар 16, 22:12    [18917619]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
AlanDenton,

Буфер-пулу должно быть сугубо фиолетово что там у вас, табличная переменная, временная таблица или же обычная таблица. Он работает со страницами. Если страницей кто-то часто пользуется, она будет хранится в буфере, если страница не нужна и потребовалось память, она будет вытеснена, если страница "грязная", она будет сохранена на диск.
10 мар 16, 23:02    [18917766]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
Mind,

А что сохранять на диск доя табличных переменных, если их сразу и очистить? Они же еще и не журналируются
10 мар 16, 23:12    [18917802]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Mike_za
Они же еще и не журналируются
Распространенное заблуждение. Очень даже журналируются.
use tempdb;
go

declare @t table (id int);
declare @lsn nvarchar(30), @tid nvarchar(30);

select top (1) @lsn = [Current LSN] from sys.fn_dblog(null, null) order by [Current LSN] desc;

insert into @t select top (1) object_id from sys.objects;

select
 *
from
 sys.fn_dblog(null, null)
where
 [Transaction ID] = (select top (1) [Transaction ID] from sys.fn_dblog(null, null) where [Transaction Name] = N'TVQuery' and SPID = @@spid and [Current LSN] >= @lsn);
11 мар 16, 00:10    [18917953]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
invm,

Ээээ. А зачем их журналировать, если они не откатываются?
11 мар 16, 10:07    [18918572]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
Mike_za,

да наверное движок один, отдельную ветку было лень писать
а в чем разница журналируется там чего или нет?
деньги посчитали и сделали оптимально
11 мар 16, 10:37    [18918718]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Mind
Если страницей кто-то часто пользуется, она будет хранится в буфере, если страница не нужна и потребовалось память, она будет вытеснена, если страница "грязная", она будет сохранена на диск.

Это мне понятно. Вопрос немного в другом. Интересно, освобождаются ли страницы в BufferPool если из табличной переменной удаляются все данные (пример выше с #1)? Справка и здравый смысл говорит, что нет. Но хотелось бы узнать у коллег это точно. Возможно я не прав.
11 мар 16, 10:50    [18918784]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Mike_za
Ээээ. А зачем их журналировать, если они не откатываются?
И подтверждаются и откатываются.
Модификация данных в табличной переменной осуществляется в отдельной именованной (TVQuery) системной транзакции в рамках текущей сессии. Для простоты можете считать это "автономной транзакцией".
11 мар 16, 11:24    [18918957]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
AlanDenton
Интересно, освобождаются ли страницы в BufferPool если из табличной переменной удаляются все данные
Не понятно, что вы имеете в виду под "освобождаются".
11 мар 16, 11:29    [18918976]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
invm
AlanDenton
Интересно, освобождаются ли страницы в BufferPool если из табличной переменной удаляются все данные
Не понятно, что вы имеете в виду под "освобождаются".


Вангую - "Становятся доступными для других объектов".
11 мар 16, 16:26    [18920730]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
invm
Не понятно, что вы имеете в виду под "освобождаются".

Чисто гипотетическая ситуация:
BEGIN
	
    DECLARE @t TABLE ...
    INSERT INTO @t
    ... -- 100Mb

    --- что-то делали с @t

    DELETE FROM @t

    -- послу удаления записей из @t последующие конструкции (или другие нуждающие в памяти) могут
    -- использовать те 100Mb которую занимала ранее @t ?

END
14 мар 16, 18:09    [18929967]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
AlanDenton,

Для временных таблиц и табличных переменных занятые страницы будут "освобождены" сразу, для постоянных таблиц - нет, за исключением спецслучая для куч.
14 мар 16, 19:55    [18930405]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
o-o
Guest
У вас какая-то неукладка в голове в смысле как кому память выделяют и "очищают".
Ну не очищают ее никак, какие надо страницы в память поднять, поднимают, перезаписывая то, что там было. Вот у нас пара баз в терабайт и в 2Тб. Одна процедура переливает из таблицы в 400Гб, вторая из таблицы другой базы, тоже хочет перелить немало, 100 гиг. А памяти у сервера 48Гиг, под buffer pool всего 35гиг.эти 35 все время поделены между первой, второй базой и темпдб, ну иногда еще кто-то вклинивается. Никто ничего за собой не "чистит", один процесс нужные ему страницы грузит, второй нужные ему. На место одних другие приходят. Все.
И если даже всего из одной таблицы одной базы данные. Вытесняют ранее прочитанное, но нельзя это назвать "очисткой"

Теперь про табличные переменные.
Это те же таблицы в темпдб, как вам уже писали.
Но может вы спрашиваете, вот бэтч завершился, эти же страницы внутри самой темпдб будут переиспользоваться немедленно? Да, будут.
Я сейчас с телефона и код набирать убьюсь. Могу картинку прицепить. Смысл: объявляю табличную переменную. В нее записываю 10 страниц данных, тут же опрашиваю номера этих страниц.
Затем идет второй бэтч, там похожая табличная переменная, но влазят 4 записи на страницу. Кладу в нее 10 записей. Это всего 2 с половиной страницы. Но что видн: страница 120 снова задействована, т.е.она больше не числится за первой табличной переменной.

Теперь про то, что тут еще спрашивали, зачем сразу на диск писать. Ну, раз переменная это таблица, под нее надо место на диске в темпдб, как и под все обычные таблицы во всех базах. Точно так же и выделяются страницы. Точно так же, если нет места в темпдб, а вы наполняете таб. переменную мегабайтами, вывалится ошибка, что темпдб переполнено (выставьте темпдб фикс. размер в 8Мб и начните заполнять переменную @t, оч быстро бэтч загнется с msg 1105 Could not allocate space for object 'dbo. @t' in database 'tempdb' because the 'primary' filegroup is full)

Раньше до 2014 все балки в темповые таблицы немедленно сбрасывал на диск eager writer, с 2014ого начиная ослабили эту борьбу с зафлуживанием buffer pool страницами темпдб, я если успею, картинкой цитату приложу или потом уже из дома текстом, но позже

К сообщению приложен файл. Размер - 49Kb
14 мар 16, 20:54    [18930637]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
AlanDenton,

Да даже до DELETE FROM @t и даже во время INSERT INTO @t, кто угодно может использовать эту память. Потому что память эта, никак к сессии создавшей переменную не привязана (в отличе скажем от Workspace Memory), и ником образом монопольно не зарезервирована. Это память под буфер пул и он сам решает когда и что закэшировать. Сессия может еще работать с этой переменной, но если память понадобилась кому то другому, то она будет освобождена. Или сессия уже давно прибита вместе с таблицей, но памяти на сервера валом, поэтому все данные переменной будут "жить" в памяти, пока эта память не понадобится кому то другому.
14 мар 16, 21:13    [18930718]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
o-o
Guest
picture

К сообщению приложен файл. Размер - 136Kb
14 мар 16, 22:41    [18931104]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
o-o,

Название книжки?
14 мар 16, 23:48    [18931250]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
o-o
Guest
вот книга и нужный кусок:
T-SQL Querying By Itzik Ben-Gan, Adam Machanic, Dejan Sarka, Kevin Farlee
15 мар 16, 00:02    [18931286]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
A table variable is not a memory-only structure. 
Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data.
Table variables are created in the tempdb database similar to temporary tables.
If memory is available, both table variables and temporary tables are created
and processed while in memory (data cache).

https://support.microsoft.com/en-us/kb/305977
15 мар 16, 08:54    [18931665]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
Mike_za
Member

Откуда: Москва
Сообщений: 1176
o-o,

Спасибо
15 мар 16, 10:08    [18931883]     Ответить | Цитировать Сообщить модератору
 Re: Табличные переменные и потребление памяти  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Хотел сказать всем большое спасибо за помощь! В особенности invm и o-o
15 мар 16, 11:43    [18932508]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить