¡Hola, chicos!
En este artículo me gustaría compartir contigo una consulta que te permite identificar qué consultas utilizan un determinado índice. Este tipo de análisis puede resultar especialmente útil para identificar qué consultas están provocando que un índice tenga más operaciones de escaneo que búsquedas, por ejemplo.

La idea de este post surgió de una pregunta en un grupo de Whatsapp en el que participo.

Esta publicación es parte de una serie de artículos relacionados con el rendimiento, que contiene las siguientes publicaciones:
– Comprender cómo funcionan los índices en SQL Server
– SQL Server: comparación de rendimiento entre la función escalar y la función escalar CLR
– SQL Server – Introducción al estudio de Performance Tuning
– SQL Server: cómo identificar una consulta lenta o pesada en su base de datos
– SQL Server: cómo identificar consultas que utilizan un determinado índice a través del caché del plan

Creando datos para pruebas.

Para demostrar cómo esta consulta devuelve datos, crearé una nueva tabla, insertaré algunos registros y realizaré algunas consultas simples para que las estadísticas de la base de datos capturen estas operaciones y pueda verificar cuáles usaron qué índices:

IF (OBJECT_ID('dbo.Teste') IS NOT NULL) DROP TABLE dbo.Teste
GO

CREATE TABLE dbo.Teste (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, -- Índice clustered no ID
    Nome VARCHAR(100) NOT NULL
) WITH(DATA_COMPRESSION=PAGE)

-- Índice non-clustered no nome
CREATE NONCLUSTERED INDEX SK01_Teste ON dbo.Teste(Nome) WITH(DATA_COMPRESSION=PAGE)


INSERT INTO dbo.Teste(Nome)
VALUES
('Dirceu'), ('Resende'),
('Tiago'), ('Neves'),
('Fabricio'), ('Lima'),
('Luiz'), ('Lima'),
('Alan'), ('Mairink'),
('Arthur'), ('Luz'),
('Fabiano'), ('Amorim'),
('Rodrigo'), ('Ribeiro')


SELECT * FROM dbo.Teste WHERE Id = 2 -- Deve utilizar o índice clustered (seek)
SELECT * FROM dbo.Teste WHERE Id = 99 -- Deve utilizar o índice clustered (seek)
SELECT * FROM dbo.Teste WHERE Nome = 'Dirceu' -- Deve utilizar o índice non-clustered "SK01_Teste" (seek)
SELECT * FROM dbo.Teste WHERE Nome = 'Resende' -- Deve utilizar o índice non-clustered "SK01_Teste" (seek)
SELECT * FROM dbo.Teste -- Deve utilizar o índice non-clustered (scan)

Identificar el uso de índices

Una vez que hayamos creado los datos para probar y ejecutado algunas consultas, identificaremos qué consultas utilizaron el índice agrupado. Antes de eso, necesito identificar el nombre de este índice:

SELECT *
FROM sys.indexes
WHERE [object_id] = OBJECT_ID('dbo.Teste')

Ahora, consultemos las consultas que utilizaron este índice agrupado (PK__Teste__3214EC075F9CA38E):

SELECT
    SUBSTRING(C.[text], ( A.statement_start_offset / 2 ) + 1, ( CASE A.statement_end_offset WHEN -1 THEN DATALENGTH(C.[text]) ELSE A.statement_end_offset END - A.statement_start_offset ) / 2 + 1) AS sqltext,
    A.execution_count,
    A.total_logical_reads / execution_count AS avg_logical_reads,
    A.total_logical_writes / execution_count AS avg_logical_writes,
    A.total_worker_time / execution_count AS avg_cpu_time,
    A.last_elapsed_time / execution_count AS avg_elapsed_time,
    A.total_rows / execution_count AS avg_rows,
    A.creation_time,
    A.last_execution_time,
    CAST(query_plan AS XML) AS plan_xml,
    B.query_plan,
    C.[text]
FROM
    sys.dm_exec_query_stats AS A
    CROSS APPLY sys.dm_exec_text_query_plan(A.plan_handle, A.statement_start_offset, A.statement_end_offset) AS B
    CROSS APPLY sys.dm_exec_sql_text(A.[sql_handle]) AS C
WHERE
    B.query_plan LIKE '%PK__Teste__3214EC075F9CA38E%'
    AND B.query_plan NOT LIKE '%dm_exec_text_query_plan%'
ORDER BY
    A.last_execution_time DESC
OPTION(RECOMPILE)

Resultado:

Consultemos también las consultas que utilizaron el índice no agrupado SK01_Teste:

SELECT
    SUBSTRING(C.[text], ( A.statement_start_offset / 2 ) + 1, ( CASE A.statement_end_offset WHEN -1 THEN DATALENGTH(C.[text]) ELSE A.statement_end_offset END - A.statement_start_offset ) / 2 + 1) AS sqltext,
    A.execution_count,
    A.total_logical_reads / execution_count AS avg_logical_reads,
    A.total_logical_writes / execution_count AS avg_logical_writes,
    A.total_worker_time / execution_count AS avg_cpu_time,
    A.last_elapsed_time / execution_count AS avg_elapsed_time,
    A.total_rows / execution_count AS avg_rows,
    A.creation_time,
    A.last_execution_time,
    CAST(query_plan AS XML) AS plan_xml,
    B.query_plan,
    C.[text]
FROM
    sys.dm_exec_query_stats AS A
    CROSS APPLY sys.dm_exec_text_query_plan(A.plan_handle, A.statement_start_offset, A.statement_end_offset) AS B
    CROSS APPLY sys.dm_exec_sql_text(A.[sql_handle]) AS C
WHERE
    B.query_plan LIKE '%SK01_Teste%'
    AND B.query_plan NOT LIKE '%dm_exec_text_query_plan%'
ORDER BY
    A.last_execution_time DESC
OPTION(RECOMPILE)

Resultado:

Al hacer clic en la columna “plan_xml”, podemos ver el plan de ejecución de esta consulta y analizar cómo apareció el índice en la caché del plan:

Algunas observaciones

Es importante recordar siempre que el caché del plan se “pierde” cuando se reinicia o se conmuta por error el servicio SQL Server, es decir, no podrás tener un historial muy largo de consultas que utilicen un índice determinado.

Otro punto interesante que me gustaría resaltar es que el caché de planes tiene una cantidad de memoria que puede usar para almacenar planes. SQL Server no puede (y no debe) guardar todos los planes para todas las consultas, por lo que debe optimizar para guardar solo los planes que considera más importantes. Por lo tanto, no siempre podrá recuperar información de una consulta determinada en la caché del plan, ya que es posible que el plan ya se haya descartado debido a problemas de límite de memoria y almacenamiento, incluso si SQL no se ha reiniciado.

Finalmente, es bueno observar y confirmar que las operaciones INSERTAR/ELIMINAR/ACTUALIZAR terminan “usando” todos los índices de la tabla. Cualquier índice específico que consulte con la consulta anterior tendrá registros INSERT/UPDATE/DELETE entre los comandos que usaron el índice (siempre que estos planes todavía estén en la caché del plan), ya que los índices deben actualizarse en las operaciones de escritura de la tabla.

Bueno amigos, ¡eso es todo! Espero que te haya gustado este artículo.
¡Un abrazo y hasta luego!