IE 2016

Про отсутствующие индексы и 1С

Опубликовал lustin в раздел Администрирование - Статистика базы данных

Хотите чтобы MS SQL подсказывал Вам, в каком объекте вы строите не оптимальные запросы или неверно спроектировали метаданные - тогда спросите у него это.

Про отсутствующие индексы и 1С

        Вместо вступления:

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

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

        Итак перед началом ознакомления со статьей хочу заметить, что на моем пути встречались следующие категории людей по типу отношения к индексам СУБД:

  1. Бог его знает что такое индекс - отличительная особенность: круглые, удивленные глаза, как реакция на фразу “Запрос не попадает в индекс”.
  2. Каждое поле таблицы (реквизит объекта) необходимо проанализировать на предмет необходимости индексации - отличительная особенность: постоянный просмотр планов запроса, даже в случае выборки одной строки из таблицы с гарантированным размером не более 10 строк.
  3. Вы слишком сильно уповаете на индексы - отличительная особенность: гарантированная реакция с ярко выраженным апломбом, в виде “Оно вам не поможет”, при попытке коллеги просмотра административных представлений MS SQL или плана выполнения сложного запроса в терминах SQL
  4. С индексами в каждом конкретном случае надо включать голову - компромиссный тип людей, отличительная особенность - знает что такое селективность, и понимает почему JOIN можно заменить на UNION и в чем будет выйгрыш.

 И последний момент который необходимо отразить: к кому себя относит автор ?

 Тут ответ прост - скорее к последнему типу, по причинам:

  1. я считаю что про индексы надо знать
  2. я знаю что анализировать необходимость индексов по полям таблицы необходимо, но не по всем полям, а на этапе проектирования только по основным, в дальнейшем по ситуации.
  3. я знаю что не всегда индексы - это самая главная проблема производительности; также ведь есть код на 1С, есть не очень хорошее аппаратное обеспечение, блокировки и солнечные вспышки - и естественно мы знаем что у индексы есть такой хитрый параметр как избыточность.
  4. я знаю различные инструменты мониторинга, и стараюсь по возможности не пренебрегать ни одним из них - и считаю что чем больше показателей информации - тем выше скорость диагностики конкретной проблемы

 Для работы нам понадобиться:

 

  • 1С Предприятие 8.2 - http://users.v8.1c.ru -  я использую версию 1С:Предприятие 8.2 (8.2.13.205)
  • Тестовая конфигурация  - режим совместимости 8.1, архитектура: 1С Сервер + MS SQL 2005 и выше (я использую Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64))
  • GameWithFire - http://main.1c-ei.ru/Articles/gamewithfire скачать можно по адресу http://dev.citykirov.ru/ или в составе Enterprise Integrator
  • любую Консоль запросов 1С

 

Отсутствующие индексы

        Как не странно еще со времен 1С 7.7, мое желание запустить ее именно под MS SQL 2005 было связано, помимо всего прочего, еще и с тем фактом что сервер СУБД собирает статистику о выполнении запросов и предоставляет мне доступ к этой информации.

С появлением 1С 8.* ничего собственно не изменилось (за исключением того что теперь нет необходимости учить 1С работать с MS SQL 2005) - архитектура метаданных может оказаться не оптимальной, а запросы на языке 1С тоже - что вполне естественно: идеальных систем не существует в принципе.

 

Так вот - MS SQL хранит эту самую статистику и данная информация может быть очень даже полезной. Что это за статистика можно ознакомиться в относительно серьезной статье “Открытие скрытых данных для оптимизации производительности приложений

Однако мы с Вами попробуем разобраться с одним из административных представлений MS SQL - а именно с sys.dm_db_missing_index_*

 Прежде чем мы начнем работать с этим представлением хочу заметить три основополагающих постулата применительно к нашей задаче:

  • то что SQL считает отсутствующим индексом- это фиксация события когда
    • SQL строил план запроса - то как он будет собирать данные по запросу чтобы отдать нам в 1С,
    • в запросе был наложен фильтр явно или не явно (секция ГДЕ, СОЕДИНЕНИЕ и т.д.)
    • SQL “хотел” использовать индекс, но не нашел его.
  • для анализа важны параметры Издержки индекса и Средний процент выигрыша, а это:
    • то какую неоправданную нагрузку Вы осуществляете на СУБД
    • что будет если вы создадите этот отсутствующий индекс - на сколько сократятся ваши затраты.
  • Для того чтобы SQL больше не считал Ваши запросы не оптимальными и перестал “семафорить” о том, что он дескать не нашел нормально способа поиска по таблице - исправлять Вы это должны на стороне 1С

 

Примечание: За рамки данной статьи конечно выходит анализ того - а не станет ли созданный по данной информации индекс потом например затратным по поддержке. Моя цель показать вам каким образом можно воспользоваться указанными данными при анализе производительности.

 

Сейчас попробуем на довольно простом примере убедиться что это очень просто:

 

Тестовый пример

 

Для начала развернем тестовую конфигурацию приложенную к данной статье.

