背景
在业务使用中，经常会遇应用响应缓慢的影响，通过第一时间需要排查的问题就是数据库性能问题，这时我们就需要查找哪些SQL需要优化？而SQL Server 并没有像MySQL直接记录慢SQL的功能，本文介绍通过使用扩展事件来捕获慢SQL。
目的
通过使用SQL Server扩展事件技术，来捕获数据库使用中产生的慢SQL，为性能优化提供便利。
SQL Server扩展事件
扩展事件体系结构使用户能够收集尽可能多的或少的数据，以排查或识别SQL Server实例中的性能问题。 扩展事件高度可配置、轻型且可很好地缩放。
捕获慢SQL操作流程
1、检查已经有的扩展事件
select*from sys.server_event_sessions;
2、删除扩展事件
if exists (select*from sys.server_event_sessionswhere name ='slow_query')drop event session slow_query on server
3、创建扩展事件
create event session [slow_query]on server add event sqlserver.rpc_completed(action(sqlos.task_time,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)where sqlserver.database_name='db01'-- and sqlserver.username = 'username'and([duration]>=3000000or[row_count]>=10000or[physical_reads]>=12800or[logical_reads]>=128000or[cpu_time]>=500000or[writes]>=12800)), add event sqlserver.sql_batch_completed( action(sqlos.task_time,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)where sqlserver.database_name='db01'-- and sqlserver.username = 'username'and([duration]>=3000000or[row_count]>=10000or[physical_reads]>=12800or[logical_reads]>=128000or[cpu_time]>=500000or[writes]>=12800)) add target package0.event_file(set filename=N'D:\MSSQL\EventLog\slow_query.xel',max_file_size=(10)) with(startup_state=on)
说明：
- create event：创建扩展事件语句
- slow_query：扩展事件名称
- add event：添加事件
- sqlserver.rpc_completed：包名（sqlserver SQL Server 相关对象）：事件名称（rpc_completed 远程过程调用完成时触发）
- add action 添加全局字段的输出
- where : 条件筛选
- sqlserver.database_name 筛选指定的数据库名称
- sqlserver.username 筛选指定的账号名称
- [duration]>=3000000 筛选执行时间大于3秒操作
- [row_count]>=10000 筛选返回行数大于10000操作
- [physical_reads] >= 12800 筛选物理读取大于100MB操作 128 * 100 * 8K
- [logical_reads] >= 12800 筛选逻辑读取大于1GB操作 128 * 1000 * 8K
- [cpu_time] >= 500000 筛选cpu执行时间大于0.5秒操作
- [writes] >= 12800 筛选写入大于100MB操作 128 * 100 * 8K
- add target：添加指定的文件输出
列出所有的action
select dxp.name,dxo.name,dxo.description,dxp.descriptionfrom sys. dm_xe_objects dxo inner join sys.dm_xe_packages dxp on dxo.package_guid= dxp.guidwhere dxo.object_type='action';
4、开启扩展事件
alter event session slow_query on server state=start;
验证效果
Case 1：执行12秒的SQL
Case 2：返回行数10000行
将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='slow_query';
结果展示 1
结果展示 2
2、通过命令分析XML结构
select cast(event_data as xml) event_data,*from sys.fn_xe_file_target_read_file(N'D:\MSSQL\EventLog\slow_query_0_133166686786020000.xel',NULL,NULL,NULL)where OBJECT_NAME='sql_batch_completed';
结果展示 1
XML结构展示 2
3、根据XML转换表格类型
sql_batch_completed 类型转换
SELECT DATEADD(hour,8,[XML Data].value('(/event[@name=''sql_batch_completed'']/@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=''username'']/value)[1]','SYSNAME')AS username,[XML Data].value('(/event/action[@name=''task_time'']/value)[1]','BIGINT')AS task_time,[XML Data].value('(/event/data[@name=''duration'']/value)[1]','BIGINT')AS[Duration (us)],[XML Data].value('(/event/data[@name=''cpu_time'']/value)[1]','BIGINT')AS[cpu_time],[XML Data].value('(/event/data[@name=''physical_reads'']/value)[1]','BIGINT')AS physical_reads,[XML Data].value('(/event/data[@name=''logical_reads'']/value)[1]','BIGINT')AS logical_reads,[XML Data].value('(/event/data[@name=''writes'']/value)[1]','BIGINT')AS writes,[XML Data].value('(/event/data[@name=''row_count'']/value)[1]','BIGINT')AS row_count,[XML Data].value('(/event/data[@name=''batch_text'']/value)[1]','SYSNAME')AS batch_text FROM(SELECT CONVERT(XML, event_data)AS[XML Data]FROM sys.fn_xe_file_target_read_file(N'D:\MSSQL\EventLog\slow_query_0_133166686786020000.xel',NULL,NULL,NULL)where OBJECT_NAME='sql_batch_completed')AS v;
rpc_completed 类型转换
SELECT DATEADD(hour,8,[XML Data].value('(/event[@name=''rpc_completed'']/@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=''username'']/value)[1]','SYSNAME')AS username,[XML Data].value('(/event/action[@name=''task_time'']/value)[1]','BIGINT')AS task_time,[XML Data].value('(/event/data[@name=''duration'']/value)[1]','BIGINT')AS[Duration (us)],[XML Data].value('(/event/data[@name=''cpu_time'']/value)[1]','BIGINT')AS[cpu_time],[XML Data].value('(/event/data[@name=''physical_reads'']/value)[1]','BIGINT')AS physical_reads,[XML Data].value('(/event/data[@name=''logical_reads'']/value)[1]','BIGINT')AS logical_reads,[XML Data].value('(/event/data[@name=''writes'']/value)[1]','BIGINT')AS writes,[XML Data].value('(/event/data[@name=''row_count'']/value)[1]','BIGINT')AS row_count FROM(SELECT CONVERT(XML, event_data)AS[XML Data]FROM sys.fn_xe_file_target_read_file(N'D:\MSSQL\EventLog\slow_query_0_133166686786020000.xel',NULL,NULL,NULL)where OBJECT_NAME='rpc_completed')AS v;
结果查询
