Изыскания на тему записи в регистр сведений

01.10.21

База данных - HighLoad оптимизация

Уважаемые коллеги, здравствуйте! Сегодня хочу поделиться с Вами своими изысканиями на тему записи в регистр сведений в контексте оптимизации одной операции. Однажды мы столкнулись со следующей проблемой: поступили жалобы от разработчиков сайта, что наш веб-сервис очень медленно реагирует, точней, обработка запроса не укладывается в таймаут 5 секунд, и сайт получает ошибку 500. Стали разбираться, и вот что выяснили.

Скачать файлы

Наименование Файл Версия Размер
Все трассировки
.rar 431,97Kb
0
.rar 431,97Kb Скачать

Предыстория Досталась задача на оптимизацию по незнакомому участку кода и структуре конфигурации

Краткое описание ключевой операции

  1. Получение запроса от сайта, обработка данных и выдача ответа
  2. Пометка в лог о взаимодействии с сайтом (фиксируем в регистр тело запроса и тело ответа) 

Первичный сбор данных MSSQL-профайлером, произведённый нашим системным администратором (вложение slow_query.trc), показал что узкое место - запись в регистр _InfoRg3656

 

 

Бросилось в глаза, что запись в регистр идёт с опцией WITH SERIALIZABLE, это означает, что блокируется вся таблица целиком. (Для MSSQL это неверно, SERIALIZABLE вызывает блокировку всей таблицы только у СУБД "версионников": PostgreSQL и Oracle)

Сложилось ощущение, что долгая запись идёт из-за ожидания на блокировках. Проверил режим блокировок - стоял Автоматический, а в этом режиме для необъектных сущностей (например, регистр сведений) уровень изоляции = SERIALIZABLE. Первым делом перевели конфигурацию в Управляемый режим блокировок.

 

 

Слева Автоматический режим блокировок, справа - Управляемый

Примечание: сравнение текстов проведено с помощью Notepad++ с помощью плагина Compare, подробней ЗДЕСЬ, этот механизм сравнения удобней встроенного в 1С тем, что не нужно предварительно сохранять тексты в файлы, а достаточно вставить тексты из буфера обмена на две разных закладки в Np++ , и нажать одну кнопку  

Итак, SERIALIZABLE ушёл. Но проблема не решилась. Стали копать глубже.

Что же может быть не так?

exec sp_executesql N'DELETE FROM T1
FROM dbo._InfoRg3656 T1
WHERE T1._Fld3658 = @P1 AND T1._Fld3659 = @P2',
N'@P1 nvarchar(4000),@P2 nvarchar(4000)',N'ЕстьЛиЧекиПоЗаказам',N'6d89de15-6bcb-43d4-8580-fbc0c093a612'

Посмотрим на план запроса. Перед этим небольшое отступление на случай, если с MSSQL Profiler-ом кто-то не знаком

 

Как читать трассировки

Записанные трассировки в Профайлере состоят из 2-х частей:

  1. Верхняя, где список событий
  2. И нижняя, где можно увидеть подробности выделенного сверху события.

 

Трассировки, приложенные к публикации в основном были собраны с фильтром на следующие 3 вида событий

  1. Showplan Statistics Profile = План выполнения запроса текстом
  2. Showplan XML Statistics Profile = План выполнения запроса графически
  3. RPC:Completed = Текст запроса 

ВАЖНО! Трассировки достаточно большие, поэтому чтоб найти интересующие нас события, связанные с проблемной операцией, через Ctrl+F делаем поиск по маске _InfoRg3656

Трассировка 1. Набор записей с замещением без отбора по дате (DELETE+SCAN).trc

Текст запроса

DELETE FROM T1
FROM dbo._InfoRg3656 T1
WHERE T1._Fld3658 = @P1 AND T1._Fld3659 = @P2


План выполнения запроса

 

 

Очевидно, проблема в том, что при отборе записей для удаления происходит скан таблицы без использования индекса. Почему так может происходить?

Если внимательно посмотреть на номера таблиц/полей в нотации SQL - можно заметить что один порядковый номер пропущен. Что наводит на мысль о том, что отбор происходит по второму и третьему измерению, и они не индексированы.

 

Проверим в конфигураторе:

 

Проблемный участок кода, обратите внимание на поля, по которым ставится отбор

// ID и время для уникальности
УИДЗапроса = Строка(Новый УникальныйИдентификатор);
ВремяЗапроса = ТекущаяДата();

