时间戳

首页 > 解决方案 > SQL Server：如何查找执行了哪些行

sql-server - SQL Server：如何查找执行了哪些行

问题描述

我正在为 SQL Server 开发一个突变测试框架，为此我需要能够计算在执行某个存储过程时执行了存储过程、函数或触发器的哪些行。

困难的部分是我想知道从我调用的存储过程中执行的确切行或语句。

通过这样的查询，我可以看到正在执行哪些存储过程/触发器/函数，因为我知道当我调用存储过程时，我可以利用时间来查看它是否被执行。

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)

我将如何找到已执行的行/语句，我还必须知道行/语句存在于哪个存储过程/触发器/函数内部以及它在哪个 shema 中。我必须考虑到可能会使用 IF/ELSE 语句。

有了这些数据，我可以做两件重要的事情：

一个可能但不是很好的解决方案是自动更改存储过程以添加将前一行插入表中的行，但这需要将过程拆分为语句，我不知道该怎么做.

请注意，我无法更改用户想要使用我的框架进行测试的代码。我可以搜索模式并替换，但手动更改程序不是一种选择。

编辑：让我们重新定义这个问题：如何以不依赖于代码样式的方式将存储过程定义拆分为其不同的语句？以及如何在找到的语句之间添加新语句？

编辑：在 SO post SQL Server: How to parse code into its different statements我找到了一种跟踪语句执行的方法，但我还不能过滤它。

标签： sql-servertsqlcode-coveragestatements

解决方案

所以扩展事件是解决方案，这就是我的做法：

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)

这会创建一个可以在每个语句完成后触发的事件，这是动态完成的以过滤数据库

然后我有 3 个程序可以轻松控制此事件

/*******************************************************************************************
    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

这些过程分别启动和停止跟踪，最后一个过程将结果存储在一个表中，它在嵌套级别进行过滤，因此我自己的代码不会被跟踪。

最后我使用它有点像这样：

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 中提出了此解决方案的建议：如何将代码解析为其不同的语句SO post

推荐阅读