I'm reading the documentation to [sp_create_plan_guide](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-create-plan-guide-transact-sql?view=sql-server-ver16#plan-guide-matching-requirements) and I find it hard to understand. My questions are: 1. Are the white spaces considered or not when matching? 1. Is there any difference between white spaces and blank spaces? 1. What about leading and trailing spaces? 1. Where is the option to create Git issue against the docs, proudly presented [here](https://learn.microsoft.com/en-us/sql/sql-server/sql-server-get-help?view=sql-server-ver16#sql-server-documentation) ? What I am missing here? Thanks in advance!

We have query which is inserting data to heap:

INSERT INTO [heap]
(
    80 COLUMNS...
)
SELECT
   14 columns
   65 NULL VALUES   
   , 'vvvvvvjvvvvvmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmvv'
FROM [heap]  h_
JOIN [dbo].[PartitionedTableWith2BilionsOfRecords] bas_
  ON (bas_.[ROWID] = h_.[ROWID])
WHERE h_.[hvr_op] IN (@0)
      AND bas_.[rowid] > @1
      AND bas_.[rowid] < @2;

*(anonymized) In heap we do not have any indexes. Table from join is partitioned and has around 2 bilions of records. Also we have nonclusteterd index on rowid. In database Force Parametrization is enabled. Unfortunately this query in most executions is building not optimal plan: Was trying to force optimal plan (20670): Unfortunate I am getting forced plan failure - NO_PLAN. According to information from other website added extended event: Which is showing forced plan failures and have only below information:

Query processor could not produce query plan because USE PLAN hint contains plan that could not be verified to be legal for query. 
Remove or replace USE PLAN hint. 
For best likelihood of successful plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by SQL Server for the same query.

Question: 1. Why sometimes force of this plan is working, sometimes not ? 2. Is plan guide will be better option for this case ? 3. What can I do to force plan for every execution of query ?

If a plan is forced on the primary in an Availability Group, is it applied to queries run on a secondary? I'm looking for answers that cover both possibilities for plan forcing: * Plan Guides * Query Store Forced Plan I have read the following that suggest QS forced plans do not carry over, but cannot find anything authoritative in the documentation, or anything about plan guides. * Query Store and Availability Groups by Erin Stellato * Query Data Store Forced Plan behavior on AlwaysOn Readable Secondary by Vikas Rana Conclusive evidence of forcing would be the presence of Use Plan or PlanGuideName and PlanGuideDB properties in the secondary's execution plan.

Have a table that is written to a lot but the data is then removed by the app side and stays relatively constant. It is hit by multiple queries that are the same except for the IN clause, there can be one, two or 50 options WHERE Name IN ('test1', 'test2', 'test3', 'etc', 'etc') The code is from within the app and cannot be altered, coming from Entity framework or something similar. I have improved several queries by using plan guides, I am aware these can create tech debt as a good plan today may not be a good plan tomorrow but as this table is written / cleared regularly we haven't experienced this. So my question, I have two queries that only differ by 2/3 of the IN clause values, I have locked in a good plan using a guide for query1 is it possible to lock the same plan handle against query2. The plans are similar the 'good' and 'bad' plans only difference is a sort operation. Running SQL 2017 Ent

