Всем нужен эксперт

Опубликовал Алексей Лустин (lustin) в раздел Администрирование - Системное

Приходя на проекты, где необходимы серьезные архитектурные решения, в прошлом году я столкнулся… Скажем, так - с завышенными ожиданиями от компетенций. То есть если вкратце – некоторые вещи, которые для нас представляют собой «базовые», для наших партнеров и друзей оказывались «открытием Америки». Почему так складывается? Лично моё предположение, что за счет огромного количества рутины – у большинства специалистов не хватает времени поднять голову и взглянуть на свою деятельность системно

Одним из таких моментов – является процесс «владения СУБД для 1С. Данная статья преследует своей целью:

  • Подготовить слушателей платного вебинара - //infostart.ru/webinars/569490, на прошлом вебинаре по PostgreSQL подобный документ мы подготовили после вибинара, в этот раз хотим поступить по-другому. Базовая документация – раньше самого вебинара .
  • Повысить уровень компетенции «группы владения» 1С+MSSQL
  • Познакомить сообщество Infostart с коллекцией скриптов от PFE инженеров MS – на последней конференции Infostart Event в секции Highload почему то оказалось, что большинство экспертов 1С почему-то даже не слышали про эти скрипты

Структура статьи будет иметь цветовую дифференциацию порядка работ.

Итак, начнем - предположим у Вас есть сервер MSSQL на котором у Вас размещена база 1С. Соответственно вы обязаны выполнить 4 последовательных этапа

  • Бордовый – настройка «инстанса» SQL сервера
  • Красный – ежедневный мониторинг и исправление
  • Желтый – еженедельный анализ и рефакторинг
  • Зеленый – исследование и развитие контура

Я преполагаю что: 

  • вы знакомы с настольной книгой эксперта 1С https://1c.ru/news/info.jsp?id=17973
  • вы умееете нажимать кнопку "Скачать на Github"
  • вы умеете немного читать код SQL

Бордовые дни

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

 Что

Как

Объяснение

Лимитирование лога ошибок

Запись в реестре NumErrorLogs

Предназначено для сокращения количества файлов «лога ошибок», чтобы исключить в продуктиве ошибки типа SQL Error Log Handle To Big

Ограничение административных подключений только одним соединением

Вызов хранимой процедуры с установкой параметра remote admin connections = 1

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

Включение сжатия по умолчанию для архивных копий если сервер не лимитирован по CPU

Установка параметра backup compression default = 1, если версия MSSQL сервера не оптимизирована под лимитацию CPU

Для версий старше 10-той, то есть SQL Server 2008 R2 – использование компрессии для архивный копий отличной от «по умолчанию» не рекомендуется. Попытка управлять этим значением может привести к артефактам в части блокировкам на уровне ресурсов CPU.

Оптимизация под OLTP нагрузку

Установка параметра optimize for ad hoc workloads = 1

Для OLTP и гибридных систем в которых идет постоянная вставка, этот параметр позволяет разделить построение планов запросов на 2 этапа – построение заглушки при первом вызове, и перекомпилирование реального плана при повторном вызове.

Отключение параметров автоматического сжатия, автоматического закрытия соединений и способа проверки страницы данных

Установка параметров

SET AUTO_CLOSE OFF WITH NO_WAIT

SET AUTO_SHRINK OFF WITH NO_WAIT

SET PAGE_VERIFY CHECKSUM WITH NO_WAIT

Чаще всего автоматическое сокращение базы и автоматическое закрытие соединений приводит к снижению производительности базы данных. Использование отличного от «CHECKSUM» способа проверки страниц, также ведет к снижению производительности. При наличии встроенных средств в 1С – другой вариант проверки считается избыточным

Автоматическое создание и обновление статистики

Установка параметров

SET AUTO_CREATE_STATISTICS ON

SET AUTO_UPDATE_STATISTICS ON

В 2016 году признано всеми вендорами включая SAP – что ни одна из попыток ручного управления «статистикой» для оптимизации поиска не дала результатов. Поэтому по умолчанию всегда включаем ее автоматическое обновление. В купе с параметром оптимизации под OLTP нагрузку приводит к отсутствию артефактов связанным с избыточным чтением, при неправильно построенных запросах.

Использование параллелизма

Установка

max degree of parallelism = 1

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

Использование памяти

Установка параметра

max server memory (MB)

Используем этот скрипт – если коротко: максимальная память зависит от архитектуры процессоров, их количества, от количества рабочих процессов

Ссылка на SQL скрипт:

Дополнительно – для перехода к ежедневному наблюдению за СУБД необходимо:

Что сделать

Скрипт настройки

Настроить агента рассылки, чтобы получать письма напрямую с SQL сервера, не дожидаясь оповещения с систем мониторинга

Используем Вот этот SQL скрипт

Заполняем параметры

  • Доступа к SMTP
  • Параметры единого ящика DBA администраторов (например у нас это группа рассылки dba.admins@silverbulleters.org с доступом на отправку только через SQL сервер.

Настроить специфичные ошибки SQL которые требуют немедленного реагирования

Используем Вот этот скрипт

Ошибки SQL сервера будут считаться со степенью важности – 10, то есть почти максимальные.

Красная зона

Итак – вы настроили сервер СУБД и запустили в него пользователей. Чтобы перейти к сложным тюнингам, и сложным исследованиям – вам необходимо купировать массовые проблемы. Причем крайне желательно делать это максимально автоматизировано, чтобы больше никогда в «красную зону не возвращаться»

Обязательная скорость реакции DBA в течении 5 минут на следующие типы ошибок

Код ошибки

Простое описание

825

Проблемы с дисковым оборудованием – вероятность потерять данные не равна 0. И скорее всего означает что физическое разрушение данные уже началось.

833

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

855

Очистка запланированных заданий не удалась – фактически для администратора это означает что кто-то изменил настройки его планов обслуживания. Ошибка связана с разделением прав на администрирование сервера. Требует внимания, так как если кто-то еще администрирует сервер – вероятность коллизий возрастает

856

Нет возможности подключиться к административным представлениям, на основе которых сейчас чаще всего строится мониторинг. Это может быть вызвано проблемой с правами, а также нарушением работы самих административных представлений. Чаще всего, как и предыдущая – происходит, когда кто-то кроме Вас поковырялся в системных настройках.

3452

При проверке архивной копии выявились некосистетные данные в таблице. Фактически вы должны понимать – что у Вас нет правильной архивной копии. И вы нормально не восстановите данные.

3619

Кончилось место для лога транзакций.  Обычно возникает когда неверно спланировали «усечение» лога транзакций» и выделенное под него место.

17119

Выход за пределы лицензий на SQL сервер, связанная с количеством соединений. Фактически часть пользовательский соединений у вас не может прочитать данные. Очень похоже на превышение количества лицензий на 1С

17883

Ошибка в SQL когда SQL не справляется с нагрузкой в части распределения между потоками и процессорным временем. Может быть багом SQL сервера, а может требовать снижения нагрузки и рефакторинга кода, вызывающего максимальную нагрузку на CPU

17884

Дедлоки… Пошли дедлоки. Фактически показывает что ваша СУБД периодически встает в ступор. Очень часто связано с ошибкой установки MAX_DOP = 0.

17887

Явный проблемы при работе с дисковой полкой. То есть когда у вас наблюдаются проблемы с низкоуровневым доступом к подсистеме ввода\вывода. Обычно возникает, когда неверные настройки проходят на уровне инфраструктуры. Microsoft рекомендует сразу обращаться на линию поддержки, потому как чаще всего это сложная проблема и требует исследования. В моем опыте почти всегда связана с не обновлённым BIOS на устройствах.

17888

Еще дедлоки, обычно возникает рядом с 17883. Группа ошибок типа 17xxx вообще достаточно низкоуровневая, но на продуктивах в 1С возникает почти всегда. Мы используем для исправления вот эту статью.

17890

Проблемы с памятью на инстансе MSSQL

28036

Не работает репликация AlwaysOn – фактически означает что у вас нет сейчас высокодостпного кластера. Мы обычно называем эту ошибку «отказоустойчивый кластер отказал»

Итак, если на Вашу почту не приходит оповещение об ошибках, указанных выше, теперь можно переходить к ежедневной проверке и контролю красных проблем.

Вообще в мире MSQL принято каждое событие нормировать параметром Severity (Серьезность)

  • Красная зона – это серьезность от 0 до 50 включительно
  • Желтая зона – это от 51 до 150 включительно
  • Зеленая зона – от 150 и до 999.

Указанные выше ошибки считаются самыми серьёзными, однако – PFE инженеры и мы, подключаем еще одну группу ошибок к рассылке SQL агентом

Серьезность

Коды ошибок

16

2508, 2511, 3271, 5228, 5229, 5242, 5243, 5250, 5901, 17130, 17300

17

802, 845, 1101, 1105, 1121, 1214, 9002

19

701

20

3624

21

605

22

5180, 8966

23

5572, 9100

24

823, 824, 832

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

exec sp_Blitz @IgnorePrioritiesAbove = 50 

Описание колонок вывода на русском:

Колонка

Описание

Priority

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

Finding Group

Группа ошибок

Finding Name

Наименование проверки

Database Name

Наименование базы данных

URL

Адрес описания проблемы

Details

Расшифровка проблемы

QueryPlan

План запроса подтверждающий проблему

QueryPlanFilterd

Плана запроса попавший в параметр отбора

CheckID

Идентификатор правила

Смотреть вручную список проблем глупо – поэтому раз в сутки результат вывода направляется получателям на почту через вызов:

exec sp_Blitz @IgnorePrioritiesAbove = 50, @EmailRecipients = ‘dba.admins@ВашДомен.ру’

Вообще вам будет удобно понять параметры данный хранимой процедуры

Параметр

Тип и назначение

@IgnorePrioritiesAbove = X

Число от 0 до 999, фильтрует проблемы по важности.

@CheckUserDatabaseObjects = 0

Проверка осуществляется в контексте системной базы данных, а не пользовательской, что быстрей. Тип – маленькое число 1 или 0.

@BringThePain = 0

«Причинять боль серверу» Тип – маленькое число 1 или 0. Предназначен для оценки сервера, на котором находятся более 50 баз данных, тогда выполнять анализ в контексте мастер базы может привести к деградации системных таблиц. 

@EmailRecipients = ‘…’

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

@EmailProfile = ‘’

Имя профиля SMTP для отправки оповещений

Остальные параметры описаны тут и необходимы уже чаще всего в сложных анализах

Кстати Как вы думаете сколько tempdb должно быть по умолчанию?.

А вы знаете что если ответить «одна», то это считается проблемой ;-).

