Can You See Table Valued Parameters in Extended Events?

I live for questions and this was an interesting one. Can you see Table Valued Parameters that have been passed in to Extended Events? I literally have no idea. I’m sure we’ll see something, I just don’t know what. Time to find out.

Table Valued Parameters

I don’t want to get in to whether or not table valued parameters are a good or bad thing. Like anything else, I’m sure they can be used for good or for evil. However, just like knowing what value was passed to an integer, I can see why you may want to know what was passed in to a table valued parameter. To get started, let’s create a table type:

CREATE TYPE ErrorList AS TABLE
(
    ErrorTime DATETIME,
    UserName sysname,
    ErrorNumber INT,
    ErrorMessage NVARCHAR(4000)
);

Now, we’ll create a simple procedure that uses a table valued parameter:

CREATE PROCEDURE dbo.LogErrors
(@errors ErrorList READONLY)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.ErrorLog
    (
        ErrorTime,
        UserName,
        ErrorNumber,
        ErrorMessage
    )
    SELECT ErrorTime,
           UserName,
           ErrorNumber,
           ErrorMessage
    FROM @errors;

END;

Simple enough. I’ll use Powershell to call the procedure so that I can track rpc_starting, sp_statement_completed and rpc_completed in my Extended Events session.

Capturing Procedures

Here’s the Session definition:

CREATE EVENT SESSION [TVPTest]
ON SERVER
    ADD EVENT sqlserver.rpc_completed
    (SET collect_data_stream = (0),
         collect_output_parameters = (0),
         collect_statement = (1)
     WHERE ([sqlserver].[database_name] = N'AdventureWorks')
    ),
    ADD EVENT sqlserver.rpc_starting
    (SET collect_data_stream = (0)
     WHERE ([sqlserver].[database_name] = N'AdventureWorks')
    ),
    ADD EVENT sqlserver.sp_statement_completed
    (SET collect_object_name = (1)
     WHERE ([sqlserver].[database_name] = N'AdventureWorks')
    ),
    ADD EVENT sqlserver.sql_batch_completed
    (SET collect_batch_text = (1)
     WHERE ([sqlserver].[database_name] = N'AdventureWorks')
    )
WITH
(
    TRACK_CAUSALITY = ON
);

A few points about this definition. I’m being a little lazy. Since it’s only a small test, I’m not outputting to file as I normally would and instead letting it go to the default, the ring buffer. I made sure that I was filtering on the database in question, just to make for less data and less messy data collection. I also turned on Causality Tracking in order to group the events together.

So…. wanna guess what I saw?

Table Valued Parameters in Extended Events

Here’s the basic output:

What should immediately leap to your eyes is the statement for rpc_starting and rpc_completed. Let me show in detail what you’re seeing:

Anyone remember creating a variable @p1? Me neither. How about those ‘insert’ statements (that code was not following my default SQL Prompt template, I assure you)? Me neither. But this is how SQL Server is dealing with the table valued parameter. It’s not. Instead, it’s using the TVP to create another table, and then using that to run the procedure.

Yet, if we look at the sp_statement_completed, it’s exactly what we expect to see:

So, can we see table valued parameters in Extended Events?

Yes, but also, a little more. We can see how SQL Server deals with those values.

Conclusion

I found this one to be really interesting. I honestly didn’t expect to see any data. On a guess, I thought maybe there’d be some kind of binary or something. I did not expect to see the creation of a table variable, not at all. So, cool! However, also, I’m excited to know that this data can be seen in Extended Events.

Please, keep those questions coming. I love figuring this stuff out. Next one I’ve got on my current list: How do you know why a plan did not go parallel?