Развертывать необходимо в клиент-серверном режиме с использованием сервера СУБД MS-SQL версии 2005 и выше.

Метаданные в данном случае совершенно простейшие:

 

Однако обращаю внимание - реквизит ДокументОснование - НЕ проиндексирован. Я про него забыл ;-).

 При запуске конфигурации будет запущен генератор документов - он создаст Вам сколько угодно документов. По умолчанию я выставляю 10000 документов

 Генератор документов создает последовательно документы - в котором каждый следующий документ имеет основанием предыдущий по МоментуВремени документ такого же типа.

 

 

В конфигурацию встроена обработка “Отсутствующие индексы SQL” .

И сейчас она будет показывать пустую информацию (или ошибку если Вы забыли проинсталировать GameWithFire.dll)

 

Давайте попробуем выполнить теперь несколько типовых запросов на выборку документа основания:

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

 

ВЫБРАТЬ  Документ1.Ссылка

ИЗ   Документ.Документ1 КАК Документ1

ГДЕ   Документ1.ДокументОснование = &ДокументОснование

;

 ВЫБРАТЬ  Документ1.Ссылка

ИЗ  Документ.Документ1 КАК Документ1

ГДЕ  Документ1.ДокументОснование = &ДокументОснование  И НЕ Проведен

;

 ВЫБРАТЬ  Документ1.Ссылка

ИЗ    Документ.Документ1 КАК Документ1

ВНУТРЕННЕЕ СОЕДИНЕНИЕ  Документ.Документ1 КАК Документ1Основание

        ПО Документ1.ДокументОснование =  Документ1Основание.Ссылка

;

 

И снова запускаем обработку “Отсутствующие индексы SQL”

 

А вот теперь в итоге посмотрите на запросы которые вы выполнили
и на ту информацию которую Вам сообщил SQL:

 

        Итак Ваше приложение выполняло запросы которые:

  • осуществляли работу с основной таблицей документ с ИД “Документ1”
  • вложенность не использовали (поле Состав предлагаемого индекса пустое)
     
  • накладывали фильтры:
    • на равенство (поле Поиск) - по реквизиту ДокументОснование
    • на больше/меньше/НЕ равно - по реквизиту Проведен

 

  • затраты на такие запросы сводно составили по этому документу 2683 “попугая” = [средняя стоимость одного запроса * средний процент  выйгрыша * (количество пользовательских операций поиска + количество пользовательских операций сканирования)]
     
  • если бы вы создали подобный индекс вы бы сократили свои издержки на 99%

 

 Так как поле Проведен недоступно для индексирования из 1С - Вы легко сделаете вывод сами, я надеюсь - как исправить данную ситуацию.

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

 

В качестве эпилога:

 

Данная статья - это:

  • концепт инструмента мониторинга - еще очень много предстоит исследовать: как минимум уже есть желание:
    • преобразовать по такому же методу остальные SQL административные представления к именам 1С, заодно переписав функцию преобразования имен объектов SQL в 1С, на более стабильную
    • добавить определение селективности измерений регистра, как с признаком индексирования, так и без оного
  • написана в качестве “ночного скетча”, вместе с тестовой конфигурацией
  • фактически приглашение Вас к дискуссии и как минимум к прочтению статей MSDN и других технических ресурсов по работе с СУБД (если Вы конечно это еще не сделали).

 Спасибо:

  1. Кудякову Германуза пример использования формы подборы строки соединения SQL
  2. Дмитрию Ощепкову за GameWithFire.
  3. Пользователям форума 1С++ - помимо старых статей про индексы в 1с 7.7 + DBF,  еще и за “Концепт ускорения регистров” 

UPDATE 20110512:

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

обработки тестировались на 1С 8.1.15 и 8.2.13 с сервером MS SQL 2008 
запускались на конфигурациях: УПП, УТ 10, БП, ЗУП, Логистика 3.0, УТ 11, Розница. 

по итогам тестирования на конец дня 12 мая 2011:

исправлено:

  • ошибка при наличии отсутствующего индекса в ЖурналахДокументов - добавлен анализ Граф журнала
  • ошибка с определением индексированности реквизита в РегистрахСведений - добавлен анализ признака Ведущее

 

выявлено к доработке:

  • необходима документация и примеры по использованию обработки в отдельной публикации
  • необходимо исправить анализ индексов по РегистрамБухгалтерии 
  • необходимо группировать объекты в дерево на обоих панелях (Отсутствующие и избыточные индексы)
  • необходимо расширить принятие решения об удалении ИзбыточногоИндекса с "Удалить" на "Удалить индексацию реквизита"

 

UPDATE 20110513

исправлено:

  • "масштаб трагедии" теперь формируется в виде дерева на обоих панелях
  • обработка теперь не просто говорит "Удалить" избыточный индекс, но и на каком реквизите

 

в работе:

  • подготовка документации
  • анализ системных индексов регистра бухгалтерии по счетам

UPDATE 20111014

новый функционал:

попытка поиска ошибочного запроса и представление его в терминах 1С метаданных

