script para ver el historico de queries hechos en sql server

Script para saber el histórico de queries ejecutados SQL

En el mundo del desarrollo de bases de datos, la gestión y optimización de consultas SQL es una tarea fundamental para garantizar el rendimiento y la integridad de los datos. Una práctica recomendada es mantener un script histórico de las queries ejecutadas en SQL. En este artículo, exploraremos las razones por las cuales es necesario conservar este historial y cómo puede beneficiar a tu organización.

1. Auditoría y Cumplimiento

Uno de los motivos más importantes para mantener un historial de consultas SQL es la auditoría. En muchas industrias, existen regulaciones estrictas que requieren un seguimiento detallado de quién accede a la base de datos y qué acciones realizan. Un script histórico proporciona una pista de auditoría completa que puede ser invaluable durante auditorías internas y externas.

2. Resolución de Problemas

Cuando se presenta un problema en la base de datos, ya sea un error o un rendimiento lento, tener un historial de consultas ejecutadas puede ayudar a identificar rápidamente la causa. Los administradores de bases de datos pueden revisar las consultas previas para ver si hubo algún cambio significativo o una consulta ineficiente que esté afectando el rendimiento.

3. Optimización del Rendimiento

El análisis de las consultas históricas permite a los administradores identificar patrones y tendencias en el uso de la base de datos. Con esta información, pueden optimizar las consultas y mejorar el rendimiento general del sistema. Por ejemplo, si una consulta específica se ejecuta frecuentemente y tarda mucho tiempo, se pueden tomar medidas para optimizarla o ajustar los índices de la base de datos.

4. Recuperación de Datos

En situaciones donde se ha producido una pérdida de datos accidental, el historial de consultas puede ser crucial para la recuperación. Al revisar las consultas ejecutadas, es posible identificar qué datos se vieron afectados y restaurarlos a su estado original.

5. Capacitación y Mejora Continua

Para equipos de desarrollo y administración de bases de datos, el historial de consultas es una herramienta de aprendizaje. Los nuevos miembros del equipo pueden estudiar las consultas previas para entender las mejores prácticas y los estándares de la organización. Además, permite una revisión continua y mejoras en las prácticas de escritura de consultas SQL.

6. Documentación

Mantener un historial de consultas ejecutadas también sirve como documentación viva del uso de la base de datos. Esto puede ser útil no solo para los administradores, sino también para otros stakeholders que necesiten entender cómo se está utilizando la base de datos.