НаборЗаписей = РегистрыСведений.ЛогЗапросовОтСайта.СоздатьНаборЗаписей();
// Уникальный ID запроса, некое поле, искусственно созданное для дополнительной уникальности в рамках секунды
НаборЗаписей.Отбор.УидЗапроса.Установить(УИДЗапроса); // Отбор на третье измерение "УИДЗапроса"
// ИмяМетода - входящий строковый параметр, где может встречаться до 10 различных значений (иными словами, селективность низкая, но об этом позднее)
НаборЗаписей.Отбор.ТипЗапроса.Установить(ИмяМетода);  // Отбор на второе измерение "ИмяМетода"
        
Движение = НаборЗаписей.Добавить();
// измерения
Движение.ВремяЗапроса = ВремяЗапроса;
Движение.УидЗапроса = УИДЗапроса;
Движение.ТипЗапроса = ИмяМетода;
// ресурсы
Движение.ВходящийJSON = ТелоЗапроса;
Движение.ОтветныйJSON = ТелоОтвета;
// запись с замещением
НаборЗаписей.Записать();

Что мы видим? При установке отбора в наборе записей не ставится отбор на первое измерение "ВремяЗапроса".

Посмотрим, какие индексы есть на этом регистре?

И действительно, измерения явно не индексированы!

В конфигураторе на всех измерениях - Не индексировать

А если посмотреть индексы таблицы - увидим, что есть только служебный по измерениям

 

 

Таким образом, происходит вот что (А) набор происходит с замещением, поэтому перед записью выполняется DELETE в соответствии с установленным отбором, (Б) отбор при записи набора ставится на второе и третье измерение, первое в отборе не используется, посему система не может использовать существующий "системный" индекс по измерениям, и в итоге сваливается в скан. Решить эту проблему есть несколько способов. 

Способ первый: изменить порядок измерений регистра таким образом, чтоб отбор попадал в индекс, например неиспользуемое в отборе ВремяЗапроса поставить на третье место. Проделаем, обновим конфигурацию ИБ, и повторим замер.

 

 

Трассировка 2. Набор записей с замещением без отбора по дате, поменял порядок измерений в регистре (оптимальный DELETE).trc

 

 

Что видим:

  1. SCAN пропал
  2. Количество обработанных строк уменьшилось до одной
  3. Duration устремился к нулю

Отлично! Задача решена. Но в реальной практике данный способ (менять порядок измерений) может быть недоступен, так как например в других местах в отборе используются другие измерения, и оптимизировав здесь - мы рискуем испортить что-то в другом месте.

Рассмотрим другой способ, которым можно было бы воспользоваться в данном случае. Если мы обратим внимание на заполнение измерения УидЗапроса, мы увидим что оно заполняется значением Новый УникальныйИдентификатор, то есть по сути всегда уникальное, поэтому замещение можно и отключить. Попробуем. 

НаборЗаписей.Записать(Ложь);

Трассировка 3. Набор записей БЕЗ ЗАМЕЩЕНИЯ без отбора по дате, исходный порядок измерений (SELECT вместо DELETE, скана нет).trc

 

Что мы видим:

  1. DELETE исчез, вместо него явился SELECT
  2. SELECT (для контроля уникальности платформой 1С) при отборе использует все три измерения
  3. Таким образом, отбор попадает в существующий платформенный индекс по измерениям, и получается Clustered Index Seek вместо скана, всё оптимально.

Задача решена. Но не всегда можно отключить замещение. Какие ещё есть варианты? Можно ли оптимизировать запись в регистр в данном случае, не трогая структуру конфигурации вообще? Да. Чтоб платформа смогла задействовать существующий неявный индекс по измерениям - достаточно поставить отбор и на это поле тоже. Сделаем (Добавим одну строку кода, при этом конфигурацию откатим в первоначальное состояние)

// ID и время для уникальности
УИДЗапроса = Строка(Новый УникальныйИдентификатор);
ВремяЗапроса = ТекущаяДата();

НаборЗаписей = РегистрыСведений.ЛогЗапросовОтСайта.СоздатьНаборЗаписей();
// Уникальный ID запроса, некое поле, искусственно созданное для дополнительной уникальности в рамках секунды
НаборЗаписей.Отбор.УидЗапроса.Установить(УИДЗапроса); // Отбор на третье измерение "УИДЗапроса"
// ИмяМетода - входящий строковый параметр, где может встречаться до 10 различных значений (иными словами, селективность низкая, но об этом позднее)
НаборЗаписей.Отбор.ТипЗапроса.Установить(ИмяМетода);  // Отбор на второе измерение "ИмяМетода"
// ОПТИМИЗАЦИЯ ЗДЕСЬ! { 
// ВремяЗапроса - первое измерение, и именно его не хватало в отборе, чтоб был задействован индекс
НаборЗаписей.Отбор.ВремяЗапроса.Установить(ВремяЗапроса); 
// }
        