в работе:

  • подготовка документации

Файлы

Наименование Файл Версия Размер Кол. Скачив.
обработка ОтсутствуюшиеИндексы (RC2) версия для 8.2
.epf 130,89Kb
19.02.13
228
.epf 130,89Kb 228 Скачать
набор обработок ОтсутствуюшиеИндексы (beta)
.zip 41,26Kb
19.02.13
123
.zip 41,26Kb 123 Скачать
Тестовая конфигурация 8.2
.cf 22,98Kb
19.02.13
136
.cf 22,98Kb 136 Скачать

См. также

Лучшие комментарии

43. desty 14.10.2011 09:21
(40) в ближайший месяц будет проводится рефакторинг кода обработки и заодно нарисую управляемую формочку
Ответили: (44) (45) (67)
+ 2 [ artbear; V_V_V; ]
# Ответить
21. desty 12.05.2011 11:39
К статье добавлен комплект обработки "ОтсутствующиеИндексы" для 8.1 и 8.2

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

имеет статус "бета", но однозначно является "proof of concept"

желающие могут потестировать

P.S. помимо указанных типовых конфигураций обработка тестировалась также и на локальных для меня собственных конфигурациях.
Ответили: (22) (23)
+ 2 [ hulio; artbear; ]
# Ответить
42. desty 14.10.2011 09:20
(41) значится так:
1. любые поля типа Булево индексировать нет необходимости - если SQL предлагает вам их проиндексировать, значит где-то кто-то ошибся в построении запроса (иногда и сама платформа).
2. Исправлять нужно на стороне 1С - например "увод" фильтра по булевому признаку в конец списка условий

(0) выложена версия обработки которая умеет искать ошибочный запрос - версия обработки выполненна только для 1С 8.2
Ответили: (47)
+ 1 [ artbear; ]
# Ответить

Комментарии

1. cleaner_it 18.02.2011 07:12
Отличная статья:)
# Ответить
2. Арчибальд 18.02.2011 09:53
Безотносительно к файлам - сам текст зачетен
# Ответить
3. boggonzikov 18.02.2011 10:38
спб за статью, от себя бы поставил 10+
отдельные 10+ за “Открытие скрытых данных для оптимизации производительности приложений”
Ответили: (7)
# Ответить
6. iceflash 18.02.2011 13:38
И да, скажем постгрес, дб2, Оракал тоже собирают статистику, у них есть механизмы оптимизации плана выполнения и все прочее. Наверное пользователей которые используют не ms sql СУБД очень мало здесь.
Ответили: (7)
# Ответить
7. desty 18.02.2011 14:11
(6)
я пока не нашел каким образом получить статистику от других СУБД - но у меня в планах попробовать что-нибудь с Postgres и DB2 - к сожалению к Ораклу доступа нет.
Ответили: (10)
# Ответить
8. desty 18.02.2011 14:19
(4) еще про поле "Проведен"

Поле "Проведен" штатными средствами не индексируется НИКАК, также как и поле ПометкаУдаления
Однако существует мнение что такой Индекс и не нужен - так как поле имеет низкую селективность - возможные значения то 1 и 0.

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

Единственное что я знаю - "Возможность управления индексами объекта, просится партнерами уже давно на различных семинарах, в том числе и на ТВКВ"
Ответили: (9)
# Ответить
12. desty 18.02.2011 15:01
(0)
автор "Консоли запросов с трассировкой" , которая лежит на партнерском форуме оказывает продублировал ее здесь http://infostart.ru/public/56973/
Ответили: (13)
# Ответить
16. artbear (файл скачал) 18.02.2011 15:21
Итак, по соглашению с автором любые разговоры о лицензионности подобного подхода и 1С в этой ветке считаются оффтопом и будут удаляться!
# Ответить
17. JohnyDeath (файл скачал) 19.02.2011 19:04
Алексей, все хорошо, но вот ссылки на закрытые партнерские форумы удручают
# Ответить
18. desty 19.02.2011 21:44
JohnyDeath пишет:

Алексей, все хорошо, но вот ссылки на закрытые партнерские форумы удручают

(17)
Жень, привет - ссылку поменял на Инфостартовскую.
автор "Консоли запросов с трассировкой" , которая лежит на партнерском форуме оказывает продублировал ее здесь http://infostart.ru/public/56973/
# Ответить
19. winder (файл скачал) 24.02.2011 20:34
Хорошо бы обработку сделать внешней и именовать объекты и выражение индексов в терминах SQL, что позволит добавлять индексы прямо в MS SQL (для тех объектов или свойств, по которым нельзя настраивать индексирование в 1С).
Я вот так и сделал (еще и для 8.1 к тому же) и затем, добавив индексы в SQL получил дополнительный выигрыш (в терминах данной обработки), который нельзя было получить средствами 1С.
# Ответить
20. desty 25.02.2011 16:24
winder пишет:
Хорошо бы обработку сделать внешней и именовать объекты и выражение индексов в терминах SQL, что позволит добавлять индексы прямо в MS SQL (для тех объектов или свойств, по которым нельзя настраивать индексирование в 1С).
Я вот так и сделал (еще и для 8.1 к тому же) и затем, добавив индексы в SQL получил дополнительный выигрыш (в терминах данной обработки), который нельзя было получить средствами 1С.


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

