AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX: Прочие вопросы
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 04.06.2010, 10:29   #1  
Poleax is offline
Poleax
Модератор
Аватар для Poleax
MCP
Сотрудники компании GMCS
MCBMSS
Злыдни
 
1,353 / 593 (22) +++++++
Регистрация: 17.02.2005
Адрес: msk
Записей в блоге: 34
Post SQL Server 2005, 2008: Создание недостающих индексов
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 20052008

--

-- 
Скрипт анализирует статистикусобранную сервером баз данных об отсутствующих индексах в базах данных и

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

SET NOCOUNT ON

DECLARE @dbid int

IF (object_id('tempdb..##IndexAdvantage'IS NOT NULLDROP TABLE ##IndexAdvantage

CREATE TABLE ##IndexAdvantage ([Преимущество индекса] float, [База данных] varchar(64), [Transact SQL код для создания индекса] varchar(512), 

[Число компиляцийint, [Количество операций поискаint, [Количество операций просмотраint,

[
Средняя стоимость int, [Средний процент выигрышаint );

DECLARE 
DBases CURSOR FOR

SELECT database_id FROM sys.master_files -- Получаем список ID баз данных

WHERE state 
AND -- ONLINE

has_dbaccess
(db_name(database_id)) = -- 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 NULLDROP 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 NULLDROP TABLE ##IndexAdvantage

IF (object_id('tempdb..##IndexAdvantage2'IS NOT NULLDROP TABLE ##IndexAdvantage2
-------------------------------------------------------------------------------------------- 
Как использовать данный скрипт:

Можно создать JOB на основе этого скрипта и выполнять его периодически.

Если скрипт выявит необходимость создать какой либо индекс, то он сообщит Вам об этом, послав электронное письмо (компонент Database Mail должен быть включен и настроен).

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

Пример такого приложения – Microsoft Dynamics AX.

Илгиз Мамышев
__________________

This posting is provided "AS IS" with no warranties, and confers no rights.
За это сообщение автора поблагодарили: mazzy (2), Maximin (1), sukhanchik (4), lev (3), zZ_TOP_Zz (1), imir (0).
Старый 04.06.2010, 10:34   #2  
twilight is offline
twilight
MCTS
MCBMSS
 
735 / 144 (6) +++++
Регистрация: 17.10.2004
Адрес: Москва
В SQL 2008 есть инструмент database tuning advisor, которому можно дать трассировку профайлера или SQL запрос на вход, и он выдаст, какие индексы нужно добавить.
__________________
I could tell you, but then I would have to bill you.
Старый 04.06.2010, 10:42   #3  
Poleax is offline
Poleax
Модератор
Аватар для Poleax
MCP
Сотрудники компании GMCS
MCBMSS
Злыдни
 
1,353 / 593 (22) +++++++
Регистрация: 17.02.2005
Адрес: msk
Записей в блоге: 34
Цитата:
Сообщение от twilight Посмотреть сообщение
В SQL 2008 есть инструмент database tuning advisor, которому можно дать трассировку профайлера или SQL запрос на вход, и он выдаст, какие индексы нужно добавить.
Вариантов оптимизации полно, у каждого свой бубен. Данная статья показалась интересной, разместил пост.

Database tuning advisor анализирует лог профайлера, а данная статья описывает вариант с индексами на основе статистики.
Не приятно, что Аксапта все переделает после синхронизации Так что правим индексы в самой аксе.

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

This posting is provided "AS IS" with no warranties, and confers no rights.

Последний раз редактировалось Poleax; 04.06.2010 в 10:44.
Старый 04.06.2010, 13:05   #4  
Poleax is offline
Poleax
Модератор
Аватар для Poleax
MCP
Сотрудники компании GMCS
MCBMSS
Злыдни
 
1,353 / 593 (22) +++++++
Регистрация: 17.02.2005
Адрес: msk
Записей в блоге: 34
Рекомендовал бы для скрипта строку:

PHP код:
CREATE TABLE ##IndexAdvantage ([Преимущество индекса] float, [База данных] varchar(64), [Transact SQL код для создания индекса] varchar(512), 
заменить на

PHP код:
CREATE TABLE ##IndexAdvantage ([Преимущество индекса] float, [База данных] varchar(128), [Transact SQL код для создания индекса] varchar(4000), 
Отрабатывает быстро, по всем БД.
__________________

This posting is provided "AS IS" with no warranties, and confers no rights.

Последний раз редактировалось Poleax; 04.06.2010 в 13:16.
Старый 04.06.2010, 14:19   #5  
AraraT® is offline
AraraT®
Участник
1C
 
158 / 106 (4) +++++
Регистрация: 13.01.2006
Адрес: Республика Татарстан, г. Казань
Вот блог, в котором приводился код этого скрипта, там кстати много еще полезной инфы:
http://msmvps.com/blogs/gladchenko/
Старый 05.06.2010, 01:05   #6  
gl00mie is offline
gl00mie
Участник
MCBMSS
Most Valuable Professional
Лучший по профессии 2017
Лучший по профессии 2015
Лучший по профессии 2014
Лучший по профессии AXAWARD 2013
Лучший по профессии 2011
Лучший по профессии 2009
 
3,601 / 5291 (184) ++++++++++
Регистрация: 28.11.2005
Адрес: Москва
Записей в блоге: 2
Цитата:
Сообщение от Poleax Посмотреть сообщение
Не приятно, что Аксапта все переделает после синхронизации Так что правим индексы в самой аксе.
А вы ее научите не переделывать то, что сделано не через AOT.
За это сообщение автора поблагодарили: Poleax (1).
Старый 05.06.2010, 01:28   #7  
RVS is offline
RVS
Сенбернар
Аватар для RVS
Злыдни
 
668 / 125 (6) +++++
Регистрация: 27.02.2003
Адрес: Королев МО
Ужас... Создание Недостающих Индексов...

Это оно само - за Вас - решает?

Я бы поостерегся
__________________
Best Regards,
Roman
Теги
index, performance, sql

 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
Dynamics AX Sustained Engineering: SQL Server 2005 sp3 & SQL Server 2008 with Dynamics AX Blog bot DAX Blogs 0 12.02.2009 06:08
Dynamics AX: Looking into SQL Server 2008 Blog bot DAX Blogs 0 16.01.2009 05:06
jinx: Microsoft Dynamics AX 2009, Windows Server 2008 und SQL Server 2008 Blog bot DAX auf Deutsch 0 12.10.2008 20:10
Dynamics AX: Dynamics AX 2009 & SQL Server 2008 Blog bot DAX Blogs 0 10.06.2008 21:08
aEremenko: Диагностика проблем при установке Microsoft Dynamics Ax 4.0 на Microsoft SQL Server 2005 Blog bot DAX Blogs 0 28.10.2006 16:01
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 16:42.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.