Движение = НаборЗаписей.Добавить();
// измерения
Движение.ВремяЗапроса = ВремяЗапроса;
Движение.УидЗапроса = УИДЗапроса;
Движение.ТипЗапроса = ИмяМетода;
// ресурсы
Движение.ВходящийJSON = ТелоЗапроса;
Движение.ОтветныйJSON = ТелоОтвета;
// запись с замещением
НаборЗаписей.Записать();

Трассировка 4. Набор записей с замещением с отбором по дате (DELETE без SCANа).trc

 

Что видим?

  1. Clustered Index Delete без скана
  2. В отборе используется все три поля, индекс задействован, всё оптимально
  3. В точки зрения плана запроса трассировка идентична той, когда мы меняли порядок измерений

Хорошо. Имеем 3 способа решить эту задачу.

А как изменится трассировка, если всё вернём в первоначальный вид, но проиндексируем используемое в отборе поле с низкой селективностью (ТипЗапроса)

 

 

Трассировка 5. Набор записей с замещением без отбора по дате (DELETE, поиск по диапазону индекса), проиндексировано второе измерение с низкой селективностью.trc

 

 

Ситуация в некотором смысле промежуточная:

  1. Индекс используется, но из-за низкой селективности отбирается по нему не 1 строка сразу, а 29тыс (напомню, всего в таблице 151тыс строк)
  2. А дальше уже по этому набору находятся нужная одна - и по ней идёт удаление
  3. Duration - не 0 как в других вариантах, но 6 миллисекунд - тоже замечательно 

Добро. Теперь сравним этот вариант с похожим. Что будет, если опять же CF оставить первозданным, но проиндексировать не второе измерение, а третье (УидЗапроса с уникальными значениями и, соответственно, с наивысшей селективностью)?

 

 

Трассировка 6. Набор записей с замещением без отбора по дате (DELETE, поиск по индексу), проиндексировано третье измерение с высокой селективностью.trc

7. Менеджер с замещением без отбора, измерения не индексированы, статистика после обновления не обновлена.trc

Данный вариант не совсем по теме регистров сведений, но тоже относится к оптимизации с точки зрения обслуживания баз на уровне СУБД, точнее обновления статистики. Явился он в процессе исследования еще одного способа решения исходной задачи. Через смену способа записи в регистр: что если использовать менеджер записи вместо набора записей? (СF изначальный)

ЗаписьВЛог = РегистрыСведений.ЛогЗапросовОтСайта.СоздатьМенеджерЗаписи();
// измерения
ЗаписьВЛог.ТипЗапроса = СтроковоеИмяМетода;
ЗаписьВЛог.УидЗапроса = УИДЗапроса;
ЗаписьВЛог.ВремяЗапроса = ТекущееВремя;
// ресурсы
ЗаписьВЛог.ВходящийJSON = ТелоЗапроса;
ЗаписьВЛог.ОтветныйJSON = ТелоОтвета;
// запись в базу
Замещать = Истина;
ЗаписьВЛог.Записать(Замещать);

 В трассировке наблюдаем странное: обычно запросы идут парами Showplan+RPC:Completed (ПланВыполненияЗапроса+ТекстСамогоЗапроса ), а тут перед запросом (RPC:Completed) аж 3 плана (Showplan), причём первые два с пометками "Предупреждение: Столбцы без статистики" 

 

 

 

Последний из 3х планов выглядит отлично, но время сравнительно медленное. Смею предположить, что оно было потрачено на построение 2х первых "неподходящих" планов, после чего был инициирован пересчёт статистик по связанным объектам, и поэтому... (см. trace 8)

8. Менеджер с замещением без отбора, измерения не индексированы, статистика после обновления не обновлена, второе выполнение, почему-то уже ОДИН план вместо 3х до DELETE.trc

 

 

Мораль сей басни

  1. Если не настроен регламентный достаточно частый пересчёт статистик, при надобности статистика пересчитается на лету, чем нагрузит базу данных в рабочее время, и незначительно будут замедлены запросы на построение не подходящих планов
  2. Запись в независимый регистр сведений (набора из одной строки) оптимальней делать через менеджер записи, а не набором записей(с точки зрения производительности ниже риск непопадания в индекс + изящней в коде)

Конечный вариант оптимизации на этой конкретной задаче

  1. Изменён порядок измерений, так как было известно, что это не помешает в других местах, а тратить место на дополнительный индекс не хотелось
  2. Запись в регистр переписана с набора записей на менеджер записи

Версия платформы, на которой проводились работы

8.3.16.1148, подозреваю, что на всех 8.3 будет аналогично.

