2

I have a query which is slow and it takes 15-20 sec when i run from the application.So i captured that query using SQL profiler and executed on SSMS with OPTION(RECOMPILE) and the query runs faster in 2 seconds. I checked the query plan cache and noticed that the query is using the cached plan.I followed the below steps to create a plan guide for that query;

  • Called sp_create_plan_guide_from_handle to create a plan.

sp_create_plan_guide_from_handle 'Search_With_County',0x06000600950F7C38C0FD5F135102000001000000000000000000000000000000000000000000000000000000

  • From SSMS i did Drop and create for that plan guide and changed the @hints from the XML to N'OPTION(RECOMPILE)'

The Plan guide looks like the one below.I have simplified the query.

EXEC sp_create_plan_guide @name = N'[IncidentSerach_With_County]', @stmt = N'SELECT TOP (@p__linq__7) 
    [SearchModel].[IncidentName] AS [IncidentName], 
    [SearchModel].[NatureOfIncident] AS [NatureOfIncident], 
    [SearchModel].[InvestBy] AS [InvestBy], 
    [SearchModel].[SecondaryOfficer] AS [SecondaryOfficer], 
    [SearchModel].[Disposed] AS [Disposed], 
    [SearchModel].[Property] AS [Property], 
    [SearchModel].[PropertyDescriptions] AS [PropertyDescriptions], 
    [SearchModel].[Forfeiture] AS [Forfeiture], 
    [SearchModel].[ColdCaseNumber] AS [ColdCaseNumber], 
    [SearchModel].[DateOccurred] AS [DateOccurred], 
    [SearchModel].[TimeOccurred] AS [TimeOccurred], 
    [SearchModel].[FirstSubmissionDate] AS [FirstSubmissionDate], 
    [SearchModel].[IsReportOffline] AS [IsReportOffline], 
    [SearchModel].[IsSupplement] AS [IsSupplement], 
    [SearchModel].[PrimaryOfficerId] AS [PrimaryOfficerId], 
    [SearchModel].[SecurityLevel] AS [SecurityLevel]
    FROM [dbo].[SearchModel] AS [SearchModel]) AS [Extent1]
            LEFT OUTER JOIN [dbo].[IncidentDetailsPages] AS [Extent2] ON [Extent1].[IncidentDetailPageId] = [Extent2].[Id]
            WHERE ((N''Public'' = [Extent1].[SecurityLevel]) OR (N''Private'' = [Extent1].[SecurityLevel]) OR ([Extent1].[PrimaryOfficerId] = @p__linq__0) ) AND ([Extent1].[ReportDateTime] >= @p__linq__3) AND ([Extent1].[ReportDateTime] <= @p__linq__4) AND (([Extent1].[County] = @p__linq__5) OR (([Extent1].[County] IS NULL) AND (@p__linq__5 IS NULL))) AND ( NOT EXISTS (SELECT 
                1 AS [C1]
                FROM  (SELECT 
                    N''MI3300287'' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
                UNION ALL
                    SELECT 
                    N''MI3300567'' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]
                UNION ALL
                    SELECT 
                    N''MI3300568'' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
                WHERE ([UnionAll2].[C1] = [Extent1].[AgencyOri]) OR (CASE WHEN ([UnionAll2].[C1] <> [Extent1].[AgencyOri]) THEN cast(1 as bit) WHEN ([UnionAll2].[C1] = [Extent1].[AgencyOri]) THEN cast(0 as bit) END IS NULL)
            )) AND ([Extent1].[IsReportOffline] <> 1) AND ([Extent1].[IsSupplement] <> 1)
        )  AS [Project6]
    )  AS [Project6]
    WHERE [Project6].[row_number] > @p__linq__6
    ORDER BY [Project6].[AgencyOri] ASC, [Project6].[ReportIncidentNumber] ASC, [Project6].[SupplementNumber] ASC', @type = N'SQL', @module_or_batch = N'SELECT TOP (@p__linq__7) 
    [SearchModel].[IncidentName] AS [IncidentName], 
    [SearchModel].[NatureOfIncident] AS [NatureOfIncident], 
    [SearchModel].[InvestBy] AS [InvestBy], 
    [SearchModel].[SecondaryOfficer] AS [SecondaryOfficer], 
    [SearchModel].[Disposed] AS [Disposed], 
    [SearchModel].[Property] AS [Property], 
    [SearchModel].[PropertyDescriptions] AS [PropertyDescriptions], 
    [SearchModel].[Forfeiture] AS [Forfeiture], 
    [SearchModel].[ColdCaseNumber] AS [ColdCaseNumber], 
    [SearchModel].[DateOccurred] AS [DateOccurred], 
    [SearchModel].[TimeOccurred] AS [TimeOccurred], 
    [SearchModel].[FirstSubmissionDate] AS [FirstSubmissionDate], 
    [SearchModel].[IsReportOffline] AS [IsReportOffline], 
    [SearchModel].[IsSupplement] AS [IsSupplement], 
    [SearchModel].[PrimaryOfficerId] AS [PrimaryOfficerId], 
    [SearchModel].[SecurityLevel] AS [SecurityLevel]
    FROM [dbo].[SearchModel] AS [SearchModel]) AS [Extent1]
            LEFT OUTER JOIN [dbo].[IncidentDetailsPages] AS [Extent2] ON [Extent1].[IncidentDetailPageId] = [Extent2].[Id]
            WHERE ((N''Public'' = [Extent1].[SecurityLevel]) OR (N''Private'' = [Extent1].[SecurityLevel]) OR ([Extent1].[PrimaryOfficerId] = @p__linq__0)) AND ([Extent1].[ReportDateTime] >= @p__linq__3) AND ([Extent1].[ReportDateTime] <= @p__linq__4) AND (([Extent1].[County] = @p__linq__5) OR (([Extent1].[County] IS NULL) AND (@p__linq__5 IS NULL))) AND ( NOT EXISTS (SELECT 
                1 AS [C1]
                FROM  (SELECT 
                    N''MI3300287'' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
                UNION ALL
                    SELECT 
                    N''MI3300567'' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]
                UNION ALL
                    SELECT 
                    N''MI3300568'' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
                WHERE ([UnionAll2].[C1] = [Extent1].[AgencyOri]) OR (CASE WHEN ([UnionAll2].[C1] <> [Extent1].[AgencyOri]) THEN cast(1 as bit) WHEN ([UnionAll2].[C1] = [Extent1].[AgencyOri]) THEN cast(0 as bit) END IS NULL)
            )) AND ([Extent1].[IsReportOffline] <> 1) AND ([Extent1].[IsSupplement] <> 1)
        )  AS [Project6]
    )  AS [Project6]
    WHERE [Project6].[row_number] > @p__linq__6
    ORDER BY [Project6].[AgencyOri] ASC, [Project6].[ReportIncidentNumber] ASC, [Project6].[SupplementNumber] ASC', @params = N'@p__linq__0 int,@p__linq__1 int,@p__linq__2 varchar(8000),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 varchar(8000),@p__linq__6 int,@p__linq__7 int', @hints = N'OPTION(RECOMPILE)'
