Работа с TempDB-таблицами

[sukhanchik]

*** Выделено изQuery::Insert_recordset() - выборка из временной таблицы ***

Цитата:

Сообщение от ax_mct

Я бы избегал использовать insert_recordset, а также join c временными таблицами, если только не стоит отдельной задачи оптимизации.

У меня к примеру RecId в InMemory таблице в версии 10.0.16 в какой-то момент какая-то корова просто слизывает. Было к примеру 7456, а потом 0.

TempDB-таблицы себя замечательно ведут и замечательно джойнятся. А уж тот факт, что RecId там является счетчиком, который управляется СУБД - вообще достойно уважения. Так что джойны с TempDB-шными таблицами очень даже хороши. Особенно, когда нужно отфильтровать по списку RecId (RecordReferenceList тоже работает - но его потом нужно чистить)

[ax_mct]

Цитата:

Сообщение от sukhanchik

TempDB-таблицы себя замечательно ведут и замечательно джойнятся. А уж тот факт, что RecId там является счетчиком, который управляется СУБД - вообще достойно уважения. Так что джойны с TempDB-шными таблицами очень даже хороши. Особенно, когда нужно отфильтровать по списку RecId (RecordReferenceList тоже работает - но его потом нужно чистить)

Один черт страшно.
Вот как там в Prod Azure DB TempDB чистится? Черт его знает.

[sukhanchik]

Цитата:

Сообщение от ax_mct

Один черт страшно.
Вот как там в Prod Azure DB TempDB чистится? Черт его знает.

Там принцип простой. Берется таблица, структура которой определена в АОТ и при первом обращении - создается таблица в TempDB под названием tXXXXXX_YYY..YYYY, где XXXXX Х - это TableId таблички, а YYY..YYYY - некоторый перечень букв, который формируется случайным образом при первом вызове select из кода. Повторное обращение в Х++ к этой же таблице другой переменной (через select) без использования метода linkToPhysicalInstance - создает вторую такую же таблицу, где первая часть tXXXXXX_ - такая же, а YYY..YYYY - уже другая.

Старый объект удаляется, как только он становится не нужным. Поэтому таблица как таковая не чистится - она просто становится мусором. И через некоторое время то место, на котором она находилась в файле базы TempDB - перезаписывается данными другой временной таблицы.
Поэтому тут никаких чисток нет, принцип един и общий и какой-то мегаособенности работы SQL Server с TempDB в Azure (по сравнению с локальной версией) нет.

[trud]

Цитата:

Сообщение от sukhanchik

Старый объект удаляется, как только он становится не нужным. Поэтому таблица как таковая не чистится - она просто становится мусором. И через некоторое время то место, на котором она находилась в файле базы TempDB - перезаписывается данными другой временной таблицы.

Вот это кстати интерестный момент. А кто ее удаляет? На одном из клиентов наблюдал что таблицы были в большом кол-ве в tempdb

[Raven Melancholic]

Цитата:

Сообщение от ax_mct

Я бы избегал использовать insert_recordset, а также join c временными таблицами, если только не стоит отдельной задачи оптимизации.

Так с InMemory временными таблицами (а до DAX2012 просто с временными таблицами) никакой оптимизации и не будет в массовых обработчиках. Только может быть более лаконичный синтаксис, а обработка всё равно будет по каждой записи.

[sukhanchik]

Цитата:

Сообщение от trud

Вот это кстати интерестный момент. А кто ее удаляет? На одном из клиентов наблюдал что таблицы были в большом кол-ве в tempdb

https://docs.microsoft.com/en-us/sql...empdb-database

Цитата:

SQL Server PDW drops tables from tempdb when:

• The DROP TABLE statement is executed.
  
• A session is disconnected. Only temporary tables for the session are dropped.
  
• The appliance is shutdown.
  
• The Control node has a cluster failover.

Большое количество временных таблиц - это не показатель. Там же на каждый чих используется временная таблица. Ну и не стоит забывать, что перезагрузка SQL Server приводит к пересозданию всей базы tempDB