Благодарности

  1. Моему уважаемому коллеге, который нечаянно создал сложившуюся неоптимальность, чем подарил мне возможность провернуть все эти эксперименты и собрать материал для статьи
  2. Дмитрию Юхтимовскому, за знания полученные на курсе и вообще 

Вместо эпилога

Всем добра, любви и процветания
Спасибо за внимание
До свидания! (с)

Что-нибудь жмите, что-нибудь пишите! (с)

оптимизацияпроизводительности MSSQL profiler lowlevel

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    2957    spyke    26    

42

Быстродействие типовой 1С

HighLoad оптимизация Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    5094    vasilev2015    19    

37

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7624    158    ZAOSTG    67    

96

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

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5955    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8844    ivanov660    6    

76

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    5093    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16163    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. ManyakRus 483 21.09.21 09:55 Сейчас в теме
отлично разобрались :-)
Только можно было так догадаться сделать и без никаких измерений :-)
DrAku1a; buganov; +2 Ответить
2. Hatson 528 21.09.21 10:18 Сейчас в теме
Винрарная статья. Хорошая шпаргалка. Плюсую.
DrAku1a; METAL; +2 Ответить
23. Eremkin 23.09.21 09:34 Сейчас в теме
(2)Винрарная статья. Интересное определение ))
24. Hatson 528 23.09.21 09:55 Сейчас в теме
(23) Очень старый термин )). Означает, что материал не потеряет актуальность долгое время.
33. METAL 289 23.09.21 22:32 Сейчас в теме
45. TerveRus 27.09.21 09:30 Сейчас в теме
(24) древние вроде говорили "рарная", а Windows позже появился )
3. kser87 2438 21.09.21 11:11 Сейчас в теме
Поменять режим блокировок и не словить кучу багов это прямо фантастическое везение.
Merkalov; Garfild12; DrAku1a; Yashazz; nekit_rdx; buganov; msvd; sinichenko_alex; mysm; coollerinc; Ali1976; SerVer1C; +12 Ответить
55. METAL 289 28.09.21 19:02 Сейчас в теме
(3) Правильно ли понимаю, что под багами в данном случае понимаются проблемы, которые возникли из-за того, что в нужных местах не были наложены управляемые блокировки?
57. kser87 2438 28.09.21 22:09 Сейчас в теме
4. gybson 21.09.21 11:21 Сейчас в теме
Сначала ввели реквизит в добавок ко времени (уникальность внутри секунды), а потом перестали писать время.

И остается загадкой почему используют РС, а не справочник. В чем фишка-то генерить самому УИД и дописывать в конец? Просто 1С и сами так делают я смотрю
JohnyDeath; +1 Ответить
25. Hatson 528 23.09.21 10:00 Сейчас в теме
(4)
И остается загадкой почему используют РС, а не справочник.


Наверно в справочнике много лишнего: стандартные реквизиты, иерархия, контроль ссылочной целостности + справочник так же просто как РС не очистишь.
27. METAL 289 23.09.21 11:50 Сейчас в теме
(25) Ну да. Верно.
Иными словами, перечисленное Вами - следствия того, что справочник - объектная сущность. А любые логи - необъектная. Поэтому регистр сведений подходит больше. В качестве подтверждения этих слов хочется поинтересоваться у сторонников справочников для логов: кто когда видел любые логи в типовых на справочниках? Я не встречал.
Дмитрий74Чел; orfos; +2 Ответить
29. Yashazz 4709 23.09.21 22:23 Сейчас в теме
(27) Я делал логи на документах. Меньше служебных полей, проще индексация, и хорошо подходит для больших объёмов. Справочник это всё-таки условно-постоянная НСИ обычно.
32. METAL 289 23.09.21 22:31 Сейчас в теме
(29) Исходя из того, что замеры в БСП (в ряде наших баз отлично работают, в том числе нагруженных) - на регистрах сведений, думаю, они больше подходят для сбора и хранения подобной информации

А так - в некоторых случаях на короткое время (на период отладки какой-то области, например) журнал регистрации еще можно использовать..
37. Cyberhawk 135 24.09.21 17:46 Сейчас в теме
(29)
Меньше служебных полей
Справочник можно сделать без кода и наименования, и тогда из служебных останется сколько - одно - ПометкаУдаления? А у документа помимо пометки всегда будет еще и дата, по которой к тому же еще строится безусловный дополнительный индекс. Чем же документ предпочтительнее справочника?
46. gybson 27.09.21 19:46 Сейчас в теме
(27) необъектным сущностям УИД не присваивают обычно. У "Битрикс 24" логи синхронизации в РС, но отображаются в виде дерева. Справочник с кодом неплохо подходит еще и тем, что порядок записей однозначный даже без метки времени. Думаю в него просто дольше запись происходит, проверять лень =)
26. METAL 289 23.09.21 11:46 Сейчас в теме
(4)
а потом перестали писать время