Желтая зона

Итак – если вы уже НЕ получаете оповещений о проблемах серьезности выше чем 50. Кстати внимательный пользователь – запустивший у себя скрипты уже, наверное, знает, что проблемы отсутствующих индексов – это проблемы уровня 50. То есть если вы настроили правильно рассылку, то уже увидели то что у вас на продуктивной базе 1С их около 300 ;-). Это кстати классическое среднее число на активной базу 1С. Надеюсь вы уже воспользовались обработкой //infostart.ru/public/439778/ для определения где и почему у вас отсутствующие индексы.

А мы вернемся к желтой зоне, она состоит из 5 этапов которые необходимо выполнить

Этап

Способ осуществления

Оценка состояния индексов

USE <вашаБаза>
exec sp_BlitzIndex @Mode = 4

Оценка состояния статистики

	
exec view_Stats_Last_Update.sql

Выявление главной проблемы по CPU

exec sp_BlitzCache @ExpertMode = 1, @SortOrder = 'CPU'

Выявление главное проблемы по RAM

'

	
exec sp_BlitzCache @ExpertMode = 1, @SortOrder = 'memory grant'

Выявление главной проблемы по чтению

	
exec sp_BlitzCache @ExpertMode = 1, @SortOrder = 'reads'

Выявление главной проблемы по записи

exec sp_BlitzCache @ExpertMode = 1, @SortOrder = 'writes'

Вам понадобится следующий комплект скриптов:

Методика выявления желтых проблем следующая:

Статистика

  • Вначале делаем так чтобы статистика была актуальной и объем измененных записей не превышал 20000 между обновлением статистики и текущим значением модификации.
  • Откуда взялось значение 20000? Эмпирическое число от Microsoft для систем типа 1С, SAP, Dynamics
  • Для версий MSSQL младше не забываем включить флаги трассировки
    • 1118 
    • 1117
    • 2371
    • 4199
    • 8048

Визуально это выглядит так:

То есть если у вас есть таблицы с объемом модификаций выше 20.000 где статистика не обновлялась автоматически – эти таблицы добавляются в принудительное обновление статистики чаще чем раз в день. Планы обслуживания СУБД – тема отдельной статьи, однако для базовых вещей достаточно этой статьи на ИТС

Индексы – проблемы от 51 до 150 считаются желтыми».

Классические проблемы индексов:

  • Дубликаты индексов (Multiple Index Personalities: Borderline duplicate keys)
    • Отображение – много записей, мало чтений
    • Например - Reads: 6 (6 seek) Writes:142,319
    • Способ исправления – сравнить индексы на таблице с помощью
      • EXEC dbo.sp_BlitzIndex @DatabaseName='yourdb', @SchemaName='dbo', @TableName='_TableName';

      • Найти дубликат индекса
      • Зайти в метаданные и оставьте только один индекс. Как это сделать – изучите настольную книжку 1С:Эксперт.
  • Слишком много индексов (Index Hoarder: Many NC indexes on a single table)
    • Отображение – много записей, мало чтений
    • Например - 25,534 reads (ALL); 1,551,717 writes (ALL);
    • Способ исправление
      • EXEC dbo.sp_BlitzIndex @DatabaseName='yourdb', @SchemaName='dbo', @TableName='_TableName';

      • Сравнить все столбцы поиска
        • Исправлять только когда удалены дубликаты
        • Оптимизировать состав индексов
        • Зайти в метаданные – поставить нормальные индексные выражения
        • Зайти в код – найти выражения поиска по таблице в запросах, проверить что выражения поиска используют новый индекс
        • Отрефакторить код
        • После рефакторинга убедиться, что не появилось «отсутствующих индексов»
  • Неиспользуемые индексы (Index Hoarder: Unused NC index)
    • Отображение – нет чтений совсем, нет записи
    • Например -  Reads: 0 Writes: 0
    • Способ исправления
      • Зайти в метаданные
      • Убрать индексацию
      • Исправлять только когда нет отсутствующих и дубликатов

Есть еще несколько классических проблем – но в целом подход к исправлению один и тот же.

Проблемы кэша

Последнее, но самое сложно по пониманию – это «желтые» проблемы работы с ресурсами. Вы должны понять следующее:

«Ни один запрос к СУБД не должен
превышать 25% от общей нагрузки на ресурс инфраструктуры»

Поэтому выполните следующие команды в консоле SQL:

exec sp_BlitzCache @ExpertMode = 1, @SortOrder = 'CPU'

exec sp_BlitzCache @ExpertMode = 1, @SortOrder = 'memory grant'

exec sp_BlitzCache @ExpertMode = 1, @SortOrder = 'reads'

exec sp_BlitzCache @ExpertMode = 1, @SortOrder = 'writes'

Найдите запрос который выполняет максимальную нагрузку по указанному ресурсу:

В колонке «План запроса» вы увидите главного «нагибателя» Вашей СУБД. Используя Инструменты разработчика - //infostart.ru/public/15126/ вы быстро найдете примерный код в 1С который является проблемой. Рефакторинг 1С решений – это также тема отдельной статьи, но как показывает наша практика на 2016 год основными проблемами являются

  • Неумением писать запросы на языке 1С с применением ключевого слова СОЕДИНЕНИЕ
    • Большинство разработчиков не понимают, что соединять нужно таблицы в порядке увеличения записей – то есть вначале с меньшим количество, а уже к ней с большим.
  • НЕ индексирование временных таблиц
    • Временная таблица должна индексировать всегда, если не доказано обратное, а не наоборот. Поля индексации временной таблицы выбираются так, чтобы СОЕДИНЕНИЕ строилось только по индексированным полям
  • Работа «с двумя точками»
    • Выборка данных НИКОГДА не должна делаться через 2 точки, а явно подразумевает необходимые внутренние соединения внутри запроса.
  • Незнание поведения конструкции В(&СписокИлиТаблица)
    • При использовании конструкции .УложитьСписокОбъектов(КакойТоСписок) если значений больше чем 128 – то на SQL это будет выглядитель как созданием временной таблицы, а не как конструкция IN(Params)
  • Неверное использование конструкции ПОДОБНО
    • В запросе использование конструкции ПОДОБНО «%ЧтоТо%» еще со времен FoxPro вызывает полное сканирование всей таблицы и не индексируется почти никогда.  Для проверки на вхождение в строку её части в любом месте – должен применятся специализированный алгоритм «Полнотекстовый поиск», либо внутренний в 1С, либо внешний с помощью например ElasticSearch