С другой стороны Вы правы в одном: в терминах MSSQL отсутствующие индексы были бы хороши для создания индекса уже средствами SQL.
Однако писать такой инструмент считаю нецелесообразным: тот человек который умеет создавать индексы средствами MSSQL однозначно помнит что:
1. еще в SQL 2000 был такой хитрый инструмент как Index Tunning Wizard
2. начиная с SQL 2005 - есть такой комплект как Performance Dashboard Reports
оба этих инструмента помимо информации об отсутствующем индексе, если мне не изменяет память, еще и создадут текст SQL скрипта типа ALTER TABLE, CREATE INDEX и т.д.
# Ответить
21. desty 12.05.2011 11:39
К статье добавлен комплект обработки "ОтсутствующиеИндексы" для 8.1 и 8.2

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

имеет статус "бета", но однозначно является "proof of concept"

желающие могут потестировать

P.S. помимо указанных типовых конфигураций обработка тестировалась также и на локальных для меня собственных конфигурациях.
Ответили: (22) (23)
+ 2 [ hulio; artbear; ]
# Ответить
22. artbear (файл скачал) 12.05.2011 15:16
(21) Запустил на 2005, строку подключения указал, проверил.
Запустил док на проведение, потом нажал выполнить в обработке.
И нифига :(
что не так?
ЗЫ если у тебя есть время, жду в аське или скайпе :)
# Ответить
23. artbear (файл скачал) 12.05.2011 15:32
(21) Не совсем понятна методика использования обработки :(
Работаем так: открываем обработку, задаем строку запуска, нажимаем Выполнить, далее что-то делаем в базе (например, выполняем запрос), и еще раз нажимаем Выполнить и смотрим результаты, верно или нет?
Если запустить в рабочей/боевой базе, получим результаты работы всех пользователей, верно?
Опиши параметры минимальные количества, селективность и "индекс использовался не ранее", плиз.
Ответили: (24)
# Ответить
24. desty 12.05.2011 15:44
(23)
методика пока такая:

0. если рабочая база крутиться на MS SQL сервере
1а. открываем обработку в рабочей базе - можно даже в момент реальной работы, хоть прямо сейчас
2б. прогоняем шаблоны тестирования в тестовой базе (пусть даже ручные), открываем обработку в тестовой базе
2. вводим строку соединения к базе SQL рабочей конфигурации
3. нажимаем Выполнить
4. видим "масштаб трагедии"
5. принимаем решение о реакции на ситуацию в каждом конкретном объекте метаданных (TODO надо деревом сделать)

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


PS С избыточными индексами чуть сложнее.
PSS Кстати Артур может имеет смысл обработку выложить отдельной публикацией с примерами и картинками - я уже сейчас могу сказать что она близка с стабильному инструменту диагностики.
+ 1 [ artbear; ]
# Ответить
25. desty 12.05.2011 16:11
обновил обработку:

1. добавлен анализ Граф журналов
2. исправлены фильтры по умолчанию: фильтр по дате индекса, количеству таблиц и размеру таблицы убран.
# Ответить
26. desty 12.05.2011 16:21
с закладкой избыточные индексы советую всем быть аккуратней

избыточным индексом обработка считает:

1. если реквизит мы проиндексировали
2. если мы активно пишем объект - операции записи
3. мы почти совсем не ищем по проиндексированному реквизиту (отношение чтения к записи менее 0.05)

тогда такой индекс считается избыточным

индексное выражение это:

например на регистре сведений у вас показывает удалить Индекс с индексным выражением Товар,Склад,Организация
это означает что надо убрать индексацию реквизита Товар - так как в индексном выражении индексируемый реквизит всегда первый
TODO: Добавлю в обработка не просто решение Удалить, а Удалить индексацию <ИмяРеквизита>

P.S. Буду готовить отдельную публикацию с картинками и документацией ;-)
Ответили: (27) (28)
+ 1 [ chemezov; ]
# Ответить
27. artbear (файл скачал) 12.05.2011 17:47
(26) Да, требуется отдельная публикация.
# Ответить
28. artbear (файл скачал) 13.05.2011 07:46
(26) ИМХО было бы удобно добавить в таблицу отсутствующих индексов сортировку по умолчанию по количеству вызовов (по убыванию)
# Ответить
29. artbear (файл скачал) 13.05.2011 07:52
Скачал только что последнюю версию (исправления по ведущим измерениям), запускаю на типовой БП 1.6.25.9,
для избыточных индексов выдаются ошибки.
В Режиме отладки следующие сообщения:
Для Справочник.Номенклатура
не найдено определение индекса _ReferenceChangeRec463_ByNodeMessage_RN
Для Справочник.ДоговорыКонтрагентов
не найдено определение индекса _ReferenceChangeRec385_ByDataKey_RRN
Для Справочник.ДоговорыКонтрагентов
не найдено определение индекса _ReferenceChangeRec385_ByNodeMessage_RN
Для Справочник.ФизическиеЛица
не найдено определение индекса _ReferenceChangeRec719_ByDataKey_RRN
Для Справочник.ФизическиеЛица
не найдено определение индекса _ReferenceChangeRec719_ByNodeMessage_RN
Для Справочник.Контрагенты
не найдено определение индекса _ReferenceChangeRec438_ByNodeMessage_RN
Для Справочник.Контрагенты
не найдено определение индекса _ReferenceChangeRec438_ByDataKey_RRN
Для Справочник.Контрагенты
не найдено определение индекса _Reference24_ByField437_SR
Для Справочник.Контрагенты
не найдено определение индекса _Reference24_ByParentField432_RLRSR
Для Справочник.Контрагенты
не найдено определение индекса _Reference24_ByField433_RSR
Для Справочник.Контрагенты
не найдено определение индекса _Reference24_ByParentField436_RLSR
Для Справочник.Контрагенты
не найдено определение индекса _Reference24_ParentCode_RLSR
Для Справочник.Контрагенты
не найдено определение индекса _Reference24_Code_SR
Для Справочник.Контрагенты
не найдено определение индекса _Reference24_ByParentField434_RLSR
Для Справочник.Контрагенты
не найдено определение индекса _Reference24_Descr_SR
Для Справочник.Номенклатура
не найдено определение индекса _ReferenceChangeRec463_ByDataKey_RRN
Для Справочник.Контрагенты
не найдено определение индекса _Reference24_ParentDescr_RLSR
Для Справочник.Контрагенты
не найдено определение индекса _Reference24_ByField435_SR
====
Что и как исправлять?
Ответили: (31)
# Ответить
30. artbear (файл скачал) 13.05.2011 08:05
Баг- при повторном нажатии Выполнить в таблице Избыточных индексов очистка таблицы не происходит, строки добавляются в конец таблицы.
В итоге дублирование :(
Ответили: (31)
# Ответить
31. desty 13.05.2011 09:40
(30) у тебя время на час вперед относительно Москвы ? :D
(29) Это значит я вчера поторопился.

сейчас на свежую голову сделаю все по нормальному.
# Ответить
32. desty 13.05.2011 09:44
artbear пишет:

Скачал только что последнюю версию (исправления по ведущим измерениям), запускаю на типовой БП 1.6.25.9,

Что и как исправлять?


на всякий случай проверь - база SQL точно та ?
код поиска не исправлялся.

в верхнем левом углу пишется Имя базы SQL из которой в текущий момент забираются отсутствующие индексы
# Ответить
33. desty 13.05.2011 11:49
обновил обработку:

* добавлено представление в виде дерева - показалось удобным
* добавлена сортировка по умолчанию по КоличествуВызовов
* добавил решение по избыточному индексу типа "Удалить <ИмяРеквизита>"
Ответили: (34) (35) (36) (61) (64)
# Ответить
34. artbear (файл скачал) 13.05.2011 13:55
(33) Если есть время, жду в аське для продолжения вчерашнего :)
ЗЫ ИМХО по результатам нашего общения можно будет выложить какие-то примеры с конкретными рекомендациями по решению проблем с индексами :)
Ответили: (37) (51)
# Ответить
35. artbear (файл скачал) 13.05.2011 14:01
(33) По дереву - наружная сортировка нормальная (по убывания), а внутри узла - нет (не по убыванию) :(
Например, в таблице отсутствующих индексов.
Ответили: (37)
# Ответить
36. artbear (файл скачал) 13.05.2011 14:03
(33) В Таблице избыточных индексов цифры есть только внутри узла, на верхних узлах дерева цифр нет.
В связи с этим сортировка сделана не пойми как :( и очень сложно увидеть, какие наибольшие разницы у каких строк :(
Ответили: (37)
# Ответить
37. desty 13.05.2011 15:47
(36) надо еще придумать что там выводить ;-)
(34) я в принципе уже в аське, только у меня тут небольшой аврал на работе, завтра весь день буду в скайпе - народ слиняет в деревню, а весь день дома
(35) уже исправил у себя, кстати также нашел косяк с отображением дерева (поправил уже у себя) - путь к метаданному типа ТабличнаяЧасть отображается без имени табличной части
Ответили: (38)
# Ответить
38. zarucheisky 14.05.2011 16:36
(37)Лешь!
А ведь есть заранее известные моменты, что та, или иная операция не попадет в индекс,
например, используя структуру данных можно сразу на уровне проектирования получить, что запрос изначально будет не оптимален.
1С умеет отдавать структуру индексов таблиц. Т.е. имеет смысл разбить задачу на 2 части:
1) Анализ задач заведомо не попадающих в индекс средствами самой 1С без всяких трассировок (контроль плохих запросов, например);
2) Как раз анализ статистики "правильных" операций.
Ответили: (39)
# Ответить
39. artbear (файл скачал) 15.05.2011 11:20
(38) ОФФ. Мои школьные 5 с плюсом по русскому подсказывают мне, что правильно все-таки "Леш!" :)
# Ответить
40. V_V_V (файл скачал) 27.06.2011 21:54
Эх, обработочку бы еще и для управляемых форм 8.2 ... :D
Ответили: (43)
# Ответить
41. artbear (файл скачал) 20.08.2011 11:25
(0) А все-таки дай ответ на свое же задание (не всем же понятно решение) :
>> Так как поле Проведен недоступно для индексирования из 1С - Вы легко сделаете вывод сами, я надеюсь - как исправить данную ситуацию.
Ответили: (42) (51) (61) (64)
# Ответить
42. desty 14.10.2011 09:20
(41) значится так:
1. любые поля типа Булево индексировать нет необходимости - если SQL предлагает вам их проиндексировать, значит где-то кто-то ошибся в построении запроса (иногда и сама платформа).
2. Исправлять нужно на стороне 1С - например "увод" фильтра по булевому признаку в конец списка условий

