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.