Обычно, в 95% случае оценка кэша – выдает именно эти проблемы в запросах 1С. Ну и конечно массовая, не гранулярная вставка значений в регистры.

«После красно-желтых дней»

Если подытожить, то порядок работы следующий

  • Настроить сервер
    • Память, SMTP и параллелизм
  • Включить Email рассылку для ключевых проблем
    • Подключить 2 SQL скрипта Microsoft и BrentOzar
    • Купировать проблемы от «нуля» до «50»
  • Оценить
    • Отсутствующие индексы
    • Затратные индексы
    • Неиспользуемые индексы
    • Деградацию статистики
  • Зафиксировать наиболее проблемные
    • Запросы по CPU
    • Запросы по RAM
    • Запросы по READS
    • Запросы по WRITES
  • Сформировать план по рефакторингу кода и метаданных
    • Включить в работы по проекту план по оптимизации исходя из следующего правила
      • Одна проблема «желтой» зоны за 2 недели должна быть исправлена

В качестве заключения небольшой FAQ

  • Есть ли какие-нибудь другие скрипты у PFE инженеров?  
    • - Да конечно. Тысячи их.
  • Как всегда быть в курсе, что новенького у PFE инженеров?
    • Обычно делается так: создается email sqlexpert@example.com и на него делается регистрация списка рассылки. Если знаешь английский – то используется способ подачи Issue через репозитории GitHub.
  • Можно ли исправлять красные и желтые проблемы только на уровне MSSQL ?
    • НЕТ. Если в Вашем продуктиве есть такие проблемы, то 99% случаев – это проблемы некачественного кода в вашей конфигурации или в Ваших доработках типовой.
  • А где же ЦУП и остальное?
    • По нашему скромному мнению – внедрять ЦУП или другие средства, специализированные для 1С, стоит только после применения инструментов MSSQL для купирования первичных классических проблем. А уже потом заниматься тонким тюнингом.
  • Можно ли создавать индексы средствами СУБД?
    • На сегодняшний день рекомендации компании Microsoft следующие – для купирования проблем: да можно, но, если индексы созданы на уровне СУБД – обязательна фаза рефакторинга кода приложения. В нашем случае (применительно к 1С) – создание индексов средствами СУБД является нарушением лицензионного соглашения и фактически снимает Вас с поддержки. Поэтому если объединить обе рекомендации – то получается. Создавать индексы средствами СУБД можно только в одном случае, если вы обязуетесь после этого исправить код приложения – точнее 1С конфигурации. Если вы этого не сделаете – Вы подставились. Если бизнес не готов согласовывать доработку и рефакторинг – с таким бизнесом лучше работать «жестоко» и напрямую указывать, что их действия по несогласованию рефакторинг ведут к непредсказуемым последствиям в части потери данных или выхода из строя всего контура.
  • Вы с чем то в статье не согласны?
    • Предполагаю что к подобной статье будет множество комментариев формата "Я не согласен", поэтому заранее прошу всех не согласных не рефлексировать, а писать свои статьи, в которых отражать свой подход к владению конутром 1С+MSSQL
  • А как же сервисы Гилева ?
    • Я думаю Вячеслав с командой давно изучил указанные скрипты и имплементировал в своих сервисах наиболее интересные решения. За что ему наш почет и уважение. Нам же просто удобней использовать именно эти скрипты на процессах аудита - так как компания BrentOzar специализируется именно на MSSQL решениях https://www.brentozar.com/

См. также

Комментарии
1. Dorosh Dorosh (Dorosh) 94 09.01.17 10:10 Сейчас в теме
Спасибо за серьезный материал. А по Postgre подобная статья планируется?
turrrrist; Silverbulleters; mrDSide; amon_ra; +4 Ответить
2. Алексей Новиков (Новиков) 287 09.01.17 11:08 Сейчас в теме
Спасибо Алексей! Было бы здорово, если бы Вы что-то также порекомендовали по оборудованию и организации дисковой подсистемы на сервере СУБД. Вот тут был небольшой вброс, но очень жидковато получилось.
3. Татьяна Лустина (Silverbulleters) 78 09.01.17 11:08 Сейчас в теме
Учитывая выход скриптов для Windows от испанских инженеров по PG - да планируется. Это наш "почти технический" долг перед сообществом.
KroVladS; +1 Ответить
4. Sergey Andreev (starik-2005) 959 09.01.17 12:53 Сейчас в теме
5. Алексей А. (Разумов) 09.01.17 13:10 Сейчас в теме
Еще не вникая, хочется поставить плюс за подход к написанию статьи. Очень "на качество" написана, без торопливости и лени. Даже просто эстетически глаз радует.
fuxic; ZOMI; +2 Ответить
6. Денис Козлов (Akbis) 43 09.01.17 13:21 Сейчас в теме
Если можно получать список с ошибками через скрипт
exec sp_Blitz @IgnorePrioritiesAbove = 50, @EmailRecipients = ‘dba.admins@ВашДомен.ру’

то зачем нужно настраивать job
https://github.com/Microsoft/tigertoolbox/blob/master/MaintenanceSolution/6­_Agent_Alerts.sql

?
7. Сисой Сисой (Сисой) 79 09.01.17 13:55 Сейчас в теме
.УложитьСписокОбъектов(КакойТоСписок) - это о чем?
8. Сисой Сисой (Сисой) 79 09.01.17 14:05 Сейчас в теме
>>Большинство разработчиков не понимают, что соединять нужно таблицы в порядке увеличения записей – то есть вначале с меньшим количество, а уже к ней с большим.

Интересно, а на курсах 1С этому учат? Когда я учился (в нулевые) никто об этом даже и не заикался.
9. Алексей Дубичев (1cWin) 09.01.17 14:26 Сейчас в теме
10. mishgan mishgan (ganshinm@mail.ru) 09.01.17 15:11 Сейчас в теме
>>Большинство разработчиков не понимают, что соединять нужно таблицы в порядке увеличения записей – то есть вначале с меньшим количество,
>> а уже к ней с большим.
Извините, мимо этого пройти не смог. Что автор курит?
ll13; 4rtehouse; Silverbulleters; +3 1 Ответить 2
11. Алексей Лустин (lustin) 817 09.01.17 16:11 Сейчас в теме
(6) это фишка - BrentOzar просто присылает список ошибок, а Microsoft скрипт "валит" оповещение на каждую отдельное письмо. Чтобы не было желания отмахнуться и забить.