(0) выложена версия обработки которая умеет искать ошибочный запрос - версия обработки выполненна только для 1С 8.2
Ответили: (47)
+ 1 [ artbear; ]
# Ответить
43. desty 14.10.2011 09:21
(40) в ближайший месяц будет проводится рефакторинг кода обработки и заодно нарисую управляемую формочку
Ответили: (44) (45) (67)
+ 2 [ artbear; V_V_V; ]
# Ответить
44. V_V_V (файл скачал) 14.10.2011 11:04
(43) Хорошая новость! Я несколько раз пытался что-то свое на ее основе соорудить - ничего не вышло, так и забросил. Подожду авторскую версию. :)
# Ответить
45. V_V_V (файл скачал) 14.10.2011 12:34
(43) В догонку - маленькая просьба. Если будете публиковать обновленную версию отдельной статьей - напишите ЗДЕСЬ в комментариях хоть какое-то сообщение с ссылкой на нее, тогда всем подписавшимся на эту темку придет сообщение по почте... Ну очень НЕ хочется случайно пропустить такое событие... :)
Ответили: (46)
# Ответить
46. desty 14.10.2011 13:01
(45) так и планировалось - обновленная обработка будет отдельной публикацией и естественно ссылка будет опубликована и в самой статье и в комментариях
# Ответить
47. artbear (файл скачал) 17.10.2011 17:10
Дополняя новость из (42), пока автор занят.
Чтобы увидеть сам ошибочный запрос и его план, нужно просто щелкнуть дважды мышкой на строке в таблице "Отсутствующие индексы", откроется новая форма "План запроса отсутствующего индекса".
далее нужно нажать кнопку "Поиск запроса", немного подождать, и увидеть прекрасный результат - сам неверный запрос и его план запроса.
Рекомендую.
ЗЫ участвовал в тестировании этой версии, суперзамечательно сделано!
# Ответить
48. German (файл скачал) 15.11.2011 09:35
49. desty 15.11.2011 10:50
(48) Практика показывает что не все рекомендуемые индексы необходимо создавать, большинство проблем связано с ошибочным кодом на уровне платформы. Посмотри у меня как я план ищу и как "вывод" делаю о причине появления такой рекомендации - может получится сделать покрасивше: но главная идея - не давать возможность создавать все индексы которые предлагает MS SQL.

