I am using sp_executesql to pass a complicated selection with a few parameters. It is much slower doing it this way than it is by taking it out of the stored procedure and declaring the variables.
I have seen many questions about SQL parameter sniffing and my scenario sounds like this could be the case. However even after calling DBCC FREEPROCCACHE or amending the outer Select with Option (Recompile) it still uses a different and inefficent execution plan compared to writing the same query outside the stored procedure.
However still using the stored procedure but setting up copies of the parameters as local variables will use the efficent execution plan.
Does this scenario rule out SQL parameter sniffing as the cause? Becuase i recompile the query surely there is no pre existing execution plan it uses. If so what could be possible other reasons for this behaviour?
Just to give you an idea of the sql query you can see it below (Messy as generated via Entity framework). This is the fast query but when put into the sp_executesql proc with the variable taken out and put in parameters it generates the inefficient execution plan
DECLARE @p__linq__0 INT = 2032
,@p__linq__1 UNIQUEIDENTIFIER = '8CC161FC-B8DE-4746-BA4F-62FA55DF26DE'
,@p__linq__2 uniqueidentifier= '8CC161FC-B8DE-4746-BA4F-62FA55DF26DE',
@p__linq__3 uniqueidentifier= '8CC161FC-B8DE-4746-BA4F-62FA55DF26DE',
@p__linq__4 uniqueidentifier= '8CC161FC-B8DE-4746-BA4F-62FA55DF26DE',
@p__linq__5 INT = 6771
SELECT
[Limit1].[UserIdValue] AS [UserIdValue]
FROM (SELECT [Extent1].[Id] AS [Id]
FROM [dbo].[Request] AS [Extent1]
WHERE ([Extent1].[InstanceId] = @p__linq__0) AND ([Extent1].[DeletedDate] IS NULL) AND (( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
[Extent2].[TeamId] AS [TeamId],
[Extent2].[HasUpdateAccess] AS [HasUpdateAccess]
FROM [dbo].[RequestTypeTeam] AS [Extent2]
WHERE [Extent1].[RequestTypeId] = [Extent2].[RequestTypeId]
) AS [Project1]
WHERE ([Project1].[HasUpdateAccess] = 1) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[UserTeam] AS [Extent3]
WHERE ([Project1].[TeamId] = [Extent3].[TeamId]) AND ([Extent3].[UserId] = @p__linq__1)
))
)) OR (([Extent1].[InsertUserId] = @p__linq__2) AND ( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
[Extent4].[TeamId] AS [TeamId],
[Extent4].[HasCreatorAccess] AS [HasCreatorAccess]
FROM [dbo].[RequestTypeTeam] AS [Extent4]
WHERE [Extent1].[RequestTypeId] = [Extent4].[RequestTypeId]
) AS [Project4]
WHERE ([Project4].[HasCreatorAccess] = 1) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[UserTeam] AS [Extent5]
WHERE ([Project4].[TeamId] = [Extent5].[TeamId]) AND ([Extent5].[UserId] = @p__linq__3)
))
))) OR ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[RequestTeam] AS [Extent6]
WHERE ([Extent1].[Id] = [Extent6].[RequestId]) AND ([Extent6].[TeamId] IN (3147, 3165))
)) OR ( EXISTS (SELECT
1 AS [C1]
FROM ( SELECT
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[RequestControlData] AS [Extent8]
WHERE ([Project8].[Id] = [Extent8].[ControlId]) AND ([Extent8].[RequestId] = [Extent1].[Id]) AND ([Extent8].[UserIdValue] = @p__linq__4)) AS [C1]
FROM ( SELECT
[Extent7].[Id] AS [Id]
FROM [dbo].[Control] AS [Extent7]
WHERE ([Extent7].[RequestTypeId] IS NOT NULL) AND ([Extent1].[RequestTypeId] = [Extent7].[RequestTypeId]) AND ([Extent7].[DeletedDate] IS NULL) AND ([Extent7].[IsAuthorisation] = 1)
) AS [Project8]
) AS [Project9]
WHERE [Project9].[C1] > 0
))) AND ( NOT ([Extent1].[StatusId] IN (1071))) AND ( NOT ([Extent1].[RequestTypeId] IN (1215)))) AS [Filter11]
OUTER APPLY (SELECT TOP (1)
[Extent9].[ControlId] AS [ControlId],
[Extent9].[UserIdValue] AS [UserIdValue],
[Extent10].[Id] AS [Id],
[Extent10].[SharedControlId] AS [SharedControlId]
FROM [dbo].[RequestControlData] AS [Extent9]
INNER JOIN [dbo].[Control] AS [Extent10] ON [Extent9].[ControlId] = [Extent10].[Id]
WHERE ([Filter11].[Id] = [Extent9].[RequestId]) AND (([Extent10].[SharedControlId] = @p__linq__5) OR (([Extent10].[SharedControlId] IS NULL) AND (@p__linq__5 IS NULL
)))
)
AS [Limit1]