(7) да действительно - моя проффесиональная деформация. Я привык называть метод УстановитьПараметр(_коллекция) по старому (по 1С++'ному) как УложитьСписокОбъектов() - это позволяет мне не забыть что чудес не бывает, и коллекции в качестве параметров в реальности превращаются в подготовку служебной таблицы для организации фильтра

(10) видимо я недостаточно понятно объяснил - имелось ввиду, что фильтры должны назначаться как можно раньше - чтобы не получились ситуации, когда для выборки "одной строки", "вы читаете половину базы"
starik-2005; +1 Ответить 1
12. Сергей Рудаков (fishca) 1050 09.01.17 16:27 Сейчас в теме
Кратко и по делу, спасибо!
13. Антон Стеклов (asved.ru) 33 09.01.17 16:44 Сейчас в теме
(10)
В общем-то мы имеем право рассчитывать, что оптимизатор определит оптимальный порядок и методы соединений.

Однако не следует забывать о пороге достаточности в поиске оптимального плана. В условиях высоких нагрузок и сложного набора соединений SQL может найти относительно приличный с его точки зрения вариант и на дальнейшую оптимизацию плана забить. Или вообще не успеть найти достаточно оптимального плана. Или иметь кривые статистики.

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

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

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

14. mishgan mishgan (ganshinm@mail.ru) 09.01.17 17:47 Сейчас в теме
(11)
>>видимо я недостаточно понятно объяснил - имелось ввиду, что фильтры должны назначаться как можно раньше - чтобы не получились ситуации,
>>когда для выборки "одной строки", "вы читаете половину базы"

Да, такая формулировка более корректна, спасибо
15. Алексей Лустин (lustin) 817 09.01.17 17:53 Сейчас в теме
(13) Вот это вот и классическая проблема - якобы какой-то оптимизатор, какой то СУБД что-то сделает за Вас. И вы осознанно на это забиваете - а потом внезапно оказывается, что оптимизатор не такой уж оптимальный, да и СУБД бывают разные. И в итоге мы все возвращаемся к истокам. А истоки нам показывают, что надежда на физический оптимизатор и есть тонкий тюнинг, а вначале следует логика запроса, которая никогда не должна быть "оптимизаторо-зависимой".

То есть повторюсь - я прекрасно знаю что оптимизатор MSSQL и две настройки

* optimize for ad hoc workloads = 1
* traceflag on 4199

Дают для 1С вауэффект, но это уже тюнинг на уровне СУБД, а причина то кроется совершенно в другом.

очень много запросов в рамках аудита у нас выглядит примерно так (псевдокод):

ВЫБРАТЬ * 
ПОМЕСТИТЬ втНоменклатуры
ИЗ Справочник.Номенклатура;
ВЫБРАТЬ ПЕРВЫЕ 1 
ИЗ РегистрСведений.Цены как ТипаЦены
ЛЕВОЕ СОЕДИНЕНИЕ втНоменклатуры ПО ТипаЦены.Товар = втНоменклатуры.Ссылка
ГДЕ ТипаЦены.Товар = &ФильтрПоТовару
...Показать Скрыть


Почему очень многие назначают фильтры "в конце" запроса - для нас остается загадкой. Именно поэтому я очень сильно рефлексирую на выражение "Нас спасет оптимизатор, а мы пока запросы конструктором погенерируем".

16. mishgan mishgan (ganshinm@mail.ru) 09.01.17 17:53 Сейчас в теме
(13)
порядок соединения таблиц, указываемый разработчиком не влияет ни на что (если разумеется других отличий нет).
ни на решение, принимаемое оптимизатором.
ни на поиск оптимального плана.
проверить несложно

для того, чтобы СУБД использовала ваш порядок соединений - используются хинты, например option (force order) для MSSQL, /*+ ORDERED */ для Oracle.
но в 1С штатными средствами их использовать нельзя.
ll13; fuxic; 4rtehouse; Andreynikus; lustin; +5 Ответить 1
17. mishgan mishgan (ganshinm@mail.ru) 09.01.17 17:56 Сейчас в теме
(15)
>>очень много запросов в рамках аудита у нас выглядит примерно так (псевдокод)
тут конечно, никакой оптимизатор не спасет.
но это фильтрация, а не порядок соединений
18. Сисой Сисой (Сисой) 79 09.01.17 18:27 Сейчас в теме
Я думаю, автору нужно поправить рекомендацию про соединение. Потому что 99% разработчиков читают эту рекомендацию следующим образом:
Для любой конструкции
Select ... From Table 1 Left Join Table 2 On ...
строго-настрого нужно следить, чтобы количество записей в Table1 было меньше, чем в Table2.

А это совсем не то, что хотел донести автор.
Видимо, имелось в виду следующее: при использовании соединений условия выборки должны максимально отрабатываться условием соединения и (автор об этом не написал) параметрами виртуальных таблиц 1С (если они используются в запросе).
shalimski; lustin; +2 Ответить
19. Антон Стеклов (asved.ru) 33 09.01.17 18:52 Сейчас в теме
(16) Вам знакомо понятие устойчивости плана запроса? Оно очень близко к тому, о чем я писал.

Повторюсь, чтобы увидеть эффект от изменения порядка таблиц в соединении, нужно:
1) Предельно высокие нагрузки
2) Сложное соединение с большим количеством таблиц
Также неплохо будет, если в запросе участвуют временные таблицы, а потом откатыватся транзакция. В таком случае кэширование планов не работает.

Что само по себе является проблемой, решать которую нужно отнюдь не изменением порядка соединения. Однако знать о такой особенности бывает полезно.

Еще можно, кстати, упомянуть о привычке MSSQL не обращать внимания на статистику по второму полю кластерного индекса, если первое поле высокоселективно. Для разделенных баз это выглядит достаточно печально.
20. Антон Стеклов (asved.ru) 33 09.01.17 19:02 Сейчас в теме
(15) От приведенного примера оптимизатор не спасет. Здесь результативна только эвтаназия.

Речь о ситуации, когда алгоритм исполнения запроса определяется исключительно на стороне СУБД.
21. mishgan mishgan (ganshinm@mail.ru) 09.01.17 19:05 Сейчас в теме
(19)
понятие устойчивости плана запроса знакомо.
применительно к данной теме, Вы видимо имели ввиду следующее:
1) есть запрос sel ect * from t1 inner join t2 ...., он выполняется много-много раз, его план в кеше
2) есть другой запрос select * fr om t2 inner join t1 ...., т.е. запрос точно такой же как и первый, но порядок таблиц другой (все остальное в точности совпадает, выборки одинаковые)

в это случае план второго запроса будет взят не из кеша, а построен заново и мы потеряем время построение плана.

так я Вам больше скажу - чтобы вытащить план из кеша, для СУБД имеет значение каждый пробел в тексте запроса, регистр всех символов и т.п., т.е. должно быть 100% совпадение текста запроса (хотя справедливости ради нужно отметить, что на этапе трансляции SDBL->SQL кое что "сглаживается").
22. mishgan mishgan (ganshinm@mail.ru) 09.01.17 20:33 Сейчас в теме
Господи, какой бред

- "Временная таблица должна индексировать всегда, если не доказано обратное, а не наоборот. Поля индексации временной таблицы выбираются так, чтобы СОЕДИНЕНИЕ строилось только по индексированным полям"
В большинстве случаев, временные таблицы совершенно не требуют индексирования

- "Выборка данных НИКОГДА не должна делаться через 2 точки, а явно подразумевает необходимые внутренние соединения внутри запроса."
Соединение через точку не несет ничего плохого. Какая разница, кто напишет left join - сам разработчик 1С, либо платформа.
Запрос на SQL будет одинаковый.
За исключением одного случая - обращение через точку к составному типу, например, к полю типа Справочник ссылка произвольного типа, либо документ ссылка произвольного типа.
В этом случае запрос на SQL будет содержать несколько десятков лишних джойнов и таких обращений через точку, конечно, следует избегать.
Но не нужно формулировать, что следует избегать вообще всех обращений через точку.

Нужно понимать, что плохо, а что нет. Автор нахватался всяких советов, не очень осознал их, такими статьями можно окончательно запудрить неокрепшие умы 1с-ников.
ipoloskov; Andrsan; 4rtehouse; +3 Ответить 3
23. Alexander Speshilov (speshuric) 909 09.01.17 23:06 Сейчас в теме
(21)
чтобы вытащить план из кеша, для СУБД имеет значение каждый пробел в тексте запроса, регистр всех символов и т.п., т.е. должно быть 100% совпадение текста запроса

Что правда что ли? А почему MS пишет что это не так?
Легко же проверить:
Код

Обратите внимание, что на первые 3 селекта к ttt один query_hash и на все четыре селекта к ttt один query_plan_hash
Berckk; ganshinm@mail.ru; +2 Ответить 3
24. Alexander Speshilov (speshuric) 909 09.01.17 23:27 Сейчас в теме
(22)
В современных версиях 1С (т.е. 8.3), если СУБД MS SQL (мы же про нее сейчас?), то платформа при создании индексированной ВТ достаточно разумно сначала создаёт кластеризованный индекс, а потом наполняет её данными. Если отсечь пограничные глупые случаи (типа "положи мне в ВТ всю базу" и "2 ГБ памяти на сервере и tempdb на самом медленном диске"), то заметить затраты на один единственный кластеризованный индекс вы не сможете.
Если у вас нет ни одного индекса на ВТ, а записей больше 2000, то мало того, что у вас единственная операция "просканируй меня", так еще и статистика начинает уплывать. Если сразу указать удачный индекс на ВТ, то SQL Server может его применить в соединениях, например. Так что рекомендация достаточно разумная: хуже чем куча вряд ли будет, а лучше - при минимальном здравом смысле - будет.
25. Петр Базелюк (pbazeliuk) 1245 09.01.17 23:31 Сейчас в теме
(22)
В большинстве случаев, временные таблицы совершенно не требуют индексирования

