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.