Нет же, с чего Вы взяли? Период фиксировать не перестали, но отбор по нему при записи набора - первоначально не ставился, это другое
5. ixijixi 1775 21.09.21 11:31 Сейчас в теме
Почему я не удивлён?
Прикрепленные файлы:
rinat_alp2; akR00b; TerveRus; DrAku1a; Dach; Yashazz; Hatson; +7 Ответить
28. Hatson 528 23.09.21 13:08 Сейчас в теме
(5) Среднестатистический программист 1С не знает ничего про индексы, транзакции, блокировки и т.д. с отговоркой "А зачем мне это?".
В итоге: любой пользователь (даже тот, кто никогда не работал в 1С) знает, что 1С - самая медленная и тупая система - >
В итоге: репутация специалистов в отрасли, скажем так, не на самом высоком уровне ->
В итоге: "они не тру программисты", а значит их ЗП не может быть как у например Java разработчиков
AneJIbcuH; akR00b; CheBurator; orfos; +4 Ответить
6. brr 182 21.09.21 13:03 Сейчас в теме
Бросилось в глаза, что запись в регистр идёт с опцией WITH SERIALIZABLE, это означает, что блокируется вся таблица целиком.


Не означает.
Dach; mitia.mackarevich; CSiER; pm74; +4 Ответить
10. METAL 289 21.09.21 21:44 Сейчас в теме
(6) Заинтриговали. А что же это означает, по-вашему?
12. brr 182 22.09.21 09:03 Сейчас в теме
(10) Уровень изоляции транзакции.
14. METAL 289 22.09.21 13:03 Сейчас в теме
(12) И что означает уровень SERIALIZABLE ?
19. brr 182 22.09.21 16:55 Сейчас в теме
(14) SERIALIZABLE
Указывает следующее.

Инструкции не могут считывать данные, которые были изменены другими транзакциями, но еще не были зафиксированы.

Другие транзакции не могут изменять данные, считываемые текущей транзакцией, до ее завершения.

Другие транзакции не могут вставлять новые строки со значениями ключа, которые входят в диапазон ключей, считываемых инструкциями текущей транзакции, до ее завершения.

Блокировка диапазона устанавливается в диапазоне значений ключа, соответствующих условиям поиска любой инструкции, выполненной во время транзакции. Обновление и вставка строк, удовлетворяющих инструкциям текущей транзакции, блокируется для других транзакций. Это гарантирует, что если какая-либо инструкция транзакции выполняется повторно, она будет считывать тот же самый набор строк. Блокировки диапазона сохраняются до завершения транзакции. Это самый строгий уровень изоляции, поскольку он блокирует целые диапазоны ключей и сохраняет блокировку до завершения транзакции. Из-за низкого параллелизма этот параметр рекомендуется использовать только при необходимости. Этот параметр действует так же, как и настройка HOLDLOCK всех таблиц во всех инструкциях SELECT в транзакции.

https://docs.microsoft.com/ru-ru/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver15
akR00b; Dach; METAL; CSiER; +4 Ответить
20. пользователь 22.09.21 18:16
Сообщение было скрыто модератором.
...
22. METAL 289 22.09.21 19:51 Сейчас в теме
(19) Да, Вы абсолютно правы, для MSSQL это неверно, SERIALIZABLE вызывает блокировку всей таблицы только у СУБД "версионников": PostgreSQL и Oracle, подправил
akR00b; Merkalov; brr; +3 Ответить
7. zarankony 304 21.09.21 13:07 Сейчас в теме
Всегда думал что менеджер записи - это просто другая обертка для набора записей потому что он так же ловится событиями из модуля набора записей. А оказалось что нет. П.С. Эпилог прочитал голосом Юрия профессионала.
G_116547965820392844911; Jestery; METAL; d4rkmesa; +4 Ответить
13. mitia.mackarevich 72 22.09.21 09:51 Сейчас в теме
(7)
это просто другая обертка для набора записей потому что он так же ловится событиями из модуля набора записей. А оказалось что нет. П.С. Эпилог прочитал голосом Юрия профессионала.