Индексирование уменьшит деградацию запроса при росте количества данных.
P.S. Из опыта одного проекта: при матричном управлении товарами с учетом характеристик и других показателей, временную таблицу необходимо несколько раз переиндексировать на разных уровнях иерархии. Без индексов расчет ассортиментной матрицы (25 складов) занимает больше часа, с индексами 30 секунд.
26. Alexander Speshilov (speshuric) 909 09.01.17 23:49 Сейчас в теме
(0) Кроме скриптов Брента Озара, я бы рекомендовал еще посмотреть на скрипты Гленна Берри. Пока Брент жмотил свои скрипты и давал ссылку только после регистрации - у Гленна уже они были доступны "по клику". Сейчас-то Брент выложил на github, но у меня осадочек остался.
Плюс у Гленна не создаётся ничего, голые селекты, а у Брента всё в ХП.
Ну и, конечно, у каждого набора скриптов есть свои нюансы, так что, по-хорошему, у DBA оба должны быть под рукой.
gadjik; JohnyDeath; +2 Ответить
27. Алексей Лустин (lustin) 817 09.01.17 23:57 Сейчас в теме
(22) началось... пошли слова "бред", "селективность", "нахватался".

специально для Вас в статье секция
Вы с чем то в статье не согласны?


(23) Саш - ты чего, это же классический "микровебинар" Брента Самый показательный в мире вебинар про сохраненный план запроса

shalimski; FirePyres; JohnyDeath; ganshinm@mail.ru; +4 Ответить 2
28. mishgan mishgan (ganshinm@mail.ru) 10.01.17 00:23 Сейчас в теме
(24)
Если я использую временную таблицу, то с большой вероятностью индекс будет не нужен.
Потому что
1) Во временную таблицу кладется относительно небольшая порция данных
2) Как правило эта временная таблица и так будет полностью прочитана. Т.е. full scan будет в любом случае, независимо от того, есть там индекс или нет.

Можно придумать, случай когда индексы на временную таблицу нужны. Например, мы кладет порцию данных во временную таблицу, а потом к ней делает 100 запросов, выбирая каждый раз по одной записи. В таком случае, конечно, индекс пригодится, но это неправильный сценарий использования временной таблицы.
29. mishgan mishgan (ganshinm@mail.ru) 10.01.17 00:29 Сейчас в теме
(23)
Спасибо. Кажется я где-то что-то пропустил.
Разберусь, возможно отпишусь.
30. mishgan mishgan (ganshinm@mail.ru) 10.01.17 00:32 Сейчас в теме
(24),(25),(0)
Приведите, пожалуйста, пример, достаточно часто встречающийся на практике, когда на ваш взгляд, использование индексов на временную таблицу чем то улучшило ситуацию
31. Alexander Speshilov (speshuric) 909 10.01.17 00:38 Сейчас в теме
(27) А я, кстати, не видел. Интересно, как он этого добился? У меня на 2016 на моём примере выдаёт один query_hash. Хотя с другой стороны - я помню как где-то "select ... from config where ..." с кучей разных гуидов в dm_exec_query_stats были (прямо по количетву объектов).
32. mishgan mishgan (ganshinm@mail.ru) 10.01.17 00:40 Сейчас в теме
(27)
Извините.
Если Вы не согласны с моими замечаниями, приведите пожалуйста примеры
- с индексированными временными таблицами (когда это хорошо)
- обращение через точку (когда это плохо)

Спасибо за ответ на (23)
33. Alexander Speshilov (speshuric) 909 10.01.17 01:07 Сейчас в теме
(32) Дискуссия комментарии примерно 70-80. Да, в 8.1 и 8.2 индексирование было сделано неэффективно. В 8.3 сделали разумно. Если смотреть планы и трассу, то на самом деле ускорение достаточно частое даже на банальном "В (Выбрать)". Особенно, если эта ВТ используется в нескольких фильтрах в запросе.

Тут еще 3 момента важны
1. Это часто проявляется не на простых запросах, а когда уже 1С-ных объектов в запросе давно считается десятками, а план запросов уже и в студии смотреть некомфортно. Тогда оптимизатор уже не строит из себя умника и его приходится водить за ручку.
2. Замерять время запроса в тестовой базе 1С - это хреновая метрика. Когда всё прокешировано - ограничением будет CPU, а когда эти запросы в бою потребуют IO, то CPU станет несущественным.
3. Какой попало индекс, конечно, не поможет.
34. mishgan mishgan (ganshinm@mail.ru) 10.01.17 01:32 Сейчас в теме
(33)
"Особенно, если эта ВТ используется в нескольких фильтрах в запросе."
В это случае индексирование ВТ может быть оправдано. Да и то не всегда.

Крайний случай я привел в (28) "Например, мы кладет порцию данных во временную таблицу, а потом к ней делает 100 запросов, выбирая каждый раз по одной записи"

Автор же предлагает "Временная таблица должна индексировать всегда, если не доказано обратное, а не наоборот".
Я как раз считаю обратное.
35. Никита Грызлов (nixel) 74 10.01.17 02:22 Сейчас в теме
(34)
> Автор же предлагает "Временная таблица должна индексировать всегда, если не доказано обратное, а не наоборот".
Я как раз считаю обратное.

У Вас с доказательством как-то тухло. Единственное, что я уловил из Ваших слов - там все равно будет table scan, какая разница.

ВТшки не так уж и редко бывают средними или даже большими по размеру. Table scan на таблицах со строками даже больше 100 уже может быть медленнее, чем индекс с хорошей селективностью.

Если в ВТ три строки или из этой ВТ нужно просто выбирать большую часть данных, то да, индекс может и не помочь. Но это все же не основной сценарий. А в общем случае рекомендовать индексы стоит.
36. Никита Грызлов (nixel) 74 10.01.17 02:25 Сейчас в теме
(35) из личного опыта - одно только грамотное расставление и попадание в имеющиеся индексы позволило сократить время выполнения ряда довольно тяжёлых запросов суммарно на 30-40 миллионов строк выборки с четырёх часов до 3 минут.
37. mishgan mishgan (ganshinm@mail.ru) 10.01.17 02:33 Сейчас в теме
(35)
>>У Вас с доказательством как-то тухло
У Вас тоже, коллега
Пример давайте
38. mishgan mishgan (ganshinm@mail.ru) 10.01.17 02:34 Сейчас в теме
(36)
Речь идет про временные таблицы. 30-40 млн строк во временные таблицы не кладут
39. mishgan mishgan (ganshinm@mail.ru) 10.01.17 02:39 Сейчас в теме
(35)
>>Table scan на таблицах со строками даже больше 100 уже может быть медленнее, чем индекс с хорошей селективностью.
Ответьте на вопрос - зачем во временную таблицу класть больше, чем планируете использовать?

40. Алексей Лустин (lustin) 817 10.01.17 02:57 Сейчас в теме
(35) Никит - я напомню. Мы пишем код на 1С.

Поэтому:

вот эта конструкция багом не считается - что там этих пользователей то ? 100-200 строк.

CRE ATE   TABLE #Users
    (
        ID          INT IDENTITY(1,1),
        UserID      INT,
        UserName    VARCHAR(50)
    )
    
    INS ERT IN TO #Users
    (
        UserID,
        UserName
    )   
    SEL ECT 
         UserID     = u.UserID
        ,UserName   = u.UserName
    FR OM dbo.Users u
...Показать Скрыть


а вот это считается

ВЫБРАТЬ
  Пользователи.Ссылка
ПОМЕСТИТЬ втПользователи
ИЗ Справочник.Пользователи как Пользователи
...Показать Скрыть


И я опять же - достаточно прозрачно добавил.
Если не доказано обратное
.
Если у Вас достаточно знаний и компетенций, чтобы это обосновать - Вам почет и уважуха. Если нет - будьте любезны индексировать временную таблицу в 1С.

Все что происходит сейчас в нашей дискуссии является классической ошибкой грамотного DBA. Он забывает что 1С:

* это некий такой ORM
* сегодня это MSSQL, а завтра что-то другое
* DBA всегда кажется что его знания про физику MSSQL достаточно для построения рекомендаций в части проектирования метаданных и выработки стандарта кодирования

несколько лет назад был такой же "холивар" про отсутствующие индексы.

Если подытожить, то получается - всем понравилась секция где я описал, на какие классические косяки 1С-ников мы нарвались в прошлом году. Если я правильно понял - я наступил на больную мозоль и основные участники считают:

* временные таблицы в 1С индексировать не надо
* контролировать количество соединений в 1С запросе не надо
* всё отдаем на откуп оптимизатора MSSQL - он магичен, он дальше разберется.

Как я указал выше - если где-то мы с Вами пересечемся: придется "похоливарить". Но извините за деньги вашего заказчика. Бесплатно мне что-то холиварить не хочется - в прошлом году мы и так задолбались объяснять прописные истины.
41. Алексей Лустин (lustin) 817 10.01.17 03:10 Сейчас в теме
(38) еще как кладут... да так кладут, что могут за сутки "полтеррабайта" через temdb прогнать.

