Skip to content

sp_ReportCache – Reporte de Cache en SQL Server

¿Dónde empezar el Performance Tuning de SQL Server? Podemos dar muchas y diferentes respuestas. Una de las fuentes de información más útiles en cualquier sistema siempre es el Cache que guarda en memoria, las ejecuciones más recientes realizadas en el servidor. Entonces ¿cómo generamos un Reporte de Cache?

El procedimiento sp_ReportCache permite consultar aquellas ejecuciones que se mantienen en memoria y obtener un detalle de cuáles fueron las que más consumo de lectura tuvieron, más requerimiento de memoria, o cuáles fueron las más recientes ejecuciones.

Direccionemos el Performance Tuning con sp_ReportCache

Iniciemos creando el Procedimiento Almacenado mostrado a continuación. Veremos que existen tres parámetros que serán explicados más abajo.

CREATE OR ALTER PROC sp_ReportCache 
    @Advanced int = 0,
    @Text nvarchar(30) = NULL,
    @OrderBy nvarchar(30) = NULL
 
AS
BEGIN
 
DECLARE
    @Query nvarchar(max) = NULL,
    @LineFeed NVARCHAR(10)
-------------------------------------------------
 

select @LineFeed = CHAR(13) + CHAR(10)

IF(@Advanced = 0)
BEGIN
	SET @Query = N'
	SELECT
		[deqs].[plan_handle] AS [PlanHandle],
		[deqs].[execution_count] AS [ExecCount],
		[dest].[text] AS [Text],
		SUBSTRING(
			[dest].text,
			([deqs].statement_start_offset / 2) + 1,
			((CASE [deqs].statement_end_offset
				WHEN -1 then DATALENGTH([dest].text)
				ELSE [deqs].statement_end_offset
			END - [deqs].statement_start_offset) / 2) + 1) AS [QueryText], 
		[deqp].[query_plan] AS [QueryPlan], 
		[deqs].[creation_time] AS [PlanCreatedAt], 
		[deqs].[last_execution_time] AS [LastExecutedAt], 
		[deqs].last_logical_reads, 
		[deqs].total_elapsed_time,
		[deqs].last_elapsed_time,
		[deqs].[last_dop] AS [LastDoP],
		[deqs].[last_rows] AS [Last#Rows],
		[deqs].[total_grant_kb] AS [TotalGrant],
		[deqs].[last_used_grant_kb] AS [LastUsedGrant],
		[deqs].[last_ideal_grant_kb] AS [LastIdealGrant],
		[deqs].sql_handle as firstfield
	FROM 
		sys.dm_exec_query_stats AS [deqs]
		CROSS APPLY sys.dm_exec_sql_text ([deqs].sql_handle) as [dest]
		CROSS APPLY sys.dm_exec_query_plan ([deqs].plan_handle) as [deqp]
	'
END

ELSE
	BEGIN
		SET @Query = N'
		SELECT
			[deqs].[plan_handle] AS [PlanHandle], 
			[deqs].[execution_count] AS [ExecCount],
			[dest].[text] AS [Text], 
			SUBSTRING(
				[dest].text, 
				([deqs].statement_start_offset / 2) + 1,
				((CASE [deqs].statement_end_offset
					WHEN -1 then DATALENGTH([dest].text)
					ELSE [deqs].statement_end_offset
				END - [deqs].statement_start_offset) / 2) + 1) AS [QueryText], 
			[deqp].[query_plan] AS [QueryPlan], 
			[deqs].[creation_time] AS [PlanCreatedAt], 
			[deqs].[last_execution_time] AS [LastExecutedAt], 
			[deqs].last_logical_reads, 
			[deqs].total_elapsed_time, 
			[deqs].last_elapsed_time, 
			[deqs].[last_dop] AS [LastDoP],
			[deqs].last_reserved_threads,-- 
			[deqs].[last_rows] AS [Last#Rows],
			[deqs].[total_rows] AS [Total#Rows],--
			[deqs].[min_rows] AS [Min#Rows],--
			[deqs].[max_rows] AS [Max#Rows],--
			[deqs].[total_grant_kb] AS [TotalGrant],
			[deqs].[last_used_grant_kb] AS [LastUsedGrant],
			[deqs].[last_ideal_grant_kb] AS [LastIdealGrant],
			[deqs].statement_start_offset,--
			[deqs].sql_handle as firstfield
		FROM 
			sys.dm_exec_query_stats AS [deqs]
			CROSS APPLY sys.dm_exec_sql_text ([deqs].sql_handle) as [dest]
			CROSS APPLY sys.dm_exec_query_plan ([deqs].plan_handle) as [deqp]
		'
	END


IF(@Text is not null)
	SET @Query = @Query + @LineFeed + N'WHERE text like ''%' + @Text + '%''' + @LineFeed
	



SET @Query = CASE @OrderBy
	WHEN 'reads' THEN @Query + @LineFeed + N'ORDER BY last_logical_reads DESC'
	WHEN 'duration' THEN @Query + @LineFeed + N'ORDER BY last_elapsed_time DESC'
	WHEN 'memory' THEN @Query + @LineFeed + N'ORDER BY last_used_grant_kb DESC'
	ELSE @Query + @LineFeed + N'ORDER BY last_execution_time DESC'
END

EXEC (@Query)


END

@OrderBy

Por defecto la llamada al procedimiento sp_ReportCache tiene los datos ordenados para mostrar primero los comandos ejecutados más recientes. Para cambiar este comportamiento, es posible considerar 3 valores diferentes: reads (cantidad de lectura), duration (tiempo de duración de ejecución) o memory (requerimiento de memoria de la consulta)

EXEC sp_ReportCache @OrderBy = 'duration'

@Advanced

El valor «Advanced» permite recibir mayor cantidad de información de la ejecución del SP donde puede observarse valores de máximos y mínimos en los correspondiente a los procesos ejecutados. El valor por defecto es «0», así que si se necesita mayor información basta con ejecutar el SP con el parámetro en valor «1».

EXEC sp_ReportCache @Advanced = 1

@Text

Este parámetro nos da la facilidad de realizar la búsqueda de una palabra clave que se requiera buscar dentro de lo que almacena el Cache.

EXEC sp_ReportCache @Text = 'DELETE'

Ninguno de los parámetros es exclusivo así que pueden combinarse en función a las necesidades de cada consulta.

EXEC sp_ReportCache @Text = 'INSERT', @OrderBy = 'memory', @Advanced = 1

Consideraciones al Reporte de Cache en SQL Server

  • Aquello que se genera en el Reporte de Cache es todo aquello que en ese momento se encuentra en memoria en el servidor.
  • Si el servidor tuvo un reinicio reciente o si tuvo una liberación de caché, entonces es posible que el reporte no tenga los datos esperados.
  • Puedes obtener el procedimiento almacenado desde el github.
  • La ejecución de este procedimiento almacenado permitirá generar un registro de comandos recientemente solicitados.

Puedes ver más herramientas entregadas en nuestro portal.

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 2 comentarios

  1. Porque al ejecutar el procedimto me da error ?
    EXEC sp_ReportCache @Advanced = 1

    Msg 207, Level 16, State 1, Line 23
    Invalid column name ‘last_dop’.
    Msg 207, Level 16, State 1, Line 24
    Invalid column name ‘last_reserved_threads’.
    Msg 207, Level 16, State 1, Line 29
    Invalid column name ‘total_grant_kb’.
    Msg 207, Level 16, State 1, Line 30
    Invalid column name ‘last_used_grant_kb’.
    Msg 207, Level 16, State 1, Line 31
    Invalid column name ‘last_ideal_grant_kb’.

    1. Hola Cesar! Eso quiere decir que tienes una versión anterior a SQL Server. Puedes editar la consulta y eliminar las columnas que te muestre el mensaje de error.
      Gracias por el comentario, vamos a hacer un ajuste para que esto no vuelva a suceder.

Los comentarios están cerrados.

Carrito
Volver arriba