откройте СП и вы увидете что это так и есть, это обертка
"При этом, в общем случае, используются два набора записей: один предназначен для удаления "старой" записи, другой - для записи данных, определенных менеджером записи."
Вывод про менеджер очень странный, ясно ведь написано, что "Предназначен для интерактивной работы с записью регистра сведений. "
и вывод по эффективности субъективно - неверен.
47. gybson 27.09.21 19:49 Сейчас в теме
(13)записав пустой набор можно очистить весь регистр, а вод с менеджером записи такого казуса не выйдет. Набор это же коллекция записей + отбор
8. СергейК 51 21.09.21 14:29 Сейчас в теме
9. starik-2005 3033 21.09.21 15:16 Сейчас в теме
Отлично, когда опыты превращаются в статью. Автор научился и закрепил научение через выражение в методический материал. Такое образование самое качественное (как парное).
Merkalov; akR00b; nekit_rdx; Kovekh; Алексей_mir2mb; fancy; METAL; cleaner_it; +8 Ответить
11. t278 56 22.09.21 03:26 Сейчас в теме
Всем добра, любви и процветания
Спасибо за внимание
До свидания! (с)

Где-то я это слышал ))).
Спасибо за статью
METAL; Алексей_mir2mb; +2 Ответить
15. zabaluev 461 22.09.21 13:54 Сейчас в теме
Непонятно, зачем использовать регистр сведений для хранения логов. Есть же более простой и быстрый объект, "Справочник".
Запись в справочники менее трудоемкая операция. Индекс всего один, "Наименование".
30. Yashazz 4709 23.09.21 22:27 Сейчас в теме
(15) А при нулевой длине наименования?))
16. Tavalik 3350 22.09.21 14:09 Сейчас в теме
В целом, можно было догадаться о проблеме уже вглядываясь в код.
Но статья получилась очень хорошая, наглядная и легко читается. Спасибо.
Правда остался лишь слегка затронут переход с автоматических на управляемые блокировки. Раз и перешли, так просто? )
Dach; orfos; METAL; +3 Ответить
21. METAL 289 22.09.21 18:43 Сейчас в теме
(16) Спасибо за спасибо! )
По поводу перехода - повезло с конфигурацией, после беглого анализа мест для прописывания управляемых блокировок не нашёл, поэтому да, "раз - и перешли", за 2.5 месяца проблем не выявлено
31. Yashazz 4709 23.09.21 22:27 Сейчас в теме
(21)
"раз - и перешли", за 2.5 месяца проблем не выявлено
Везёт некоторым. Завидую белой завистью.
17. Fedos 22.09.21 15:37 Сейчас в теме
Спасибо, неплохая статья про анализ производительности и поиск проблемных мест.
Но, лучше всего она показывает, сколько времени можно потратить, криво спроектировав структуру данных.
Если уж решили использовать РС, то зачем ВремяЗапроса и ТипЗапроса пихать в измерения? Там же УИДа достаточно. И проблемы бы не возникло.
Sley; Vlad_2008; akR00b; zabaluev; orfos; +5 Ответить
18. m_aster 111 22.09.21 15:38 Сейчас в теме
Отлично, интересно, спасибо, и Вам добра!
Вспомнилась статья на ИТС из стандартов разработки о важности соответствия следования(порядка измерений) и их выборки в запросе.
Merkalov; muskul; +2 Ответить
34. logarifm 1117 24.09.21 10:56 Сейчас в теме
Проблема была ясна вначале но мне зашло, что автор разобрал разные вариации и сравнил все методы решения, а не выбрал какой-то один и все. Однозеачно автору +
35. Dach 372 24.09.21 13:12 Сейчас в теме
(0) за разбор проблемы однозначно плюс

Но выводы:

"Если не настроен регламентный достаточно частый пересчёт статистик, при надобности статистика пересчитается на лету, чем нагрузит базу данных в рабочее время, и незначительно будут замедлены запросы на построение не подходящих планов
Запись в независимый регистр сведений (набора из одной строки) оптимальней делать через менеджер записи, а не набором записей(с точки зрения производительности ниже риск непопадания в индекс + изящней в коде)"

не столь однозначны и не совсем верны.

1. Единственный на сегодня вариант пересчета статистик "на лету" для MS SQL - это включить их асинхронное автообновление + флаг трассировки 2371 (который управляет порогом "устаревания"). Данное действие может привести к своим собственным проблемам - ожиданиям на блокировках схемы. На одном из IS Event (2019 год) от компании SoftPoint был подробный доклад на эту тему.

2. Запись набором и менеджером с точки зрения СУБД ничем не отличается, набор удобнее тем, что можно самому в коде управлять отборами. Для записи одной конкретной строки - конечно, менеджер подходит лучше (просто меньше кода писать).

