Ничто не оптимизирует скорость работы базы так, как правильно подобранные индексы (С)
Многим, кто сталкивался с промышленной эксплуатацией баз данных определенных (скажем так, очень больших) размеров, приходилось проводить кропотливый анализ запросов на предмет возможной оптимизации скорости их выполнения.
Как оказалось, сама СУБД 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
