Lo primero para empezar el trabajo con SQL Server es preparar el ambiente. La instalación…
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.
Related Posts
Esta entrada tiene 2 comentarios
Los comentarios están cerrados.
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’.
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.