SQL Server 2005, 2008: Создание недостающих индексов
Запись от Poleax размещена 04.06.2010 в 10:50
SQL Server 2005, 2008: Создание недостающих индексов
Оригинал статьи: http://itband.ru/2009/07/sql-server-...dex/#more-1872
В SQL Server начиная с версии 2005 есть функция отсутствующих индексов, которая использует объекты DMO и классы событий Showplan для получения сведений об отсутствующих индексах, благодаря которым можно повысить производительность обработки запросов в SQL Server.
А как получить эти сведения, обработать и принять решение о необходимости создания тех или иных индексов?
Интересная методика представлена в Учебном курсе Microsoft “SQL Server 2005 Реализация и обслуживание”.
Решение о эффективности индекса предлагается принять из расчета некоторого значения по формуле user_seeks * avg_total_user_cost * (avg_user_impact * 0.01). Исходные данные для расчета берутся из представлений sys.dm_db_missing_index*.
Значение выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.
Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.
Немного творчества и получаем вот такой скрипт:
Как использовать данный скрипт:
Можно создать JOB на основе этого скрипта и выполнять его периодически.
Если скрипт выявит необходимость создать какой либо индекс, то он сообщит Вам об этом, послав электронное письмо (компонент Database Mail должен быть включен и настроен).
Отмечу также, что если приложение само управляет объектами базы данных, то и индексы следует создавать через функционал данного приложения.
Пример такого приложения – Microsoft Dynamics AX.
Илгиз Мамышев
P.S.
SQL Server 2005, 2008: Создание недостающих индексов
Оригинал статьи: http://itband.ru/2009/07/sql-server-...dex/#more-1872
В SQL Server начиная с версии 2005 есть функция отсутствующих индексов, которая использует объекты DMO и классы событий Showplan для получения сведений об отсутствующих индексах, благодаря которым можно повысить производительность обработки запросов в SQL Server.
А как получить эти сведения, обработать и принять решение о необходимости создания тех или иных индексов?
Интересная методика представлена в Учебном курсе Microsoft “SQL Server 2005 Реализация и обслуживание”.
Решение о эффективности индекса предлагается принять из расчета некоторого значения по формуле user_seeks * avg_total_user_cost * (avg_user_impact * 0.01). Исходные данные для расчета берутся из представлений sys.dm_db_missing_index*.
Значение выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.
Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.
Немного творчества и получаем вот такой скрипт:
PHP код:
--------------------------------------------------------------------------------------------
-- Создание недостающих индексов баз данных на SQL Server 2005, 2008
--
-- Скрипт анализирует статистику, собранную сервером баз данных об отсутствующих индексах в базах данных и
-- предлагает создать индексы, которые могут обеспечить значительное повышение производительности.
SET NOCOUNT ON
DECLARE @dbid int
IF (object_id('tempdb..##IndexAdvantage') IS NOT NULL) DROP TABLE ##IndexAdvantage
CREATE TABLE ##IndexAdvantage ([Преимущество индекса] float, [База данных] varchar(128), [Transact SQL код для создания индекса] varchar(4000),
[Число компиляций] 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 [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.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 ((SELECT COUNT(*) FROM ##IndexAdvantage2) >= 1) BEGIN
DECLARE @subject_str varchar(255),
@message_str varchar(1024),
@separator_str varchar(1),
@email varchar(128)
SET @separator_str=CHAR(9) -- Символ табуляции
SET @email = 'email_address@webzavod.ru'
-- Подготовим текст сообщения
SET @subject_str = 'SQL Server '+@@SERVERNAME+': Предложение создать индексы в базе данных.'
SET @message_str = 'Сервер '+@@SERVERNAME + '. Выявлена необходимость создать индексы в базе данных!
Во вложении - таблица с кодом предлагаемых индексов.
Значение "Преимущество индекса" выше 5000 в промышленных системах означает, что следует рассмотреть возможность создания этих индексов.
Если же значение превышает 10000, это обычно означает, что индекс может обеспечить значительное повышение производительности для операций чтения.
Динамические административные представления, которые помогли нам получить информацию об отсутствующих индексах, не являются заменой помощника по настройке ядра СУБД, который также рассматривает индексированные представления и секции и обеспечивает более всесторонний анализ индексов, но они могут быть очень эффективны на начальном уровне анализа.'
-- Отправляем email
EXEC msdb.dbo.sp_send_dbmail
@recipients = @email,
@query = 'SELECT * FROM ##IndexAdvantage2',
@subject = @subject_str,
@body = @message_str,
@attach_query_result_as_file = 1,
@query_result_separator = @separator_str,
@query_result_width = 7000
END
-- Удаляем временную таблицу
IF (object_id('tempdb..##IndexAdvantage') IS NOT NULL) DROP TABLE ##IndexAdvantage
IF (object_id('tempdb..##IndexAdvantage2') IS NOT NULL) DROP TABLE ##IndexAdvantage2
--------------------------------------------------------------------------------------------
Можно создать JOB на основе этого скрипта и выполнять его периодически.
Если скрипт выявит необходимость создать какой либо индекс, то он сообщит Вам об этом, послав электронное письмо (компонент Database Mail должен быть включен и настроен).
Отмечу также, что если приложение само управляет объектами базы данных, то и индексы следует создавать через функционал данного приложения.
Пример такого приложения – Microsoft Dynamics AX.
Илгиз Мамышев
P.S.
SQL Server 2005, 2008: Создание недостающих индексов
Всего комментариев 1
Комментарии
-
Добрый день. Запустил Ваш скрипт, получилось более 100 предложений по индексам, где значение превышает 10 тыс., из них около 50 - со значениями более 10 тыс., около 20 - со значением более 1 млн, а максимальное значение - 820 миллионов, причем это не какая-то доморощенная табличка, а таблица InventDim. На втором месте - предложение по индексу для InventSum, и далее в этой сотне встречаются многократно предложения и по InventSum, и по InventDim, и по InventTrans. Не понятно, как интерпретировать такие безумные коэффициенты.Запись от Zabr размещена 30.09.2010 в 15:30 
