4

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.

1
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.