Задача была узнать в какую файловую группу чаше всего происходит запись и чтение данных. Что бы можно было перенести файловые группы на разные хранилки с разными скоростями. И так же размер таблиц
/************************************************************
* * k.moskvichev ©
* Time: 18.08.2016 13:19:03
************************************************************/
/************************************************************
* Code formatted by SoftTree SQL Assistant © v7.4.435
* Time: 18.08.2016 13:18:52
************************************************************/
SELECT
@@ServerName AS ServerName
,DB_NAME() AS DBName
,OBJECT_NAME(ddius.object_id) AS TableName
,SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups) AS Reads
,SUM(ddius.user_updates) AS Writes
,SUM(
ddius.user_seeks + ddius.user_scans + ddius.user_lookups
+ ddius.user_updates
) AS [Reads&Writes] /* ( SELECT DATEDIFF(s, create_date, GETDATE()) / 86400.0
FROM master.sys.databases
WHERE name = 'tempdb'
) AS SampleDays ,
( SELECT DATEDIFF(s, create_date, GETDATE()) AS SecoundsRunnig
FROM master.sys.databases
WHERE name = 'tempdb'
) AS SampleSeconds, */
,f.name AS filegr
,SUM(u.total_pages) * 8 / 1024 AS SizeMB
FROM
sys.dm_db_index_usage_stats ddius
INNER JOIN
sys.indexes i
ON ddius.object_id = i.object_id
AND
i.index_id = ddius.index_id
INNER JOIN
sys.data_spaces f
ON i.data_space_id = f.data_space_id
INNER JOIN
sys.tables t
ON ddius.object_id = t.object_id
INNER JOIN
sys.partitions AS p
ON t.object_id = p.object_id
INNER JOIN
sys.allocation_units u
ON p.partition_id = u.container_id
WHERE
OBJECTPROPERTY(ddius.object_id ,'IsUserTable') = 1
AND
ddius.database_id = DB_ID()
GROUP BY
OBJECT_NAME(ddius.object_id)
,f.name
--ORDER BY [Reads] DESC;
--ORDER BY [Reads&Writes] DESC;
ORDER BY
[SizeMB] DESC;
GO