Como usuario de SQL Server, es posible que a menudo te encuentres en una situación en la que necesitas saber qué consultas están utilizando un índice o tabla en particular. Esta información puede ser crucial cuando se trata de ajustar índices existentes, crear nuevos índices o eliminar índices innecesarios.

En esta publicación del blog, te mostraré cómo puedes aprovechar la caché de planes de SQL Server para obtener información detallada sobre las consultas que están utilizando un índice o tabla específica.

El script proporcionado a continuación recuperará datos de la caché de planes, por lo que es importante tener en cuenta que si se ha eliminado algún plan de consulta de la caché debido a la presión de memoria u otra razón, no podrás recuperar esa información de consulta utilizando este script.

use <NombreBaseDeDatos>
go
SELECT OBJECT_SCHEMA_NAME (stx.objectid, stx.dbid) +'.'+OBJECT_NAME(stx.objectid, stx.dbid) AS nombre_objeto,
  SUBSTRING(stx.[text],(eqs.statement_start_offset / 2) + 1,
(CASE WHEN eqs.statement_end_offset =-1 
THEN DATALENGTH(stx.text) 
ELSE eqs.statement_end_offset 
END - eqs.statement_start_offset
 )/ 2 + 1) AS TextoConsulta,
  CAST(pl.query_plan AS XML) AS plan_sql,
  stx.[text] as texto_completo,
  eqs.execution_count,
  eqs.creation_time [tiempo_compilación],
  eqs.total_worker_time/execution_count AS tiempo_cpu_promedio,
  eqs.total_worker_time AS tiempo_cpu_total,
  eqs.total_logical_reads/execution_count AS lecturas_logicas_promedio,
  eqs.total_logical_reads,
  eqs.last_execution_time
FROM sys.dm_exec_query_stats AS eqs
     CROSS APPLY sys.dm_exec_text_query_plan(eqs.plan_handle, 
 eqs.statement_start_offset, 
 eqs.statement_end_offset) AS pl
     CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS stx
WHERE pl.query_plan not like '%OBJECT_SCHEMA_NAME (stx.objectid, stx.dbid) %'
and pl.dbid=DB_ID()
and pl.query_plan like '%XXXXXXXXX%' ----Reemplaza "XXXXXXXXX" con el nombre del índice

Para demostrar cómo funciona este script, sigamos un enfoque paso a paso. Utilizaremos la base de datos WideWorldImporters como ejemplo.

Primero, ejecuta el código proporcionado en la base de datos WideWorldImporters para generar una carga de trabajo aleatoria. Esto asegurará que haya consultas que utilicen la tabla que queremos analizar.

USE WideWorldImporters
GO
------Carga de trabajo 1
declare @LastCutoffdatetime2
declare @NewCutoffdatetime2
select 
@LastCutoff = ValidFrom,
@NewCutoff = ValidFrom
from Application.People
order by NEWID() 
exec Integration.GetEmployeeUpdates @LastCutoff, @NewCutoff
GO 100
------Carga de trabajo 2
SELECT top 10 FullName, PreferredName FROM Application.People
order by NEWID()
GO 10
------Carga de trabajo 3
declare @SearchText nvarchar(1000)
declare @MaximumRowsToReturn int = 5
select 
@SearchText = CONCAT(p.FullName, N' ', p.PreferredName)
from [Application].People p
order by NEWID()
exec [Website].[SearchForCustomers] @SearchText, @MaximumRowsToReturn
go 80
------Carga de trabajo 4
select top 5 StateProvinceID, SalesTerritory 
from Application.StateProvinces
order by NEWID() 
go 20

Ahora, ejecutemos el script proporcionado para verificar qué consultas están utilizando la tabla “People”. Si deseas verificar qué consultas están utilizando un índice específico, puedes reemplazar el nombre de la tabla con el nombre del índice en el script.

use WideWorldImporters
go
SELECT OBJECT_SCHEMA_NAME (stx.objectid, stx.dbid) +'.'+OBJECT_NAME(stx.objectid, stx.dbid) AS nombre_objeto,
  SUBSTRING(stx.[text],(eqs.statement_start_offset / 2) + 1,
(CASE WHEN eqs.statement_end_offset =-1 
THEN DATALENGTH(stx.text) 
ELSE eqs.statement_end_offset 
END - eqs.statement_start_offset
 )/ 2 + 1) AS TextoConsulta,
  CAST(pl.query_plan AS XML) AS plan_sql,
  stx.[text] as texto_completo,
  eqs.execution_count,
  eqs.creation_time [tiempo_compilación],
  eqs.total_worker_time/execution_count AS tiempo_cpu_promedio,
  eqs.total_worker_time AS tiempo_cpu_total,
  eqs.total_logical_reads/execution_count AS lecturas_logicas_promedio,
  eqs.total_logical_reads,
  eqs.last_execution_time
FROM sys.dm_exec_query_stats AS eqs
     CROSS APPLY sys.dm_exec_text_query_plan(eqs.plan_handle, 
 eqs.statement_start_offset, 
 eqs.statement_end_offset) AS pl
     CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS stx
WHERE pl.query_plan not like '%OBJECT_SCHEMA_NAME (stx.objectid, stx.dbid) %'
and pl.query_plan like '%People%' ----Reemplaza "XXXXXXXXX" con el nombre del índice
and pl.dbid=DB_ID()

La salida de la consulta anterior te mostrará todas las consultas que están utilizando la tabla “People”. Puedes ver que “Carga de trabajo 4” no aparece en la salida porque no está utilizando la tabla “People”.

Si deseas saber qué índice de la tabla está siendo utilizado por una consulta específica, puedes hacer clic en el plan de consulta de esa consulta. Te proporcionará información detallada sobre el índice que se está utilizando.

Espero que este consejo te ayude a solucionar problemas de SQL Server y optimizar tus índices. Si tienes alguna idea o pregunta, por favor deja un comentario abajo. ¡Feliz aprendizaje!