3

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.

5

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:

slow hash match

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

fast index seek

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.

  • Joe Obbish:Your findings are correct.I modified that TSQL and it is running fast now.I now know that using Kitchen sink queries are really bad for the plan.I have read this " if most of your workload consists of stored procedures calls, you can take benefits from the parameter sniffing, but if you have a lot of ad-hoc queries running within your workload, then it is better not to use the parameter sniffing.".Since most of my workload consists of ad-hoc queries(LINQ to SQL) i have a thought of turning Parameter sniffing off at database level(SQL Server 2016 has that option).Any thoughts? – user9516827 Jun 12 '18 at 14:07
  • How did you identify that specific TSQL block from that execution plan?Also how you were able to find out that it took 2.4 sec to run?Can you explain that? – user9516827 Jun 12 '18 at 15:20

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.