SQL Server SQL语句跟踪之Profiler
Tags： ProfilerSQL ServerSQL跟踪
简介
SQL 语句跟踪，是DBA必备的一项技能。在日常运维中，主要用于性能问题排查、业务问题排查、慢SQL跟踪、死锁跟踪、审计等。
在实际场景中，我曾遇到过多种需要进行SQL跟踪排查问题的案例。某个产品功能，开发与用户操作都没提示错误，但功能操作结果是异常的，这种时候他们就找到DBA进行实时SQL跟踪排查。类似的，多租户操作异常问题，按某个租户进行SQL跟踪排查。
我们可以定义两种SQL 跟踪，一种是长期跟踪，一种是临时跟踪。
- 长期跟踪，通常为性能与审计相关的跟踪，如慢SQL、死锁、审计、等待/堵塞等，将长期为系统解决性能及安全问题。
- 临时跟踪，主要用在临时的异常功能排查、SQL语句性能、客户问题排查。
那么有哪些工具和方法可以跟踪呢？
一、SQL Server Profiler
SQL Server Profiler 是 SQL Trace 的图形用户界面，用于监视数据库引擎或者分析服务的实例。SQL Server Profiler 现主要用于临时的 SQL 语句跟踪，并保持实时刷新。当然，它可以将数据保存到文件或者保存到表中（不过仍需要保留界面实时刷新）。
SQL Server Profiler 有二十多个事件类（下图），点击左下方的“显示所有事件”则可全部显示，每个事件类有多个事件可选跟踪。右下角的“列筛选器”，可以对某个字段进行筛选跟踪。如跟踪耗时（Duration）大于某个时间的事件、跟踪某个数据库或用户产生的事件等。
SQL Server Profiler 工具需要界面实时刷新，短期跟踪非常不方便，因为UI刷新需要缓存较多数据。使用 T-SQL 创建也是比较方便的，如 sp_trace_create 等存储过程创建事件跟踪。有一种比较方便的方法，可以将 SQL Server Profiler 定义好的跟踪生产 SQL 脚本。（在 Profiler 中点击菜单“文件”—>导出——>编写跟踪定义的脚本——>适用于 SQL Server 2005 - 2019）
生成的脚本需要调整一下，设置保存文件路径、文件大小等。不过一定要注意，千万别配置 SHUTDOWN_ON_ERROR，避免因为错误而将实例服务停止。
SQL Server Profiler 是一个比较开销资源相对大的工具，避免在生成环境中使用，避免对所有的 SQL 语句进行跟踪。如果进行长期的 SQL 跟踪，建议使用扩展事件。
二、扩展事件 (XEvents)
扩展事件完全替换了 SQL 跟踪和 SQL Server Profiler 功能。扩展事件具有高度可配置性、轻量级和很好的缩放能力。扩展事件是使用最少性能资源的轻型性能监视系统。SQL Server Management Studio 提供了一个图形用户界面，供扩展事件用于创建和修改会话以及显示和分析会话数据。
使用扩展事件，我们可以跟踪慢 SQL、死锁、堵塞、审计等。如下示例，创建慢 SQL 跟踪。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | CREATE EVENT SESSION [MonitorSlowSQL] ON SERVER ADD EVENT sqlserver.rpc_completed(SET collect_data_stream=(1) ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name ,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[greater_than_equal_uint64]([duration],(3000000)) --3秒 AND (NOT[sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%SQLAgent%')))), ADD EVENT sqlserver.sql_batch_completed(SET collect_batch_text=(1) ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name ,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username) WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)) AND [package0].[greater_than_equal_uint64]([duration],(3000000)) --3秒 AND (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%SQLAgent%')))) ADD TARGET package0.event_file( SET filename=N'C:\trace\MonitorSlowSQL.xel' ,max_file_size=(50) ,max_rollover_files=(10)) WITH ( MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=ON, STARTUP_STATE=ON) GO ALTER EVENT SESSION [MonitorSlowSQL] ON SERVER STATE =START GO |
长期跟踪的扩展事件，我们通常将数据库保存到文件中，方便后续读取。但扩展事件保存的是 XML 类型的数据。需要通过系统函数 fn_xe_file_target_read_file 读取文件内容，并解析 XML 字段数据。示例参考如下。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | select n.value('(@name)[1]', 'varchar(50)') as event_name, n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name, dateadd(hour,8,n.value('(@timestamp)[1]', 'datetime2') ) AS excute_time, n.value('(data[@name="duration"]/value)[1]', 'bigint') as duration, n.value('(data[@name="cpu_time"]/value)[1]', 'bigint') as cpu_time, n.value('(data[@name="logical_reads"]/value)[1]', 'bigint') as logical_reads, n.value('(data[@name="physical_reads"]/value)[1]', 'bigint') as physical_reads, n.value('(data[@name="writes"]/value)[1]', 'bigint') as writes, n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count, n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement, n.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as sql_text, n.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(128)') as client_app_name, n.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(128)') as client_hostname, n.value('(action[@name="username"]/value)[1]', 'nvarchar(128)') as username from ( select cast(event_data as XML) as event_data from sys.fn_xe_file_target_read_file('C:\trace\MonitorSlowSQL*.xel', null, null, null) ) ed cross apply ed.event_data.nodes('event') as q(n) |
SQL Server Management Studio 可以在线查看目标数据，可以进行数据筛选、分组、排序等，非常方便。
三、XEvent 探查器
SQL Server Management Studio 提供了一个非常快捷的 SQL 跟踪功能——XEvent 探查器。XEvent 探查器可显示扩展事件的实时查看器窗口。不同于 SQL Profiler，XEvent 探查器直接集成到 SSMS，基于 SQL 引擎中可缩放的扩展事件技术，对运行的 SQL 服务器具有更低的侵入性。
打开 SSMS 的对象资源管理器，在连接实例的最下方，就是 XEvent 探查器，双击“标准”或者“TSQL”可立即打开跟踪窗口跟踪SQL。
总结
以上三种方法主要用于对 SQL 语句的跟踪，还有多种事件跟踪。SQL 跟踪的目的是解决系统问题、保证系统安全稳定运行。
SQL Server 的跟踪，还有默认跟踪（default trace enabled）、数据收集、Windows性能计数器、MSSQL 实例性能计数器（sys.dm_os_performance_counters）、审核（审计）、服务器触发器、SQL Server日志、错误日志、查询存储、SQL缓存等待。跟踪是为了方便解决问题，了解跟踪方法可以提高我们定位问题能力。