Вообще - я на вебинаре покажу 2 метрики которую я всегда смотрю в части временных таблиц:

* объем данных проведенных через tempdb деленный на объем данных базы данных - если за сутки этот параметр составляет более 20%: ищи в коде помещение во временные таблицы больших объемов данных: косяк там.

если

exec sp_BlitzCache @ExpertMode = 1, @SortOrder = 'reads'


в качестве участника запроса выводит хоть что-то связанное с
#tt
- ищи временные таблицы в 1С без индексов.

В любом случае - это всегда проблема логики запросов, а не индексов на временной таблицы. Но как показывает практики - эффект достигается простым индексированием как заметили коллеги
42. mishgan mishgan (ganshinm@mail.ru) 10.01.17 03:33 Сейчас в теме
(40)
Ничего по делу нет. Я попросил пример ВЫБОРКИ, которая показывает, что индекс оправдывает себя.
Вы привели пример заполнения на TSQL и на 1C. При чем тут индексы?
43. mishgan mishgan (ganshinm@mail.ru) 10.01.17 03:40 Сейчас в теме
(41)
>>еще как кладут... да так кладут, что могут за сутки "полтеррабайта" через temdb прогнать.
Если кладут - это проблемы с головой того, кто кладет.
Видел я такие случаи. Выборка во временную таблицу - помещается, скажем, 10000 записей. Потом идет выборка из этой временной таблицы и всего выбирается, скажем, 100 записей.
И вот местный умник мне тоже рассказывает, что он добавил индекс и стало все круто и вообще индексы на временные таблицы рулят.
Все что надо в этом случае - наложить доп. ограничение и заполнять временную таблицу ровно тем количеством строк, которое реально используется. Тогда в большинстве случаев индекс не требуется.

ПС. Я реально видел случаи, когда во временную таблицу клали все проводки в базе (вообще все и это не шутка). Что ж теперь, считать это нормой?
44. Антон Стеклов (asved.ru) 33 10.01.17 05:54 Сейчас в теме
(21) До оптимизатора текст запроса не доходит вообще. Оптимизатор работает с результатом его декомпозиции и интерпретации. Поэтому у разных по тексту запросов могут быть одинаковые планы.
Изменение в тексте запроса, не меняющее его формальную логику, тем не менее, может влиять на результат интерпретации текста, делая неоптимизированный план ближе или дальше к считаемому оптимальным. Что влияет на скорость нахождения достаточно оптимального плана и на сам критерий достаточной оптимальности.

В общем, читайте ЕМНИП главы 10 и 11 Microsoft SQL Server internals, где-то там про это рассказывается. Но повторюсь, материя достаточно тонкая и большинству 1С-ников совершенно бесполезная.

Валидность кэшированного плана, опять же, определяется не текстом запроса, а на более глубоком уровне, в том числе и по состоянию таблиц. Если ВТ была удалена, например, откатом транзакции, а потом создана идентичная ВТ - план в кэше будет уже инвалид.
45. Антон Стеклов (asved.ru) 33 10.01.17 06:02 Сейчас в теме
(40) По индексации временных таблиц - сделайте простой нагрузочный тест: селект в индексированную и неиндексированную ВТ. Смотрите на IO по tempdb.
Речь о том, что при написании запроса необходимо оценивать объемы и сортировку выбираемых данных и принимать решение исходя из этого. Однако на практике это мало кто делает и рекомендацию индексировать ВТ в целом можно считать корректной - для большинства разработчиков.
brr; JohnyDeath; +2 Ответить 1
46. mishgan mishgan (ganshinm@mail.ru) 10.01.17 09:30 Сейчас в теме
(45)
Приведите хоть один пример, когда использование индекса во временной таблице оправдано.

>>Однако на практике это мало кто делает и рекомендацию индексировать ВТ в целом можно считать корректной - для большинства разработчиков.
Бредовая рекомендация, которая, конечно, устроит 95% 1с-ников, которые все равно не понимают ни назначения индексов, ни уж тем более смысла индексирования временных таблиц
47. Антон Стеклов (asved.ru) 33 10.01.17 09:38 Сейчас в теме
(46)
Любой случай, когда ВТ, достаточно большая для того, чтобы nested loops был невыгоден, джойнится с достаточно большим индексом. Потому, что merge join будет выгоднее hash join, а с неиндексированной - и следовательно не имеющей гарантии упорядоченности ВТ merge join невозможен.

Другой вопрос, каковы будут затраты на индексацию. Это зависит от сложности индекса и производительности tempdb.
ganshinm@mail.ru; +1 Ответить 2
48. Алексей Лустин (lustin) 817 10.01.17 09:52 Сейчас в теме
(42) уважаемый. Я сознательно Вам ничего не представляю... Когда закончите использовать формулировки типа "Ничего по делу нет". Вы умный, мы глупые - Вам удачи.
Dem1urg; Artem.Po; JohnyDeath; +3 Ответить 2
49. mishgan mishgan (ganshinm@mail.ru) 10.01.17 10:10 Сейчас в теме
(47)
Да, согласен, пожалуй единственный случай, когда использование индексов На временные таблицы оправдано.
50. mishgan mishgan (ganshinm@mail.ru) 10.01.17 10:17 Сейчас в теме
(48)

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

"
Неумением писать запросы на языке 1С с применением ключевого слова СОЕДИНЕНИЕ

Большинство разработчиков не понимают, что соединять нужно таблицы в порядке увеличения записей – то есть вначале с меньшим количество, а уже к ней с большим.

НЕ индексирование временных таблиц

Временная таблица должна индексировать всегда, если не доказано обратное, а не наоборот. Поля индексации временной таблицы выбираются так, чтобы СОЕДИНЕНИЕ строилось только по индексированным полям

Работа «с двумя точками»

Выборка данных НИКОГДА не должна делаться через 2 точки, а явно подразумевает необходимые внутренние соединения внутри запроса.
"

я бы вообще порекомендовал Вам завязать с практикой писать статьи и не проводить никаких вебинаров. Уровень глубины не тот.
Понимаю, конечно, что пипл хавает. Но если уж Вы путаете соединение с фильрацией....
51. Roman Seagal (4rtehouse) 10.01.17 11:09 Сейчас в теме
(48) Алексей, 3 пункта из статьи, описанные в (50), очень и очень спорные, может и не полный бред, но как минимум дезинформация.
По крайней мере в такой формулировке.
Видимо у вас накипело, и хочется дать универсальный совет для начинающих, как не накосячить по-крупному, но для специалиста с опытом видеть в такой статье такие советы... просто сразу возникают сомнения в компетенции автора...
ganshinm@mail.ru; +1 Ответить 1
52. Максим Кузнецов (Makushimo) 149 10.01.17 12:40 Сейчас в теме
Спасибо.
Как чек лист и шпаргалка идеальная статья.
в суперизбранное.
53. Иван Петров (dgolovanov) 10.01.17 14:47 Сейчас в теме
(51) Аргументировать можете?
54. Roman Seagal (4rtehouse) 10.01.17 15:46 Сейчас в теме
(53) по поводу СОЕДИНЕНИЙ сам автор в (15) прокомментировал, что имел ввиду - добавлять условия во временные (и, наверно, виртуальные) таблицы, а не в итоговую выборку из них. Т.е. не выбирать из БД то, что не пригодится.

А в статье написано это: "Большинство разработчиков не понимают, что соединять нужно таблицы в порядке увеличения записей – то есть вначале с меньшим количество, а уже к ней с большим."

т.е. если количество записей в Т1 больше, чем в Т2, то в запросе следует писать:
Т2 <ЛЕВОЕ|ПРАВОЕ?> СОЕДИНЕНИЕ Т1

я так понял...
55. Roman Seagal (4rtehouse) 10.01.17 15:55 Сейчас в теме
(53) Работа "с двумя точками"

Я за то, чтобы запросы читались как можно легче (конечно не в ущерб производительности).
И две (три, четыре...) точки в этом помогают.
Если ты специалист адекватный и понимаешь с какими прикладными типами работаешь (во что превратится итоговый запрос), ничего страшного в этом нет.

Смущает безапелляционное "НИКОГДА".

56. Roman Seagal (4rtehouse) 10.01.17 16:32 Сейчас в теме
(53) Про индексы сказали уже достаточно, и MERGE JOIN даже упомянули (47) (очень важная вещь кстати).
Участвовать в этом холиваре не буду, лишь приведу пример, когда индексы во временных таблицах не нужны абсолютно:

