SQL рекомендуемые индексы - Форум (скрипты, вопросы...)
27.02.2017
Эффективная Навигация М2М
[ Новые сообщения · Участники · Правила форума · Поиск · RSS ]
Страница 1 из 11
Форум (скрипты, вопросы...) » Прочее » Прочее, что не вошло в другие части форума » SQL рекомендуемые индексы (для тех, у кого SQL standart)
SQL рекомендуемые индексы
logoffДата: Четверг, 10.10.2013, 13:38 | Сообщение # 1
Тамбов
Группа: Администраторы
Сообщений: 642
Репутация: 17
Статус: Offline
Для SQL 2005 или старше

Скрипт на основании собранной статистики запросов (статистика собирается обычно с момента старта SQL) предложит вам рекомендуемые для использования индексы.

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

Так как любой индекс требует места в базе данных, то не рекомендуется огульно использовать результаты для их создания. По русски говоря - если вы не понимаете что произойдет в результате, то пользоваться КРАЙНЕ осторожно, а лучше после консультаций со специалистом.
Например, индексы по таблице sys_Dev_archivedata будут у вас отжирать вероятнее всего крайне много места в базе данных. И желаемый результат может не получиться (так как будут затраты на запись в индекс, практически сопоставимые с записью в саму таблицу).


Код
SET NOCOUNT ON
DECLARE @dbid int
                   
IF (object_id('tempdb..##IndexAdvantage') IS NOT NULL) DROP TABLE ##IndexAdvantage
CREATE TABLE ##IndexAdvantage ([Преимущество индекса] float, [База данных] varchar(64), [Transact SQL код для создания индекса] varchar(512),
[Последнее использование] datetime,[Число компиляций] int, [Количество операций поиска] int, [Количество операций просмотра] int,
[Средняя стоимость ] int, [Средний процент выигрыша] int );
                   
DECLARE DBases CURSOR FOR
SELECT database_id FROM sys.master_files -- Получаем список ID баз данных          
WHERE state = 0 AND -- ONLINE
has_dbaccess(db_name(database_id)) = 1 -- Only look at databases to which we have access
GROUP BY database_id
                   
OPEN DBases
FETCH NEXT FROM DBases
INTO @dbid
                   
WHILE @@FETCH_STATUS = 0
BEGIN          
                   
INSERT INTO ##IndexAdvantage
SELECT [Преимущество индекса] = user_seeks * avg_total_user_cost * (avg_user_impact * 0.01),
               [База данных] = DB_NAME(mid.database_id),
               [Transact SQL код для создания индекса] = 'CREATE INDEX [missing_index_IX_' + OBJECT_NAME(mid.object_id,@dbid) + '_' +
               CAST(mid.index_handle AS nvarchar) + '] ON ' +
               mid.statement + ' (' + ISNULL(mid.equality_columns,'') +
               (CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', ' ELSE'' END) +
               (CASE WHEN mid.inequality_columns IS NOT NULL THEN + mid.inequality_columns ELSE '' END) +
               ')' +
               (CASE WHEN mid.included_columns IS NOT NULL THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END) +
               ';',
              [Последнее использование] = migs.last_user_seek,
               [Число компиляций] = migs.unique_compiles,
               [Количество операций поиска] = migs.user_seeks,
               [Количество операций просмотра] = migs.user_scans,
               [Средняя стоимость ] = CAST(migs.avg_total_user_cost AS int),
               [Средний процент выигрыша] = CAST(migs.avg_user_impact AS int)
FROM  sys.dm_db_missing_index_groups mig
JOIN  sys.dm_db_missing_index_group_stats migs
ON    migs.group_handle = mig.index_group_handle
JOIN  sys.dm_db_missing_index_details mid
ON    mig.index_handle = mid.index_handle
AND   mid.database_id = @dbid
                   
             FETCH NEXT FROM DBases
             INTO @dbid
END          
CLOSE DBases
DEALLOCATE DBases
GO
                   
SELECT * FROM ##IndexAdvantage ORDER BY 1 DESC
-- Значение ''Преимущество индекса'' выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.
-- Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.

-- Отправляем email с рекомендацией создать индекс
IF (object_id('tempdb..##IndexAdvantage2') IS NOT NULL) DROP TABLE ##IndexAdvantage2
SELECT * INTO ##IndexAdvantage2 FROM ##IndexAdvantage WHERE [Преимущество индекса] >= 5000 ORDER BY 1 DESC
                   
                   
-- Удаляем временную таблицу
IF (object_id('tempdb..##IndexAdvantage') IS NOT NULL) DROP TABLE ##IndexAdvantage

IF (object_id('tempdb..##IndexAdvantage2') IS NOT NULL) DROP TABLE ##IndexAdvantage2

Скрипт выдаст вам таблицу, одно из полей которого будет sql скрипт для создания индекса. Для его использования его нужно запустить как отдельный запрос на выполнение и все.

Вес необходимости использования индекса можно оценить по колонке [Преимущество индекса]
Колонка считается как произведение колонок
[Количество операций поиска] *  [Средняя стоимость ] *  [Средний процент выигрыша]

чем там большее число - тем больше SQL считает необходимость в индексе. Число компиляций - количество, сколько бы данный индекс был бы использован

Сам запуск скрипта "безопасен" - если сами код из колонки не скопируете и не запустите - ничего в базе данных не изменится.
Скрипт быстр, так как используется УЖЕ накопленную SQL статистику.

Индексы по таблице Sys_dev_ArchiveData - будут создаваться дольше всего
Например
Код
CREATE INDEX
[missing_index_IX_[b]SYS_DEV_ArchiveData_121] ON [BN].[dbo].[[b]SYS_DEV_ArchiveData] ([ObjectID], [tele_time]) INCLUDE ([id], [CurrentRun]);
будет создаваться относительно долго, ибо сама таблица обычно большая (история координат).

Помним, что каждый индекс занимает определенного место. Если в индексе перечислить все колонки таблицы ты и займет он столько же места, что и сама таблица.

Так же на обновление индекса будет тратиться время SQL, при записи (обновлении) таблицы, но не много. Выигрыш от индекса именно при чтении данных. Но так как при записи производится и чтение, то выигрыш будет.

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

Больше информации можно найти тут
http://goo.gl/68x18B
 
Форум (скрипты, вопросы...) » Прочее » Прочее, что не вошло в другие части форума » SQL рекомендуемые индексы (для тех, у кого SQL standart)
Страница 1 из 11
Поиск:

LogOff © 2017
Сайт создан в системе uCoz Рейтинг GPS Клуба. GPS навигаторы. GPS мониториг. GPS трекеры. ГЛОНАСС