А так да - "круто". Меня вот давно интересует - когда EI доберется до прямым управлением таблицей фоновых заданий.
# Ответить
50. Maks_Payn 23.11.2011 08:50
Очень познавательная статья! Спасибо!
# Ответить
51. fillin (файл скачал) 06.12.2011 13:21
Спасибо!
Классная штука.

Однако, проблемка есть.

1. Дважды кликаю в таблице "Отсутствующие индексы".
Открывается окно "План запроса отсутствующего индекса". Все поля пустые.
2. Жму "Поиск запроса"
Ошибка:
"{Форма.ПланПроблемногоЗапроса.Форма(41)}: Внутрення ошибка построения запроса SQL
{ВнешняяОбработка.ОтсутствующиеИндексы13.МодульОбъекта(80)}: {ВнешняяОбработка.ОтсутствующиеИндексы13.МодульОбъекта(34)}: Ошибка при вызове метода контекста (ADORecordsetВТаблицуЗначений)
ВызватьИсключение "Внутрення ошибка построения запроса SQL"
В окне сообщений выводится:
"ADOUtils/ : IDispatch error #3105."

Пробовал с десятком разных строк в 2-х разных базах.
Ответили: (52)
# Ответить
52. desty 06.12.2011 14:03
(51) ADOUtils/ : IDispatch error #3105.

эта ошибка явно относиться к GameWithFire.dll и его работой с ADO

навскидку: либо неверная строка подключения, либо DmitrO в библиотеке чего-то не учел.