Ну и самый простой способ проверить. Берем табличку в АХ, которая является временной в tempDB (например, TmpRecIdFilter). Пишем джобик на Х++ (класс в D365), где делаем select из этой таблицы, после чего получаем в локальную переменную значение метода cursor.getPhysicalTableName() - это и будет настоящее имя таблицы. Останавливаем исполнение кода в отладчике (чтобы сессия не закрылась), идем в SQL Manamegent Studio и делаем запрос select * from tempDB..[то имя, которое мы получили из метода getPhysicalTableName()].

После завершения работы джобика (если мы отпустим отладчик) этот запрос уже невозможно будет выполнить (будет ошибка)

[fed]

Цитата:

Сообщение от sukhanchik

Ну и самый простой способ проверить. Берем табличку в АХ, которая является временной в tempDB (например, TmpRecIdFilter). Пишем джобик на Х++ (класс в D365), где делаем select из этой таблицы, после чего получаем в локальную переменную значение метода cursor.getPhysicalTableName() - это и будет настоящее имя таблицы. Останавливаем исполнение кода в отладчике (чтобы сессия не закрылась), идем в SQL Manamegent Studio и делаем запрос select * from tempDB..[то имя, которое мы получили из метода getPhysicalTableName()].

После завершения работы джобика (если мы отпустим отладчик) этот запрос уже невозможно будет выполнить (будет ошибка)

С другой стороны, я неоднократно наблюдал такую картину: После скажем месяца аптайма останавливаем AOSы и оно как-то очень медленно останавливается. Если в этот момет понаблюдать за происходящим на сервере БД, то можно обнаружить что туда сотнями идут запросы вида Drop table tempdb.tXXXXXX_YYY..YYYY
Фраза "A session is disconnected. Only temporary tables for the session are dropped." относиться, в моем понимании, только к временным таблицам самого SQL Server, имена которых, по правилам, должны начинаться с символов # или ##. А Аксаптовские tempDb таблицы, с точки зрения SQL Server, это самые обычные таблицы, вполне постоянные, только они храняться в tempDB и рестарта сервера БД не переживают. Я подозреваю, что как только таблица в Аксапте перемещается между SQL сессиями (например - после заполнения в серверном методе начинает использоваться как data source в форме; а все формы для броузинга таблиц используют одну shared-сессию), весь механизм автоматического удаления таблицы отключается и она (возможно после удаления из нее смысловых данных) болтается в БД до остановки AOS. Возможно микрософт просто не смог сделать нормального сборщика мусора для временных таблиц и часть из них удаляет только при останове сервера...

Кстати - примерно полтора-два года назад, микрософт сделал новую фичу в D365FO, которая заставляет его в случае использования Azure SQL хранить tempDB таблицы в основной базе данных. И по моему эта фича уже год как включена и после экспорта данных из Tier2-instance можно увидеть в основной БД какое-то количество таблиц с "временными" именами.

[Logger]

Цитата:

Сообщение от fed

С другой стороны, я неоднократно наблюдал такую картину: После скажем месяца аптайма останавливаем AOSы и оно как-то очень медленно останавливается. Если в этот момет понаблюдать за происходящим на сервере БД, то можно обнаружить что туда сотнями идут запросы вида Drop table tempdb.tXXXXXX_YYY..YYYY

Да, точно так.
Более того - если аос падает или его пристрелили не дожидаясь пока он сам остановится, то множество этих табличек во временной базе так и остаются пока не рестартуют SQL целиком.

У меня коллега заморочился этим вопросом, даже написал скрипт по вычищению этих табличек.

[Raven Melancholic]

А из участников обсуждения есть модераторы?
Может стоит перенести обсуждение особенностей работы Аксы с TempDB в отдельную ветку?
Ну чтобы впоследствии, если кто будет искать именно работу с Query::Insert_recordset, получали более сконцентрированную информацию, а кто искал бы особенности TempDB получил своё.

[sukhanchik]

Цитата:

Сообщение от fed

С другой стороны, я неоднократно наблюдал такую картину

Спасибо. Я не обращал на это внимание. Буду знать. Тогда прошу прощения за мое неверное понимание работы механизма

[ax_mct]

Цитата:

Сообщение от fed

