Как найти то, что Хранимые процедуры используют что индексы?
Я пытаюсь определить, какие индексы больше не используются в моей Базе данных. У меня была большая удача с помощью следующего запроса:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
i.Type_Desc as [Index Type],
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
WHERE i.name is not null
AND
( OBJECT_NAME(S.[OBJECT_ID]) = 'Table1'
OR
OBJECT_NAME(S.[OBJECT_ID]) = 'Table2'
OR
OBJECT_NAME(S.[OBJECT_ID]) = 'Table3'
)
ORder by S.[OBJECT_ID], user_Seeks desc , user_scans desc
Что я хотел бы найти, теперь то, о чем Хранимые процедуры вызывают Искание, сканирования и поиски что вышеупомянутые отчеты о запросах. Эта информация хранится в системных представлениях/таблицах?
РАЗЪЯСНЕНИЕ
Поскольку gbn указал, что Хранимая процедура непосредственно не использует индекс, это использует таблицу, которая использует индекс. Ниже объяснение, что я надеюсь, разъяснит то, что я пытаюсь спросить здесь.
Для меня действительно ли возможно определить то, что SQL был выполнен, который заставил вышеупомянутые индексы использоваться? Например, если один из индексов сообщил относительно, имеет 10 User_Seeks, был бы это быть возможным определить это exec sp_1 вызванный, что использование 7 раз и exec sp_2 вызванный то использование 3 раза?
4 ответа
Редактировать (опять же, после обновления вопроса):
Нет реальных шансов. Вы можете попробовать профилировщик и записать текстовый план. Я видел это однажды, и это убило сервер: нужно записывать много текста. YMMV: -)
Хранимые процедуры не используют индексы.
Сохраненные процессы используют таблицы (и индексированные представления), которые , затем используют индексы (или не используют, как вы выяснили выше)
Выполнение SELECT col1, col2 FROM myTable WHERE col2 = 'foo' ORDER BY col1 одинаков как в хранимой процедуре, представлении, пользовательской функции, так и отдельно.
Изменить: наш скрипт использования индекса, загруженный откуда-то ...
SELECT
o.name AS [object_name],
i.name AS index_name,
i.type_desc,
u.user_seeks, u.user_scans,
u.user_lookups, u.user_updates,
o.type
FROM
sys.indexes i
JOIN
sys.objects o ON i.[object_id] = o.[object_id]
LEFT JOIN
sys.dm_db_index_usage_stats u ON i.[object_id] = u.[object_id] AND
i.index_id = u.index_id AND
u.database_id = DB_ID()
WHERE
o.type IN ('U', 'V') AND
i.name IS NOT NULL
ORDER BY
u.user_seeks + u.user_scans + u.user_lookups, u.user_updates
В студии управления SQL вы можете точно увидеть, как выполняется запрос, с помощью параметра «Показать план выполнения» в меню запроса.
У меня нет доступа к SQL Management Studio дома, но, возможно, вы можете посмотреть зависимости хранимых процедур (т. Е. эта процедура хранения зависит от этих таблиц и поэтому может использовать эти индексы)
Эта страница может дать вам некоторую подсказку, например, использование системной таблицы INFORMATION_SCHEMA.ROUTINES :
SELECT routine_name, routine_type
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Employee%'
Вы можете заполнить эту информацию во временную таблицу, а затем использовать ее для запроса используемых индексов, просматривая статистику использования индекса.
Извините, что я не могу привести вам практический пример, просто теоретический пример ...
У вас есть количество выполнений для всех операторов в sys.dm_exec_query_stats , и вы можете извлечь план XML с помощью sys .dm_exec_query_plan . План содержит детали, такие как используемые операторы сканирования, поэтому между этими двумя вы можете составить много информации из того, что вы спрашиваете. Например, следующий запрос покажет вам операторы IndexScan в часто выполняемых операторах из кэшированных планов, которые вызывают много логических операций чтения:
with xmlnamespaces ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sp)
select top(100)
q.total_logical_reads, q.execution_count
, x.value(N'@Database', N'sysname') as [Database]
, x.value(N'@Schema', N'sysname') as [Schema]
, x.value(N'@Table', N'sysname') as [Table]
, x.value(N'@Index', N'sysname') as [Index]
, substring(t.text, q.statement_start_offset/2,
case when 0 < q.statement_end_offset then (q.statement_end_offset - q.statement_start_offset)/2
else len(t.text) - q.statement_start_offset/2 end) as [Statement]
from sys.dm_exec_query_stats q
cross apply sys.dm_exec_query_plan(plan_handle)
cross apply sys.dm_exec_sql_text(sql_handle) as t
cross apply query_plan.nodes(N'//sp:IndexScan/sp:Object') s(x)
where execution_count > 100
order by total_logical_reads desc;