Создание недостающих индексов в 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
.sql 2,17Kb
16.05.12
120
.sql 2,17Kb 120 Скачать

См. также

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

6. squad 23.05.2012 07:49
Почему то в публикации нигде не увидел предупреждения об "оборотной" стороне индексов - замедление записи в таблицу, на которой повешен индекс.
Тяжелый сложный индекс может серьезно замедлить запись и тем самым сведет все ваши усилия по "ускорению" в минус.
Вообще говоря нужно некоторые исследования проводить (скорость записи до и после) перед тем как создавать дополнительные индексы,
не говоря же о том, что по типовой схеме создания индексов в платформе создается избыточно много индексов например на регистрах. И обычно задача как раз обратная - удалять "лишние" индексы чтобы ускорить запись в регистры.
Ответили: (11)
+ 1 [ svartemov; ]
# Ответить
12. kursaloff 29.05.2012 18:07
(8) mxm2, сам скрипт выдает только табличку со списком индексов которые нужно создать (ремомендуемые СКУЭЛЬ сервером). Сам процесс создания индексов может занимать довольно продолжительное время и для того чтобы их создать нужно взять результат работы скрипт колонка create_index_if_exists_statement. Просто копи-пастом в новое окно для запроса.
Ответили: (13)
+ 1 [ mxm2; ]
# Ответить

Комментарии

1. Stepa86 17.05.2012 15:17
Чем лучше http://infostart.ru/public/81694/ ?
Ответили: (3)
# Ответить
2. kursaloff 17.05.2012 15:33
Хех спасибо за ссылку, может случиться что и велосипед :), если честно обработку не пробовал, но обязательно посмотрю, но...
скрипт не требует запуска 1С. Если на сервере несколько информационных баз отобразится список индексов для всех баз которые рекомендуют создать. Копи пастом результат можно перенести в шедулер, чтобы индексы пересоздавались, если они будут удалены. Вот собственно и все на the best не претендую :)
# Ответить
3. anig99 17.05.2012 16:36
(1) тут решено всё в пределах sql и проще. В другой публикации всё как-то запутано и требует сторонний софи
# Ответить
4. khaoos 21.05.2012 06:45
Хорошо. Давно размышлял о создании нехватающих индексов. Было бы неплохо разработать свод правил (изменений конфигурации), при которых эти индексы слетят, чтобы осмысленно подходить к модификации. А так плюс, и на заметку. Молодец.
# Ответить
5. kursaloff 21.05.2012 08:35
дак в этом случае заморачиваться о том слетят или не слетят созданные индексы как раз не нужно - если текст запроса перенести в шедулер, (там проверяется существует индекс или нет) то при удалении индекса он будет создан повторно, если ни одного индекса повторно создавать не нужно, скрпт отработает за секунду.
# Ответить
6. squad 23.05.2012 07:49
Почему то в публикации нигде не увидел предупреждения об "оборотной" стороне индексов - замедление записи в таблицу, на которой повешен индекс.
Тяжелый сложный индекс может серьезно замедлить запись и тем самым сведет все ваши усилия по "ускорению" в минус.
Вообще говоря нужно некоторые исследования проводить (скорость записи до и после) перед тем как создавать дополнительные индексы,
не говоря же о том, что по типовой схеме создания индексов в платформе создается избыточно много индексов например на регистрах. И обычно задача как раз обратная - удалять "лишние" индексы чтобы ускорить запись в регистры.
Ответили: (11)
+ 1 [ svartemov; ]
# Ответить
8. mxm2 (файл скачал) 27.05.2012 09:30
MS SQL 2008, после запуска пишет "Запрос успешно выполнен" (таблица показывает 100 строк), при этом никакие индексы не создались, по крайней мере запрос "select * from sysindexes where name like 'missing_index%'" дал результат содержащий 0 строк, в SQL - не профи, но тем не менее, не поясните в каком месте в тексте предлагаемого "Запроса" выполняется добавление индексов? Или может быть я не в том месте "запускаю" запрос (делаю через контекстное меню базы данных в списке баз "Создать запрос")? Заранее прошу прощения, если "сморозил" )).
Ответили: (12)
# Ответить
9. electronik 28.05.2012 13:19
такую вещь нужно тестировать как только появятся деньги скачаю и протестирую идея довольно интересная
# Ответить
10. Sairys 29.05.2012 09:40
хорошая статья нужно попробовать
# Ответить
11. kursaloff 29.05.2012 17:56
(6) squad, совершенно согласен - индексы не панацея и они могут и отрицательно влиять на производительность, да и размер базы если индексы создаются на больших регистрах растут очень сильно. Поэтому к созданию индексов нужно подходить обдуманно, данный скрипт только дает рекомендации
# Ответить
12. kursaloff 29.05.2012 18:07
(8) mxm2, сам скрипт выдает только табличку со списком индексов которые нужно создать (ремомендуемые СКУЭЛЬ сервером). Сам процесс создания индексов может занимать довольно продолжительное время и для того чтобы их создать нужно взять результат работы скрипт колонка create_index_if_exists_statement. Просто копи-пастом в новое окно для запроса.
Ответили: (13)
+ 1 [ mxm2; ]
# Ответить
13. mxm2 (файл скачал) 31.05.2012 22:35
(12) kursaloff, Спасибо, реализовал, тестирую.
# Ответить
14. Munsera (файл скачал) 01.06.2012 17:55
Как долго обрабатывается сей скрипт?
У меня уже работает как 10 часов. Ресурсов не жрет, только время идет и все...
Ответили: (15)
# Ответить
15. kursaloff 01.06.2012 21:34
(14) Munsera, что то слишком долго у меня на рег бухгалтерии максимум полчаса индекс создавался. Лучше прервать, посмотреть какой индекс завис и исключить его из скрипта. Я когда создавал индексы выполнял построчно,чтобы видеть какой индекс создается. И еще при создании индексов работа с текущей таблицей лочится
# Ответить
16. Munsera (файл скачал) 04.06.2012 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.2015 10:39
Умничка. Спасибо за скрипт.
Не хватает скрипта, который убивает созданные дополнительные индексы или описания как их удалить вручную.
# Ответить
Внимание! За постинг в данном форуме $m не начисляются.
Внимание! Для написания сообщения необходимо авторизоваться
Текст сообщения*
Прикрепить файл