*summary* We have an Object-Oriented database schema that we query with Hibernate with: - 1 parent table (NAL_Actions) - 7 child tables (NAL_*) If I want to query a couple of these "action"-objects we have to join all 1+7 tables together (because we don't know what type of child object we need) which isn't great but because of the query execution plan generated by SQL Server results in very slow queries (index scans with 2,5 million rows). *details* The parent table also has a foreign key (actionGroups) that according to the fullscan statistics results in 1.15 "action"-rows on average. This query plan - edit, now also on: paste-the-plan is over estimating the number of rows for each subsequent table that is joined which I and statistics know is never more than two rows as all tables are joined on their (common) distinct primary key, which is also the clustered index, which means that the number of estimated rows cannot increase, as can be seen from this definition of one of the Child-tables: USE [NAL_PRD] GO /****** Object: Table [dbo].[NAL_Unblocks] Script Date: 6/24/2020 6:04:40 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[NAL_Unblocks]( [actionId] [bigint] NOT NULL, [unblockingAmount] [decimal](5, 2) NOT NULL, [productSequenceNumber] [int] NULL, CONSTRAINT [PK_NAL_UNBLOCKS] PRIMARY KEY CLUSTERED ( [actionId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[NAL_Unblocks] WITH CHECK ADD CONSTRAINT [fk_unblocks_actions] FOREIGN KEY([actionId]) REFERENCES [dbo].[NAL_Actions] ([id]) ON DELETE CASCADE GO ALTER TABLE [dbo].[NAL_Unblocks] CHECK CONSTRAINT [fk_unblocks_actions] GO As SQL Server falsely assumes the number of rows exponentially increases with each joined table it changes the join strategy for the last 3 tables: - from: "clustered index seek + nested loops" - to: "clustered index scan + merge join" This means that we need to scan the clustered index of a table of 2,500,000 rows instead of 1 or 2 index seeks *what I've tried so far* 1. hints work, e.g.: WITH(FORCESEEK) for every join, or: OPTION (LOOP JOIN) for the entire query, (but I cannot change the application) 2. this leads me to suggest plan guides as the next option (for SQL Server 2016 - 13.0.5102.14) but although that works fine with sp_executesql the plan guide does not seem to take immediate effect in the application, although I took the SQL from the Query Store and updated the statistics (but maybe updating statistics was a false assumption so maybe I need to restart the application and/or drop the plan - but how without DBCC FREEPROCCACHE as I am limited to db_owner permissions, so not VIEW/ALTER SERVER STATE permissions? Maybe I should switch the application / the JDBC driver to use sp_executesql with prepareSQL=2 instead of the default (sp_prepare + sp_execute) ?. What I have not yet tried: - upgrading to SQL Server 2017 to try adaptive query processing, should that fix this?

Does plan recompilation happens for plan guide due to the, for example, auto update stats? P.S Do **not** take into account adding a hint **OPTION (RECOMPILE)** in the plan guide

I created a plan guide using the following query: EXEC sp_create_plan_guide @name = N'Entity_Property fix', @stmt = N'SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, CREATED, UPDATED, json_value FROM jirascheme.entity_property WHERE ENTITY_NAME=@P0 AND ENTITY_ID=@P1 AND PROPERTY_KEY=@P2', @type = N'SQL', @params = N'@P0 nvarchar(255), @P1 numeric(18, 0), @P2 nvarchar(255)', @hints = N'OPTION (OPTIMIZE FOR UNKNOWN)'; It seems to work fine, but I noticed that there is a little warning icon on the plan in Object Explorer. It looks like this: I don't get any warnings when executing the query, and I can't find any information about it when hovering over it or checking the properties of the plan guide. This is only applied in a test environment but why does it show up and should I be worried about it?

I have an application that's querying a 10 million rows table. The query is filtering by an integer column which is indexed. This column contains only NULLs and the query always looks for non-NULL values, so it should go fast. The thing is that the application sends the query with sp_prepare and then sp_execute and it's basically OPTIMIZE FOR (UNKNOWN) (see Erik's answer for more information about this). In short: 1. sp_execute from the application => optimize for (unknown) 2. density vector used to get estimates 3. only one value (NULL) for the entire row, so estimate = row count 4. full scan of the table when the query returns 0 rows = bad performance Since I can't change the application code, I thought that a plan guide with OPTIMIZE FOR (@P0 = 1) would fix everything. I got the query text from an extended event session (copied from the sql_text column, but removed the (@P0 bigint) part) And then I created the plan guide like so: EXEC sp_create_plan_guide @name = N'Test_1', @stmt = N'select subsidiary0_.primary_event_gkey as primary40_1_, subsidiary0_.gkey as gkey1_, subsidiary0_.gkey as gkey1667_0_, subsidiary0_.operator_gkey as operator2_1667_0_, subsidiary0_.complex_gkey as complex3_1667_0_, subsidiary0_.facility_gkey as facility4_1667_0_, subsidiary0_.yard_gkey as yard5_1667_0_, subsidiary0_.placed_by as placed6_1667_0_, subsidiary0_.placed_time as placed7_1667_0_, subsidiary0_.event_type_gkey as event8_1667_0_, subsidiary0_.applied_to_class as applied9_1667_0_, subsidiary0_.applied_to_gkey as applied10_1667_0_, subsidiary0_.applied_to_natural_key as applied11_1667_0_, subsidiary0_.note as note1667_0_, subsidiary0_.billing_extract_batch_id as billing13_1667_0_, subsidiary0_.quantity as quantity1667_0_, subsidiary0_.quantity_unit as quantity15_1667_0_, subsidiary0_.responsible_party as respons16_1667_0_, subsidiary0_.related_entity_gkey as related17_1667_0_, subsidiary0_.related_entity_id as related18_1667_0_, subsidiary0_.related_entity_class as related19_1667_0_, subsidiary0_.related_batch_id as related20_1667_0_, subsidiary0_.acknowledged as acknowl21_1667_0_, subsidiary0_.acknowledged_by as acknowl22_1667_0_, subsidiary0_.flex_string01 as flex23_1667_0_, subsidiary0_.flex_string02 as flex24_1667_0_, subsidiary0_.flex_string03 as flex25_1667_0_, subsidiary0_.flex_string04 as flex26_1667_0_, subsidiary0_.flex_string05 as flex27_1667_0_, subsidiary0_.flex_date01 as flex28_1667_0_, subsidiary0_.flex_date02 as flex29_1667_0_, subsidiary0_.flex_date03 as flex30_1667_0_, subsidiary0_.flex_double01 as flex31_1667_0_, subsidiary0_.flex_double02 as flex32_1667_0_, subsidiary0_.flex_double03 as flex33_1667_0_, subsidiary0_.flex_double04 as flex34_1667_0_, subsidiary0_.flex_double05 as flex35_1667_0_, subsidiary0_.created as created1667_0_, subsidiary0_.creator as creator1667_0_, subsidiary0_.changed as changed1667_0_, subsidiary0_.changer as changer1667_0_, subsidiary0_.primary_event_gkey as primary40_1667_0_, subsidiary0_1_.instrument_value as instrument2_1671_0_, subsidiary0_1_.check_time as check3_1671_0_, subsidiary0_1_.node_name as node4_1671_0_, subsidiary0_1_.instrument_gkey as instrument5_1671_0_, subsidiary0_2_.move_kind as move2_1836_0_, subsidiary0_2_.ufv_gkey as ufv3_1836_0_, subsidiary0_2_.line_op as line4_1836_0_, subsidiary0_2_.carrier_gkey as carrier5_1836_0_, subsidiary0_2_.exclude as exclude1836_0_, subsidiary0_2_.fm_pos_loctype as fm7_1836_0_, subsidiary0_2_.fm_pos_locid as fm8_1836_0_, subsidiary0_2_.fm_pos_loc_gkey as fm9_1836_0_, subsidiary0_2_.fm_pos_slot as fm10_1836_0_, subsidiary0_2_.fm_pos_orientation as fm11_1836_0_, subsidiary0_2_.fm_pos_name as fm12_1836_0_, subsidiary0_2_.fm_pos_bin as fm13_1836_0_, subsidiary0_2_.fm_pos_tier as fm14_1836_0_, subsidiary0_2_.fm_pos_anchor as fm15_1836_0_, subsidiary0_2_.fm_pos_orientation_degrees as fm16_1836_0_, subsidiary0_2_.to_pos_loctype as to17_1836_0_, subsidiary0_2_.to_pos_locid as to18_1836_0_, subsidiary0_2_.to_pos_loc_gkey as to19_1836_0_, subsidiary0_2_.to_pos_slot as to20_1836_0_, subsidiary0_2_.to_pos_orientation as to21_1836_0_, subsidiary0_2_.to_pos_name as to22_1836_0_, subsidiary0_2_.to_pos_bin as to23_1836_0_, subsidiary0_2_.to_pos_tier as to24_1836_0_, subsidiary0_2_.to_pos_anchor as to25_1836_0_, subsidiary0_2_.to_pos_orientation_degrees as to26_1836_0_, subsidiary0_2_.che_fetch as che27_1836_0_, subsidiary0_2_.che_carry as che28_1836_0_, subsidiary0_2_.che_put as che29_1836_0_, subsidiary0_2_.che_qc as che30_1836_0_, subsidiary0_2_.dist_start as dist31_1836_0_, subsidiary0_2_.dist_carry as dist32_1836_0_, subsidiary0_2_.t_carry_complete as t33_1836_0_, subsidiary0_2_.t_dispatch as t34_1836_0_, subsidiary0_2_.t_fetch as t35_1836_0_, subsidiary0_2_.t_discharge as t36_1836_0_, subsidiary0_2_.t_put as t37_1836_0_, subsidiary0_2_.t_carry_fetch_ready as t38_1836_0_, subsidiary0_2_.t_carry_put_ready as t39_1836_0_, subsidiary0_2_.t_carry_dispatch as t40_1836_0_, subsidiary0_2_.t_tz_arrival as t41_1836_0_, subsidiary0_2_.rehandle_count as rehandle42_1836_0_, subsidiary0_2_.twin_fetch as twin43_1836_0_, subsidiary0_2_.twin_carry as twin44_1836_0_, subsidiary0_2_.twin_put as twin45_1836_0_, subsidiary0_2_.restow_account as restow46_1836_0_, subsidiary0_2_.service_order as service47_1836_0_, subsidiary0_2_.restow_reason as restow48_1836_0_, subsidiary0_2_.processed as processed1836_0_, subsidiary0_2_.pow as pow1836_0_, subsidiary0_2_.che_carry_login_name as che51_1836_0_, subsidiary0_2_.che_put_login_name as che52_1836_0_, subsidiary0_2_.che_fetch_login_name as che53_1836_0_, subsidiary0_2_.berth as berth1836_0_, subsidiary0_2_.category as category1836_0_, subsidiary0_2_.freight_kind as freight56_1836_0_, subsidiary0_3_.cv_id as cv2_1838_0_, subsidiary0_3_.transaction_count as transact3_1838_0_, subsidiary0_3_.run_time_ms as run4_1838_0_, case when subsidiary0_1_.ahe_gkey is not null then 1 when subsidiary0_2_.mve_gkey is not null then 2 when subsidiary0_3_.edievent_gkey is not null then 3 when subsidiary0_.gkey is not null then 0 end as clazz_0_ from srv_event subsidiary0_ left outer join srv_app_health_event subsidiary0_1_ on subsidiary0_.gkey=subsidiary0_1_.ahe_gkey left outer join inv_move_event subsidiary0_2_ on subsidiary0_.gkey=subsidiary0_2_.mve_gkey left outer join edi_event subsidiary0_3_ on subsidiary0_.gkey=subsidiary0_3_.edievent_gkey where subsidiary0_.primary_event_gkey= @P0 ', @type = N'SQL', @params = N'@P0 bigint', @hints = N'OPTION (OPTIMIZE FOR (@P0 = 1))' GO I also created a plan guide without the spaces at the end of the statement, just to be sure, but none of these are being used by the application. The weird thing is that if i run the query by myself, the plan guide is considered and it performs like I thought. declare @p1 int; exec sp_prepare @p1 output, N'@P0 bigint', N'select subsidiary0_.primary_event_gkey as primary40_1_, subsidiary0_.gkey as gkey1_, subsidiary0_.gkey as gkey1667_0_, subsidiary0_.operator_gkey as operator2_1667_0_, subsidiary0_.complex_gkey as complex3_1667_0_, subsidiary0_.facility_gkey as facility4_1667_0_, subsidiary0_.yard_gkey as yard5_1667_0_, subsidiary0_.placed_by as placed6_1667_0_, subsidiary0_.placed_time as placed7_1667_0_, subsidiary0_.event_type_gkey as event8_1667_0_, subsidiary0_.applied_to_class as applied9_1667_0_, subsidiary0_.applied_to_gkey as applied10_1667_0_, subsidiary0_.applied_to_natural_key as applied11_1667_0_, subsidiary0_.note as note1667_0_, subsidiary0_.billing_extract_batch_id as billing13_1667_0_, subsidiary0_.quantity as quantity1667_0_, subsidiary0_.quantity_unit as quantity15_1667_0_, subsidiary0_.responsible_party as respons16_1667_0_, subsidiary0_.related_entity_gkey as related17_1667_0_, subsidiary0_.related_entity_id as related18_1667_0_, subsidiary0_.related_entity_class as related19_1667_0_, subsidiary0_.related_batch_id as related20_1667_0_, subsidiary0_.acknowledged as acknowl21_1667_0_, subsidiary0_.acknowledged_by as acknowl22_1667_0_, subsidiary0_.flex_string01 as flex23_1667_0_, subsidiary0_.flex_string02 as flex24_1667_0_, subsidiary0_.flex_string03 as flex25_1667_0_, subsidiary0_.flex_string04 as flex26_1667_0_, subsidiary0_.flex_string05 as flex27_1667_0_, subsidiary0_.flex_date01 as flex28_1667_0_, subsidiary0_.flex_date02 as flex29_1667_0_, subsidiary0_.flex_date03 as flex30_1667_0_, subsidiary0_.flex_double01 as flex31_1667_0_, subsidiary0_.flex_double02 as flex32_1667_0_, subsidiary0_.flex_double03 as flex33_1667_0_, subsidiary0_.flex_double04 as flex34_1667_0_, subsidiary0_.flex_double05 as flex35_1667_0_, subsidiary0_.created as created1667_0_, subsidiary0_.creator as creator1667_0_, subsidiary0_.changed as changed1667_0_, subsidiary0_.changer as changer1667_0_, subsidiary0_.primary_event_gkey as primary40_1667_0_, subsidiary0_1_.instrument_value as instrument2_1671_0_, subsidiary0_1_.check_time as check3_1671_0_, subsidiary0_1_.node_name as node4_1671_0_, subsidiary0_1_.instrument_gkey as instrument5_1671_0_, subsidiary0_2_.move_kind as move2_1836_0_, subsidiary0_2_.ufv_gkey as ufv3_1836_0_, subsidiary0_2_.line_op as line4_1836_0_, subsidiary0_2_.carrier_gkey as carrier5_1836_0_, subsidiary0_2_.exclude as exclude1836_0_, subsidiary0_2_.fm_pos_loctype as fm7_1836_0_, subsidiary0_2_.fm_pos_locid as fm8_1836_0_, subsidiary0_2_.fm_pos_loc_gkey as fm9_1836_0_, subsidiary0_2_.fm_pos_slot as fm10_1836_0_, subsidiary0_2_.fm_pos_orientation as fm11_1836_0_, subsidiary0_2_.fm_pos_name as fm12_1836_0_, subsidiary0_2_.fm_pos_bin as fm13_1836_0_, subsidiary0_2_.fm_pos_tier as fm14_1836_0_, subsidiary0_2_.fm_pos_anchor as fm15_1836_0_, subsidiary0_2_.fm_pos_orientation_degrees as fm16_1836_0_, subsidiary0_2_.to_pos_loctype as to17_1836_0_, subsidiary0_2_.to_pos_locid as to18_1836_0_, subsidiary0_2_.to_pos_loc_gkey as to19_1836_0_, subsidiary0_2_.to_pos_slot as to20_1836_0_, subsidiary0_2_.to_pos_orientation as to21_1836_0_, subsidiary0_2_.to_pos_name as to22_1836_0_, subsidiary0_2_.to_pos_bin as to23_1836_0_, subsidiary0_2_.to_pos_tier as to24_1836_0_, subsidiary0_2_.to_pos_anchor as to25_1836_0_, subsidiary0_2_.to_pos_orientation_degrees as to26_1836_0_, subsidiary0_2_.che_fetch as che27_1836_0_, subsidiary0_2_.che_carry as che28_1836_0_, subsidiary0_2_.che_put as che29_1836_0_, subsidiary0_2_.che_qc as che30_1836_0_, subsidiary0_2_.dist_start as dist31_1836_0_, subsidiary0_2_.dist_carry as dist32_1836_0_, subsidiary0_2_.t_carry_complete as t33_1836_0_, subsidiary0_2_.t_dispatch as t34_1836_0_, subsidiary0_2_.t_fetch as t35_1836_0_, subsidiary0_2_.t_discharge as t36_1836_0_, subsidiary0_2_.t_put as t37_1836_0_, subsidiary0_2_.t_carry_fetch_ready as t38_1836_0_, subsidiary0_2_.t_carry_put_ready as t39_1836_0_, subsidiary0_2_.t_carry_dispatch as t40_1836_0_, subsidiary0_2_.t_tz_arrival as t41_1836_0_, subsidiary0_2_.rehandle_count as rehandle42_1836_0_, subsidiary0_2_.twin_fetch as twin43_1836_0_, subsidiary0_2_.twin_carry as twin44_1836_0_, subsidiary0_2_.twin_put as twin45_1836_0_, subsidiary0_2_.restow_account as restow46_1836_0_, subsidiary0_2_.service_order as service47_1836_0_, subsidiary0_2_.restow_reason as restow48_1836_0_, subsidiary0_2_.processed as processed1836_0_, subsidiary0_2_.pow as pow1836_0_, subsidiary0_2_.che_carry_login_name as che51_1836_0_, subsidiary0_2_.che_put_login_name as che52_1836_0_, subsidiary0_2_.che_fetch_login_name as che53_1836_0_, subsidiary0_2_.berth as berth1836_0_, subsidiary0_2_.category as category1836_0_, subsidiary0_2_.freight_kind as freight56_1836_0_, subsidiary0_3_.cv_id as cv2_1838_0_, subsidiary0_3_.transaction_count as transact3_1838_0_, subsidiary0_3_.run_time_ms as run4_1838_0_, case when subsidiary0_1_.ahe_gkey is not null then 1 when subsidiary0_2_.mve_gkey is not null then 2 when subsidiary0_3_.edievent_gkey is not null then 3 when subsidiary0_.gkey is not null then 0 end as clazz_0_ from srv_event subsidiary0_ left outer join srv_app_health_event subsidiary0_1_ on subsidiary0_.gkey=subsidiary0_1_.ahe_gkey left outer join inv_move_event subsidiary0_2_ on subsidiary0_.gkey=subsidiary0_2_.mve_gkey left outer join edi_event subsidiary0_3_ on subsidiary0_.gkey=subsidiary0_3_.edievent_gkey where subsidiary0_.primary_event_gkey= @P0 '; exec sp_execute @p1, 123123; exec sp_unprepare @p1; I also noted that, when I run the query above in SSMS, it appears in the extended events as sql_batch and not as rpc. Maybe that's something to do with the problem?

I work on SQL Server 2016 and i had severe performance issues with few queries. So I posted several posts in here and got solution/suggestion etc and greatly improved the execution time and learned a lot. Recently I got to know that most of the queries are running fast, but at times some queries are running slow.So i checked for few queries and learned that the query plans are getting stored in the plan cache,but i see lots of plan for a each queries.Some plans are widely used with double digit execution count while a lot are single digit execution count. I checked the parameters of some plans and I see different parameters.So the general gotcha will be it is due to parameter sniffing. Another thing I learned when investigating slow execution of a particular highly used query is that..among good parallel execution plans for that query ,sometimes the query is generating a serial plan and using it even though a parallel plan is available in the plan cache.Next thing i do is delete the serial plan and then the query runs fast.I have been doing this every now and then when i see the query executing slow. (Application is a Dot Net application and most if the queries are generated through LINQ-SQL.So i am limited with options and changing query by adding query hints(which needs code change) is not a solution for me.) After a lot of research and going thorough lots of posts from this forum,i learned that since i am using SQL server 2016 SP2.I have two options: 1. Query Store 2. Plan Guide. I thought of using plan guides initially : to force some query hints or forced parameterization. Since i am using LINQ-SQL ,the queries generating is already parametrized and don't have to force it.So that option is out of scope for me.(Still i created a plan guide with forced parametrization ,but the query still used cached plan and ignored plan guide) I created plan guides ,to add query hints like OPTION(RECOMPILE) as mentioned in this post. Unfortunately i didn't get an answer or some direction to the question.I am not sure why the plan guide didn't work.I couldn't find a proper instruction/information regarding plan guides for LINQ-SQL generated queries or the query with sp_executesql. So i moved to Query Store, and there everything is via GUI and it is easy.I could find most expensive queries and can see the plans they used previously. I tried what i was not able to do in plan guides,ie forcing a good plan.But still,i found out that the queries are going slow at times and serial plans are being generated in plan cache and query store also failed me. I learned that my plan cache is being cleared out periodically because i see different counts for plan when i search for a query.Probably that is why serial plans generate ,because good plan gets removed and on subsequent execution serial plan gets into plan cache. On analyzing the plan cache i learned that most of my queries are Adhoch and is that why plan cache is getting cleared.? Any suggestions/solutions? Optmize for adhoc workload is enabled. Total RAM is 128 GB and max server memory is set to 126 GB Changing parametrization from simple to forced for the database helps in someway.I think good plans are reused and new serial plans are not getting generated. Issues that exist: - Plan cache is getting cleared for some reason. - Plan guide to add a query hint like OPTION(RECOMPILE) is not working for sp_executesql type queries. - Some queries which i tuned(removed forced serialization by replacing scalar functions with TVF) are running slow because index seeks/scan of a table(with only two columns) is very costly when i check the execution in SentryOne Plan explorer.

I have been working on query plan stored in plan cache and every time i search for a particular query with the below sql query, SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount,cp.plan_handle, st.TEXT AS QueryText, qp.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st where st.text like '%SearchModel%' order by ExecutionCount desc I see different counts for the result.I am not clearing the plan cache and no other developers. I am working on SQL Server 2016 and i have turned on Optimize for Adhoc option.The queries are generated from application using LINQ-SQL.So i hope the queries generated are adhoc.Please correct me if i am wrong. Why is that the cached plan counts varies each time.? Does anyone has any insight from the below screenshot regarding plan cache usage.It has more memory allocated for adhoch queries as queries in my application comes from LINQ-SQL. I guess prepared queries are compiled queries?

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] [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] [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.

I have problems with a Plan Guide I have created in Microsoft SQL Server 2008 R2. The Plan guide is purposed for a performance issue in Microsoft Dynamics AX 2009 and forces a specific question to use a TABLE HINT index. However, when I perform a SQL Profiler Trace, the execution plan still uses two other indexes (one of them is a clustered). The Plan Guide itself works only when the parameters @p1 and @p2 are specified in the question, not for random parameter values. I suspect the problem can be related to how variables are sent from Dynamics AX. I have double checked that the SELECT statement is exactly the same as the one found in Profiler. Do you have any advice how to troubleshoot this problem? In my world, it should really work. T-SQL USED FOR CREATING NON WORKING PLAN GUIDE EXEC sp_create_plan_guide @name = N'PLAN_GUIDE', @stmt = N'SELECT TOP 1 A.COLUMN01,...............A.COLUMN35 FROM TABLE A WHERE ((COLUMN20VALUE=@P1) AND (COLUMN25VALUE=@P2))', @type = N'SQL', @module_or_batch = NULL, @params = N'@P1 nvarchar(4),@P2 nvarchar(20)', @hints = N'OPTION (TABLE HINT (A, INDEX(I_708COLUMN20)))' T-SQL FOR CREATING PLAN GUIDE (THAT WORKS) WITH PARAMETER VALUES IN THE SELECT QUERY (@params commented out) EXEC sp_create_plan_guide @name = N'', @stmt = N'SELECT TOP 1 A.COLUMN01,...............A.COLUMN35 FROM TABLE A WHERE ((COLUMN20VALUE='abc') AND (COLUMN25VALUE='L3355'))', @type = N'SQL', @module_or_batch = NULL, --@params = N'@P1 nvarchar(4),@P2 nvarchar(20)', @hints = N'OPTION (TABLE HINT (A, INDEX(I_708COLUMN20)))'

I have one SQL Server 2016 instance that every night BizTalk inserts messages into the database. BizTalk is managed by a third party company, and the way they are inserting the messages is causing a lot of RESOURCE_SEMAPHORE_QUERY_COMPILE waits on the instance and "A time out occurred while waiting to optimize the query. Rerun the query." on the SQL Error Log. They told us they run 200 connections and what I captured from sp_WhoisActive is a batch that runs thousands of inserts like you can see below (the three dots represent the other inserts): SET XACT_ABORT ON BEGIN TRAN DECLARE @eip INT, @r__ int, @e__ int SET @eip = 0 DECLARE @V1 nvarchar(40);INSERT [DWH].[Market_Message_ID] (MarketMessageNumber, Date_Created, Date_Imported, Message_Name, VersionNumber, TxRefNbr, Import_Start, Import_End, Import_Status, Import_By, Version) VALUES (N'341', N'28/02/2017 19:00:46', N'28/02/2017 19:00:46', N'DSO_MIM_341_1488195501479_9088551', N'11.00.00', N'0000000369457220', N'28/02/2017 19:00:46', N'28/02/2017 19:00:46', N'Success', N'BizTalk', N'1'); SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT IF (@e__ != 0 OR @r__ != 1) SET @eip = 1 SELECT @V1 = SCOPE_IDENTITY( );INSERT [DWH].[Market_Message_341] (MarketMessageID, Jurisdiction, MPRN, ReadDate, ReadingReplacementVersionNumber, UOM_Code, IntervalValue, IntervalPeriodTimestamp, IntervalStatusCode) VALUES (@V1, N'DSO', N'10000064887', N'26/02/2017 00:00:00', N'1', N'KWT', N'113.255', N'2017-02-26T00:00:00+00:00', N'VVAK'); SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT IF (@e__ != 0 OR @r__ != 1) SET @eip = 1 ... IF (@eip != 0) ROLLBACK ELSE COMMIT SET XACT_ABORT OFF There are two types of inserts, one on Market_Message_ID table and the other on Market_Message_ID, because they are using literals I was testing on a test server the use of Plan Guides to reduce the compilations. I've also compared using optimize for ad hoc workloads configuration and changing the database Parameterization parameter to force. On the three tests the wait event on the top was always the RESOURCE_SEMAPHORE_QUERY_COMPILE event, and on the plan guide I was never able to see the queries using this on the XML execution plan. I've used the following syntax to create the plan guide: DECLARE @stmt nvarchar(max); DECLARE @params nvarchar(max); EXEC sp_get_query_template N'INSERT [DWH].[Market_Message_341] (MarketMessageID, Jurisdiction, MPRN, ReadDate, ReadingReplacementVersionNumber, UOM_Code, IntervalValue, IntervalPeriodTimestamp, IntervalStatusCode) VALUES (N''1'', N''DSO'', N''10000064887'', N''26/02/2017 00:00:00'', N''1'', N''KWT'', N''113.255'', N''2017-02-26T00:00:00+00:00'', N''VVAK'');', @stmt OUTPUT, @params OUTPUT; EXEC sp_create_plan_guide N'CPSTemplateGuide', @stmt, N'TEMPLATE', NULL, @params, N'OPTION(PARAMETERIZATION FORCED)'; GO I'm I doing something wrong or is the nature of the batch that it is not using the plan guides or even get rid of the wait event using the other configuration options? The production instance is SQL Server 2016 RTM CU3 on a Windows 2012 R2 Standard VM with 8 CPUs and 64GB of RAM. SQL Max Memory 60GB The test instance is SQL Server 2016 RTM CU3 on a Windows 2012 R2 Standard VM with 4 CPUs and 16GB of RAM. I'm using SQL Query Stress to simulate the inserts on the database using 50 threads.

I have a query that needs a plan guide, but I am having a hard time setting it up. Query below from the procedure cache... (@state nvarchar(14), @jobName nvarchar(18), @jobGroup nvarchar(28), @oldState nvarchar(6)) UPDATE JOB_TRIGGERS SET TRIGGER_STATE = @state WHERE JOB_NAME = @jobName AND JOB_GROUP = @jobGroup AND TRIGGER_STATE = @oldState SQL Server chooses to perform a clustered index scan vs a non clustered index seek. I am having sporadic deadlock issues with this update statement and a certain select statement on the table. I understand why SQL is choosing an clustered index scan on the table....Rows < 100 and PageCount < 25. The table has a large of amount of activity, and since its a 3rd party product I don't have the ability to modify the query and supply an index hint. The query cost of using the non clustered index is more, but I believe it will improve concurrency based on testing.... I need to tell it to use the non clustered index below WITH (INDEX (ix_jobname_jobgroup_triggerstate)) Help setting this up would be much appreciated..

Let's say I have a complex query and for this query there are huge Differences between the estimated and actual execution plans . These differences are causing spillage into tempDb as described here . The problem is that in this query there are tables that are too big for me to update the statistics . How can I manipulate the query plan so that I can have more control on memory grants? I want to avoid memory spillage to tempdb. In order for this question to be objective and not too broad, we could concentrate for instance on the hash join operator, when can I replace it by a merge join operator? Are there other operators with memory grant that could be replaced depending on the work load? Understanding SQL server memory grant -- Search cache for queries with memory grants: SELECT t.text, cp.objtype,qp.query_plan FROM sys.dm_exec_cached_plans AS cp JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t WHERE qp.query_plan.exist('declare namespace n=''http://schemas.microsoft.com/sqlserver/2004/07/showplan ''; //n:MemoryFractions') = 1

To make a long story short, I have a view called vwRelatives which uses CTE recursion to build family trees. It's meant to be queried for a single person at a time. This runs in about a quarter second: SELECT * FROM vwRelatives WHERE person_id = 5 This (the way the query is executed from the application) takes more like 4.5 seconds: exec sp_executesql N'SELECT * FROM vwRelatives WHERE person_id = @P1',N'@P1 int',5 (Note that I've simplified the query a bit. The real thing has an explicit column list and an ORDER BY, but the WHERE semantics are the same. I get the same symptoms with either version.) Most likely, SQL Server is able to take person_id = 5 into account when creating an execution plan for the first query, but parameterizing it is causing the whole view to be run and *then* filtered by person_id. So I figured I'd create a plan guide. And now I have two problems. These are the steps I'm taking, which appear to have no effect. First, run the 'good' query to get it into the plan cache... SELECT * FROM vwRelatives WHERE person_id = 5 ...then perform the standard steps to turn it into a plan guide... --Get the 'good' plan SET @xml_showplan = ( SELECT query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp WHERE st.text LIKE N'SELECT * FROM vwRelatives WHERE person_id = 5' ) --Apply a plan guide to the meat of the sp_executesql query EXEC sp_create_plan_guide @name = N'vwRelatives_Test_Plan_Guide', @stmt = N'SELECT * FROM vwRelatives WHERE person_id = @P1', @type = N'SQL', @module_or_batch = NULL, @params = N'@P1 int', @hints = @xml_showplan; This completes successfully, but when I run the original sp_executesql statement again, it still takes 4.5 seconds. I have Profiler running, and both the Plan Guide Successful and Plan Guide Unsuccessful events are selected. Neither of those events ever shows up in the trace. What am I doing wrong that's preventing SQL Server from seeing this plan guide as a match for the sp_executesql query?

A piece of, um, software seems to be issuing ~20k queries of this form every few seconds: DELETE FROM macVendor WHERE companyId=N' 08-00-'; INSERT INTO macVendor (companyId,organization) VALUES (N' 08-00-',N'TEKTRONIX INC.'); I don't know why, and no the values don't change... Anyway, it really messes up the output of dm_exec_cached_plans so I figured I'd create a plan guide to force parameterization : EXEC sp_create_plan_guide @name = N'[PlanGuide-Stop-The-macVendorMadness]', @stmt = N'DELETE FROM macVendor WHERE companyId=@0; INSERT INTO macVendor (companyId,organization) VALUES (@0,@1);', @type = N'TEMPLATE', @params = N'@0 nvarchar(4000),@1 nvarchar(4000)' , @hints = N'OPTION(PARAMETERIZATION FORCED)' It seems however that plan guides can't be created for multiple statements? Msg 10506, Level 16, State 1, Line 1 Cannot create plan guide 'PlanGuide-Stop-The-macVendorMadness' because parameter @stmt has more than one statement. Any hints/help would be much appreciated! ---------- Update: I've created 2 plan guides (DELETE/INSERT) and they *were* used but only ~17k times (each) out of the 110k executions.

We recently ran into the tipping point issue and some of our report queries that used to complete execution within a couple of seconds are now taking more than 2 min because the query optimizer simply ignores the non clustered index on the search column. An example query below: select top 100 * from [dbo].[t_Call] where ID > 0 and throwtime between '3/20/2014 7:00:00 AM' and '3/24/2014 6:59:59 AM' order by id The ID column is clustered Index and Throwtime has nonclustered index. In this case, we noticed that ordering by throwtime instead of ID changes the query plan and nonclustered index is used.We are also planning to archive some of the old data (it currently has 20 mln rows!!). But making these changes in application is going to take some time and I need to find a way to make reports run reasonably fast, without making changes at the application level (oh well, such is life!). Enter plan guide. I created the below plan guide with a nonclustered index query hint and for some reason, the nonclustered index is still not used. Am I missing something? EXEC sp_create_plan_guide @name = N'[prod2reports_callthrowtime]', @stmt = N'select top 100 * from [dbo] . [t_Call] where ID > @0 and @1 @0 and @1 < = ThrowTime and ThrowTime < = @2 order by ID', @params = N'@0 int, @1 datetime, @2 datetime', @hints = N'OPTION (TABLE HINT( [dbo] . [t_Call], INDEX(IDX_NC_t_call_ThrowtimeProblemCodes)))' GO

When validating a plan guide for a piece of SQL in a stored procedure that references a temporary table named "#test" the function fn_validate_plan_guide returns the error: Invalid object name '#test'. But the the plan guide still pushes the query hint into the SQL and the desired execution is achieved. Does this highlight a problem with the fn_validate_plan_guide function? The script below recreates the problem. --Enable the actual execution plan before running the query so the plans can be compared USE [msdb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[test] AS BEGIN CREATE TABLE #test ( ID INT ) INSERT INTO #test SELECT ROW_NUMBER() OVER(ORDER BY job_id) FROM dbo.sysjobs SELECT * FROM #test t JOIN #test t2 ON t.ID = t2.ID DROP TABLE #Test END GO --Execution before the plan guide is created will have a hash join in the second batch EXEC msdb.dbo.test GO --Create the plan guide EXEC sp_create_plan_guide 'test', ' SELECT * FROM #test t JOIN #test t2 ON t.ID = t2.ID', 'OBJECT', 'dbo.test', NULL, 'OPTION (MERGE JOIN)' GO --Validate the plan guide. This returns the error "Invalid object name '#test'." SELECT plan_guide_id, msgnum, severity, state, message, name, create_date, is_disabled, query_text, scope_type_desc, scope_batch, parameters, hints FROM sys.plan_guides CROSS APPLY fn_validate_plan_guide(plan_guide_id); GO --Execution after the plan guide is created will have a merge join in the second batch EXEC msdb.dbo.test GO EXEC sp_control_plan_guide 'DISABLE', 'test' GO --Execution after the plan guide is disabled will go back to having a hash join in the second batch EXEC msdb.dbo.test GO EXEC sp_control_plan_guide 'ENABLE', 'test' GO --Execution after the plan guide is re-enabled will go back to having a merge join in the second batch EXEC msdb.dbo.test GO --Clean up EXEC sp_control_plan_guide 'DROP', 'test' GO DROP PROCEDURE test GO Is this function giving errors that are false positives or are these just warnings that the plan guide may fail or is it something else I haven't thought of? I have created a Connect item here with the text above but am yet to have a response.

I'm try to get force Parameterization on a simple adhoc SQL query.As explained in this article https://www.simple-talk.com/sql/performance/fixing-cache-bloat-problems-with-guide-plans-and-forced-parameterization/ But even trying to do this with the simplest query I cant get it to work CREATE TABLE fruit ( id BIGINT PRIMARY KEY(id) ,title VARCHAR(150) ) INSERT INTO fruit VALUES ( 1, 'Apple') , ( 2, 'Banana'), ( 3, 'Orange'), ( 4, 'Pear') DECLARE @params nvarchar(max); DECLARE @stmt nvarchar(max); EXEC sp_get_query_template N'SELECT title FROM fruit WHERE id = 4',@stmt OUTPUT, @params OUTPUT; --SELECT @params EXEC sp_create_plan_guide N'fruitGuide', @stmt, N'TEMPLATE', NULL, @params, N'OPTION(PARAMETERIZATION FORCED)'; GO SELECT title FROM fruit WHERE id = 1 Plan XML: Shows a compile and doesn't use the plan guide, is there something i'm missing here? Where am i going wrong?