Единственная реальная проблема с временными таблицами tempDB(а на самом деле - это ОБЫЧНЫЕ таблицы, просто система их автоматически удаляет) - это возможность засорить sql statement cache. То есть - если у тебя в D365FO стоит такой вот примерно код:
for(i=0;i<10000;i++)
{
myFavoritetempDbTable myTable;
//do something with the table
}
то велика вероятность того, что система создаст 10000 одинаковых временных таблиц с разными именами, потом отправит 10000 разных запросов на вставку и выборку и тп. В результате кэш сиквела затрешиться.
Но от такого антипаттерна легко защититься, добавив еще одно поле для хранения i во временную таблицу, создать только один экземпляр этой временной таблицы и просто во все запросы добавить условие myTable.fieldI == i

А InMemory временные таблицы изначально были достаточно бесполезной штукой. Запросы с их участием обрабатывались на AOS (То есть - из запроса исключалась временная таблица, остатки запроса отправлялись на SQL, оттуда приходил результат - зачастую сотни мегабайт, результат записывался куда-то на AOSовский диск и потом это все медленно и печально джойнилось с inMemory table где-то на дисках AOS). Если просто быстродействия хочется (без необходимости джойнить временную таблицу куда-то), то лучше Map или там List использовать, потому что они в памяти остаются, а не высвопляются на диск AOS если в таблице больше нескольких сотен записей образовалось.

Вряд ли единственная. Вот тут упоминаются 4 базовые проблемы и 2 специфичные.

https://community.dynamics.com/ax/f/...-tempdb/898314

Assuming you don't have basic configuration issues with your TempDB, i.e. too small with not enough auto-expand, insufficient storage on the hosting volume, not enough files per the recommended configuration, sufficiently fast storage to handle you workload, etc., you could have one of two types of problems.

First, [упоминается проблема когда просто не влезает] но можно как-то увидеть.

Second, [упоминается проблема c версиями записей при долгом запросе] что нелегко идентифицировать.

Цитата:

Сообщение от fed

Если просто быстродействия хочется (без необходимости джойнить временную таблицу куда-то), то лучше Map или там List использовать, потому что они в памяти остаются, а не высвопляются на диск AOS если в таблице больше нескольких сотен записей образовалось.

Именно. Программист может полагаться на сборщик мусора, не C++ в конце концов, но чтобы твой код полагался на то что на стороне SQL Server адекватное администрирование и ресурсы, это слишком оптимистично.

То есть при использовании TempDB мы отвечаем за наше некое решение, в котором полагаемся на то что вне нашего контроля. Возможно сказать что это не ответственность программиста раз штатное средство, но клиент нанимает опытных специалистов именно за их интуитивное избегание ненужных проблем.

[sukhanchik]

Цитата:

Сообщение от ax_mct

Именно. Программист может полагаться на сборщик мусора, не C++ в конце концов, но чтобы твой код полагался на то что на стороне SQL Server адекватное администрирование и ресурсы, это слишком оптимистично.

