经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 数据库/运维 » MySQL » 查看文章
如何让SQL Server像MySQL一样拥有慢查询日志(Slow Query Log慢日志)
来源:cnblogs  作者:桦仔  时间:2024/7/29 9:29:57  对本文有异议

如何让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. --窗口1
  2. --建表
  3.  
  4. USE testdb
  5. GO
  6.  
  7. CREATE TABLE Account(id INT, name NVARCHAR(200))
  8. INSERT INTO [dbo].[Account]
  9. SELECT 1,'Lucy'
  10. UNION ALL
  11. SELECT 2,'Tom'
  12. UNION ALL
  13. SELECT 3,'Marry'
  14.  
  15. --查询
  16. 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脚本,那么其他服务器就不用这样用界面去创建这么繁琐了。

生成出来的扩展事件

  1. CREATE EVENT SESSION [slowquerylog]
  2. ON SERVER
  3. ADD EVENT sqlserver.blocked_process_report
  4. (ACTION
  5. (
  6. sqlserver.client_app_name,
  7. sqlserver.client_hostname,
  8. sqlserver.database_id,
  9. sqlserver.database_name,
  10. sqlserver.plan_handle,
  11. sqlserver.query_hash,
  12. sqlserver.request_id,
  13. sqlserver.session_id,
  14. sqlserver.sql_text
  15. )
  16. )
  17. ADD TARGET package0.event_file
  18. (SET filename = N'E:\DBExtentEvent\slowquerylog.xel')
  19. WITH
  20. (
  21. STARTUP_STATE = ON
  22. );
  23. GO

完成

你可以勾选

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

b.查看实时捕获的数据

立刻启动扩展事件

 

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

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

  1. --窗口2
  2. --locked process thresholdSQL Server2005推出的一个选项
  3.  
  4. --设置阻塞进程阈值
  5. sp_configure 'show advanced options', 1 ;
  6. GO
  7. RECONFIGURE ;
  8. GO
  9. sp_configure 'blocked process threshold', 10 ; --10
  10. GO
  11. RECONFIGURE ;
  12. GO

执行一个update语句,不要commit

  1. --窗口3
  2. USE testdb;
  3. GO
  4.  
  5. BEGIN tran
  6. update Account
  7. set name ='Test'
  8. where ID = 2
  9.  
  10. --commit

查询数据

  1. -- 窗口4
  2. USE testdb;
  3. GO
  4.  
  5. -- 这个查询会被窗口3中的事务阻塞
  6. SELECT * FROM Account
  7. WHERE ID = 2

 

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

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

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

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

 


 

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

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

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

SQL语句

  1. -- 查询扩展事件 XEL 文件内容
  2. SELECT
  3. event_data.value('(event/@name)[1]', 'VARCHAR(50)') AS event_name,
  4. event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_timestamp,
  5. event_data.value('(event/data[@name="duration"]/value)[1]', 'INT') AS duration,
  6. event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'VARCHAR(255)') AS client_app_name,
  7. event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'VARCHAR(255)') AS client_hostname,
  8. event_data.value('(event/action[@name="database_name"]/value)[1]', 'VARCHAR(255)') AS database_name,
  9. event_data.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS sql_text
  10. FROM
  11. sys.fn_xe_file_target_read_file('E:\DBExtentEvent\slowquerylog*.xel', NULL, NULL, NULL) AS t
  12. CROSS APPLY
  13. t.event_data.nodes('event') AS XEvent(event_data);



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

  1. import pyodbc
  2. import pandas as pd
  3. # 设置数据库连接
  4. conn = pyodbc.connect(
  5. 'DRIVER={SQL Server};'
  6. 'SERVER=your_server_name;'
  7. 'DATABASE=your_database_name;'
  8. 'UID=your_username;'
  9. 'PWD=your_password'
  10. )
  11. # 查询 XEL 文件内容
  12. query = """
  13. SELECT
  14. event_data.value('(event/@name)[1]', 'VARCHAR(50)') AS event_name,
  15. event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_timestamp,
  16. event_data.value('(event/data[@name="duration"]/value)[1]', 'INT') AS duration,
  17. event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'VARCHAR(255)') AS client_app_name,
  18. event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'VARCHAR(255)') AS client_hostname,
  19. event_data.value('(event/action[@name="database_name"]/value)[1]', 'VARCHAR(255)') AS database_name,
  20. event_data.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(MAX)') AS sql_text
  21. FROM
  22. sys.fn_xe_file_target_read_file('E:\DBExtentEvent\slowquerylog*.xel', NULL, NULL, NULL) AS t
  23. CROSS APPLY
  24. t.event_data.nodes('event') AS XEvent(event_data);
  25. """
  26.  
  27. # 使用 pandas 读取数据
  28. df = pd.read_sql(query, conn)
  29. # 关闭数据库连接
  30. conn.close()
  31. # 显示数据
  32. print(df)
  33. # 将数据保存为 CSV 文件
  34. df.to_csv('slowquerylog.csv', index=False)

 

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

 

 

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

直接读取 XEL 文件的内容,然后导出CSV文件,让其他编程语言处理

 

Step 1: 创建 PowerShell 脚本 ReadXELFile.ps1

  1. # 加载所需的程序集
  2. Add-Type -Path "C:\Program Files\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.SqlServer.XEvent.Linq.dll"
  3.  
  4. # 定义XEL文件路径
  5. $xelFilePath = "E:\DBExtentEvent\slowquerylog*.xel"
  6.  
  7. # 创建XEventData对象
  8. $events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($xelFilePath)
  9. # 初始化一个空数组来存储事件数据
  10. $eventDataList = @()
  11. # 遍历每个事件并提取所需的字段
  12. foreach ($event in $events) {
  13. $eventData = New-Object PSObject -Property @{
  14. EventName = $event.Name
  15. Timestamp = $event.Timestamp
  16. Duration = $event.Fields["duration"].Value
  17. ClientAppName = $event.Actions["client_app_name"].Value
  18. ClientHostname = $event.Actions["client_hostname"].Value
  19. DatabaseName = $event.Actions["database_name"].Value
  20. SqlText = $event.Actions["sql_text"].Value
  21. }
  22. $eventDataList += $eventData
  23. }
  24. # 将事件数据导出为CSV文件
  25. $eventDataList | Export-Csv -Path "E:\DBExtentEvent\slowquerylog.csv" -NoTypeInformation

 

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

  1. import pandas as pd
  2. # 定义CSV文件路径
  3. csv_file_path = "E:\\DBExtentEvent\\slowquerylog.csv"
  4.  
  5. # 使用pandas读取CSV文件
  6. df = pd.read_csv(csv_file_path)
  7. # 显示数据
  8. print(df)

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

 


 

总结

本文介绍了利用【SQL Server的扩展事件】捕获慢查询语句的功能,也就是我们常说的开源数据库的慢日志

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

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

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

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

 

 

本文版权归作者所有,未经作者同意不得转载。

原文链接:https://www.cnblogs.com/lyhabc/p/18322758/Let-SQLServer-have-slowquerylogging-like-MySQL

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号