SQL Server通过扩展事件捕获DDL

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: SQL Server通过扩展事件捕获DDL


背景

在业务使用中,有时需要了解实例的性能情况,影响性能最终的原因还是SQL的执行,其中DDL操作就是最重要的一环,所以了解所有的DDL执行情况就是重中之重。本文介绍通过使用扩展事件来捕获所有的DDL操作。

目的

通过使用SQL Server扩展事件技术,来捕获数据库使用中产生的所有DDL,为性能诊断提供技术手段。


扩展事件

扩展事件(Extended Events)引入于2008版本,具有高度可伸缩可配置的体系结构,使用户能够按需收集解决性能问题或确定问题所需的信息。扩展事件是轻型性能监视系统,性能消耗非常少。

特点

  • 事件同步触发,但可同步或异步进行处理。
  • 几乎任何动作都可以与任何事件配对,从而能够更深入地监控系统。
  • 支持大量谓词,允许使用布尔逻辑来构建复杂的过滤规则。
  • 可以使用 Transact-SQL 对扩展事件会话进行全面控制。
  • 可以监控性能关键代码而几乎不会对性能产生影响。

捕获DDL操作流程


1、检查并删除同名的扩展事件

 

if exists (select*from sys.server_event_sessionswhere name ='audit_ddl')drop event session audit_ddl on server;


2、创建扩展事件

CREATE EVENT SESSION [audit_ddl]ON SERVER 
ADD EVENT sqlserver.object_altered(    ACTION(sqlserver.database_name,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)WHERE(([database_id]>(4)))AND([object_type]<>('STATISTICS'))),ADD EVENT sqlserver.object_created(    ACTION(sqlserver.database_name,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)WHERE(([database_id]>(4)))AND([object_type]<>('STATISTICS'))),ADD EVENT sqlserver.object_deleted(    ACTION(sqlserver.database_name,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username,sqlserver.sql_text,sqlserver.username)WHERE((([database_id]>(4)))AND([object_type]<>('STATISTICS'))))ADD TARGET package0.event_file(SET filename=N'D:\MSSQL\EventLog\audit_ddl.xel',max_file_size=(50),max_rollover_files=(10))WITH (STARTUP_STATE=ON);

说明:

  • [database_id]>(4) 条件,去除系统数据库
  • [object_type]<>('STATISTICS') 条件,去除统计信息对象
  • package0.event_file ,添加存储路径,此为异步消耗事件,设置文件最大50MB,最多10个文件进行滚动存储
  • [ddl_phase]=('Commit') 有三个阶段,Begin,Commit,Rollback。此语句没有对 ddl_phase 进行过滤。方法观察DDL整体的运行情况。

3、启动扩展事件

alter event session [audit_ddl]on server state=start;


验证效果


1、测试

CREATETABLE tabl (n intnotnull);-- 普通表CREATETABLE #tab1 (n intnotnull);-- 临时表altertable tabl add CONSTRAINT PK_tab PRIMARY KEY CLUSTERED (n);-- 普通表 添加主键聚集索引altertable #tab1 add CONSTRAINT PK_#tab PRIMARY KEY CLUSTERED (n);-- 临时表 添加主键聚集索引DROPTABLE tabl;-- 删除DROPTABLE #tab1;-- 删除


2、查看效果

  • 捕获表的创建、修改、删除事件


  • 创建表的Begin阶段


  • 创建表的Commit阶段


将XML转换成表格输出


1、获取XML路径

select s.nameas xe_session_name,    cast(st.target_dataas xml)as target_data
from sys.dm_xe_sessions s 
inner join sys.dm_xe_session_targets st 
on s.address=st.event_session_addresswhere s.name='audit_ddl';


2、获取XML结构

select cast(event_data as xml) event_data,*from sys.fn_xe_file_target_read_file(N'D:\MSSQL\EventLog\audit_ddl_0_133167564005860000.xel',NULL,NULL,NULL);


3、根据XML结构输出表格

SELECT DATEADD(hour,8,[XML Data].value('(/event[@package=''sqlserver'']/@timestamp)[1]','DATETIME'))AS[TimeStamp],[XML Data].value('(/event/action[@name=''database_name'']/value)[1]','SYSNAME')AS[Database Name],[XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','SYSNAME')AS[SQL_Text],[XML Data].value('(/event/action[@name=''client_hostname'']/value)[1]','SYSNAME')AS client_hostname,[XML Data].value('(/event/action[@name=''client_app_name'']/value)[1]','SYSNAME')AS client_app_name,[XML Data].value('(/event/action[@name=''username'']/value)[1]','SYSNAME')AS username,[XML Data].value('(/event/data[@name=''object_name'']/value)[1]','SYSNAME')AS[object_name],[XML Data].value('(/event/data[@name=''object_type'']/text)[1]','SYSNAME')AS object_type,[XML Data].value('(/event/data[@name=''transaction_id'']/value)[1]','BIGINT')AS[transaction_id],[XML Data].value('(/event/data[@name=''ddl_phase'']/text)[1]','SYSNAME')AS ddl_phase
FROM(SELECT CONVERT(XML, event_data)AS[XML Data]FROM  sys.fn_xe_file_target_read_file(N'D:\MSSQL\EventLog\audit_ddl_0_133167570445450000.xel',NULL,NULL,NULL))AS v orderby[TimeStamp];

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
18天前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
1月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数(三)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
3月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
72 13
|
3月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
2月前
|
SQL 存储 关系型数据库
|
3月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
51 6
|
2月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
270 0
|
3月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
197 1
|
3月前
|
SQL 监控 数据库
SQL Server 查询超时问题排查
【7月更文挑战第8天】排查 SQL Server 查询超时涉及五个主要方面:检查复杂查询、评估服务器性能、审视配置参数、更新统计信息和分析执行计划。关注点包括查询的结构(如连接、子查询和索引),服务器资源(CPU、内存、网络延迟),连接和内存设置,以及统计信息的时效性。通过这些步骤可定位并解决性能瓶颈。
|
3月前
|
SQL 存储 Oracle
TDengine 3.3.2.0 发布:新增 UDT 及 Oracle、SQL Server 数据接入
**TDengine 3.3.2.0 发布摘要** - 开源与企业版均强化性能,提升WebSocket、stmt模式写入与查询效率,解决死锁,增强列显示。 - taos-explorer支持geometry和varbinary类型。 - 企业版引入UDT,允许自定义数据转换。 - 新增Oracle和SQL Server数据接入。 - 数据同步优化,支持压缩,提升元数据同步速度,错误信息细化,支持表名修改。 - 扩展跨平台支持,包括麒麟、Euler、Anolis OS等。
101 0