“谁把我的表给删拉”，”谁删了整个表阿”…碰到这种棘手的情况，你如果没有预先做好准备，真的是头都要急炸了。那怎么能抓出这个”凶手”呢？sql trace, sql profile，sql trigger，extended events等着伺候你呢，更别说clr，service broker等重量级武器了
“谁把我的表给删拉”，”谁删了整个表阿”…碰到这种棘手的情况，你如果没有预先做好准备，真的是头都要急炸了。那怎么能抓出这个”凶手”呢？sql trace, sql profile，sql trigger，extended events等着伺候你呢，更别说clr，service broker等重量级武器了，别急！
工具虽好，但是也得用得贴合场景才能发挥作用，要不然跟你的sql server抢io，那就得不偿失了。比如高频的oltp场合，你还用trigger把大量的dml语句都写到当前数据库去，那不是给数据库增加一倍工作量么。当然控制好权限，做好测试也能保证你的数据安全。这不妨碍我们讨论auditing sql。
sql profiler是基于sql trace的，而sql trace是会被更高版本的sql server给逐渐摒弃的，所以我们就只讨论一个就可以了。一开始我知道 sql profiler是可以可视化监控即时的sql server活动的，但是缺点是不能保存或者自动执行，需要人工干预。经过研究，它是可以自动保存截取结果的。
让我们认识下“源”：想要抓“破坏分子”，首先要知道“破环分子”的标示有哪些，漏抓，错抓，都是失手的表现。访问sql server的方式有很多种，有ado.net, jdbc, sql server management studio, odbc等各种方法，也有 ad-hoc sql, stored procedure等表现形式，是否每一种的方式都有各自不同的格式呢？最好的方法就是针对每种方式都来做个例子验证下。
首先最简单的ssms方式，我们将sql profiler限制到某一个数据库，lenistest4。 在ssms里面输入：
use lenistest4go
sql profiler显示的是sql:batchcompleted。
这里与有没有go无关。当你选中一块sql区域并执行，其中如果有go那就有关系了，比如：
select top 10 * from sys.tablesgoselect top 10 * from dbo.regiongo
这里一个go分割了一个batch。最后一个go没有意义，我们提交了一段代码，这段代码相当于是一个大batch，如果中间有go，那这个go 就代表了一个子batch。
那么用动态语句，会有什么trace格式呢：
declare @sqlstatement nvarchar(max) = n'select top 10 * from dbo.region' ;exec sp_executesql @sqlstatement
这里有sql:batch*, sql:stmt*,sp:stmt， sql:stmt*代表的是一系列的sql命令，比如declare, set等；sp:stmt*代表了stored procedure的范围内语句。
declare @sqlstatement nvarchar(max) = n'declare @regionname varchar(20) = ''china''select top 10 * from dbo.regionwhere regionname = @regionname' ;exec sp_executesql @sqlstatement
上面这段sql，验证了sql命令select和declare是不是都被看作是sp:stmt* ? 其实在一个batch里面，sql命令里面dml语句也是被当作statement来处理的。
如果在动态sql里面有go是不是也会有sp:batch*? 我们接着往下看：
declare @sqlstatement nvarchar(max) = n'declare @regionname varchar(20) = ''china''select top 10 * from dbo.regionwhere regionname = @regionname;godeclare @regionname2 varchar(20) = ''england''select top 10 * from dbo.regionwhere regionname = @regionname2' ;exec sp_executesql @sqlstatement
msg 102, level 15, state 1, line 12
incorrect syntax near ‘go’.
可以看到go是不能用在动态语句里面的。
为了验证sp:stmt*是不是指的是存储过程里面的语句，我们先创建一个stored procedure,然后再执行它：
create procedure dbo.getregionnameasbegindeclare @regionname varchar(20) = 'china'select top 10 * from dbo.regionwhere regionname = @regionnamedeclare @regionnamex varchar(20) = 'england'select top 10 * from dbo.regionwhere regionname = @regionnamexendexec dbo.getregionname
正是如此 ！综上所述， sql:batch* ， 这里的batch相当于是个scope，一个大的执行空间，里面的所有 sql 语句都是statement，包括dml，ddl等一系列 t-sql语句 ；而sp:stmt*又是存储过程的执行空间，里面所有的 t-sql语句都是statement。
下面看段c#调用这个stored procedure，看看trace是如何识别的：
using system;using system.collections.generic;using system.linq;using system.text;using system.threading.tasks;using system.data.sqlclient;using system.data.sqltypes;using system.data;namespace accesslenistest4db{ class program { static void main(string[] args) { sqlconnection iconn = new sqlconnection(@data source = (localhost);initial catalog = lenistest4 ;user id = sa; password = sas;); sqlcommand icmd = new sqlcommand(); icmd.connection = iconn; icmd.commandtype = system.data.commandtype.storedprocedure; icmd.commandtext = dbo.getregionname; sqldataadapter ida = new sqldataadapter(icmd); dataset localds = new dataset(); try { iconn.open(); ida.fill(localds); } catch(sqlexception se) { console.write(se.tostring()); } } }}
这儿多了个rpc， remote procedure call。其他都一样，所以rpc可以看作是一种命名空间，用阿里区别访问协议。这里要区别的是我们调用的是stored procedure，所以会rpc。所以如果我们是用纯sql来访问数据库，那会不会有 rpc标示呢：
using system;using system.collections.generic;using system.linq;using system.text;using system.threading.tasks;using system.data.sqlclient;using system.data.sqltypes;using system.data;namespace accesslenistest4db{ class program { static void main(string[] args) { sqlconnection iconn = new sqlconnection(@data source = (localhost);initial catalog = lenistest4 ;user id = sa; password = sas;); sqlcommand icmd = new sqlcommand(); icmd.connection = iconn; icmd.commandtype = system.data.commandtype.text; icmd.commandtext = select * from dbo.region; sqldataadapter ida = new sqldataadapter(icmd); dataset localds = new dataset(); try { iconn.open(); ida.fill(localds); } catch(sqlexception se) { console.write(se.tostring()); } } }}
并没有rpc。 事实证明 rpc只出现在客户端语言调用存储过程的例子。
trace的手段 ：“破坏分子”的特征被识别了，接下来就是怎么去抓捕的手段问题了。可以有即时的gui工具，比如sql profiler，extended event(ssms自带)，也可以用脚本去抓，并放在特定存储里面供稍后分析使用。
sql profiler 是即时的gui工具很好用，可以保存结果，也可以自定义模板，缺点在于你必须开一个额外的窗口去跟踪，有时候数据量太大，还会影响传输，对多太sql server做监控就不怎么容易了，一个一个手工去开窗口，是不是很麻烦 ?
这里有extended events可以帮我们用脚本的形式去捕捉这些t-sql语句，这里有个简单的例子：
通过 extended events ，我们可以监控一段时间内的的 sql completed 情况，简要介绍下：1.1 extended events 概念：由一系列自动触发的 event 产生性能数据，经过 event engine 的收集，存放到指定的输出文件，以供后续的分析。
1.2 xe 涉及到的动态管理试图 : sys.dm_xe_packages; sys.dm_xe_objects;sys.dm_xe_sessions
1.3 基本用法:
2.3.1 创建一个 event session
create event session capture_sql_eventson serveradd event sqlserver.sql_statement_completed(action(sqlserver.sql_text))add target package0.event_file(set filename = 'e:\data_bu\capture_sql_events.xel', metadatafile = 'e:\data_bu\capture_sql_event.xem')go
2.3.2 启用这个 event session 来收集数据
alter event session capture_sql_eventson serverstate = startgo
2.3.3 停用这个 event session
alter event session capture_sql_eventson serverstate = stopgo
2.3.4 修改一个 session 来增加或者删除对 event 的监控
alter event session capture_sql_events on serveradd event sqlserver.sql_batch_completed (action(sqlserver.sql_text))go
2.3.5 查看正在运行的 event session
select * from sys.dm_xe_sessionsselect * from sys.dm_xe_session_events
2.3.6 查看收集到的统计数据
select top 10 * from dbo.regiongoselect * , cast(event_data as xml) as event_data_xmlfrom sys.fn_xe_file_target_read_file('e:\data_bu\capture_sql_events*.xel',null,null,null)where event_data like n'%region%'
从结果集我们可以看到，sql_statement_completed这个event抓到的结果中，包含了action中我们指定的内容，还包含了其他的一些统计信息：
00020661062select top 10 * from dbo.regionselect top 10 * from dbo.region
我们在action里面加入对database_name,和plan_handle的捕捉，可以从结果看到又多出来两个元素：
create event session capture_sql_eventson serveradd event sqlserver.sql_statement_completed(action(sqlserver.sql_text,sqlserver.database_name,sqlserver.plan_handle))add target package0.event_file(set filename = 'e:\data_bu\capture_sql_events.xel', metadatafile = 'e:\data_bu\capture_sql_event.xem')
00020661062select top 10 * from dbo.region06002000448a700f00d62b7a0300000001000000000000000000000000000000000000000000000000000000lenistest4select top 10 * from dbo.region