ВЫБРАТЬ ...
ПОМЕСТИТЬ Т1
;
ВЫБРАТЬ ...
ПОМЕСТИТЬ Т2
;
ВЫБРАТЬ * ИЗ Т1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ * ИЗ Т2
57. Антон Стеклов (asved.ru) 33 10.01.17 19:48 Сейчас в теме
(56) Если забить на определение, какие поля следует индексировать, то я еще проще могу :D

ВЫБРАТЬ ...
ПОМЕСТИТЬ Т1
;
УНИЧТОЖИТЬ Т1
speshuric; JohnyDeath; +2 Ответить
58. Евгений Мартыненков (JohnyDeath) 290 10.01.17 19:50 Сейчас в теме
(56) вам не кажется, что в данном случае создавать Т1 и Т2 не нужно абсолютно?
59. Alexander Speshilov (speshuric) 909 10.01.17 19:56 Сейчас в теме
(56) Необходимость индексов в этом запросе сильно зависит от того, как используется Т1 во втором запросе ("ВЫБРАТЬ ... ПОМЕСТИТЬ Т2" - нигде не сказано, что Т1 в нем не используется :) )
60. Roman Seagal (4rtehouse) 11.01.17 09:40 Сейчас в теме
(58)Я просто имел ввиду - когда из временной таблицы выбираются все данные индексы зачем нужны?
Автор пишет ВСЕГДА нужны.

И даже если можно временную таблицу не использовать а выбирать данные прямо в основном запросе, я имею право её всё-таки использовать, хотя бы с целью упрощения всего запроса и последующей легкой поддержкой.
В примере запрос из двух таблиц, а если их будет порядка 50-ти?
61. Roman Seagal (4rtehouse) 11.01.17 09:42 Сейчас в теме
(59) Нет, с этим все понятно, когда есть какие-то отборы или соединения, индексы нужны, согласен. Здесь имелась ввиду просто выборка всех таблиц полностью.
62. Alexey (AlexeyFreeLife) 11.01.17 09:44 Сейчас в теме
По-моему, на автора статьи сильно наехали НЕ заслуженно - человек старался, писал - вещи умные. Выдал в свободный доступ для использования и размышлений. Большинству разработчиков это точно не помешает - а кто обладает более глубокими знаниями просто может ими поделиться, а не пытаться уменьшить значимость данного материала.
fuxic; shalimski; headMade; vovan_victory; Artem.Po; +5 Ответить 1
63. Евгений Мартыненков (JohnyDeath) 290 11.01.17 10:05 Сейчас в теме
(60)
я имею право её всё-таки использовать, хотя бы с целью упрощения всего запроса и последующей легкой поддержкой.

Вот из-за такого подхода и появляются такие споры и статьи.
Вам лишь бы проще поддерживать, а кому-то очень критична скорость работы.
Тоже самое и с "пятью точками": ведь проще написать и "поддерживать" пять точек, чем 5 джойнов (или вызов какой-то серверной ф-ии, если речь идет о разыменовании в реквизитов объекта в коде, а не запросе)
64. Евгений Мартыненков (JohnyDeath) 290 11.01.17 10:07 Сейчас в теме
(62) Автор уже выразил свое отношение ко всем "протестующим" строками прямо в статье: "Есть про что рассказать - велкам! Почитаем вашу статью"

Помимо очень полезной информации в статье, из нее еще и неплохой вброс получился ;)
65. Roman Seagal (4rtehouse) 11.01.17 10:25 Сейчас в теме
(63) Какого "такого подхода"?
Ситуации разные бывают, здесь мне так захотелось, а там по-другому. Мне опыт позволяет решения принимать, т.к. знаю как это работает на более низком уровне.
Раньше тоже стремился оптимизировать всё и вся, сейчас пришел к тому, что главное - быстро сделать то что, нужно бизнесу, далее легкая поддержка.
Рефакторинг только для себя.
Оптимизация только того, на что жалуются, т.к. это самая неблагодарная работа.

А в статье меня не суть этих советов смущает, а их формулировка.
Если бы автор так написал, вопросов бы вообще никаких не было:

"... Рефакторинг 1С решений – это также тема отдельной статьи, но как показывает наша практика на 2016 год основными проблемами являются:

Избыточная выборка данных из БД
(Отсутствие условий в больших временных и виртуальных таблицах)
Отсутствие индексов у больших временных таблиц
Обращение к полям составного типа «через две точками»
Неоптимальное использование конструкции В(&СписокИлиТаблица)
Неверное использование конструкции ПОДОБНО


подробности в отдельной статье ;)
..."
66. Евгений Мартыненков (JohnyDeath) 290 11.01.17 11:15 Сейчас в теме
(65)
Какого "такого подхода"?

подхода "лишь бы быстрей и проще для разработки"

Ведь если вы ВТ будете всегда добавлять индексы, то как минимум хуже от этого не станет. Верно?
67. Олег Веселов (sml) 35 11.01.17 11:26 Сейчас в теме
(50) согласен с этим.
Единственный случай, когда пришлось индексировать ВТ:
Выбрать Ссылка, ТипНоменклатуры
ПОМЕСТИТЬ ВТ1
ИЗ Справочник.Номенклатура
ГДЕ
Родитель В &СпГрупп

;
...Показать Скрыть

В дальнейшем ВТ1 соединяется с таблицей регистра и по Номенклатуре, и по Типу с другим Регистром

Индекс строил по Типу

В остальных случаях выборка в ВТ должна быть минимально достаточной для дальнейшей работы и не требует индексов
68. Иван Петров (dgolovanov) 11.01.17 11:35 Сейчас в теме
(66)
Ведь если вы ВТ будете всегда добавлять индексы, то как минимум хуже от этого не станет. Верно?

"Как минимум" на создание индекса тратится время. Запрос с индексами может работать дольше, чем запрос без индексов.
69. Иван Петров (dgolovanov) 11.01.17 11:39 Сейчас в теме
Коллеги, а вы на курсы по вождению ходили? Экзамен в ГАИ сдавали? Тоже на каждый совет инструктора слюной брызгали "да я так делал и нормально", "адекватному водителю можно"? Есть правила - подходят 90%, есть некие отклонения, когда ты отдаешь себе отчет - это остаток, малая часть. А вы всю статью на какие-то частности растащили.
JohnyDeath; +1 Ответить
70. Roman Seagal (4rtehouse) 11.01.17 12:04 Сейчас в теме
(66) Понимаете в чем дело, я ведь раньше с таким лозунгом и жил. И всем джуниорам говорил - "Когда создаете ВТ обязательно индексируйте все поля, которые потом используете в условиях соединений!"
И вот, однажды, один товарищ сидит, отчет оптимизирует, и спрашивает меня: "Вот ты говорил всё индексировать, а я сюда индекс добавляю и запрос на несколько секунд дольше работает. Как же так, Рома?"
Можно объяснять про затраты на создание индекса, про то что объем данных потом вырастет - индекс пригодится...
Тут вопрос в доверии.
Т.е. человеку профессионал с многолетним стажем дает рекомендацию, а потом он на личном опыте видит, что рекомендация даёт обратный результат. И доверие к этому "профессионалу" уже не то...
71. Alexander Speshilov (speshuric) 909 11.01.17 12:52 Сейчас в теме
(68) А можно конкретный пример? Сколько тратится времени? Как я уже писал выше - в 8.3 в ВТ создаётся кластеризованный индекс до вставки строк. При этом на самом деле разницу между вставкой в кучу и в КИ не так-то просто заметить.
Заметная разница в пользу кучи может быть если:

1. Один план параллельный, другой нет. Но если у вас параллельные планы при заполнении ВТ, то у меня для вас плохие новости: либо у вас ВТ огромная , либо вам надо что-то делать с запросом или структурой БД вне зависимости от ВТ.
2. Одна операция минимально протоколируемая, другая - нет. Да, вставка в пустую кучу будет "чаще" минимально протоколируемой. Только если у вас узкое место - журнал транзакций на заполнении ВТ, то опять же - это значит, что она огромная и у вас проблема гораздо глубже.
3. Вы выбрали очень неудачный индекс и для сортировки по кластеризованному индексу сгенерирован "плохой" план. Ну так возьмите более удачный индекс.