Script para ver el histórico de queries lanzados en sql (Insert)

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],
        case 
            when sql_handle IS NULL then ' '
            else(substring(st.text,(qs.statement_start_offset+2)/2,(
                case
                    when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
                    else qs.statement_end_offset    
                end - qs.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]
from sys.dm_exec_query_stats as qs with(readuncommitted)
cross apply sys.dm_exec_sql_text(qs.[sql_handle]) as st
cross apply sys.dm_exec_query_plan(qs.[plan_handle]) as sp
WHERE st.[text] LIKE '%insert%'
order by creation_time desc
DESCARGAR SCRIPT PARA VER HISTORICO DE QUERIES LANZADOS EN SQL

Explicación de la Consulta anterior que muestra el histórico de queries en SQL

Consulta SQL que sirve para monitorizar y obtener estadísticas detalladas sobre consultas que contienen la palabra «INSERT«. Este tipo de análisis es fundamental para identificar y optimizar el rendimiento de las operaciones de inserción en una base de datos.

script para ver el historico de queries hechos en sql server
Script para ver el historico de queries hechos en sql server

Desglose de la Consulta

Selección de los Primeros 100 Registros

TOP(100)

La consulta selecciona los primeros 100 registros de los datos que cumple con las condiciones especificadas.

Campos y Cálculos

A continuación, se seleccionan varios campos y se realizan ciertos cálculos para proporcionar métricas detalladas sobre el rendimiento de las consultas:

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],

Explicación de Campos Seleccionados

  • creation_time: Tiempo de creación de la consulta.
  • last_execution_time: Última vez que se ejecutó la consulta.
  • execution_count: Número de veces que se ha ejecutado la consulta.
  • total_worker_time / 1000 AS CPU: Tiempo total de CPU utilizado, convertido a segundos.
  • AvgCPUTime: Tiempo promedio de CPU por ejecución.
  • TotDuration: Duración total de la consulta en milisegundos.
  • AvgDur: Duración promedio por ejecución.
  • Reads: Lecturas lógicas totales.
  • Writes: Escrituras lógicas totales.
  • AggIO: I/O agregado (lecturas + escrituras).
  • AvgIO: I/O promedio por ejecución.
  • sql_handle, plan_handle: Identificadores de la consulta y del plan de ejecución.
  • statement_start_offset, statement_end_offset: Posiciones de inicio y fin de la consulta dentro del batch.
  • plan_generation_num: Número de veces que se ha generado el plan de ejecución.
  • total_physical_reads: Lecturas físicas totales.
  • AvgIOPhysicalReads: Lecturas físicas promedio por ejecución.
  • AvgIOLogicalReads: Lecturas lógicas promedio por ejecución.
  • AvgIOLogicalWrites: Escrituras lógicas promedio por ejecución.
  • query_hash, query_plan_hash: Hash de la consulta y del plan de ejecución.
  • total_rows: Número total de filas afectadas.
  • AvgRows: Número promedio de filas por ejecución.
  • total_dop: Grado total de paralelismo.
  • AvgDop: Grado promedio de paralelismo.
  • total_grant_kb, total_used_grant_kb, total_ideal_grant_kb: Memoria total concedida, usada e ideal en KB.
  • AvgGrantKb, AvgUsedGrantKb, AvgIdealGrantKb: Promedio de memoria concedida, usada e ideal en KB por ejecución.
  • total_reserved_threads, total_used_threads: Hilos totales reservados y usados.
  • AvgReservedThreads, AvgUsedThreads: Hilos promedio reservados y usados por ejecución.

Texto de la Consulta y Plan de Ejecución

CASE 
    WHEN sql_handle IS NULL THEN ' '
    ELSE SUBSTRING(st.text, (qs.statement_start_offset + 2) / 2, 
        (CASE
            WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), st.text)) * 2      
            ELSE qs.statement_end_offset    
        END - qs.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]
  • query_text: Extrae el texto de la consulta SQL.
  • database_name: Nombre de la base de datos donde se ejecutó la consulta.
  • object_name: Nombre del objeto (tabla, vista, etc.) relacionado con la consulta.
  • query_plan: Plan de ejecución de la consulta.

Tablas y Funciones del Sistema

CASE 
    WHEN sql_handle IS NULL THEN ' '
    ELSE SUBSTRING(st.text, (qs.statement_start_offset + 2) / 2, 
        (CASE
            WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), st.text)) * 2      
            ELSE qs.statement_end_offset    
        END - qs.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]
  • sys.dm_exec_query_stats: Vista de estadísticas de ejecución de consultas.
  • sys.dm_exec_sql_text: Función que devuelve el texto completo de la consulta SQL.
  • sys.dm_exec_query_plan: Función que devuelve el plan de ejecución de la consulta.

Filtrado y Orden

WHERE st.[text] LIKE '%insert%'
ORDER BY creation_time DESC;
  • Filtro: Se filtran las consultas cuyo texto contiene la palabra «insert».
  • Orden: Los resultados se ordenan por el tiempo de creación en orden descendente.

Esta consulta es una herramienta útil para analizar el rendimiento y el uso de recursos de las consultas SQL que contienen la palabra «insert» en su texto. Proporciona una visión detallada de cómo se comportan estas consultas en términos de CPU, duración, lecturas/escrituras y otros recursos, ayudando a los administradores de bases de datos a optimizar y solucionar problemas de rendimiento.

¿Qué hace DBCC CHECKDB?

Script Creación de Roles en SQL Server

Generando Script de creación de Usuarios en SQL Server

Eliminar usuarios huérfanos SQL server

Oscar Morán

¿Eres un padawan del SQL Server? ¡Conviértete en un Maestro SQL Jedi!
En el místico universo de las bases de datos, el SQL Server emerge como un Jedi, dominando el lenguaje T-SQL con destreza.

Más que simples administradores de datos sql, son arquitectos de la información, esculpiendo bases de datos eficientes y robustas que soportan el peso de las aplicaciones más exigentes.

Related posts

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *


El periodo de verificación de reCAPTCHA ha caducado. Por favor, recarga la página.

error: Contenido protegido :)