Для начала я думаю надо "покурить" строку подключения, а потом попробуем с автором библиотеки связаться.
Ответили: (53) (54)
# Ответить
53. fillin (файл скачал) 06.12.2011 14:18
(52) Ну, да.
Ошибку дает с такой строкой (win-авторизация):
Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=1c81;Data Source=KE-MAIN-S04;Initial File Name="";Server SPN=""
А с такой все ОК:
Provider=SQLNCLI10.1;Integrated Security="";Persist Security Info=False;User ID=1c;Initial Catalog=1c81;Data Source=KE-MAIN-S04;Initial File Name="";Server SPN=""

Точнее с последней строкой оно внятно говорит, что запрос не найден.
Ответили: (54)
# Ответить
54. fillin (файл скачал) 07.12.2011 13:43
(52) Связался с автором?
Я хочу win-авторизацию, как в 1-й строке. (см. (53))
Ответили: (56)
# Ответить
56. desty 16.12.2011 16:33
(54) Беглый поиск в Гугле по ключевой фразе "IDispatch error #3105 on connect" дает нам предварительное ощущение - что дело в строке подключения для ADO.

Значит надо правильно ее составить с учетом Win авторизации.

Самый простой способ который использую я - еще со времен 1С++ http://www.connectionstrings.com/
# Ответить
57. kiros (файл скачал) 24.01.2012 17:33
Ребяты, вы просто молодцы!!! Давно искал что нибудь такое полезное, беру на заметку, как только ручки доберутся все проверю, очень интересно на сколько в моей базе все "криво" :)
# Ответить
58. w-divin (файл скачал) 17.05.2012 19:35
{ВнешняяОбработка.ОтсутствующиеИндексы13.МодульОбъекта(80)}: У объекта Документ.СверкаВзаиморасчетов не найдено определение поля _Fld7511
		ВызватьИсключение ТекстОшибки;

а такое с чем связано и как лечится?
При включенной "отладке" валится на:
{ВнешняяОбработка.ОтсутствующиеИндексы13.МодульОбъекта(698)}: Индекс находится за границами массива
	Если путьМетаданного[0] = "Системные" Тогда Возврат ""; КонецЕсли;

в сообщениях последнее:
Обработка Справочник.Номенклатура ([microtron].[dbo].[_Reference49]) ...
Ошибка разбора метаданных объекта  неверное имя самого объекта

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

еще вот такое получил на рабочей базе:
{ВнешняяОбработка.ОтсутствующиеИндексы13.МодульОбъекта(80)}: {ВнешняяОбработка.ОтсутствующиеИндексы13.МодульОбъекта(33)}: Ошибка при вызове метода контекста (Execute): Произошла исключительная ситуация (Microsoft OLE DB Provider for SQL Server): Operand data type ntext is invalid for count operator.
		ВызватьИсключение ТекстОшибки;

А при поиске избыточных выпадает вот такое:
{ВнешняяОбработка.ОтсутствующиеИндексы13.МодульОбъекта(80)}: В описании метаданных не найдено описание объекта v8users
		ВызватьИсключение ТекстОшибки;
Ответили: (59) (61)
# Ответить
59. desty 21.05.2012 09:35
(58) навскидку - в Ваших метаданных есть что-то такое что я не учитываю при разборе из внутреннего SQL представления

точно известно что для исключения последней ошибки с v8users
необходимо дополнить процедуру ДобавитьСистемныеТаблицы1С()
следующим кодом

    конфигурация.ИмяТаблицыХранения = "v8users";
    конфигурация.ИмяТаблицы = "Системные.Пользователи";
    конфигурация.Метаданные = "Системные.Пользователи";
    конфигурация.Назначение = "Основная";
КонецПроцедуры
...Показать Скрыть


по остальным ошибкам надо думать - но для начала проверьте строку подключения: точно с рабочей базой соединяетесь ?
Ответили: (61)
# Ответить
61. w-divin (файл скачал) 26.05.2012 12:49
(59) +(58)
при попытке поиска неоптимального запроса иногда выскакивает вот такое:
{Форма.ПланПроблемногоЗапроса.Форма(41)}: Внутрення ошибка построения запроса SQL
{ВнешняяОбработка.ОтсутствующиеИндексы13.МодульОбъекта(80)}: {ВнешняяОбработка.ОтсутствующиеИндексы13.МодульОбъекта(33)}: Ошибка при вызове метода контекста (Execute): Произошла исключительная ситуация (Microsoft OLE DB Provider for SQL Server): XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.
		ВызватьИсключение "Внутрення ошибка построения запроса SQL

