gifts2017

Создание недостающих индексов в MSSQL

Опубликовал Константин Урсалов (kursaloff) в раздел Администрирование - Оптимизация БД (HighLoad)

Для тех, кто борется с производительностью 1С8.
MSSQL  сервер оказывается накапливает статистику о недостающих индексах и есть возможность получить скрипты по их созданию

Сам скрипт, выполнив который можно получить список рекомендуемых индексов во всех базах, находящихся на сервере, опубликован тут. http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx (спасибо Вячеславу Гилеву) за идею.

Получаем очень интересные результаты. У нас к примеру после создания некоторых недостающих индексов производительность операций (создание и запись реализаций на основании заказов покупателя) возрасла в 10 раз.

Но в случае с 1С8 - есть риск что созданные индексы после изменения конфигурации или проверки и тестирования будут удалены. Поэтому немного переработал данный скрипт, так чтобы он генерировал SQL код для создания индекса так, чтобы предварительно анализировать создан ли уже данный индекс.  В результате можно.

 1) Запустить скрипт получить результат колонки create_index_if_exists_statement

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

3) Когда все необходимые индексы созданы можно этот же скрипт запустить на ежедневный или еженедельный запуск - индексы будут пересозданы, только в том случае если это индекс был по каким то причинам удален из базы.

4) Просмотреть все созданные недостающие индексы тоже легко - там собственный префикс выбираете при помощи запроса:

select * from sysindexes where name like 'missing_index%'

 

 

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

Наименование Файл Версия Размер
Create_Missing_Indexes.SQL 127
.sql 2,17Kb
16.05.12
127
.sql 2,17Kb Скачать

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Антон Степанов (Stepa86) 17.05.12 15:17
2. Константин Урсалов (kursaloff) 17.05.12 15:33
Хех спасибо за ссылку, может случиться что и велосипед :), если честно обработку не пробовал, но обязательно посмотрю, но...
скрипт не требует запуска 1С. Если на сервере несколько информационных баз отобразится список индексов для всех баз которые рекомендуют создать. Копи пастом результат можно перенести в шедулер, чтобы индексы пересоздавались, если они будут удалены. Вот собственно и все на the best не претендую :)
3. Александр Медведев (anig99) 17.05.12 16:36
(1) тут решено всё в пределах sql и проще. В другой публикации всё как-то запутано и требует сторонний софи
4. Василий Антонов (khaoos) 21.05.12 06:45
Хорошо. Давно размышлял о создании нехватающих индексов. Было бы неплохо разработать свод правил (изменений конфигурации), при которых эти индексы слетят, чтобы осмысленно подходить к модификации. А так плюс, и на заметку. Молодец.
5. Константин Урсалов (kursaloff) 21.05.12 08:35
дак в этом случае заморачиваться о том слетят или не слетят созданные индексы как раз не нужно - если текст запроса перенести в шедулер, (там проверяется существует индекс или нет) то при удалении индекса он будет создан повторно, если ни одного индекса повторно создавать не нужно, скрпт отработает за секунду.
6. Марат Биккин (squad) 23.05.12 07:49
Почему то в публикации нигде не увидел предупреждения об "оборотной" стороне индексов - замедление записи в таблицу, на которой повешен индекс.
Тяжелый сложный индекс может серьезно замедлить запись и тем самым сведет все ваши усилия по "ускорению" в минус.
Вообще говоря нужно некоторые исследования проводить (скорость записи до и после) перед тем как создавать дополнительные индексы,
не говоря же о том, что по типовой схеме создания индексов в платформе создается избыточно много индексов например на регистрах. И обычно задача как раз обратная - удалять "лишние" индексы чтобы ускорить запись в регистры.
8. mxm2 mxm2 (mxm2) 27.05.12 09:30
MS SQL 2008, после запуска пишет "Запрос успешно выполнен" (таблица показывает 100 строк), при этом никакие индексы не создались, по крайней мере запрос "select * from sysindexes where name like 'missing_index%'" дал результат содержащий 0 строк, в SQL - не профи, но тем не менее, не поясните в каком месте в тексте предлагаемого "Запроса" выполняется добавление индексов? Или может быть я не в том месте "запускаю" запрос (делаю через контекстное меню базы данных в списке баз "Создать запрос")? Заранее прошу прощения, если "сморозил" )).
9. Andrey Dyak (electronik) 28.05.12 13:19
такую вещь нужно тестировать как только появятся деньги скачаю и протестирую идея довольно интересная
10. Иван (Sairys) 29.05.12 09:40
хорошая статья нужно попробовать
11. Константин Урсалов (kursaloff) 29.05.12 17:56
(6) squad, совершенно согласен - индексы не панацея и они могут и отрицательно влиять на производительность, да и размер базы если индексы создаются на больших регистрах растут очень сильно. Поэтому к созданию индексов нужно подходить обдуманно, данный скрипт только дает рекомендации
12. Константин Урсалов (kursaloff) 29.05.12 18:07
(8) mxm2, сам скрипт выдает только табличку со списком индексов которые нужно создать (ремомендуемые СКУЭЛЬ сервером). Сам процесс создания индексов может занимать довольно продолжительное время и для того чтобы их создать нужно взять результат работы скрипт колонка create_index_if_exists_statement. Просто копи-пастом в новое окно для запроса.
13. mxm2 mxm2 (mxm2) 31.05.12 22:35
(12) kursaloff, Спасибо, реализовал, тестирую.
14. Алекс Гопнев (Munsera) 01.06.12 17:55
Как долго обрабатывается сей скрипт?
У меня уже работает как 10 часов. Ресурсов не жрет, только время идет и все...
15. Константин Урсалов (kursaloff) 01.06.12 21:34
(14) Munsera, что то слишком долго у меня на рег бухгалтерии максимум полчаса индекс создавался. Лучше прервать, посмотреть какой индекс завис и исключить его из скрипта. Я когда создавал индексы выполнял построчно,чтобы видеть какой индекс создается. И еще при создании индексов работа с текущей таблицей лочится
16. Алекс Гопнев (Munsera) 04.06.12 11:57
Если запускаю через CMD:
sqlcmd -d test_b -i "E:\SQL_Bases\SQLQuery1.sql" -o E:\SQL_Bases\result_restore.txt

То выполняется мгновенно. А в техстовом файле пустота.
test_b - это база которую тестирую...
Прикрепленные файлы:
result_restore.txt
17. Алексей Захаров (almas) 30.09.15 10:39
Умничка. Спасибо за скрипт.
Не хватает скрипта, который убивает созданные дополнительные индексы или описания как их удалить вручную.