Наиболее используемые индексы и таблицы

Всегда полезно анализировать нагрузку на сервер баз данных. В частность важно знать какие таблицы используются наиболее часто. Это важно, например, при разнесении БД по нескольким файловым группам расположенным на разных дисках.

--get most used tables
SELECT
db_name(ius.database_id) AS DatabaseName,
t.NAME AS TableName,
SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.tables t ON t.OBJECT_ID = ius.object_id
WHERE database_id = DB_ID('MyDb')
GROUP BY database_id, t.name
ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC


--get most used indexes
SELECT
db_name(ius.database_id) AS DatabaseName,
t.NAME AS TableName,
i.NAME AS IndexName,
i.type_desc AS IndexType,
ius.user_seeks + ius.user_scans + ius.user_lookups AS NbrTimesAccessed
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON i.OBJECT_ID = ius.OBJECT_ID AND i.index_id = ius.index_id
INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id
WHERE database_id = DB_ID('MyDb')
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC

источник




declare @dbid int
--To get Datbase ID
set @dbid = db_id()

select
db_name(d.database_id) database_name
,object_name(d.object_id) object_name
,s.name index_name,
c.index_columns
,d.*
from sys.dm_db_index_usage_stats d
inner join sys.indexes s
on d.object_id = s.object_id
and d.index_id = s.index_id
left outer join
(select distinct object_id, index_id,
stuff((SELECT ','+col_name(object_id,column_id ) as 'data()' FROM sys.index_columns t2 where t1.object_id =t2.object_id and t1.index_id = t2.index_id FOR XML PATH ('')),1,1,'')
as 'index_columns' FROM sys.index_columns t1 ) c on
c.index_id = s.index_id and c.object_id = s.object_id
where database_id = @dbid
and s.type_desc = 'NONCLUSTERED'
and objectproperty(d.object_id, 'IsIndexable') = 1
order by
(user_seeks+user_scans+user_lookups+system_seeks+system_scans+system_lookups) desc

источник