галка "отладка" выдает в сообщения следующее:
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan[MissingIndexes and MissingIndexes/MissingIndexGroup/MissingIndex[@Database = "[microtron]" and @Table = "[_AccumRgTn3946]" and ColumnGroup[@Usage = "EQUALITY" and Column[@Name = "[_Fld3938RRef]"]] and ColumnGroup[@Usage = "INEQUALITY" and Column[@Name = "[_Period]"]] and ColumnGroup[@Usage = "INCLUDE" and Column[@Name = "[_Fld3929RRef]"] and Column[@Name = "[_Fld3931RRef]"] and Column[@Name = "[_Fld3939]"] and Column[@Name = "[_Fld3940]"] and Column[@Name = "[_Fld3941]"]]]]
{ВнешняяОбработка.ОтсутствующиеИндексы13.МодульОбъекта(33)}: Ошибка при вызове метода контекста (Execute): Произошла исключительная ситуация (Microsoft OLE DB Provider for SQL Server): XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.
SELECT TOP 1
	s.total_elapsed_time/1000 as [ЗатраченноеВремя]
	,(s.total_elapsed_time/s.execution_count)/1000 as [СреднееВремяВыполнения]
	,s.last_execution_time as [ПоследнееВремяВызова]
	,SUBSTRING(t.text,s.statement_start_offset/2 +1, 
                 (CASE WHEN s.statement_end_offset = -1 
                       THEN LEN(CONVERT(nvarchar(max), t.text)) * 2 
                       ELSE s.statement_end_offset end -
                            s.statement_start_offset
                 )/2
             ) as [ТекстЗапроса]
	,s.execution_count as [КоличествоВызовов]
	,p.query_plan as [ПланЗапроса]
	,DB_name(p.dbid) as [ИмяБазы]
FROM sys.dm_exec_query_stats s 
	CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS p
	CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
WHERE 
	s.last_execution_time >= {ts '2012-05-25 11:14:11'} 
	AND
	p.query_plan.exist(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
						/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/Query­Plan[MissingIndexes and MissingIndexes/MissingIndexGroup/MissingIndex[@Database = "[microtron]" and @Table = "[_AccumRgTn3946]" and ColumnGroup[@Usage = "EQUALITY" and Column[@Name = "[_Fld3938RRef]"]] and ColumnGroup[@Usage = "INEQUALITY" and Column[@Name = "[_Period]"]] and ColumnGroup[@Usage = "INCLUDE" and Column[@Name = "[_Fld3929RRef]"] and Column[@Name = "[_Fld3931RRef]"] and Column[@Name = "[_Fld3939]"] and Column[@Name = "[_Fld3940]"] and Column[@Name = "[_Fld3941]"]]]]') = 1
ORDER BY s.last_execution_time DESC

...Показать Скрыть
Ответили: (62)
# Ответить
62. desty 28.05.2012 12:26
(61) не могли бы вы сохранить дерево ОтсутствующихИндексов в xls (или mxl) и выслать на bucket(at)lustin(on)org - очень интересно что же за объект у вас вызывает такое поведение
Ответили: (63) (64)
# Ответить
63. w-divin (файл скачал) 30.05.2012 12:04
(62) отправил
# Ответить
64. w-divin (файл скачал) 15.06.2012 18:23
(62) не нашел закономерности, но при поиске запросов иногда продолжает выскакивать:
{Форма.ПланПроблемногоЗапроса.Форма(41)}: Внутрення ошибка построения запроса SQL
{ВнешняяОбработка.ОтсутствующиеИндексы13.МодульОбъекта(80)}: {ВнешняяОбработка.ОтсутствующиеИндексы13.МодульОбъекта(33)}: 
Ошибка при вызове метода контекста (Execute): 
Произошла исключительная ситуация (Microsoft OLE DB Provider for SQL Server): 
XML datatype instance has too many levels of nested nodes. 
Maximum allowed depth is 128 levels.
ВызватьИсключение "Внутрення ошибка построения запроса SQL
...Показать Скрыть
# Ответить
65. CheBurator 13.08.2014 18:45
ознакомился... хорошо бы мою wms помониторит на предмет описанного втсатье...
Ответили: (66)
# Ответить
66. lustin 13.08.2014 19:17
(65) CheBurator, ну ты даешь. Я думал, что ты подобное уже и внедрил. Тебе моя помощь нужна ?
# Ответить
67. lustin 28.07.2015 03:12
(43) desty, обработка понемногу начинает мигрировать в формат конфигурации https://github.com/silverbulleters/vanessa-dbaqa
Ответили: (68)
# Ответить
68. adminitltd (файл скачал) 16.11.2015 17:22
(67) lustin, не могу понять где взять последнюю версию обработки. Перехожу вот по этой ссылке https://github.com/silverbulleters/vanessa-dbaqa ничего там найти не могу
# Ответить
69. lustin 16.11.2015 20:46
Обработку тут - а на github начинается работа по конфигурации. Собственно на этой конфигурации обкатывается еще и BDD концепция.

То есть - работающая обработка здесь. Ссылка на github дана для очень сильно интересующихся ;-), тех кого волнует во что это превратиться в будущем
# Ответить
70. Gilev.Vyacheslav (файл скачал) 21.01.2016 21:49
ну и мы тоже умеем http://www.gilev.ru/sqlsize/ немного, раз тут все отмечаются
# Ответить
Внимание! За постинг в данном форуме $m не начисляются.
Внимание! Для написания сообщения необходимо авторизоваться
Текст сообщения*
Прикрепить файл






IE 2016