GO

After creating the plan guide also when i run the query from application it is slow.The query is still using the plan from plan cache.

It was supposed to be simple and i used sp_create_plan_guide_from_handle so the query text is exact.

Please suggest if you find any issues in the approach i followed.This is the first time i am creating plan guide.

This is the reference i followed.

As i am not able to give OPTION(RECOMPILE) on the LINQ-SQL generated query,i am generating plan guide with query hint OPTION(RECOMPILE).

I would like to know if this is how someone create plan guide for my scenario.

The reasons i am going with plan guide is as below:

I have tried to improve the query earlier and i was successful to an extend. I did change the views used in the query and that made the query to run parallel which brough big impact in the performance. I am not able to change a single part in the dynamic sql ,as it is LINQ-SQL generated query and application code change is not a viable option for me. So i have to use plan guides/query store as far as i know.

So please provide me a solution which is suitable to my situation.

  • 1
    If you're using the handle from the plan you generated in SSMS (which was fast), that's potentially going to be a different handle from the plan generated by the app (usually because at least one crucial SET setting is different). – Aaron Bertrand Jul 20 '18 at 1:18
  • I didn't use the plan handle from the plan generated using SSMS for creating plan guide.If a plan guide is created next time when i run the query from application,does it search for query plan in plan cache? – user9516827 Jul 20 '18 at 1:58
  • So did you compare slow and fast plans? Could you show us what you found? – Aaron Bertrand Jul 20 '18 at 1:59
  • 1
    But surely there is a slow plan in the cache when you run it from the application? Otherwise how are you 100% positive the application is generating the exact same query text every time and that the plan from SSMS actually matches the plan from the app? Again, if you have a plan that's slow and a plan that's fast, let's compare them. Plan guides with OPTION (RECOMPILE) can solve some query issues, but I'm not convinced that it is the right solution here. – Aaron Bertrand Jul 20 '18 at 3:06
  • 1
    they have used interceptor to tackle the issue..but i am trying to use plan guides. – user9516827 Jul 20 '18 at 12:13

Your Answer

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

Browse other questions tagged or ask your own question.