背景
在业务使用中，有时需要了解实例的性能情况，影响性能最终的原因还是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];
