SQL Query obtener StoredProcedure con mayor demanda

Link artïculo

SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,

  execution_count,s2.objectid,

    (SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,

      ( (CASE WHEN statement_end_offset = -1

  THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)

ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,

       last_execution_time

FROM sys.dm_exec_query_stats AS s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x

WHERE sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'

and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1

and exists (SELECT 1 FROM sys.procedures s

WHERE s.is_ms_shipped = 0

and s.name = x.ProcName )

ORDER BY execution_count DESC

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