Execution time high for Query in sp_executesql

Question

I have a query generated from a .net application and parameters are linq parameters. The procedure I followed to tune the query is as below.

• I run the application and capture the query using profiler.

• The query is of the form

  exec sp_executesql N'SELECT 
  [Extent1].[Id] AS [Id], 
  [Extent1].[GroupId] AS [GroupId], 
  [Extent1].[Agency] AS [Agency], 
  [Extent1].[Group] AS [Group], 
  [Extent1].[Claim] AS [Claim], 
  [Extent1].[StartDate] AS [StartDate], 
  [Extent1].[ExpireDate] AS [ExpireDate], 
  [Extent1].[IsActive] AS [IsActive]
  FROM [dbo].[Permissions] AS [Extent1]
  WHERE ([Extent1].[GroupId] = @p__linq__0) AND (([Extent1].[ExpireDate] IS NULL)
  OR ([Extent1].[ExpireDate] > @p__linq__1)) AND ([Extent1].[IsActive] <> 1)',
  N'@p__linq__0 int,@p__linq__1 datetime2(7)',
  @p__linq__0=15,@p__linq__1='2017-05-10 00:00:00'
  

This is just a sample query. The actual query is much bigger and includes lots of joins involving multiple views and table etc.

My question here is:

• When the run the query as it is, the execution times is around 15 sec
• When I take out the query alone from sp_executesql and apply the filter values to the query, it runs in less than 1 sec.

I understand that since the values are hard-coded into the query it will be faster, but why is that much difference in execution time? Also the execution plans are different for both:

SQL Execution plan with sp_executesql

Execution Plan with hard-coded SQL

I am the only user working on this server and I execute same parameters in both scenarios. I have applied OPTION(RECOMPILE) to the end of the sp_executesql and it executes in 1 sec compared to 15 sec. But the execution plan is still not similar to the hard-coded plan.

Anyways OPTION(RECOMPILE) makes the query run fast in one second as it forces the query to compile and create new plan each time. But I am not supposed to hack the code and change the query. I will suggest OPTION(RECOMPILE) as a last resort to my application team.

Answer 1

The query optimizer is able to use parameter sniffing for the dynamic query as can be seen in the actual plan:

<ParameterList>
  <ColumnReference Column="@p__linq__5" ParameterDataType="varchar(8000)" ParameterCompiledValue="'MI3300287'" ParameterRuntimeValue="'MI3300287'" />
  <ColumnReference Column="@p__linq__4" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2009-06-11 23:59:00.0000000'" ParameterRuntimeValue="'2009-06-11 23:59:00.0000000'" />
  <ColumnReference Column="@p__linq__3" ParameterDataType="datetime2(7)" ParameterCompiledValue="'2008-06-11 00:00:00.0000000'" ParameterRuntimeValue="'2008-06-11 00:00:00.0000000'" />
  <ColumnReference Column="@p__linq__2" ParameterDataType="varchar(8000)" ParameterCompiledValue="'MI3300287'" ParameterRuntimeValue="'MI3300287'" />
  <ColumnReference Column="@p__linq__1" ParameterDataType="int" ParameterCompiledValue="(90495)" ParameterRuntimeValue="(90495)" />
  <ColumnReference Column="@p__linq__0" ParameterDataType="int" ParameterCompiledValue="(90495)" ParameterRuntimeValue="(90495)" />
</ParameterList>

Those sniffed values can influence the query plan that's chosen. However, the query plan still needs to be safe for all possible values of the input variables. Let's look at one difference between the plans, the access on the IncidentDetailsPage_Header table. In the slower, dynamic plan you get a scan and a hash match which in total takes about 2.4 seconds:

In the faster plan with the hardcoded values you get an index seek instead which takes about 0 ms:

Here's the T-SQL that corresponds to that part of the plan:

 AND (([Extent1].[agencyori] = @p__linq__5)
      OR (([Extent1].[agencyori] IS NULL)
          AND (@p__linq__5 IS NULL)))

Hardcoding the values results in a single use query plan just for those parameter values. With your parameter values, that T-SQL can be simplified to this:

([Extent1].[AgencyOri] = 'MI3300287')

That is eligible for an index seek. The other code is not. Avoid kitchen sink queries when you can.