Поэтому, те кто утверждает, что на индексирование тратится время: пруф в студию. Только а) на свежих 8.3 и MS SQL 2012-2016 (лучше на 2016 с новым cardinality estimator), б) воспроизводимый, в) без параллельных планов, г) с планом запросов и с заметной (т.е. более 20%) разницей на writes в tempdb и duration д) временная таблица, конечно, не с миллионами-миллиардами строк. Эта задача выполнима, но те кто её могут выполнить, уже точно не нуждаются в рекомендациях, как писать запросы.
Bronislav; Berckk; Sergey.Noskov; JohnyDeath; nixel; +5 Ответить 2
72. Alexander Speshilov (speshuric) 909 11.01.17 12:57 Сейчас в теме
(71) Забыл еще случай, когда в ВТ куча дублирующихся строк, но это частный случай п.3. И так тоже не надо делать (или взять другие поля для кластеризованного индекса)
73. Денис Козлов (Akbis) 43 11.01.17 13:26 Сейчас в теме
(71) Ну понятно что какое-то время тратится, а также надо учитывать текущую нагрузку на базу.
Рекомендация 1С "для всех" - индексировать все ВТ. Так стабильнее если разрастется база, сменится СУБД, разброс данных поменяется.
Дальше уже кто шарит, понимает что далеко не всегда индексирование дает пользу.
А иногда даже забирает лишние милисекунды.
74. Alexander Speshilov (speshuric) 909 11.01.17 13:31 Сейчас в теме
(73)
понятно что какое-то время тратится
Непонятно. Пруф с предложенными ограничениями есть? Сколько именно потрачено лишнего внемени и IO?
75. Денис Козлов (Akbis) 43 11.01.17 13:34 Сейчас в теме
(74) А по вашему время не тратится?
Индексировать можно все подряд на здоровье?)
76. Евгений Мартыненков (JohnyDeath) 290 11.01.17 13:37 Сейчас в теме
(73) вот вас и просят показать эти "иногда даже забирает лишние милисекунды".
77. Денис Козлов (Akbis) 43 11.01.17 13:39 Сейчас в теме
(76) Именно на 8.3 и 2016 сервере?
78. Alexander Speshilov (speshuric) 909 11.01.17 14:01 Сейчас в теме
(75) А что такое "тратится"? На ВТ возможен ровно один индекс, причем кластеризированный (КИ). Причем ВТ всегда на начало заполнения - пустая (это, кстати - предпосылка к неполному протоколированию вставки). Если параллельных планов нет, и планы выполнения "похожи", то скорее всего у вас будет одно из двух ограничений - либо получение данных запросом (но у нас же планы похожи), либо скорость вставки. Скорость вставки в кучу "типа" выше, пока мы не вспомнили про RID (которых может не быть в КИ, если значения уникальны). Так, если речь идет о ВТ со списком уникальных ссылок на один объект метаданных, разница в размере может быть почти в 1,5 раза в пользу кластеризованного индекса.

Если планы похожи, но в случае КИ есть сортировка, а в случае кучи - нет и на этом разница производительности больше 20% для запроса заполнения ВТ, то у вас очень большая ВТ. Не забываем, что это обычно лишь маленький этап большого пакета. Насколько оправданно создание такой ВТ в принципе? Насколько она полезна без индекса?

Если планы разные, то сложно вообще говорить в терминах "тратится". Просто один запрос с эффективным планом, а второй - нет. Я представляю, как заставить SQL свалиться в эту ситуацию, но а) я знаю как с этим бороться б) мне сложно придумать практическое применение результатам такого запроса. Именно поэтому я и прошу воспроизводимую ситуацию.
79. Alexander Speshilov (speshuric) 909 11.01.17 14:04 Сейчас в теме
(77) 8.3 - обязательно. В (33) ссылка почему 8.2 и 8.1 не подойдут. SQL можно и 2008, но начиная с 2014 появился новый cardinality estimator и может случиться так, что вы просто докажете, что пора переходить на свежий SQL.
dgolovanov; +1 Ответить 1
80. Антон Стеклов (asved.ru) 33 11.01.17 18:08 Сейчас в теме
(79)
начиная с 2014 появился новый cardinality estimator

А вы знаете, почему платформа сейчас требует полномочия sa на MSSQL2014?
roma_semenov79; +1 Ответить 1
81. Евгений Алексеев (BaldRzn) 11.01.17 18:38 Сейчас в теме
Какой-то странный холивар организовали.
Естественно не стоит воспринимать слова в статье как прямое руководство к действию. Естественно нужно их "переварить" и применять обдуманно.
Все претензии - к немного "обобщенным" формулировкам на мой взгляд.
Алексей - спасибо за статью.
83. Антон Стеклов (asved.ru) 33 12.01.17 13:52 Сейчас в теме
(82) Ну, еще один шаг. Что делает T4199.
84. ash (ashvik) 12.01.17 14:45 Сейчас в теме
(83) Включает исправление, которое по умолчанию отключено https://support.microsoft.com/en-us/kb/974006
85. Марат Хафизов (Painted) 16 11.05.17 12:00 Сейчас в теме
В некоторых версиях MS SQL затираются индексные DVM-ки при ребилде индексов. Поэтому довольно опасно доверять "sp_BlitzIndex", который собирает информацию с этих DVM. Там не представительная статистика при частом ребилде.
86. friend0 11.05.17 18:03 Сейчас в теме
Решил снова проверить, вдруг действительно в 8.3 чудо-чудное с индексированием временных таблиц сделали. Но чуда не произошло. Может конечно дело в том что скуль 2012, но другого у меня нет.

В обработке запускал 4 запроса (бухия, 94 тыщи строк) на основе:
	ЗапросН.Текст = "ВЫБРАТЬ 
	                |	ДоговорыКонтрагентов.Ссылка,
	                |	ДоговорыКонтрагентов.владелец,
	                |	ДоговорыКонтрагентов.СрокДействия
	                |ПОМЕСТИТЬ ттт
	                |ИЗ
	                |	Справочник.ДоговорыКонтрагентов КАК ДоговорыКонтрагентов
	                |;
	                |
	                |////////////////////////////////////////////////////////////­////////////////////
	                |УНИЧТОЖИТЬ ттт";
...Показать Скрыть

1. Исходный
2. Индекс: Ссылка
3. Индексы: владелец, СрокДействия
4. Индексы: Ссылка, владелец, СрокДействия

Если не брать во внимание первоначальное создание таблиц, то результаты на картинке
Прикрепленные файлы:
87. Sergey Andreev (starik-2005) 959 11.05.17 21:08 Сейчас в теме
(86) а что за циферки-то? Создал и удалил? И все? Полагаю, что если потом эти ВТзаюзать длячего-либо, может возникнуть профит.
88. friend0 12.05.17 01:55 Сейчас в теме
(87)Может возникнуть, а может не возникнуть. Вопрос стоял в том, что лучше: бездумно создавать или бездумно не создавать индексы для временных таблиц. Я всегда был за второй вариант (создание индексов полюбому требует ресурсов, а будут ли они использоваться - это бабушка надвое сказала). Некоторые товарищи в теме утверждали, что индексы волшебным образом не требуют доп. ресурсов - вот я и решил проверить.

П.С. Циферки стандартные профайлеровские: CPU, Read, Write, Duration. Большие циферки - собственно чтение-вставка, маленькие - уничтожение.
89. Alexander Speshilov (speshuric) 909 12.05.17 02:42 Сейчас в теме
(88) Ну и посмотрите внимательно на свои результаты.
1. Варианты 3 и 4 по факту меняют план запроса, остаётся сравнивать однократный запуск 1 и 2.
2. Writes поровну. Чтений почти поровну. Duration отличается на 22% ( (135-104)/135 ). Отсутствие индексов, если эта ВТ используется почти гарантированно будет дороже.
3. Формулировка в 3 и 4 неправильная: не "Индексы", а "Индекс" - на ВТ он в 1С всегда один. Это важно.
JohnyDeath; +1 Ответить
90. Sergey Andreev (starik-2005) 959 12.05.17 10:20 Сейчас в теме
(88)
Может возникнуть, а может не возникнуть.
Нужно сделать так, чтобы возник. А еще нужно мозг напрячь и подумать, а возникнет ли вообще профит от создания ВТ или соединения достаточно (многие тут мозг в принципе включать не умеют - учиться надо!).

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

В общем нужен мозг. И не нужно всю базу в ВТ засовывать, чтобы потом три записи получить - это все азбука. А если нужно всю базу в отчет засунуть, то тут ВТ вообще не нужны - просто селекти все и не парь мозг никому.
Оставьте свое сообщение