Судя по тому, что у вас 8.3.16.1148 и автоматический режим управления блокировками был изменен на управляемый - в базе в итоге стал режим изоляции RCSI. Несмотря на это, операторы DELETE и UPDATE в этом режиме по прежнему использует для защиты вычитываемых данных U-блокировку (см.: https://sqlperformance.com/2014/05/t-sql-queries/data-modifications-under-rcsi ), а не хранилище версий строк (как это делается при чтении внутри транзакции). Таким образом, если вдруг в процессе DELETE мы будем вычитывать весь какой-то индекс по какому-то измерению (даже если индекс есть, мы все равно можем его сканить из-за протухшей на нем статистики) - ну и из-за скана мы можем его заблокировать. И тут возможны не только ожидания на U-блокировке других сессий, но и дедлоки. Как раз такой недавно был пойман, вот его дедлок-репорт: https://pastebin.com/s0behUrt

В моем примере индекс по независимому непериодическому РС был, отбор при записи на него накладывался, но все равно произошел его скан, несмотря на весьма неплохую селективность по накладываемому отбору. Как выяснилось - из-за протухшей статистики. Протухала более быстрыми темпами она только в самые загруженные дни - в дни закрытия периода. Проблему решили ее более частым пересчетом в эти дни.

Так что одна из основных обязанностей админа СУБД - борьба за актуальные статистики.
Ну а забота программиста - стараться попадать в индексы при написании запросов.
Вот эти выводы, хоть и банальны, но всегда будут актуальны)))
54. METAL 289 28.09.21 18:42 Сейчас в теме
(35) Спасибо за такой развёрнутый комментарий и опыт!

Единственный на сегодня вариант пересчета статистик "на лету" для MS SQL - это включить их асинхронное автообновление + флаг трассировки 2371 (который управляет порогом "устаревания").

На нашей базе эти параметры по-умолчанию, см скрин, а именно
1. Автоматическое обновление статистики (AUTO_UPDATE_STATISTICS): ВКЛЮЧЕНО (Делать или нет?)
2. Асинхронное автоматическое обновление статистики (AUTO_UPDATE_STATISTICS_ASYNC): ВЫКЛЮЧЕНО (Как именно делать, синхронно или нет?)

Согласно http://www.gilev.ru/updatestat/ данные настройки означают, что статистика таки будет пересчитана автоматически ("на лету"), если она окажется устаревшей, и только после этого будет выполнен запрос, что я и наблюдал в трассировках 7 и 8. Если же включить ASYNC, то оптимизатор плана не будет дожидаться обновления статистики, "согласится" на неактуальную статистику и возможно неоптимальный план запроса, а параллельно (асинхронно) будет запущено обновление статистики (для будущих запросов).
Прикрепленные файлы:
58. Dach 372 29.09.21 17:26 Сейчас в теме
(54)

Все верно, статья на их сайте повторяет официальную документацию MS. Только вот и там и там скромно умалчивают о том, что "соглашается" с неоптимальной статистикой только текущий запрос, который и вызвал ее пересчет. Все остальные запросы ("будущие") - если они придут в тот момент, когда статистика перестраивается, - они все как раз НЕ соглашаются и встанют в очередь на блокировке схемы. Так что "асинхронность" - она только для запроса, проинициировавшего пересчет.

Я ловил такую ситуацию, есть и другие подтверждения. Если не верите - напишите в личку, скину ссылку на доклад от SoftPoint

Лично я рекомендую выключить оба флага. И спокойно обслуживать стату в регламентное окно.

Если же у вас высокая olp-нагрузка и стата устаревает очень быстро, то подумайте о внедрении адаптивного обновления статистик.
Dorosh; METAL; +2 Ответить
36. Cyberhawk 135 24.09.21 17:43 Сейчас в теме
Запись в независимый регистр сведений (набора из одной строки) оптимальней делать через менеджер записи, а не набором записей
Так и не понял, из какой части статьи был сделан (на чем основан) этот вывод...
38. METAL 289 25.09.21 12:17 Сейчас в теме
(36) Из того, что если бы изначально запись в регистр шла через менеджер - данной статьи бы не было :)
Vlad_2008; akR00b; +2 Ответить
39. Cyberhawk 135 25.09.21 12:45 Сейчас в теме
(38) Но ведь в начале статьи описывается запись через НЗ одной записи, но без отбора по одному из измерений. Это логически другая операция, нежели запись через МЗ.
Если в НЗ поставить все отборы (как это неявно происходит при записи через МЗ), то какие остаются у МЗ преимущества? Кажется, что кроме более краткого кода - никаких, а поэтому так и не ясно, и какой оптимальности МЗ перед НЗ шла речь...
52. METAL 289 28.09.21 18:04 Сейчас в теме
(39) Как показали трассировки, запись в регистр через МЗ и НЗ (с отбором на все измерения) - идентичны, и с этой точки зрения разницы нет.

