SQL Server：如何查找执行了哪些行 [英] SQL Server: How to find what lines are executed
问题描述
我正在为SQL Server设计一个变异测试框架，为此，我需要能够计算在执行某个存储过程时执行存储过程，函数或触发器的哪些行。
I am working on a mutation test framework for SQL Server, for this I need to be able to calculate what lines of a stored procedure, function or trigger are executed when I execute a certain stored procedure.
困难的部分是我想知道我调用的存储过程中正在执行的确切行或语句。
The difficult part is that I want to know the exact lines or statements being executed from the stored procedure I call.
带有查询像这样，我可以看到正在执行哪些存储过程/触发器/函数，因为我知道当我调用存储过程时，我可以花时间查看它是否已执行。
With a query like this I can see what stored procedures/triggers/functions are being executed, since I know when I call the stored procedure I can use the time to see if it was executed.
SELECT d.object_id, d.database_id,
OBJECT_NAME(object_id, database_id) AS proc_name,
MAX( d.last_execution_time) as last_execution_time,
OBJECT_DEFINITION(object_id) as definition
FROM sys.dm_exec_procedure_stats AS d
WHERE d.database_id = DB_ID()
GROUP BY d.object_id, d.database_id,
OBJECT_NAME(object_id, database_id)
我如何找到已执行的行/语句，我也必须知道这些行/语句存在什么存储过程/触发器/函数，以及在哪个存储区中。我必须考虑到可以使用IF / ELSE语句。
How would I find the lines/statements that have been executed, I also have to know inside what stored procedure/trigger/function the lines/statements exists and in which shema this is. I have to take into account that a IF/ELSE statement may be used.
有了这些数据，我可以做2件重要的事情：
With this data I can do 2 important things:
- 生成代码覆盖率报告
- 优化要突变的行，因为我不必突变未发现的行。
一种可能但不是很好的解决方案是自动更改存储过程以添加将上一行插入表中的行，但这将需要将过程分成语句，我不知道该怎么做。
A possible, but not a very nice, solution would be to automaticly change stored procedures to add a line that inserts the previous line into a table, but this will require splitting up the procedure into statements, which I don't know how to do.
请注意，我无法更改用户要使用我的框架进行测试的代码。我可以搜索模式并进行替换，但是不能选择手动更改过程。
Please note that I cannot change the code users want to test with my framework. I can search for patterns and replace but manually changing procedures is NOT a option.
编辑：
让我们重新思考一下这个问题：如何将存储过程定义拆分为它不依赖于代码样式的不同语句？
和如何在找到的语句之间添加新语句？
Lets redifine this question: How to split a stored procedure definition into its different statements in a way that does not depend on code style? and How to add a new statement in between found statements?
编辑：在SO帖子 SQL Server：如何将代码解析为不同的语句我找到了一种跟踪语句执行的方法，
in the SO post SQL Server: How to parse code into its different statements I have found a way to trace statement execution, but I can't filter it yet.
推荐答案
所以扩展事件是解决方案，这是我的方法：
So the extended events are the solution, this is how I have done it:
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='testMSSQLTrace')
DROP EVENT SESSION testMSSQLTrace ON SERVER;
DECLARE @cmd VARCHAR(MAX) = '';
SELECT @cmd = 'CREATE EVENT SESSION testMSSQLTrace
ON SERVER
ADD EVENT sqlserver.sp_statement_completed
(WHERE (sqlserver.database_name = N''' + DB_NAME() + '''))
ADD TARGET package0.ring_buffer
WITH (
MAX_MEMORY = 2048 KB,
EVENT_RETENTION_MODE = NO_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 3 SECONDS,
MAX_EVENT_SIZE = 0 KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF
);'
EXEC (@cmd)
这将创建一个事件，该事件可在每次语句完成后触发，并动态地进行过滤以对数据库进行过滤
This creates an event that can be fired after every statement completion, this is done dynamicly to filter on the database
然后我有3个过程使控制此事件变得容易
Then I have 3 procedures that make controlling this event easy
/*******************************************************************************************
Starts the statement trace
*******************************************************************************************/
CREATE OR ALTER PROC testMSSQL.Private_StartTrace
AS
BEGIN
ALTER EVENT SESSION testMSSQLTrace
ON SERVER
STATE = START;
END
GO
/*******************************************************************************************
Ends the statement trace, this also clears the trace
*******************************************************************************************/
CREATE OR ALTER PROC testMSSQL.Private_StopTrace
AS
BEGIN
ALTER EVENT SESSION testMSSQLTrace
ON SERVER
STATE = STOP;
END
GO
/*******************************************************************************************
Saves the statements trace
*******************************************************************************************/
CREATE OR ALTER PROC testMSSQL.Private_SaveTrace
AS
BEGIN
DECLARE @xml XML;
SELECT @xml = CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets AS xet INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
WHERE xe.name = 'testMSSQLTrace'
INSERT INTO testMSSQL.StatementInvocations (testProcedure, procedureName, lineNumber, statement)
SELECT testMSSQL.GetCurrentTest(),
OBJECT_NAME(T.c.value('(data[@name="object_id"]/value)[1]', 'int')),
T.c.value('(data[@name="line_number"]/value)[1]', 'int'),
T.c.value('(data[@name="statement"]/value)[1]', 'VARCHAR(900)')
FROM @xml.nodes('RingBufferTarget/event') T(c)
WHERE T.c.value('(data[@name="nest_level"]/value)[1]', 'int') > 3
END
GO
这些程序分别开始和停止跟踪和最后一个将结果存储在一个表中，该表在嵌套级别对其进行过滤，因此不会跟踪我自己的代码。
These procedures respectivly start and stop the trace and the last one stores the result in a table where it filters on the nest level so my own code is not traced.
最后，我有点像这样：
start trace
start tran/savepoint
run SetUp (users code)
run test (users code)
save trace
save trace to variable
rollback tran (also catch errors and stuff like that)
save variable back to table so the trace is not rolled back
特别感谢@Jeroen Mosterd最初在 SQL Server：如何将代码解析为不同的语句这样发布
Special thanks to @Jeroen Mosterd for originally coming up with a proposal for this solution in this SQL Server: How to parse code into its different statements SO post
这篇关于SQL Server：如何查找执行了哪些行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
