Оптимизируем базу на MSSQL: определяем required indexes

Ничто не оптимизирует скорость работы базы так, как правильно подобранные индексы (С)

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

Как оказалось, сама СУБД MSSQL версии 2005 и выше содержит механизм, грамотное использование которого может очень сильно облегчить работу по поиску некоторых узких мест.

Это sys.dm_db_missing_index_group_stats и связанные с ней функции (подробнее >>)
Данные для них формируются на основании статистики запросов к базе данных, и потому являются довольно хорошей информацией, от которой можно оттолкнуться при оптимизации.

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

SELECT
        [Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),
        [TABLE] = [statement],
        [CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_'
                        + sys.objects.name COLLATE DATABASE_DEFAULT
                        + '_'
                        + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')+ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_')
                        + ' ON '
                        + [statement]
                        + ' ( ' + IsNull(mid.equality_columns, '')
                        + CASE
                                WHEN mid.inequality_columns IS NULL THEN ''
                                ELSE
                                        (CASE
                                                WHEN mid.equality_columns IS NULL THEN ''
                                                ELSE ','
                                         END)
                                        + mid.inequality_columns
                                END
                        + ' ) '
                        + CASE
                                WHEN mid.included_columns IS NULL THEN ''
                                        ELSE 'INCLUDE (' + mid.included_columns + ')'
                                END
                        + ';',
        mid.equality_columns,
        mid.inequality_columns,
        mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
        INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
        INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE   (migs.group_handle IN
                (SELECT TOP (500) group_handle
                FROM sys.dm_db_missing_index_group_stats WITH (nolock)
                ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
                AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

Естественно, для выполнения этого запроса нужно обладать правом VIEW SERVER STATE или любым другим правом, включающим в себя VIEW SERVER STATE.
Так же перед использованием желательно обновить статистику БД.

P.S. Еще можно почитать (и даже посмотреть видео) вот тут.

P.S.2 Определяем, как часто "пользуются" индексами:

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
 I.[NAME] AS [INDEX NAME],
 USER_SEEKS,
 USER_SCANS,
 USER_LOOKUPS,
 USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
 INNER JOIN SYS.INDEXES AS I
 ON I.[OBJECT_ID] = S.[OBJECT_ID]
 AND I.INDEX_ID = S.INDEX_ID

Нравится

Поделиться

0 комментариев
Показать все комментарии