Skip to content
Sp_ReportIndexDetails

sp_ReportIndexDetails – Uso de Índices

Uno de mis temas favoritos para el Performance Tuning de SQL Server es el Tuning de índices. Para ayudar en este camino, creamos el sp_ReportIndexDetails.

Cuando diseñamos los índices adecuados y en la cantidad necesaria, podemos darle una impresionante mejora a la velocidad de nuestras consultas y de nuestros sistemas.

¿Por dónde empezamos el análisis de índices?

Particularmente prefiero iniciar conociendo los índices que tenemos creados en una Base de Datos y analizar su uso en el tiempo. Para esto nos apoyaremos en el sp_ReportIndexDetails.

CREATE PROCEDURE sp_ReportIndexDetails
AS

BEGIN

SELECT 
	'INDEX DETAILS' AS [REPORT],
    @@SERVERNAME AS [ServerName], 
    DB_NAME() AS [DatabaseName], 
    SCHEMA_NAME([obj].[schema_id]) AS [SchemaName], 
    [obj].[name] AS [ObjectName], 
    CASE 
        WHEN [obj].[type] = 'U' THEN 'Table' 
        WHEN [obj].[type] = 'V' THEN 'View' 
    END AS [ObjectType], 
    [ind].[index_id] AS [IndexID], 
    ISNULL([ind].[name], '-- Here is no index') AS [IndexName], 
    CASE 
        WHEN [ind].[type] = 0 THEN 'Heap' 
        WHEN [ind].[type] = 1 THEN 'Clustered' 
        WHEN [ind].[type] = 2 THEN 'NonClustered' 
        WHEN [ind].[type] = 3 THEN 'XML' 
        WHEN [ind].[type] = 4 THEN 'Spatial' 
        WHEN [ind].[type] = 5 THEN 'Clustered Columnstore Index'        /* Applies to: SQL Server 2014 (12.x) through SQL Server 2017. */ 
        WHEN [ind].[type] = 6 THEN 'Nonclustered Columnstore Index'     /* Applies to: SQL Server 2012 (11.x) through SQL Server 2017. */ 
        WHEN [ind].[type] = 7 THEN 'Nonclustered Hash Index'            /* Applies to: SQL Server 2014 (12.x) through SQL Server 2017. */ 
    END AS [IndexType], 
    STATS_DATE([ind].OBJECT_ID, [ind].index_id) AS [LastStatsUpdated], 
    [ddius].[user_seeks] AS [UserSeeks], 
    [ddius].[user_scans] AS [UserScans], 
    [ddius].[user_lookups] AS [UserLookups], 
    [ddius].[user_updates] AS [UserUpdates], 
    [ddius].[last_user_seek] AS [LastUserSeek], 
    [ddius].[last_user_scan] AS [LastUserScan], 
    [ddius].[last_user_lookup] AS [LastUserLookup], 
    [ddius].[last_user_update] AS [LastUserUpdate],                        /* Includes Inserts, Deletes. Counts operations, not rows*/ 
    [ddios].[leaf_insert_count] AS [LeafLevelInsertCount], 
    [ddios].[leaf_update_count] AS [LeafLevelUpdateCount], 
    [ddios].[leaf_delete_count] AS [LeafLevelDeleteCount] 
FROM 
    [sys].[indexes] AS [ind] 
    INNER JOIN [sys].[objects] AS [obj] 
        ON [ind].[object_id] = [obj].[object_id] 
    LEFT JOIN [sys].[dm_db_index_usage_stats] AS [ddius] 
        ON [ind].[object_id] = [ddius].[object_id] 
        AND [ind].[index_id] = [ddius].[index_id] 
        AND [ddius].[database_id] = DB_ID() 
    LEFT JOIN [sys].[dm_db_index_operational_stats] (DB_ID(),NULL,NULL,NULL) AS [ddios] 
        ON [ind].[object_id] = [ddios].[object_id] 
        AND [ind].[index_id] = [ddios].[index_id] 
WHERE 
    1 = 1 
    AND [obj].[is_ms_shipped] = 0 
    /* AND [obj].[name]  IN ('TBL1','TBL2', 'TBLn') */					/* Uncomment and add TableNames if necessary */ 
    /* AND [ind].[is_disabled] = 0 */                                   /* Uncomment to list only enabled indexes*/     
ORDER BY 2, 3, 4, 5, 7 

END

Como podemos ver, simplemente obtenemos información específica y estadísticas de los siguientes objetos :

Basta con seleccionar la Base de Datos que queremos analizar y ejecutar inextenso el procedimiento. Mientras más tiempo tengamos arriba al servidor, mayor será la información y más valiosa respecto a su uso.

¿Qué analizamos de las estadísticas de índices?

  1. ¿Se están utilizando los índices que tenemos diseñados?
  2. ¿Existen más UserScan que UserSeek en el uso de índices? Esto no necesariamente es malo, sin embargo nos puede ayudar a identificar problemas en la búsqueda de Predicados.
  3. ¿Es grande la diferencia entre Scan/Seek vs Update? Puede ayudarnos a identificar problemas en el diseño del índice.
  4. ¿Las estadísticas están actualizadas? Muchos de los problemas de performance se inician por mal estimaciones que hace el motor en la construcción de planes de ejecución. Esta estimación se calcula con las últimas estadísticas de índices y columnas.

Puedes ver más herramientas entregadas en nuestro portal.

Aprovecha el uso de sp_ReportIndexDetails

Puedes crear el SP en tu servidor y ejecutarlo en periodos que te provean información relevante.

Si has reiniciado el servicio de SQL Server los contadores aparecerán desde cero así que aprovecha la información mientras está vigente.

Detalles importantes para revisarlos con los equipos de desarrollo para generar más valor en los programas.

www.datoptim.com
I love working on SQL Server Performance Tuning and finding the origin of the problems. Music and SQL Server passionate.

Esta entrada tiene 6 comentarios

  1. muchas gracias Pablo esta increíble la query, gracias por compartir.

    Pd: soy miembro de la comunidad PASS, asistí al sqlsaturday de abril 2019, fui el que gano el chocolate jajajaja que por cierto estaba buenísimo. Saludos máster.

  2. Mi estimado, Muchas Gracias por tus herramientas, tuve el privilegio de conocerte en el SQL Saturday 2019 y me ha encantado tu Blog, con decirte que estaba inclinandome por Oracle, pero fuiste la razon de regresar a SQL, escucharte charlar con tanta pasion!.

  3. Hola Pablo, por un compañero del curso que hago en Mater SQL SERVER, me ha llegado tu enlace para crear automáticamente el DICCIONARIO DE DATOS que necesito para entregar esta noche en la Universidad.
    Y agradecerte el post que hiciste sobre el tema, no se si tengas adicional a ello un video explicando el proceso a seguir, por otro lado, quería preguntarte si tienes algo sobre la SEGURIDAD en este motor SQL SERVER, te lo agradecería de antemano.

    1. Hola Erica! Me alegro que al llegar aquí hayas encontrado lo que buscabas.
      Está en nuestra agenda publicar una serie de videos con este y otros temas. Espero que pronto te puedan ayudar, gracias por tu paciencia.
      Un abrazo

Los comentarios están cerrados.

Carrito
Volver arriba