Cómo verificar el historial de consultas de SQL Server

Los datos almacenados en el historial de consultas son uno de los más valiosos para cualquier DBA. En muchos casos, se requiere el seguimiento del historial de consultas de SQL Server. Por ejemplo, es posible que deba investigar un caso de trabajo específico, verificar su historial de copias de seguridad o restaurar una consulta específica si su servidor SQL ha fallado.

Afortunadamente, para cualquier falla o tarea de análisis de rutina, existe un historial completo de consultas de SQL Server y existen soluciones prácticas para trabajar con estos datos.

Si necesita ver datos sobre operaciones SQL ejecutadas en SSMS durante un período determinado, puede usar varias opciones:

  1. Las consultas se almacenan en caché a través de vistas del sistema (sys.dm_exec_query_stats, sys.dm_exec_sql_text y sys.dm_exec_query_plan)
  2. Usando el Analizador de SQL Server
  3. Usar eventos avanzados
  4. Uso de Query Store desde la versión 2016
  5. Uso de SQL Complete (SQL Complete Execution History) en SSMS

Por ejemplo, la opción №1 le permite mostrar 100 consultas con el tiempo de ejecución más largo (incluidos todos los retrasos) utilizando el siguiente escenario:

with s as (
select top(100)
creation_time,
last_execution_time,
execution_count,
total_worker_time/1000 as CPU,
convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
qs.total_elapsed_time/1000 as TotDuration,
convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
total_logical_reads as [Reads],
total_logical_writes as [Writes],
total_logical_reads+total_logical_writes as [AggIO],
convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
[sql_handle],
plan_handle,
statement_start_offset,
statement_end_offset,
plan_generation_num,
total_physical_reads,
convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
query_hash,
query_plan_hash,
total_rows,
convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
total_dop,
convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
total_grant_kb,
convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
total_used_grant_kb,
convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
total_ideal_grant_kb,
convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
total_reserved_threads,
convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
total_used_threads,
convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads]
from sys.dm_exec_query_stats as qs with(readuncommitted)
order by convert(money, (qs.total_elapsed_time))/(execution_count*1000) desc
)
select
s.creation_time,
s.last_execution_time,
s.execution_count,
s.CPU,
s.[AvgCPUTime],
s.TotDuration,
s.[AvgDur],
s.[AvgIOLogicalReads],
s.[AvgIOLogicalWrites],
s.[AggIO],
s.[AvgIO],
s.[AvgIOPhysicalReads],
s.plan_generation_num,
s.[AvgRows],
s.[AvgDop],
s.[AvgGrantKb],
s.[AvgUsedGrantKb],
s.[AvgIdealGrantKb],
s.[AvgReservedThreads],
s.[AvgUsedThreads],
--st.text as query_text,
case
when sql_handle IS NULL then ' '
else(substring(st.text,(s.statement_start_offset+2)/2,(
case
when s.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
else s.statement_end_offset    
end - s.statement_start_offset)/2  ))
end as query_text,
db_name(st.dbid) as database_name,
object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
sp.[query_plan],
s.[sql_handle],
s.plan_handle,
s.query_hash,
s.query_plan_hash
from s
cross apply sys.dm_exec_sql_text(s.[sql_handle]) as st
cross apply sys.dm_exec_query_plan(s.[plan_handle]) as sp

En cuanto a otras opciones, cabe destacar la posibilidad de utilizar SQL Complete en SSMS.

SQL Complete almacena información básica sobre las operaciones de SQL realizadas en SSMS durante un período específico. Con este complemento de SSMS, puede ver, editar y buscar consultas T-SQL que se ejecutan en la base de datos. La interfaz fácil de usar hace que este trabajo sea más manejable.

En particular, preste atención a las siguientes funciones proporcionadas por SQL Complete:

  • Verifique el rendimiento de las declaraciones SQL: puede determinar si tuvieron éxito o no.
  • Ver datos del historial de consultas para un período de tiempo específico
  • Ordenar datos por varios criterios
  • Buscar una aplicación específica es conveniente para cualquier analista que trabaje con grandes cantidades de datos

La solución SQL Complete almacena cada declaración que ejecuta en un archivo en el disco o en una tabla en una base de datos especificada por la cadena de conexión. El período de retención puede ser cualquiera: lo configura usted mismo en la configuración de SQL Complete. Entonces, cuando trabaja en un guión, obtiene una historia completa relacionada con cada cambio que realiza. Pero tenga en cuenta que SQL Complete almacena el historial solo para las consultas ejecutadas a través de SSMS con las que está integrado SQL Complete.

Las sentencias SQL se almacenan en una lista escrita en un archivo y/o base de datos. La solución puede eliminar automáticamente los registros antiguos del historial de consultas SQL. Además, puede rastrear quién y cuándo realizó una solicitud en particular u obtener otra información valiosa para investigación y análisis.

Puede utilizar cualquier recurso para comprobar el historial de usuario de las consultas de MS SQL Server. Una variedad de herramientas para realizar la tarea de manera simple y conveniente a su servicio.

Artículos de interés

Subir