adminbd

Записи по mssql,oracle,windows и linux

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Как узнать в какие таблицы чаше всего пишутся и читаются данные в mssql и в кокой файловой группе они состоят. И сразу размер таблиц

Задача была узнать в какую файловую группу чаше всего происходит запись и чтение данных. Что бы можно было перенести файловые группы на разные хранилки с разными скоростями. И так же размер таблиц

/************************************************************
 *  * 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

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>