В таблице reference отсутствует индекс 0 1с
Чаще всего при общении с людьми мы так или иначе оцениваем их, относим их к различным категориям - причем категории мы создаем в принципе сами. Я думаю человеку удобней фиксировать свою оценку окружающих людей навешивая им ярлыки.
Причем те самые категории/ярлыки можно присвоить на основе того как ответит человек на тот или иной вопрос. Подтверждением тому (лично для меня), является например отношение к индексам базы данных.
Итак перед началом ознакомления со статьей хочу заметить, что на моем пути встречались следующие категории людей по типу отношения к индексам СУБД:
- Бог его знает что такое индекс - отличительная особенность: круглые, удивленные глаза, как реакция на фразу “Запрос не попадает в индекс”.
- Каждое поле таблицы (реквизит объекта) необходимо проанализировать на предмет необходимости индексации- отличительная особенность: постоянный просмотр планов запроса, даже в случае выборки одной строки из таблицы с гарантированным размером не более 10 строк.
- Вы слишком сильно уповаете на индексы - отличительная особенность: гарантированная реакция с ярко выраженным апломбом, в виде “Оно вам не поможет”, при попытке коллеги просмотра административных представлений MS SQL илиплана выполнения сложного запроса в терминах SQL
- С индексами в каждом конкретном случае надо включать голову - компромиссный тип людей, отличительная особенность - знает что такое селективность, и понимает почему JOIN можно заменить на UNION и в чем будет выйгрыш.
Тут ответ прост - скорее к последнему типу, по причинам:
- я считаю что про индексы надо знать
- я знаю что анализировать необходимость индексов по полям таблицы необходимо, но не по всем полям, а на этапе проектирования только по основным, в дальнейшем по ситуации.
- я знаю что не всегда индексы - это самая главная проблема производительности; также ведь есть код на 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 “хотел” использовать индекс, но не нашел его.
- то какую неоправданную нагрузку Вы осуществляете на СУБД
- что будет если вы создадите этот отсутствующий индекс - на сколько сократятся ваши затраты.
Сейчас попробуем на довольно простом примере убедиться что это очень просто:
Тестовый пример
Для начала развернем тестовую конфигурацию приложенную к данной статье.
Развертывать необходимо в клиент-серверном режиме с использованием сервера СУБД MS-SQL версии 2005 и выше.
Метаданные в данном случае совершенно простейшие:
Однако обращаю внимание - реквизит ДокументОснование - НЕ проиндексирован. Я про него забыл ;-).
При запуске конфигурации будет запущен генератор документов - он создаст Вам сколько угодно документов. По умолчанию я выставляю 10000 документов
Генератор документов создает последовательно документы - в котором каждый следующий документ имеет основанием предыдущий по МоментуВремени документ такого же типа.
В конфигурацию встроена обработка “Отсутствующие индексы SQL” .
И сейчас она будет показывать пустую информацию (или ошибку если Вы забыли проинсталировать GameWithFire.dll)
Давайте попробуем выполнить теперь несколько типовых запросов на выборку документа основания:
Открываем консоль запросов (причем я всем последнее время помимо UCR советую также Консоль запросов с трассировкой), и выполняем набор простых запросов.
ВЫБРАТЬ Документ1.Ссылка
ИЗ Документ.Документ1 КАК Документ1
ГДЕ Документ1.ДокументОснование = &ДокументОснование
;
ВЫБРАТЬ Документ1.Ссылка
ИЗ Документ.Документ1 КАК Документ1
ГДЕ Документ1.ДокументОснование = &ДокументОснование И НЕ Проведен
;
ВЫБРАТЬ Документ1.Ссылка
ИЗ Документ.Документ1 КАК Документ1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.Документ1 КАК Документ1Основание
ПО Документ1.ДокументОснование = Документ1Основание.Ссылка
;
А вот теперь в итоге посмотрите на запросы которые вы выполнили
и на ту информацию которую Вам сообщил SQL:Итак Ваше приложение выполняло запросы которые:
- осуществляли работу с основной таблицей документ с ИД “Документ1”
- вложенность не использовали (поле Состав предлагаемого индекса пустое)
- накладывали фильтры:
- на равенство (поле Поиск) - по реквизиту ДокументОснование
- на больше/меньше/НЕ равно - по реквизиту Проведен
- затраты на такие запросы сводно составили по этому документу 2683 “попугая” = [средняя стоимость одного запроса * средний процент выйгрыша * (количество пользовательских операций поиска + количество пользовательских операций сканирования)]
- если бы вы создали подобный индекс вы бы сократили свои издержки на99%
Отдельно хотел бы заметить - после того как Вы вы выставите Индексацию у реквизита ДокументОснование - при выполнении тех же самых запросов - SQL уже не будет Вам сигнализировать о том “что кажется Вы забыли проиндексировать поля таблицы”.
В обычный погожий денек, любимый всеми понедельник, не прошло и 10 минут после начала рабочего дня, как начали поступать первые тревожные звоночки.
"У нас ничего не работает!!"
Ну что могло случиться на выходных? Все работало и вдруг перестало.
Ознакомившись с работой пользователей, выяснил, что основная масса запросов идет к регистрам с распухшим (для текущей ситуации) количеством записей - четыре регистра накопления с количеством от 1,5 до 46 млн. записей.
Сразу подумал про индексы для этих регистров, т.к. база переработанная и регистры добавленные. Поскольку всех нюансов еще не знаю, т.к. стаж работы в данной организации без году неделя, открываю конфигуратор. Так и есть - ни одного индекса по полям, участвующим в отборах. Передав привет создателю данных объектов метаданных, добавляем индексы. После обновления конфигурации, ситуация несколько выравнивается и запросы к "пухлым" регистрам идут пошустрее, но тормозит уже все вокруг: прежние "быстрые" отчеты, проведение документов, стандартные функции с обращением к БД.
Ну и как вы наверное уже догадались, для большинства объектов базы 1С в СУБД физически не существовало индексов, что и показала нам MS SQL Studio. В связи с чем, это могло произойти так и не понял. Причем индексов не было на двух базах MSSQL. Есть подозрения, что базы входили в режим восстановления, возможно это и явилось причиной.
Первым пришедшим на ум решением, было достать описание метаданных (скрипт создания) из "живых" СУБД с наличием индексов и позаимствовав оттуда скрипты создания индексов просто выполнить их в "больных" базах. Но опыта работы с MS SQL не очень много, и данные потуги потерпели фиаско. В итоге пришла мысль создать скрипты создания индексов из структуры хранения базы данных 1С.
В связи с этим на свет и появился код для "Инструментов разработчика", коим и поспешил поделиться с сообществом.
Результат выполнения - строка, которую использовал в MS SQL Studio, чтобы отслеживать прогресс выполнения создания индексов.
В код добавлена возможность создавать только отсутствующие в СУБД индексы. Для этого используем подключение ADO и анализ имеющихся в СУБД индексов.
Занимаюсь разработкой конфигураций на основе платформы 1С: Предприятие. При разворачивании копии базы сформированной средствами 1С: Предприятие регулярно появлялась ошибка:
В процессе обновления информационной базы произошла критическая ошибка
по причине:
Попытка вставки неуникального значения в уникальный индекс:
Microsoft SQL Server Native Client 11.0: Выполнение инструкции CREATE UNIQUE INDEX прервано, поскольку обнаружен повторяющийся ключ для объекта с именем «dbo._AccRg2024NG» и индекса с именем "_AccRg2024_ByPeriod_TRNNG". Повторяющееся значение ключа: (сен 30 4013 12:00AM, 0x0000001c, 0x83fd001b78e2ed3011e342e2cb8d7e1c, 1).
HRESULT=80040E2F, SQLSrvr: SQLSTATE=23000, state=1, Severity=10, native=1505, line=1Так как копии разворачивались только в целях внесения изменений в конфигурацию и тестирования, этой ошибке не предавали особого значения, пока не понадобилось добавить предопределенный счет. При обновлении конфигурации происходила реструктуризация регистра бухгалтерии и вываливалась данная не приглядная ошибка. Дальнейшее обновление прекращалось. Гугление данного вопроса результатов не дало. пришлось разбираться самим.
Выяснение имени таблицы 1С связанной с объектом определяется функцией ПолучитьСтруктуруХраненияБазыДанных, там же можно поглядеть и состав индексов.Как оказалось данные индексы в таблице SQL "_AccRg2024" отсутствовали физически. При дальнейшем анализе данных уже средствами SQL выяснилось, что не уникальными были номера записей в разрезе Периода — [_Period], регистратора — [_RecorderRRef] и номер записи [_LineNo], из за чего и не происходила реструктуризация таблицы. Кто и как умудрился удалить эти индексы история умалчивает, данный факт восстановлению не подлежит.
Вылечилась данная ситуация следующим запросом:
Изначально определяются неуникальные записи, выбираются регистраторы и в цикле происходит пере нумерация строк.После этого, уже средствами 1С, выполнилось тестирование базы с режимом «Реструктуризация таблиц информационной базы», данная процедура пересоздала индексы в таблице, и дальнейшие манипуляции, при работе с метаданными конфигурации, стали происходить без каких либо ошибок.
Если база открывается то выгрузить для перехода в сервис, во фреше загрузить, потом выгрузить уже в обновленной версии - круто? При тестировании выбери только реструктуризация как варик. Если не чего не поможет WinHex и погнал )))
(9) там нет. если отменить все изм, и реструктуризация не катит, загрузка CF со структурой до обновления не помогает. тогда только hex
(3) выгрузить конфу в файлы, в руте будет файло с идентификатором, по идентификатору находишь в тул1сд че за константа
Если база открывается в режиме предприятия то создаешь пустую базу и выгружаешь все данные через XML.
На битых данных будет падение выгрузки - их пропускаешь(31) Спасибо, не сомневаюсь. Вопрос только в том чтобы найти нужную. Да ищу, параллельно пробую спрашивать.
(32) Тебе нужно попробовать с помощью Tool_1CD удалить таблицу CONFIGSAVE
По всей видимости твоя база упала в момент реструктуризации.То есть таблица CONFIG должна быть живая.
(29) >Изначально думал поднять из сф новую базу и воспользоваться ПолучитьСтруктуруХраненияБазыДанных()
В базах даже с одинаковой конфигурацией будут разные идентификаторы.
То есть ПолучитьСтруктуруХраненияБазыДанных() даст разные данные.
это та самая схема в которой нет моей константы? как понять формат этого чтобы корректно внести данные?
таблица _CONST30015 в файле присутвует
(39) таблица CONFIGSAVE пуста, это как я понимаю копия конфигурации которую надо будет применить(47) Пример как такое делать есть ?
У меня пару месяцев назад был подобный вопрос с базой.
Тоже ругалось на отсутствие в схеме базы данных.
Я не нашел способ редактировать DBSCHEMA и решил задачу выгрузкой данных через XML.
(51) Если нужно и не найдешь стукнись на мыло. Мыло в профиле. Сброшу.
Я не помню уже где скачивал.
(57) это обычный текст, не надо его разбирать
При желании, можно в json конвертнуть или в xml.. только, не за чем(68) да. не восстанавливает.проверил на 1с8.2. но как-то мне удалось восстановить DBSchema без моего участия. может подменой похожего или пустого и реструктуризацией.
Коллеги, а почему автор не хочет очистить таблицу CONFIGSAVE и просто вернуться к той конфигурации которая была ?
(71)архива с конфой-донором нет.но если типовая то конфу-донора можно сгенерировать.возвожно- это самое простое решение. очистка CONFIGSAVE не поможет. произошло рассогласование метаданных и структуры бд или таблицы проекции метаданных в структуру бд
,кот. хранится в записи dbnames из таблицы params
пока не совсем понимаю в чем различие, правильно ли я понимаю что сами метаданные это то что хранится в таблице CONFIG
структура бд это то что храниться в DBSCHEMA а проекция это то что в храниться в dbnames и dbnames должно соответвовать DBSCHEMA ?нет. не правильно понимаете . в DBSCHEMA хранятся соответствия типов 1с и бд . и естественно DBSCHEMA должна соответствовать dbnames .но все , о чем я питсал относится к 1с8.2 . в 1с8.3 может быть по-другому.
(72) >если типовая то конфу-донора можно сгенерировать
А разве это не приведет к тому же что и создание новой базы с такой же конфой но при этом у объектов будут другие индентиффикаторы ?
(0) А как ты с этой базой столкнулся ? Может все таки есть какие то бэкапы.
Это какое то реальное безумие обновлять базу при полном отсутствии бэкапов.
(75) структура бд в новой базе может быть другой. задача - привести в соответствие метаданные , dbnames и структуру бд. поскольку тулсиди умеет выгружать-загружать конфигурацию замена конфигурации- более простая операция для файловой базы чем редактирование dbnames или изменение структуры бд.
+(75) а для серверной бд проще изменить структуру бд. и на последнем месте - правка dbnames и DBSCHEMA
Немного разобрался как свзяаны талицы. нашел свою константу в DBNames. Может ктото подскажет как ее отредактировать?
(81 )тулсиди вроде умеет выгружать- загружать таблицы. выгрузите парамс отредактируйте запись DBNames. загрузите обратно. если в DBNames будет абракадебра - то разожмите-сожмите ее c помощью v8unpack
Мне как то попадалась база с такими симптомами, у меня сложилось впечатление что при обновлении базы записалась новая dbNames? а новая dbschema не записалась, поэтому и ругается и даже если если исправите проблему с этой конкретной константой, потом будет другая и еще другая константа, и а потом потом справочник итак далее. ДЛя исправления этого надо ручками прописать dbschema для новых и измененных объектов, а задача эта достатчно муторная. Либо проверить соответствие DBShema - dbNames? и все записи которых нет в DBShema удалить из dbNames. Затем что нибудь изменить в конфе чтобы пошел процесс реструктуризации.
Все это возможно сделать если перевести базу в SQL (у меня сработала выгрузка в dt/загрузка из dt), у меня сложилось мнение что на файловой сделать это нельзя. Хотя я уже не помню, но в SQL легче переписывать эти файлы.:(
(94) ДЛя исправления этого надо ручками прописать dbschema для новых и измененных объектов, а задача эта достатчно муторная. как сформировать правильную схему?
Либо проверить соответствие DBShema - dbNames? да я пока вижу неторое количество новых констант. Как сопоставить пока не поинмаю только начинаю узучатьсди толс умеет вроде следующее, только как пока я не могу применить, по сути надо номер сопоставить с названиями с одной базе и другой и получить соответвие номеров вот как и поулчить я бы тоже хотел понять.
Поле ввода «Файл соответствия номеров» и кнопка «Замена TREF»
Иногда в процессе восстановления возникает необходимость переноса таблиц из одной базы в другую базу с такой же конфигурацией, но с несовпадающей нумерацией в DBNames. Например, разрушена таблица в центральной базе, но нужная таблица есть в периферийной базе. Кроме того, что в таких базах не совпадают имена таблиц и полей, которую можно решить правкой файла описания таблицы, есть еще проблема несовпадения типов ссылок, которые хранятся в полях с окончанием "TREF". Подробности описаны в разделе "Структура информационной базы 1С". Данный инструмент позволяет произвести замену всех значений во всех таблицах базы в полях с окончанием TREF. Список замен должен содержаться в файле, выбираемом в поле ввода. Файл представляет собой текстовый файл. В каждой строке файла содержатся два числа, разделенных табуляцией. Второе число - заменяемое. Все поля, содержащие такое значение, заменяются на первое число строки.
Все замены производятся без изменения индексов в базе!
Самый простой путь, удаляйте все из DBNames на что ругается (напишите сравнение dbnames и dbshema). Так вы по сути вернетесь к базе до обновления. ТОлько для этого надо еще все конфиги вернуть к релизу до обновления (если это типовая база), то это легко. Если все сделаете верно, то с вероятностью 95 % база снова запустится, и будет как до обновления.
я не знаю с какого релиза обновлялись.
мне вот удаление конкреной константы не помогло. хотя не исключаю что к я уже чет напутал. ладно, завтра со свежей головой
Я прочел множество различной документации по современным методам работы с SQL Server, утверждающей, что каждая таблица должна иметь кластеризованный индекс и не быть кучей с некаластеризованными индексами. Большинство источников отмечает административную выгоду от использования кластеризованных индексов. Но есть ли в этом какое-то влияние на производительность и другие положительные или отрицательные стороны?
Решение
Для проверки производительности каждого варианта мы создадим две идентичные таблицы с одним лишь отличием — в одной будет первичный ключ, созданный на основе кластеризованного индекса, а в другой первичный ключ будет создан на основе некластеризованного индекса и данные останутся в виде кучи. Ниже приведен скрипт, для создания тестовых таблиц и заполнения их данными.
Первое, что я сразу заметил, — куча использовала больше места, т.к. таблица и индекс являются раздельными структурами. При наличии кластеризованного индекса данные хранятся в листьях индекса и поэтому используют меньше места. Ниже представлена таблица, показывающая объем использованного пространства каждым объектом по окончании загрузки данных.
Таблица Индекс Использовано (КБ) Зарезервировано (КБ) Число строк testtable PK__testtabl__357D0D3E3D086A66 257952 257992 4999999 testtable2 HEAP 256992 257032 4999999 testtable2 PK__testtabl__357D0D3F2CBA35D8 89432 89608 4999999
После того, как первичные данные были загружены, приступим ко второму этапу загрузки и посмотрим на производительность с помощью SQL Profiler, а также проверим использованное пространство. Ниже приведен скрипт, загружающий дополнительные 100 000 записей в каждую таблицу.Для начала посмотрим на использованное пространство, которое, как и ожидалось (ведь страницы были наполнены до конца и ничего не удалялось), увеличилось пропорционально для каждого объекта. Обе таблицы увеличились в размере примерно одинаково.
Таблица Индекс Использовано (КБ) Зарезервировано (КБ) Число строк testtable PK__testtabl__357D0D3E3D086A66 263128 263176 5099999 testtable2 HEAP 262392 262472 5099999 testtable2 PK__testtabl__357D0D3F2CBA35D8 91216 91272 5099999
А вот SQL Profiler нам показывает более интересные вещи. Т.к. при вставке данных в кучу требуется обновление двух объектов — некластеризованного индекса и самой таблицы, то это требует и дополнительных ресурсов от ЦП, плюс ко всему в этот момент выполняется больше операций чтения и записи, чем при вставке данных в таблицу с кластеризованным индексом, следовательно, это потребует немного больше времени.
Тип индекса ЦП (мс) Операций чтения Операций записи Длительность (мс) Кластеризованный 3500 304919 654 11288 Куча 3890 406083 904 11438
Теперь мы беспорядочно удалим часть данных и выполним вставку еще 100 000 записей, чтобы проверить как наличие пустого пространства на некоторых страницах скажется на производительности. Скрипт, выполняющий удаление и вставку, приведен ниже.
На этот раз в SQL Profiler видно увеличение разрыва по производительности между двумя таблицами в пользу кластеризованного индекса.
Тип индекса ЦП (мс) Операций чтения Операций записи Длительность (мс) Кластеризованный 3562 304859 653 10334 Куча 4973 422142 7053 13042
Скорее всего, эта разница возникла из-за того, что при вставке записей в кучу СУБД будет искать пустое пространство на каждой странице для размещения в нем данных. Причиной этому служит тот факт, что данные в куче не отсортированы, а значит, вставляемые записи можно размещать куда угодно. Когда речь идет о таблице с кластеризованным индексом, то при наращивании значения первичного ключа вставка всегда осуществляется в конец таблицы. Также стоит обратить внимание на использованное таблицами пространство. В случае с кучей, при повторном использовании пространства, объем занимаемого таблицей места не должен увеличиваться, т.к. мы вставили столько же, сколько и удалили. Ниже приведена информация о таблицах, зафиксированная после выполнения последнего скрипта, в которой видно, что занимаемое кучей пространство не изменилось и это подтверждает высказанное ранее предположение.
Наименование таблицы Наименование индекса Использовано (КБ) Зарезервировано (КБ) Число строк testtable PK__testtabl__357D0D3E3D086A66 268304 268360 4199999 testtable2 HEAP 262392 262472 4199999 testtable2 PK__testtabl__357D0D3F2CBA35D8 93008 93064 4199999
Вывод
В данном исследовании мы убедились, что использование кластеризованного индекса, в сравнении с кучей, дает лучшие результаты по всем замеряемым категориям: ЦП, ввод-вывод и время выполнения. Единственным побочным эффектом является то, что при использовании кластеризованного индекса требуется немного больше пространства. Это пространство можно сократить путем перестройки индекса. В любом случае, прежде чем вносить какие-то изменения в индексы на рабочей системе, необходимо все проверить на вашем оборудовании, чтобы убедиться в улучшении производительности.Читайте также: