首页 > 数据库 >SQL Server慢查询日志

SQL Server慢查询日志

时间：2024-07-28 22:28:27浏览次数：18  
标签：name value Server SQL 日志 data event

如何让SQL Server像MySQL一样拥有慢查询日志（Slow Query Log慢日志）

如何让SQL Server像MySQL一样拥有慢查询日志（Slow Query Log慢日志）

SQL Server一直以来被人诟病的一个问题是缺少了像MySQL的慢日志功能，程序员和运维无法知道数据库过去历史的慢查询语句。

因为SQLServer默认是不捕获过去历史的长时间阻塞的SQL语句，导致大家都认为SQL Server没有历史慢日志功能

其实SQLServer提供了扩展事件让用户自己去捕获过去历史的长时间阻塞的SQL语句，但是因为不是默认出厂配置并且设置扩展事件对初级用户有一定难度，这里可以说不得不是一个遗憾，希望后续版本的SQL Server可以默认设置好慢日志的相关扩展事件，用初级用户也可以快速上手。


话不多说，这个文章主要讲述设置慢日志的扩展事件的步骤，并且把慢日志提供第三方程序读取以提供报表功能。

 

扩展事件介绍

SQL Server 扩展事件（Extended Events，简称 XE）是从 SQL Server 2008 开始引入的一种轻量级、高度可定制的事件处理系统，
旨在帮助数据库管理员和开发人员更好地监控、调试和优化 SQL Server 的性能。
扩展事件可以用于捕获和分析 SQL Server 内部发生的各种事件，以便识别和解决性能瓶颈和问题。

扩展事件优点包括轻量级、统一事件处理框架和集成性。事件设计对系统性能影响最小，确保在高负载环境下也能稳定运行。
扩展事件可以与 SQL Server Profiler 和 SQL Server Audit 结合使用，为用户提供全面的诊断和监控工具。

 

 

 

实验步骤

创建环境所需的数据库和表

复制代码 
--窗口1
--建表

USE testdb
GO

CREATE TABLE Account(id INT, name NVARCHAR(200))


INSERT INTO [dbo].[Account]
SELECT 1,'Lucy'
UNION ALL
SELECT 2,'Tom'
UNION ALL
SELECT 3,'Marry'

--查询
SELECT * FROM [dbo].[Account]
复制代码

 

 创建扩展事件

 

 输入扩展事件名称

 

 不要使用模版

 

事件库搜索block，选择blocked_process_report

 

确认事件

 

选择你需要的字段

这里选择client_app_name、client_hostname、database_id、database_name、plan_handle、query_hash、request_id、session_id、sql_text字段

当然你可以勾选自己想要的字段，这里只是抛砖引玉

续

直接下一步

这里需要注意的是，扩展事件日志不能全量保存，所以用户需要考虑好保留多长时间的扩展事件，假设一天可以产生的扩展事件大小为1GB，那么每个扩展事件文件大小1GB，最多5个扩展事件文件意味着你不能查询到5天之前的数据

比如你不能查询到前面第8天的扩展事件，扩展事件是滚动利用的。

扩展事件创建情况预览

小提示：你可以点击script生成这个扩展事件的create脚本，那么其他服务器就不用这样用界面去创建这么繁琐了。

生成出来的扩展事件

复制代码 
CREATE EVENT SESSION [slowquerylog]
ON SERVER
    ADD EVENT sqlserver.blocked_process_report
    (ACTION
     (
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.database_id,
         sqlserver.database_name,
         sqlserver.plan_handle,
         sqlserver.query_hash,
         sqlserver.request_id,
         sqlserver.session_id,
         sqlserver.sql_text
     )
    )
    ADD TARGET package0.event_file
    (SET filename = N'E:\DBExtentEvent\slowquerylog.xel')
WITH
(
    STARTUP_STATE = ON
);
GO
复制代码

完成

你可以勾选

a.扩展事件创建完成之后立刻启动

b.查看实时捕获的数据

立刻启动扩展事件

 

一定要设置locked process threshold，否则无办法捕获慢SQL语句，这个选项类似于MySQL的long_query_time参数

locked process threshold是SQL Server2005推出的一个选项，下面设置阻塞10秒就会记录

复制代码 
--窗口2
--locked process threshold是SQL Server2005推出的一个选项

--设置阻塞进程阈值
sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE ;  
GO  
sp_configure 'blocked process threshold', 10 ;   --10秒
GO  
RECONFIGURE ;  
GO
复制代码

执行一个update语句，不要commit

复制代码 
--窗口3
USE testdb;
GO

BEGIN tran
update Account
set name ='Test'
where ID = 2

--commit
复制代码

查询数据

复制代码 
-- 窗口4
USE testdb;
GO

-- 这个查询会被窗口3中的事务阻塞
SELECT * FROM Account
WHERE ID = 2
复制代码

 

执行完毕之后，你可以看到扩展事件已经记录下来了 

双击查看详细的会话里面的语句

可以很清楚的看到谁是被blocked的语句，谁是主动blocking的语句也就是源头

同时可以看到扩展事件已经记录到xel文件

 

 

使用其他编程语言制作慢查询日志报表

微软提供了使用 SQL Server Management Studio (SSMS) 和 T-SQL 查询扩展事件 XEL 文件内容的 API。

我们可以使用 sys.fn_xe_file_target_read_file 函数来读取 XEL 文件中的内容。
然后，你可以将这些数据导出为其他编程语言可以处理的格式

SQL语句

复制代码 
-- 查询扩展事件 XEL 文件内容
SELECT 
    event_data.value('(event/@name)[1]', 'VARCHAR(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_timestamp,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'INT') AS duration,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'VARCHAR(255)') AS client_app_name,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'VARCHAR(255)') AS client_hostname,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'VARCHAR(255)') AS database_name,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS sql_text
FROM 
    sys.fn_xe_file_target_read_file('E:\DBExtentEvent\slowquerylog*.xel', NULL, NULL, NULL) AS t
CROSS APPLY 
    t.event_data.nodes('event') AS XEvent(event_data);
复制代码



使用 Python 读取 XEL 文件内容
使用 pandas 库和pyodbc驱动程序从 SQL Server 导出数据并在 Python 中进行处理。
以下是一个示例脚本

复制代码 
import pyodbc
import pandas as pd

# 设置数据库连接
conn = pyodbc.connect(
    'DRIVER={SQL Server};'
    'SERVER=your_server_name;'
    'DATABASE=your_database_name;'
    'UID=your_username;'
    'PWD=your_password'
)

# 查询 XEL 文件内容
query = """
SELECT 
    event_data.value('(event/@name)[1]', 'VARCHAR(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_timestamp,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'INT') AS duration,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'VARCHAR(255)') AS client_app_name,
    event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'VARCHAR(255)') AS client_hostname,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'VARCHAR(255)') AS database_name,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS sql_text
FROM 
    sys.fn_xe_file_target_read_file('E:\DBExtentEvent\slowquerylog*.xel', NULL, NULL, NULL) AS t
CROSS APPLY 
    t.event_data.nodes('event') AS XEvent(event_data);
"""

# 使用 pandas 读取数据
df = pd.read_sql(query, conn)

# 关闭数据库连接
conn.close()

# 显示数据
print(df)

# 将数据保存为 CSV 文件
df.to_csv('slowquerylog.csv', index=False)
复制代码

 

这里的一个问题是，你不能直接读取XEL文件，本身XEL文件是一个二进制文件，必须挂接到在线SQL Server实例（任何SQL Server实例都可以，不一定是生产库的那一台SQL Server实例）

 

 

另外一个方法是使用 PowerShell 中的 Microsoft.SqlServer.XEvent.Linq.QueryableXEventData 类直接解析 XEL 文件，不用挂接到SQL Server实例

读取 XEL 文件的内容，然后导出CSV文件，让其他编程语言读取

 

Step 1: 创建 PowerShell 脚本 ReadXELFile.ps1

复制代码 
# 加载所需的程序集
Add-Type -Path "C:\Program Files\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.SqlServer.XEvent.Linq.dll"

# 定义XEL文件路径
$xelFilePath = "E:\DBExtentEvent\slowquerylog*.xel"

# 创建XEventData对象
$events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($xelFilePath)

# 初始化一个空数组来存储事件数据
$eventDataList = @()

# 遍历每个事件并提取所需的字段
foreach ($event in $events) {
    $eventData = New-Object PSObject -Property @{
        EventName      = $event.Name
        Timestamp      = $event.Timestamp
        Duration       = $event.Fields["duration"].Value
        ClientAppName  = $event.Actions["client_app_name"].Value
        ClientHostname = $event.Actions["client_hostname"].Value
        DatabaseName   = $event.Actions["database_name"].Value
        SqlText        = $event.Actions["sql_text"].Value
    }
    $eventDataList += $eventData
}

# 将事件数据导出为CSV文件
$eventDataList | Export-Csv -Path "E:\DBExtentEvent\slowquerylog.csv" -NoTypeInformation
复制代码

 

Step 2: Python 脚本 ReadCSVFile.py读取导出的 CSV 文件

复制代码 
import pandas as pd

# 定义CSV文件路径
csv_file_path = "E:\\DBExtentEvent\\slowquerylog.csv"

# 使用pandas读取CSV文件
df = pd.read_csv(csv_file_path)

# 显示数据
print(df)
复制代码

这个方法需要使用powershell，对于powershell不熟悉的朋友也是一个问题

 

 

总结

本文介绍了SQL Server的扩展捕获慢查询语句的功能，也就是我们所说的慢日志

另外，一定要设置“blocked process threshold”参数，否则设置了扩展事件也没有效果

总体来说，SQL Server作为一个企业级数据库，确实不像MySQL这种开源数据库简单直接

需要设置比较繁琐的扩展事件，对新手用户不太友好，门槛比较高，但是因为扩展事件功能非常强大

除了捕获慢查询还可以捕获死锁，索引缺失等性能问题，所以这个是在所难免的

 

标签：name,value,Server,SQL,日志,data,event
From： https://www.cnblogs.com/Leo_wl/p/18328980

相关文章

  • Linux基本操作手册与mysql知识
    Linux基本操作手册修改主机名：vim/etc/hostname修改主机hosts文件：vim/etc/hosts创建用户：useraddusername删除用户：userdel-rusername(不加-r只是删除用户，无法删除用户原本主目录)修改用户名：usermod-l新用户旧用户,(不建议修改，应改选择删除而重新创建，因为修改......
  • docker启动MySQL容器演示（centos）
    环境配置vboxcentos7.9docker(已经配置阿里镜像源)首先拉取MySQL镜像dockerpullmysql:版本号版本号不打也可以，会默认下载最新版(latest)下载后使用dockerimages查看镜像如图我下载了MySQL5.7/8.0/latest版本然后执行下面的命令就可以启动（运行）容器了dockerrun-d-......
  • MYSQL数据库（二）
    二、数据库中的约束1.非空约束（ONTNULL）字段的值不能为空2.唯一约束（UNIQUE）字段的值不能重复3.主键约束（PRIMARYKEY）主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行，通过它可以强制表的实体完整性，效果上相当于非空约束+唯一约束4.外键约束（FOREIGNKEY）作用：......
  • mysql中的事务
     一、事务的定义        事务（Transaction），就是将一组SQL语句放在同一批次内去执行，如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行。特点：一个事务中如果有一个数据库操作失败，那么整个事务的所有数据库操作都会失败，数据库数据就会回滚到该事务开始之前的状......
  • Java如何通过日志排查问题
    问题：大家有没有遇到过这样一种场景，在一个接口或者方法当中，业务逻辑很复杂，方法嵌套调用层级很深，此时要定位业务流程的走向，是不是要在每个方法中打日志，而这些日志是不串联的，比如，一个接口调用下来，程序没有报错，但没有按预期执行，怎么排查，就得翻这个方法调用时的每一条记录日志，而代......
  • 设计模式实战：日志系统的设计与实现
    问题描述设计一个日志系统，支持在应用程序中记录日志信息。系统需要确保日志记录器是唯一的实例，支持不同的日志记录方法（如文件、数据库），并且能够适配不同的日志格式（如JSON、XML）。设计分析单例模式单例模式确保一个类只有一个实例，并提供一个全局访问点。日志系统中的日志记......
  • mysql系列：sql执行原理
    sql执行流程当谈到数据库管理系统（DBMS）的核心功能时，SQL执行引擎无疑是其中最关键的部分之一。SQL执行引擎负责将我们编写的结构化查询语言（SQL）转化为实际可以操作数据库的物理操作序列。它不仅仅是一个简单的语法解析器，更是一种复杂的软件模块，涉及到查询优化、执行计划生成、......
  • MYSQL如何实现将数据同步到ES?
    MYSQL如何实现将数据同步到ES?这个问题既要考虑到既能保持数据一致性,又能够保证实时性的一个解决方案三种方式:最直接的就是在业务代码中去实现双写,也就是说每一次修改MySQL数据的时候,同时更新到ES中,注意:这种方法耦合度很高,一旦发生需求变更会有额外的一个维护......
  • LeetCode_sql_day07(579. 查询员工的累计薪水，2173.最多连胜的次数)
    描述：579.查询员工的累计薪水编写一个解决方案，在一个统一的表中计算出每个员工的 累计工资汇总 。员工的 累计工资汇总 可以计算如下:对于该员工工作的每个月，将 该月 和 前两个月 的工资 加 起来。这是他们当月的 3个月总工资和 。如果员工在前几个月没有为公......
  • SQL Server数据库的黑匣子：实现自定义日志记录
    SQLServer数据库的黑匣子：实现自定义日志记录在数据库管理中，日志记录是监控和审计数据库活动的重要手段。SQLServer提供了多种日志记录机制，但有时这些默认的日志记录可能无法满足特定的业务需求。在这种情况下，实现数据库的自定义日志记录就显得尤为重要。本文将详细介绍如......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99