Тут палка о двух концах.
Вариант 1. Когда программист не может полагаться на админов.
Вариант 2. Когда программист может полагаться на админов, вплоть до самостоятельного умения обслужить сервер
Если нужно обработать большой объём данных (такое количество данных, обработка которых занимает больше 6 часов (цифра абстрактная и приближена к длительности рабочего дня программиста), то неизбежно приходим к варианту 2.
В этом случае собственно - есть выбор - грузить ли данные в оперативную память (раздувать память, потребляемую AOS / IIS / Batch, используя Set, List и т.п. объекты), либо "променять" оперативную память на временные таблицы и джойнить таблицы уже на уровне СУБД.
Ну и собственно при превышении некоторого порога обрабатываемого объёма данных - уже приходится работать с СУБД и админами СУБД. Так сказать в тесном контакте. Но в этом случае и заказчик обычно понимает и не пренебрегает этой необходимостью.

Поэтому временные таблицы и постоянные в роли временных - достаточно неплохо выполняют свою роль. И как-то лично мне последнее время "везёт" на вопросы оптимизации БД. Собственно, поэтому я и ратую за TempDB, после того, как столкнулся с достаточно большим потреблением памяти AOS при использовании List / Set и прочих классов, которые генерируются в оперативной памяти

[ax_mct]

Цитата:

Сообщение от sukhanchik

Тут палка о двух концах.
Вариант 1. Когда программист не может полагаться на админов.
Вариант 2. Когда программист может полагаться на админов, вплоть до самостоятельного умения обслужить сервер

Вариант 2, он даже на AX2012 крайне редок к примеру на британском рынке.
Я бы и рад контролировать сервер баз данных, но это очень не принято в больших компаниях. Более того вообще другая организация может отвечать за СУБД. То есть полное разделение кода от данных

А с облачным D365FO когда Azure DB и в руках блаженных, то какой тут Вариант 2.

Вариант 1 это на мой взгляд 90% рынка. Когда программист даже не знает кто или что за базу отвечает. Необходимости взаимодействия никто даже и не поймет.

Цитата:

Сообщение от sukhanchik

ратую за TempDB, после того, как столкнулся с достаточно большим потреблением памяти AOS при использовании List / Set и прочих классов, которые генерируются в оперативной памяти

Если есть время то любопытно
-насколько это критично?
-сколько памяти на AOS?

[fed]

Цитата:

Сообщение от ax_mct

First, [упоминается проблема когда просто не влезает] но можно как-то увидеть.

Second, [упоминается проблема c версиями записей при долгом запросе] что нелегко идентифицировать.



Именно. Программист может полагаться на сборщик мусора, не C++ в конце концов, но чтобы твой код полагался на то что на стороне SQL Server адекватное администрирование и ресурсы, это слишком оптимистично.

То есть при использовании TempDB мы отвечаем за наше некое решение, в котором полагаемся на то что вне нашего контроля. Возможно сказать что это не ответственность программиста раз штатное средство, но клиент нанимает опытных специалистов именно за их интуитивное избегание ненужных проблем.

В случае InMemory таблиц, вполне могут возникнуть аналогичные проблемы (кроме, пожалуй что, проблемы переполнения row version storage, который SQL Server хранит в той же tempDB). При этом в случае tempDB, ты получишь какую-то относительно читаемую диагностику от SQL Server, а в случае inMemory таблиц, ты получишь от AOS сообщения вида errno 7 offset 0x1838484584
В первом случае по крайней мере лечге понять что именно сломалось.

[sukhanchik]

Цитата:

Сообщение от ax_mct

Если есть время то любопытно
-насколько это критично?
-сколько памяти на AOS?

Не.... тут всё не так
Задача: Сделать начальную заливку данных (точнее - подготовиться к этому). При этом многие данные для перелива большие (накладные / заказы за период и т.д.) в части количества записей, поэтому начинаем пробовать на некоторых справочниках, где количество записей условно небольшое (1,3 млн). При этом все понимают, что период заливки будет достаточно большим и после заливки скорее всего придется подгружать свежие данные. При этом во время заливки могут быть те или иные ошибки (в данных), которые хочется подправить и продолжить заливку с того места, где возникла ошибка, а не перезапускать всю процедуру заново.
Если во время процедуры заливки будет падать АОС, Windows и т.д., то также не хочется запускать всё заново, а хочется продолжения работы с того же места.

В процессе программирования и прогона - запускается процедура переливки. Пока она идет - можно смотреть в БД и с удовольствием смотреть на увеличивающееся количество записей в нужных таблицах. Параллельно смотреть на ресурсы сервера - если идет "упор" в диски - анализировать БД и индексы. Если увеличивается объем памяти, потребляемый АОСом - то анализировать причины в т.ч. Trace Parser-ом.

Ситуация. Вижу, что АОС после запуска процедуры переливки начинает достаточно быстро отбирать гигабайты памяти (условно - пусть он "отъел" с 8 до 20 Гб). При этом записей создалось несравнимо мало. Стопаю пакетник, иду разбираюсь. Вижу, что есть цикл, который перебирает записи и формирует List из некоторых записей (отобранных кодом). Позже вижу, что этот перечень используется для формирования данных в других таблицах. Возникает 2 мысли:
- а почему бы просто не перебрать эти записи через Query ?
- а почему бы не складировать RecId отобранных записей в TempDB-шную табличку, а потом ее приджойнить для выборки ?
Но для начала отключаю формирование List-а. Перезапускаю процедуру и вижу, что потребление памяти становится медленнее.

Также замечаю, что есть код, где в цикле создаются новые экземпляры классов. Перестраиваю код так, чтобы этого не было. Чтобы классы создавались либо до цикла, либо уже в методах в локальных переменных, которые вызываются из цикла.
Это еще снижает скорость потребления памяти.

Цель всей этой работы - гарантированно перелить начальные данные в час Х, когда по сути будет условно одна попытка
Поэтому тут нет конкретных цифр, но хочется исключить всякие сюрпризы в т.ч. от переобъевшегося АОСа. Ну и конечно - задача максимально сократить общее время на перелив.

[skuull]

Цитата:

Сообщение от Logger

Да, точно так.
Более того - если аос падает или его пристрелили не дожидаясь пока он сам остановится, то множество этих табличек во временной базе так и остаются пока не рестартуют SQL целиком.

У меня коллега заморочился этим вопросом, даже написал скрипт по вычищению этих табличек.

Возможно надо было просто поставить https://fix.lcs.dynamics.com/Issue/D...567?kb=3109258 ? Или руками перенести одну строку

custTmpAccountSum.dispose();

[Logger]

Цитата:

Сообщение от skuull

Возможно надо было просто поставить https://fix.lcs.dynamics.com/Issue/D...567?kb=3109258 ? Или руками перенести одну строку

custTmpAccountSum.dispose();

Спасибо.
Ссылка почему-то не открывается в хроме.
Вот такая работает и открывается
https://fix.lcs.dynamics.com/issue/results/?q=3109258

Похоже не все в этом обновлении исправлено.
У коллеги используется CU13 и все суммы нулевые, т.е. маловероятно что вообще есть какая-то разноска в главную книгу, а проблема все равно присутствует. Но теперь понятно как это лечить.

[fed]

Цитата:

Сообщение от skuull

custTmpAccountSum.dispose();

Кстати - беглый эксперимент показал что он не только перестает плодить сотни одинаковых по структуре таблиц, но и начинает повторно использовать ранее выделенное имя таблицы. То есть, ситуация когда у нас plan cache на SQL Server оказался забит тысячами однообразных операторов вида
INSERT INTO tempdb."DBO".t66407IISMYVM1_915288_A59E0A988F0C4CBD8E436CC7D1C548E0... исчезает. По крайней мере в рамках цикла в одной сесии, если в конце цикла стоит dispose, то сгенерированное имя таблицы повторно используется на следующих итерациях...

[ax_mct]

Цитата:

Сообщение от sukhanchik

Не.... тут всё не так
Задача: Сделать начальную заливку данных (точнее - подготовиться к этому).
...
- а почему бы просто не перебрать эти записи через Query ?
- а почему бы не складировать RecId отобранных записей в TempDB-шную табличку, а потом ее приджойнить для выборки ?

Цель всей этой работы - гарантированно перелить начальные данные в час Х, когда по сути будет условно одна попытка
Поэтому тут нет конкретных цифр, но хочется исключить всякие сюрпризы в т.ч. от переобъевшегося АОСа. Ну и конечно - задача максимально сократить общее время на перелив.

То что можно управлять TempDB lifetime через dispose() - это все меняет. Посмотрю как много и как именно используются TempDB в D365FO и если достаточно много то буду использовать.

Спасибо за обьяснение!

[Logger]

Цитата:

Сообщение от ax_mct

То что можно управлять TempDB lifetime через dispose() - это все меняет.

Да возможность классная. Но все же это баг.
В X++ и в CIL ресурс освобождается при обнулении счетчика ссылок (только в x++ почти сразу а в CIL когда руки дойдут). Поэтому нигде в X++ мы не увидим в конце метода принудительного вызова dispose или finalize (за очень редким исключением). Правильнее было бы также сделать и с времянками. По дефолту освобождать ресурс при обнулении ссылки. А если где нужно поведение времянок как сейчас, то никто не мешает сохранить ссылку на переменную в глобалкеш, чтобы не обнулялась ссылка. Тогда и проблем никаких бы не возникало.