Длина ключа индекса превышает максимально допустимую. Решение и рекомендации

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

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

Как найти быстро все проблемные индексы, которые приводят к ошибке?

Как выйти на те поля и таблицы, где мы что-то натворили?

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

Есть, конечно, вариант: искать по совпадению слов в данных, полученных методом ПолучитьСтруктуруХраненияБазыДанных()

Но это не всегда нам поможет быстро найти проблему.

На помощь приходят системные представления SQL сервера.

• sys.index_columns — Содержит одну строку для каждого столбца, являющегося частью индекса.
• sys.columns — Возвращает строку для каждого столбца объекта, имеющего столбцы, например представления или таблицы. В данной таблице есть столбец "max_length" максимальная длинна в байтах. На него мы и будем ориентироваться.
• sys.indexes — Содержит строку для каждого индекса или кучи табличного объекта, такого как таблица, представление или функция с табличным значением.

         sys.dm_db_index_usage_stats — Возвращает количество различных операций с индексами и время,
         которое было затрачено в SQL Server на последнее выполнение операции каждого типа.

Отправной точкой для поиска проблемных мест будет ограничение на индекс для файловой базы 1920 байт.

Выполняя запросы SQL для своей базы, не забудьте указать в начале инструкции USE <Имя базы>;

1) Сделаем первый запрос с рейтингом по размеру и сравним его с контрольной цифрой.

На скрине мы видим три таблицы с именами столбцов и индексов, которые создают нам проблему.

Осталось дело техники, найти эти данные в 1С с помощью ПолучитьСтруктуруХраненияБазыДанных() и откорректировать настройки (уменьшить длину индекса, снять индексирование).

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

Как вариант решения: уменьшаем длину строки, или снимаем индексацию (если индексирование поставлено на всякий случай).

2) Что делать, если мы сделали все, как сказано, а ошибки при обновлении на файловую базу все равно есть, как на скрине?

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

Далее смотрим первые строки и анализируем:

В данном списке мы видим количество индексов на таблицу. Начинаем анализировать сверху.

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

Т.е. нам необходимо расшифровать — какие основные поля и сколько раз поля входят в индекс.

На практике оказалось, что наш вариант под номером два.

Как решение: снятие индексации с тех полей, которые не участвуют в отборах, выборках, связях и которые проиндексированы на всякий случай.

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

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

Данная методика позволила быстро выявить проблемные индексы, столбцы которые приводили к ошибке.

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

Ответ на вопрос, какие поля индексировать, и условие создания индекса можно посмотреть в статье:

https://technet.microsoft.com/ru-ru/library/ms191195%28v=sql.105%29.aspx

Теперь нам необходимо определить неиспользуемые индексы.

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

Или мы нашли множество некластерных индексов в таблице, а не знаем, какие нам нужны, а какие нет.

В данном случае можно выполнить следующий запрос с выводом неиспользуемых индексов. Обратите внимание, что используются объекты DMO, а для них необходимо, чтобы сервер работал продолжительное время, и функционал по интересующей нас таблице использовался достаточно интенсивно.

Не рекомендуется использовать данных запрос после перезапуска сервера.