如何让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. --窗口1
2. --建表
4. USE testdb
5. GO
7. CREATE TABLE Account(id INT, name NVARCHAR(200))
10. INSERT INTO [dbo].[Account]
11. SELECT 1,'Lucy'
12. UNION ALL
13. SELECT 2,'Tom'
14. UNION ALL
15. SELECT 3,'Marry'
17. --查询
18. 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 threshold是SQL Server2005推出的一个选项
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
5. BEGIN tran
6. update Account
7. set name ='Test'
8. where ID = 2
10. --commit

复制代码

查询数据

1. -- 窗口4
2. USE testdb;
3. GO
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);
14.    

复制代码

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

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

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

复制代码

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

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

复制代码

这个方法需要利用powershell，对于powershell不熟悉的朋侪也是一个问题
 
 
总结
本文介绍了SQL Server的扩展捕捉慢查询语句的功能，也就是我们所说的慢日志
另外，一定要设置“blocked process threshold”参数，否则设置了扩展事件也没有效果
总体来说，SQL Server作为一个企业级数据库，确实不像MySQL这种开源数据库简单直接
需要设置比较繁琐的扩展事件，对新手用户不太友好，门槛比较高，但是因为扩展事件功能非常强大
除了捕捉慢查询还可以捕捉死锁，索引缺失等性能问题，所以这个是在所不免的
 
 
 
 
 
本文版权归作者所有，未经作者同意不得转载。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。