Crear índices sugeridos por SQL Server

Una de las formas mas habituales de mejorar el rendimiento de nuestra aplicación es crear índices de cobertura que optimicen nuestras consultas. Generalmente la creación de los índices adecuados suele mejorar en varios ordenes de magnitud el rendimiento de nuestras aplicaciones y lo mejor de todo es que todo se puede hacer desde la comodidad de nuestro SQL Server Management Studio y sin modificar la aplicación que lanza las consultas (esto obviamente depende de cada caso).

SQL Server nos ayuda bastante a la hora de conocer el estado de indexación de nuestro servidor, manteniendo entre otras cosas información sobre índices que de haber existido, hubieran proporcionado un % de mejora sustancial a la ejecución de cada consulta e incluso del grado de beneficio de dicho índice así como seguimiento (en caso de existir) del uso del mismo. Disponemos de varias DMV que te resumo a continuación:

• Sys.dm_db_missing_index_groups, devuelve información acerca de qué índices que faltan están contenidos en un grupo concreto de índices que faltan. Digamos que esta tabla hace de nexo de unión entre las estadísticas del grupo de índices y las columnas y detalles del índice:

• Sys.dm_db_missing_index_group_stats, devuelve información de resumen sobre los grupos de índices que faltan, excluidos los índices espaciales. Como podéis ver en la siguiente imagen esta dmv nos informa del impacto que tendría el índice en el sistema de ser creado (“avg_user_impact”) así como del numero de veces que podría ser usado (seek’s y scans):

• Sys.dm_db_missing_index_details, devuelve información detallada acerca de índices que faltan. Esta dmv nos va a permitir conocer los detalles del índice para poder crear la sentencia del índice, fijaos que informa de las columnas, la tabla etc.

• Sys.dm_db_missing_index_columns, devuelve información sobre las columnas de la tabla de la base de datos de un índice faltante y nos indica como participan en el índice.Indices sobrantes:

• Sys.dm_db_index_usage_stats, devuelve recuentos de diferentes tipos de operaciones de índice y la hora en que se realizó por última vez cada uno de los tipos de operación:

La idea por tanto es lanzar una query aprovechando las dmv mencionadas anteriormente y descubrir qué índices solicita el propio SQL Server en base a la carga de trabajo que ha recibido desde su último arranque para optimizar su rendimiento de consultas.

Una aproximación sencilla es la que ves aquí

SELECT  DB_NAME(mid.database_id) AS DatabaseID ,
        CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost
        * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans )) AS improvement_measure ,
        'CREATE INDEX missing_index_'
        + CONVERT (VARCHAR, mig.index_group_handle) + '_'
        + CONVERT (VARCHAR, mid.index_handle) + ' ON ' + mid.statement + ' ('
        + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
                    AND mid.inequality_columns IS NOT NULL THEN ','
               ELSE ''
          END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE ('
                                                              + mid.included_columns
                                                              + ')', '') AS create_index_statement ,
        migs.user_seeks ,
        migs.user_scans ,
        mig.index_group_handle ,
        mid.index_handle ,
        migs.* ,
        mid.database_id ,
        mid.[object_id]
FROM    sys.dm_db_missing_index_groups mig
        INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE   CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost
        * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans )) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * ( migs.user_seeks
                                                             + migs.user_scans ) DESC;

Donde básicamente vas a sacar un listado como este, con detalles de ratio de mejora orientativo, nº de operaciones de recorrido en anchura y en profundidad que beneficiarán, etc…

Sin embargo, uno de los mayores riesgos de crear directamente estos objetos es plantearse seriamente cuales de esos índices vamos a crear. ¿Conviene crear todos los índices? La respuesta siempre depende, porque va a depender siempre de la penalización que queramos tener en escrituras vs lecturas. Piensa por un momento si es una buena idea crear un índice a una tabla que tiene un ratio de 5x escrituras vs lecturas. De entrada yo no crearía el índice sin pensar en sus consecuencias…pero y si fuera al revés? Si tengo una tabla que tiene un ratio muy elevado de lecturas vs escrituras, la posibilidad de que la penalización sea grande es bastante mas baja, no? Aquí queda tu parte de análisis, claro 🙂

