Sql-server – Execution plan caching of ITVF called with inline vs dynamic SQL

dynamic-sqlexecution-planparameter-sniffingsql server

I would like someone to explain to me how each of the two queries below would generate their execution plan in SQL Server where my function [dbo].[GetAdditionalProjectDatesForCalendar] is an ITVF (inline table-valued function):

Dynamic SQL

sp_executesql N'SELECT * FROM [dbo].[GetAdditionalProjectDatesForCalendar]
(@Parameter1,@Parameter2)', @Parameter1 = 'Value1', @Parameter2 = 'Value2'

Inline SQL

SELECT * FROM [dbo].[GetAdditionalProjectDatesForCalendar]
('Value1','Value2')

Background

The Dynamic SQL is what's generated from a C# application that I'm working on and while it performs just as well as the Inline SQL most of the time it performs poorly for certain values of @Parameter1 and @Parameter2.

I've read up on how this is caused by Parameter Sniffing and I've tried using option(recompile) with the Dynamic SQL but that hasn't improved the performance entirely.

I feel like I need to find a way to force C# to create the Inline SQL so that it will use the exact execution plan for the given parameters instead of creating one on the fly with option(recompile). I also think I can do this by writing my dynamic sql without using parameterization but I know that would introduce a SQL Injection risk.

Best Answer

The sp_executesql plan with OPTION (RECOMPILE) should be fully optimized for the specific parameter values on each call, with parameter embedding also enabled.

Taking the AdventureWorks example from Itzik Ben-Gan's comment on the article referenced in the question:

CREATE FUNCTION dbo.F
(
    @NameLike nvarchar(50),
    @Sort tinyint
) RETURNS TABLE
AS
RETURN
    SELECT TOP (5)
        ProductID,
        Name
    FROM Production.Product
    WHERE
        @NameLike IS NULL
        OR Name LIKE @NameLike
    ORDER BY
        CASE WHEN @Sort = 1 THEN ProductID ELSE NULL END ASC,
        CASE WHEN @Sort = 2 THEN ProductID ELSE NULL END DESC,
        CASE WHEN @Sort = 3 THEN Name ELSE NULL END ASC,
        CASE WHEN @Sort = 4 THEN Name ELSE NULL END DESC;

The execution plan for:

SELECT *
FROM dbo.F(N'K%', 3);

...shows the specific parameter values being inlined (so the @NameLike IS NULL is optimized away, enabling a seek) but parameter embedding does not take place so there is a Top N Sort for the ORDER BY clause:

inlined values only

If we add the recompile query hint, a plan is generated for the specific values with parameter embedding enabled (so no sort):

SELECT *
FROM dbo.F(N'K%', 3)
OPTION (RECOMPILE);

inlined plus peo

If we wrap the query in sp_executesql we get neither inlining nor embedding, since the values are explicitly parameterized:

EXECUTE sys.sp_executesql
    N'SELECT * FROM dbo.F(@NameLike, @Sort)',
    N'@NameLike nvarchar(50), @Sort tinyint',
    @NameLike = N'K%', @Sort = 3;

parameterized

Adding OPTION (RECOMPILE) enables both optimizations again:

EXECUTE sys.sp_executesql
    N'SELECT * FROM dbo.F(@NameLike, @Sort) OPTION (RECOMPILE)',
    N'@NameLike nvarchar(50), @Sort tinyint',
    @NameLike = N'K%', @Sort = 3;

inlined and embedded

Related Question