SQL Consulta para obtener estadísticas Seek/Scan/Lookup

Link artículo

SELECT  t.name AS TablaNombre ,

        t.type_desc AS TablaTipo ,

        i.name AS IndiceNombre ,

        i.type_desc AS IndiceTipo ,

        CASE WHEN i.fill_factor = 0 THEN 100

             ELSE i.fill_factor

        END AS IndiceFillFactor ,

        ( SELECT    MAX(v)

          FROM      ( VALUES ( st.ServerStartTime), ( o.create_date),

                    ( o.modify_date) ) AS VALUE ( v )

          ) AS [EstadisticasDesde] ,

        S.USER_SEEKS ,

        S.USER_SCANS ,

        S.USER_LOOKUPS ,

        S.USER_UPDATES

FROM    sys.tables t

        INNER JOIN sys.indexes I ON I.OBJECT_ID = t.OBJECT_ID

        LEFT OUTER JOIN SYS.DM_DB_INDEX_USAGE_STATS AS S ON I.[OBJECT_ID] = S.[OBJECT_ID]

                                                            AND I.INDEX_ID = S.INDEX_ID

        LEFT OUTER JOIN sys.objects o ON i.name = o.name

        CROSS JOIN ( SELECT login_time AS ServerStartTime

                     FROM   sys.sysprocesses

                     WHERE  spid = 1

                   ) ST

                WHERE t.name IN ('table')

SQL Consulta para obtener tablas sin uso desde el último reseteo del servidor

Link artículo
1. [Conteo Total de Ejecuciones] 
2. [Procedimiento que Consume mas CPU]
3. [Procedimientos con mayor tiempo de ejecucion]


Link artículo

-- Create CTE for the unused tables, which are the tables from the sys.all_objects and 
-- not in the sys.dm_db_index_usage_stats table

; with UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate )

AS (

  SELECT DBTable.name AS TableName

     ,PS.row_count AS TotalRowCount

     ,DBTable.create_date AS CreatedDate

     ,DBTable.modify_date AS LastModifiedDate

  FROM sys.all_objects  DBTable

     JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name

  WHERE DBTable.type ='U'

     AND NOT EXISTS (SELECT OBJECT_ID 

                     FROM sys.dm_db_index_usage_stats

                     WHERE OBJECT_ID = DBTable.object_id )

)

-- Select data from the CTE

SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate

FROM UnUsedTables

ORDER BY TotalRowCount ASC

SQL obtener tablas & indices más utilizadas

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