Comments 37
Подтверждаем. Все так и есть. Наблюдаем такую проблему.
Хочется сказать: просто жесть...
Интересно, долго еще 1С будет считать что их генерируемые запросы идеальны и не позволять их переписывать "на лету", заставляя прибегать к каким то кастылям, в виде промежуточных решение, вместо того чтобы позволить в самой платформе сделать подмену, например, как это сделано в Entity Framework?
Ваша история мне чем то напомнило мою историю, когда мне тоже пришлось расширять базовый функционал. Тогда меня не устроило, как работает LIKE %текст% .
Очень интересно, хотелось бы знать, что "под капотом" творится при создании временных таблиц. Такое странное поведение truncate наводит мысль о внешних ключах связанных таблиц. Не MSMQL, но столкнулся с такой проблемой на PostgreSQL, удаление записей или truncate таблицы, на которую ссылаются внешние ключи, приводило к большим задержкам. При анализе плана запроса выяснили, что даже при truncate таблицы, СУБД прогоняло все записи в таблицах, которые ссылались на очищаемую на наличие в них ссылок. Зависимость была прямая - больше записей в таблицах с ссылками, дольше выполняется удаление и очистка. Пришли к решению ручной очистки подчиненных таблиц.
Внешних ключей нет в 1С, но мы проводили очень много исследований на потенциальную взаимосвязь с количеством строк во временных таблицах, различных настроек самого сервера, ситуаций, которые могут привести к этому - не нашли.
Проблема появилась именно в MS SQL2019. В более ранних версиях такого не наблюдали.
Проблема именно в работе баз 1С на MSSQL? С другими системами такого не наблюдали? Спрашиваю, к тому что не смог найти эту ошибку в англоязычных интернетах.
Да, речь идет именно об MSSQL Server 2019 (и новее) в связке с 1С. Мы тоже не нашли на просторах интернета чего-то полезного про эту ошибку. Я в начале статьи пояснил, что этой проблемы за пределами 1С-систем (= за пределами нашей страны) может и не быть, т.к. среди разных тиражных решений, вероятно, только 1С использует столь интенсивную работу с временными таблицами.
выяснили, что даже при truncate таблицы, СУБД прогоняло все записи в таблицах, которые ссылались на очищаемую на наличие в них ссылок
Нельзя оставить базу в неконсистентном состоянии, когда ссылка есть, а записи, на которую она ссылается — нет. Поэтому на таблицы, которые периодически полностью зачищаются TRUNCATE-ом, не надо делать ссылки.
А не проверяли, в версии MS SQL 2022 такая проблема проявляется?
Возникла идея вместо drop использовать sp_rename в какой-то уникальный GUID, а старые версии таблиц сами отвалятся, когда отключатся создавшие их коннекты. Но, как выяснилось, sp_rename не работает на временных таблицах.
Если этот ваш QProcessing достаточно развит, чтобы писать внутри него свои программы, можно сделать отслеживание версий таблиц. То есть, как только мы заметили
truncate #tt01
меняем его на
select top 0 * into #tt01_0001 from #tt01
и далее везде в пределах этого коннекта меняем #tt01 на #tt01_0001.
Как только заметили ещё раз
truncate #tt01
выполняем
select top 0 * into #tt01_0002 from #tt01_0001
и дальше в пределах этого коннекта меняем #tt01 на #tt01_0002...
TRUNCATE – это крайне простая и быстрая операция, по ней нельзя сделать rollback или наложить условия по столбцам. А потому, она выполняется мгновенно.
Какая интересная информация про ролбэк. Т.е. получается, что в результате выполнения этого:
create table #t1 (id int);
insert into #t1 (id) values (1);
select * from #t1;
begin tran;
truncate table #t1;
rollback tran;
select * from #t1;я ну никак не мог получить это:
?
Что касается "простой" операции - нужно уточнять, что это "простая" DDL-операция, которая накладывает SCH-M блокировку (которая не совместима ни с одной другой, даже SCH-S, которую накладывают запросы с NOLOCK-хинтом) на всё время выполнения, что приводит к тому, что ни одна другая сессия не может обратиться к таблице, пока операция не завершится. В случае локальных временных таблиц - это не беда, но вы ведь и не только про них пишете.
Не включено ли у вас (ваших клиентов) MEMORY_OPTIMIZED TEMPDB_METADATA?
Тоже хотел написать про транзакцию
Есть клиенты и с включенной опцией, и с выключенной. В основном, выключена у всех. На поведение TRUNCATE опция не влияла.
Плюсую про неверное утверждение о неоткатности TRUNCATE
Ого, спасибо, у меня почему-то было застрявшее убеждение что truncate это ddl не попадающая под транзакционность
Не встречался с такой проблемой. Буду следить
Право слово не понимаю почему вы на саппорт майков пеняете, вернее его недоступность, а не на 1с? Тут скорее косяк 1с что они это так реализовали, возможно стоит обратиться к ним и получить рекомендации по настройке сервера, патч для софта, пути для обхода проблемы
Проблема не в типовой конфигурации, а в коде, который написан поверх неё
для конфигураций, которые активно дорабатываются сторонними подрядчиками – это утопичная идея, потому что любой программист 1С «с детства» приучен использовать временные таблицы
Если обратиться к подрядчикам, которые писали это, они скажут: "замечательно! дайте нам 3 года и 100 миллионов денег, и мы вам перепишем весь наш код без временных таблиц".
А какие блокировки наблюдаются во время этих длинных TRUNCATE? Возможно какие-то системные вьюхи залочены и остальные сессии ждут
А есть какая-то зависимость от версии ОС под SQL сервером? Win2016/2019?
Обратите внимание на ожидания у процесса транкейта во время проблем возможно вы увидете что-то типа waitresource=“PAGE: 3:3:70133" тогда станет понятнее куда копать
Попробуйте поиграться с количесвтом файлов в TempDB и с их размером
Рекомендую к прочтению https://learn.microsoft.com/en-us/archive/blogs/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my
Понимая кто является целевой аудиторией softpoint (а это высоконагруженный ентерпрайз 1с), которые скорее всего на короткой ноге с вендором 1с или его ркл партнёром, были все таки попытки представить указанную проблему вендору ? Как эту ситуацию комментирует сам 1с ?
Пробовали использовать в запросах конструкцию УНИЧТОЖИТЬ? и/или использовать явное создание МВТ?
Первое, что здесь можно посоветовать- поработать флажками трассировки влияющими на оптимизатор, начиная с хита, 4199. Иногда это позволяло здорово улучшить ситуацию.
Второй момент- 1С совершенно дико работает с MS SQL Server. У меня иногда получались цифры чтения из памяти на уровне 2-3 Тб в минуту, что совершенно дико и радикально отличается от традиционных параметров работы. Память это уже ни средство кэширования данных а какой-то аЦЦкий математический механизм.
В остальном же, как только появляются проблемы с временными таблицами- ловите программистов и правьте им карму. Зачастую они в джоин какой-то параметр забывают вставить, вследствие чего временные таблицы разносит до несуразности. Проблема 1С в том, что эти временные таблицы сложно увидеть, сложно понять кто и как их заполняет, зачем в дальнейшем используют. Но в плане запроса видно количество строк, ровс, иногда оно несуразно. Следует отметить, что программисты, как правило, реагируют на замечания адекватно, узкие места устраняют.
Такое поведение с 2019 вполне ожидаемо, Майкрософт обещало, что нагрузка на tempdb вырастет. Проблема не в самой операции TRUNCATE, а в блокировках на метаданных, которые трудно диагностировать без специальных средств. Например, если для временной таблицы в 1С не указали создание индекса по предикатам соединений, не только TRUNCATE не будет "летать", но и запросы к dmv будут ждать высвобождения блокировок на метаданных. Там всё в tempdb в кучу сейчас свалено, нужно разгружать эту базу или давать для неё больше ресурсов (больше файлов и конечно SSD).
На метаданные, которые живут в tempdb, много чего влияет и добавляет нагрузки. Есть копоненты - "чёрные ящики", которые любят включать, не осозновая, что это может стать причиной повышения вероятности блокировок на метаданных. Поэтому, не торопитесь с внедрением CDC, AlwaysON, QS и FTS если в этом нет насущной необходимости, можно нарваться на проблемы, как в этой статье.
Часто ещё для пользовательских баз включают версионность, забывая, что если в запросе идёт соединение с временной таблицей, изоляция будет как у худшей базы, а tempdb версионность не поддерживает. Зато включение версионности значительно повышает нагрузку на TEMPDB.
Высоконагруженные базы требуют соответствующего железа и не терпят задержек к дискам. Также важен грамотный сайзинг на NUMA, без перекосов по памяти, это тоже направление для снижения влияния подобных проблем.
Записки оптимизатора 1С (часть 1). Странное поведение MS SQL Server 2019: длительные операции TRUNCATE