Dapper TVP Behavior and SQL Server Monitoring Overhead

[MRobat1980]

Hi
I’m reaching out to ask for your advice regarding how Dapper sends Table-Valued Parameters (TVPs) to SQL Server and how that appears in monitoring tools such as Extended Events.

In my project, I have defined a TVP as follows:

CREATE TYPE dbo.UserDefinedParameters AS TABLE (
    Id INT,
    Value1 INT,
    Value2 INT,
    Value3 INT
)

Dapper parameters on  call store procedure command

var searchParameters = new
{
    SearchParams = table.AsTableValuedParameter("dbo.UserDefinedParameters"),
    OtherParam01 = 10,
    OtherParam02 = 20,
};

Everything works correctly, but our database team raised a concern regarding how this call is logged in Extended Events. Specifically, they noticed that each row in the TVP is being sent as a separate INSERT INTO statement, like this:

DECLARE @p1 dbo.UserDefinedParameters

INSERT INTO @p1 VALUES (1, 1, 1388, NULL)
INSERT INTO @p1 VALUES (2, 1, 1388, 603)
-- potentially hundreds more...
EXEC dbo.Search
    @SearchParams = @p1,
    @OtherParam01 = NULL,
    @OtherParam02 = NULL,
    ...


My questions are:

Is this the expected behavior from Dapper when sending TVPs?

Is there a way to batch these values or reduce the number of INSERT statements?

Have you seen this being a real issue in practice, and would you recommend any workaround or tuning?

[mgravell]

This sounds like "Extended Events" is doing what it can to make the SQL it shows runnable, so that you can copy/paste it into SSMS etc; this isn't what actually got sent - IIRC TVPs are sent via raw TDS, the same as bulk-copy uses. Pretty sure this is just a UI thing. At no point does Dapper generate INSERT statements for TVPs.

[Charlieface]

This sounds like "Extended Events" is doing what it can to make the SQL it shows runnable, so that you can copy/paste it into SSMS etc; this isn't what actually got sent - IIRC TVPs are sent via raw TDS, the same as bulk-copy uses. Pretty sure this is just a UI thing. At no point does Dapper generate INSERT statements for TVPs.

That's correct, this is a non-issue, and it's also not specific to Dapper. From the horse's mouth:

Profiler shows you not what the client actually sent, but a kind of TSQL reproduction batch that you could run to do the same thing.

Table-Valued Parameters are a feature of the TDS network protocol, as well as the SQL Server Database Engine. And clients will send the data directly using TDS, and not in seperate INSERT statements.

If you run Profiler, you'll see that there aren't separate SQL:StmtCompleted events for inserting each row into the TVP.

The TDS protocol has a special TVP data type, which uses a stream, of TVP_ROW tokens. They're inserted using the usual bulk insert mechanism into a table variable.

The profiler/XE shows that as separate INSERT statements, which can be an issue when capturing them as they can be very large. But it's definitely not a standard INSERT even in the underlying SqlClient. The workaround is to not capture the sql_text XE variable in such cases. If necessary raise a case with MS asking to modify the XE event variables.