查询sql server 操作记录

原创

mob64ca12d06991 2024-08-09 11:25:54 ©著作权

文章标签 SQL Server sql 文章分类 SQL Server 数据库

©著作权归作者所有：来自51CTO博客作者mob64ca12d06991的原创作品，请联系作者获取转载授权，否则将追究法律责任

SQL Server 操作记录查询

SQL Server 是一个广泛使用的关系数据库管理系统，可以存储和管理大量数据。在日常维护和故障排除过程中，查询操作记录非常重要。通过这些记录，您可以审计用户活动、监控性能问题以及排查安全漏洞。本篇文章将介绍如何查询 SQL Server 的操作记录，特别是通过动态管理视图（DMV）和日志表的方式。我们将提供代码示例，并通过类图帮助理解。

1. 动态管理视图（DMV）

SQL Server 提供了一系列动态管理视图（Dynamic Management Views, DMV），可以用来查询系统状态和操作记录。最常用的 DMVs 包括 sys.dm_exec_requests 和 sys.dm_exec_sessions，它们提供当前请求和会话的信息。

1.1 代码示例

以下是一个简单的查询，展示当前正在运行的 SQL 语句和相关会话的信息：

SELECT 
    r.session_id,
    s.login_name,
    r.status,
    r.command,
    r.start_time,
    r.cpu_time,
    r.total_elapsed_time,
    r.database_id,
    DB_NAME(r.database_id) AS database_name,
    r.sql_handle
FROM 
    sys.dm_exec_requests AS r 
JOIN 
    sys.dm_exec_sessions AS s ON r.session_id = s.session_id
WHERE 
    r.session_id > 50;  --排除了系统进程

这个查询使用了 JOIN 来将 sys.dm_exec_requests 和 sys.dm_exec_sessions 结合起来，生成一个包含会话状态的详细信息表。

2. 查询执行历史记录

除了实时监控当前请求外，有时您还需要查看 SQL Server 的执行历史记录。可以使用 sys.dm_exec_query_stats 和 sys.dm_exec_sql_text() DMV 来实现。

2.1 代码示例

以下代码示例可以帮助您检索过去执行过的 SQL 查询及其执行次数和总耗时：

SELECT 
    qs.execution_count,
    qs.total_worker_time AS total_cpu_time,
    qs.total_elapsed_time,
    qs.total_logical_reads,
    qs.total_logical_writes,
    qt.text AS sql_text
FROM 
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY 
    qs.total_elapsed_time DESC;

在这个查询中，我们利用 CROSS APPLY 来联接查询统计信息和 SQL 文本，展示查询的详细执行历史。这有助于识别性能瓶颈和频繁执行的查询。

3. 类图展示

为了更好地理解这些 DMVs 之间的关系，我们可以使用类图来表示：

classDiagram
    class SQLServerDMV {
        +int session_id
        +string login_name
        +string status
        +string command
        +datetime start_time
        +int cpu_time
        +int total_elapsed_time
        +int database_id
        +string sql_handle
    }

    class SysDMExecRequests {
        +int session_id
        +string command
        +int cpu_time
        +int total_elapsed_time
    }

    class SysDMExecSessions {
        +int session_id
        +string login_name
        +string status
    }

    SQLServerDMV ..> SysDMExecRequests : uses
    SQLServerDMV ..> SysDMExecSessions : uses

以上类图中，SQLServerDMV 表示 SQL Server 动态管理视图的一个抽象概念，而 SysDMExecRequests 和 SysDMExecSessions 代表具体的动态管理视图。可以看到，SQLServerDMV 使用了这两个 DMVs 来获取详细的会话和请求信息。