Nos interesará por tanto conocer el grado de modificaciones que tiene una tabla y su volumen de datos, para determinar si es conveniente crear el índice o si por el contrario podemos crearlo sin problemas y a sabiendas de que va a ser siempre beneficioso. Para ello vamos a modificar ligeramente la query anterior para añadirle información interesante para nuestro análisis:

• Nº de filas que posee la tabla a la que queremos añadir el índice
• Nº de operaciones de modificación (insert, update, delete) que ha sufrido la tabla desde el último reinicio de SQL Server

A las DMV mencionadas anteriormente, vamos por tanto a añadirles información proveniente de:

• sys.partitions , que nos va a proveer del nº de filas aproximadas que posee la tabla en su columa rows.
• sys.dm_db_index_usage_stats , que nos va a proveer en su columna user_updates la cantidad de operaciones de modificación que ha sufrido la tabla desde el último reinicio, entre otras cosas

Con esto en mente, nuestro script podría quedar así:

DECLARE @threshold_table_rows INT = 1000 , --> solo me interesan aquellas con algunas filas
    @threshold_table_updates INT = 10000;  --> a partir de estos cambios, se entiende que la tabla sufre muchas actualizaciones 

WITH    subquery
          AS ( SELECT   DB_NAME(mid.database_id) AS DatabaseID ,
                        CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost
                        * migs.avg_user_impact * ( migs.user_seeks
                                                   + migs.user_scans )) AS improvement_measure ,
                        'CREATE INDEX missing_index_'
                        + CONVERT (VARCHAR, mig.index_group_handle) + '_'
                        + CONVERT (VARCHAR, mid.index_handle) + ' ON '
                        + mid.statement + ' (' + ISNULL(mid.equality_columns,
                                                        '')
                        + CASE WHEN mid.equality_columns IS NOT NULL
                                    AND mid.inequality_columns IS NOT NULL
                               THEN ','
                               ELSE ''
                          END + ISNULL(mid.inequality_columns, '') + ')'
                        + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement ,
                        migs.user_seeks ,
                        migs.user_scans ,                      
                        ISNULL(CONVERT (INT, (-- Multiple partitions could correspond to one index.
                                               SELECT   SUM(rows)
                                               FROM     sys.partitions s_p
                                               WHERE    mid.object_id = s_p.object_id
                                                        AND s_p.index_id = 1 -- cluster index 
                                             )), 0) AS estimated_table_rows ,
                        sus.user_updates + sus.system_updates AS rows_updated
               FROM     sys.dm_db_missing_index_groups mig
                        INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
                        INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
                        LEFT JOIN sys.dm_db_index_usage_stats sus ON sus.index_id = 1 --> quiero solo el indice clustered
                                                              AND sus.object_id = mid.object_id
                                                              AND sus.database_id = mid.database_id
               WHERE    mid.database_id = DB_ID()
                        AND CONVERT (DECIMAL(28, 1), migs.avg_total_user_cost
                        * migs.avg_user_impact * ( migs.user_seeks
                                                   + migs.user_scans )) > 10
             )
    SELECT  *
    FROM    subquery
	WHERE subquery.rows_updated < @threshold_table_updates
	AND subquery.estimated_table_rows > @threshold_table_rows
    ORDER BY improvement_measure DESC;

Quedando la consulta anterior como algo parecido a esto:

Lo cual nos ayuda bastante a decidir por crear el índice o no, ya que podemos ver de forma muy sencilla el ratio de mejora, el nº de index_seeks que podrá realizar el futuro índice asi como el tamaño en filas de la tabla base y sobre todo el nº de actualizaciones que sufre dicha tabla.