如何使用SQL Server扩展事件来抓取DDL操作指南
背景
在业务使用中，有时需要了解实例的性能情况，影响性能最终的原因还是SQL的执行，其中DDL操作就是最重要的一环，所以了解所有的DDL执行情况就是重中之重。本文介绍通过使用扩展事件来捕获所有的DDL操作。
目的
通过使用SQL Server扩展事件技术，来捕获数据库使用中产生的所有DDL，为性能诊断提供技术手段。
扩展事件
扩展事件（Extended Events）引入于2008版本，具有高度可伸缩可配置的体系结构，使用户能够按需收集解决性能问题或确定问题所需的信息。扩展事件是轻型性能监视系统，性能消耗非常少。
特点
- 事件同步触发，但可同步或异步进行处理。
- 几乎任何动作都可以与任何事件配对，从而能够更深入地监控系统。
- 支持大量谓词，允许使用布尔逻辑来构建复杂的过滤规则。
- 可以使用 Transact-SQL 对扩展事件会话进行全面控制。
- 可以监控性能关键代码而几乎不会对性能产生影响。
捕获DDL操作流程
1、检查并删除同名的扩展事件
if exists (select * from sys.server_event_sessions where 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、测试
CREATE TABLE tabl (n int not null); -- 普通表 CREATE TABLE #tab1 (n int not null); -- 临时表 alter table tabl add CONSTRAINT PK_tab PRIMARY KEY CLUSTERED (n); -- 普通表 添加主键聚集索引 alter table #tab1 add CONSTRAINT PK_#tab PRIMARY KEY CLUSTERED (n); -- 临时表 添加主键聚集索引 DROP TABLE tabl; -- 删除 DROP TABLE #tab1; -- 删除
2、查看效果
- 捕获表的创建、修改、删除事件
- 创建表的Begin阶段
- 创建表的Commit阶段
将XML转换成表格输出
1、获取XML路径
select s.name as xe_session_name, cast(st.target_data as xml) as target_data from sys.dm_xe_sessions s inner join sys.dm_xe_session_targets st on s.address=st.event_session_address where 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 order by [TimeStamp];
上一篇： 细粒度行级别安全控制