Но по факту и на практике - менеджер (как Вы сами уже упомянули) неявно поставит все необходимые отборы, а через НЗ - надо об этом позаботиться самостоятельно, и как показывает практика (хотя бы данный кейс) - случается "пропущенный отбор", отчего в итоге просадка производительности, причём критическая. Поэтому вывод - при записи одной строки в независимый РС изящней в коде и надёжней с точки зрения шансов просадки производительности отдать на откуп платформе установку необходимых отборов через запись в РС посредством МЗ.

Иными словами, я хотел сказать, что при правильном коде - разницы нет. Но запись через менеджер снижает человеческий фактор и уменьшает риски не попасть в индекс из-за недоработки программиста.
Cyberhawk; +1 Ответить
40. shininvest 26.09.21 09:53 Сейчас в теме
А разве ни у кого не возник вопрос о структуре регистра сведений ?
Он ведь изначально спроектирован неверно.
В измерениях регистра должно остаться только УидЗапроса, а всё остальное точно там присутствовать не должно !
При правильной проектировке регистров сведений они прям отображаются на таблицы базы данных с нужными индексами.
И среднестатистический 1Сник не столкнется с такими проблемами в принципе.
Sley; akR00b; triviumfan; +3 Ответить
41. Cyberhawk 135 26.09.21 10:21 Сейчас в теме
(40)
В измерениях регистра должно остаться только УидЗапроса, а всё остальное точно там присутствовать не должно !
Почему?
42. shininvest 26.09.21 11:41 Сейчас в теме
(41) Да тут даже вопросов на эту тему быть не должно.
В измерениях необходимо писать КЛЮЧИ - именно то, что уникально идентифицирует запись.
По этим ключам в базе данных и будут созданы индексы
43. Cyberhawk 135 26.09.21 11:57 Сейчас в теме
(42)
В измерениях необходимо писать КЛЮЧИ - именно то, что уникально идентифицирует запись
Разве ключ "УИД + время + тип" не позволяет это делать?
44. shininvest 26.09.21 14:41 Сейчас в теме
(43) Очень забавно это читать. Почему же тогда автор не использует все эти свои измерения. У него проблема то от того и возникла, что он оперирует УИДомЗапроса, а этот прицеп - это просто реквизиты.
Читайте теорию баз данных начального уровня.
А иначе все что угодно можно приплести к ключам.

УидЗапроса - однозначно идентифицирует запись. Следовательно это и есть ключ. Ни больше ни меньше.
Дальше вся эта эквилибристика в статье связана с тем, что автор не знаком с проектированием таблиц баз дынных.
Неправильно спроектировав регистр сведений, он пытается копать совершенно не в том направлении.
Да он находит выход из ситуации путем различных манипуляций.

А если уже в качестве ключа использовать "УИД + время + тип" - да без проблем, используйте.
Но тогда и оперируйте этим ключем в полном составе, а не отдельными полями, удивляясь странностям со стороны сервера.

И вдогонку. Автору достаточно чуть чуть поправить своей регистр. А именно измерение УИДЗапроса сделать первым.
Даже такое неправильное проектирование регистра сервер простит. И все будет просто летать.
oleg-m; AneJIbcuH; akR00b; triviumfan; alexpvs; IgorS; +6 Ответить
49. akR00b 22 28.09.21 14:06 Сейчас в теме
(44)
Автору достаточно чуть чуть поправить своей регистр. А именно измерение УИДЗапроса сделать первым.
Даже такое неправильное проектирование регистра сервер простит. И все будет просто летать.

+++
50. METAL 289 28.09.21 14:37 Сейчас в теме
(44)
Автору достаточно чуть чуть поправить своей регистр. А именно измерение УИДЗапроса сделать первым.

1. Автору статьи или автору кода?
2. Если бы Вы внимательно читали статью, Вы бы заметили данное решение среди прочих вариантов
48. buganov 200 28.09.21 08:38 Сейчас в теме
(43) я соглашусь с (42), что в измерениях достаточно УИД, если он уникален
51. METAL 289 28.09.21 14:43 Сейчас в теме
(48) А он уникален? Есть гарантия, что за 100 миллионов выполнений
Новый УникальныйИдентификатор

результат ни разу не повторится?
56. buganov 200 28.09.21 20:13 Сейчас в теме
(51) в рамках одной базы не повторится
53. METAL 289 28.09.21 18:12 Сейчас в теме
(40) Вы, безусловно, правы, что правильная структура данных - залог здоровья, но целью данной статьи была другая тема, а именно - поделиться решением оптимизационной задачи (то есть нам УЖЕ достался регистр в каком-то виде и состоянии, и не всегда мы можем кардинально менять его структуру), попутно разобрать запись в регистр сведений со всех сторон и рассмотреть разные варианты.
59. пользователь 25.10.21 14:42
Сообщение было скрыто модератором.
...
Оставьте свое сообщение