Additional Use for sp_statement_completed

Another discussion in Gothenburg (such a great group of people) around Extended Events lead to a (admittedly, tiny) idea for an additional use for sp_statement_completed.

The basics for sp_statement_completed are pretty straight forward. If you want to capture a procedure’s behavior, you use rpc_completed. If you want to know about the individual statements within the procedure, you use sp_statement_completed. Now, I don’t recommend turning this on across the board or without some good filtering in place because, it’s likely to generate quite a bit of data. However, it can be useful, including in the following manner.

What Path Did I Take?

I’ve got a silly, example, procedure here:

CREATE PROC dbo.PathTest
(
    @Path1 INT,
    @Path2 INT,
    @Path3 INT
)
AS
IF @Path1 = 1
    PRINT 'Path 1';
ELSE IF @Path2 = 1
    PRINT 'Path 2';
ELSE IF @Path3 = 1
    PRINT 'Path 3';

And, I have a pretty simple Extended Events session defined here:

CREATE EVENT SESSION [Statements]
ON SERVER
    ADD EVENT sqlserver.rpc_completed
    (WHERE (
               [sqlserver].[database_name] = N'AdventureWorks'
               AND [object_name] = N'PathTest'
           )
    ),
    ADD EVENT sqlserver.rpc_starting
    (WHERE (
               [sqlserver].[database_name] = N'AdventureWorks'
               AND [object_name] = N'PathTest'
           )
    ),
    ADD EVENT sqlserver.sp_statement_completed
    (SET collect_object_name = (1),
         collect_statement = (1)
     WHERE (
               [sqlserver].[database_name] = N'AdventureWorks'
               AND [object_name] = N'PathTest'
           )
    )
    ADD TARGET package0.event_file
    (SET filename = N'Statements')
WITH
(
    TRACK_CAUSALITY = ON
);

This is primarily for illustration purposes, so I tossed in rpc_starting and rpc_completed so there’s a full wrapper around the sp_statement_completed event.

If I execute this (remotely, through something other than a batch in SSMS):

EXEC dbo.PathTest @Path1 = 0, -- int
                  @Path2 = 0, -- int
                  @Path3 = 1; -- int

I’ll see this from the Extended Events:

You can now see the path through the code. Because I set @Path3 to 1, that’s the only one that gets to execute. We see it checking @Path1 and @Path2 and then, the PRINT statement fires.

If I changed the code to this:

EXEC dbo.PathTest @Path1 = 1, -- int
                  @Path2 = 0, -- int
                  @Path3 = 0; -- int

The output from the session then becomes:

Because the code is going to be immediately satisfied with the very first IF statement, the others do not fire. I can literally track the behavior of IF, CASE, what have you, to really see what fired. It could be a very handy troubleshooting tool.

A couple of notes about the session definition. First, if you look at sp_statement_completed in the listing, you’ll see SET collect_object_name = (1) and collect_statement = (1). These are optional columns. I want to include the statement because I want you to see it. I included the object_name so that I could filter on that. Second, I added TRACK_CAUSALITY = ON just so, if I was to run this in a production environment where I might see a lot of calls to this procedure, I could easily group them together and sort them as needed.

Conclusion

Yeah, I get it, this is hardly exciting. It is however useful and illustrates the additional uses you can put some of these events to work on. Sure, measuring performance, but also showing how your code is resolved through IF or LOOP or… So, yeah, one additional use for sp_statement_completed.