Consulta SQL para conocer las tablas e indices con mayor demanda

Link artículo

--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('DB')

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('DB')

ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC



--get tables

SELECT  TableName ,

        COUNT(*) [Dependency Count]

FROM    ( SELECT DISTINCT

                    o.name 'TableName' ,

                    op.name 'DependentObject'

          FROM      sysobjects o

                    INNER JOIN sysdepends d ON d.depid = o.id

                    INNER JOIN sysobjects op ON op.id = d.id

          WHERE     o.xtype = 'U'

          GROUP BY  o.name ,

                    o.id ,

                    op.name

        ) x

GROUP BY TableName

ORDER BY 2 DESC;