sql server - Mysterious query running from where? - Stack Overflow

My application is issuing the following query:

(@orgId bigint,@contractNumber nvarchar(12))
Select top 1 *                                                 
 From   ReceivableCut c with (nolock)                           
 Where  1=1                                                     
 And    c.ReceivableCutIndicator = 0                            
 And    c.OrgId                  =  @orgId 
 And    c.ContractNumber         =  @contractNumber     
 And        exists (select '1' from Receivable             r with(nolock) where r.OrgId = c.OrgId and r.ReceivableNumber = c.ReceivableNumber) 
 Order  by c.ReceivableNumber desc

and when I access Azure Database query performance insight, I get that the query with the highest consumption is:

(@orgId bigint,@contractNumber nvarchar(12))
Select top 1 *                                                 
 From   ReceivableCut c with (nolock)                           
 Where  1=1                                                     
 And    c.ReceivableCutIndicator = 0                            
 And    c.OrgId                  =  @orgId 
 And    c.ContractNumber         =  @contractNumber     
 And        exists (select '1' from Receivable             r with(nolock) where r.OrgId = c.OrgId and r.ReceivableNumber = c.ReceivableNumber) 
 Order  by c.ReceivableNumber desc 
option (PLAN PER VALUE(ObjectID = 0, QueryVariantID = 3, predicate_range([DB_MERCHANTS_PAYMENT].[dbo].[ReceivableCut].[OrgId] = @orgId, 100.0, 1000000.0)))

That query is not issued anywhere in my code, and it runs for the exactly the amount of times I expect my query to run.

My ORM (EFCore) is not issuing this new query, so WHERE is this coming from?!? the major issue is that @orgId is always 63 so that hint is COMPLETELY wrong!!!

My application is issuing the following query:

(@orgId bigint,@contractNumber nvarchar(12))
Select top 1 *                                                 
 From   ReceivableCut c with (nolock)                           
 Where  1=1                                                     
 And    c.ReceivableCutIndicator = 0                            
 And    c.OrgId                  =  @orgId 
 And    c.ContractNumber         =  @contractNumber     
 And        exists (select '1' from Receivable             r with(nolock) where r.OrgId = c.OrgId and r.ReceivableNumber = c.ReceivableNumber) 
 Order  by c.ReceivableNumber desc

and when I access Azure Database query performance insight, I get that the query with the highest consumption is:

(@orgId bigint,@contractNumber nvarchar(12))
Select top 1 *                                                 
 From   ReceivableCut c with (nolock)                           
 Where  1=1                                                     
 And    c.ReceivableCutIndicator = 0                            
 And    c.OrgId                  =  @orgId 
 And    c.ContractNumber         =  @contractNumber     
 And        exists (select '1' from Receivable             r with(nolock) where r.OrgId = c.OrgId and r.ReceivableNumber = c.ReceivableNumber) 
 Order  by c.ReceivableNumber desc 
option (PLAN PER VALUE(ObjectID = 0, QueryVariantID = 3, predicate_range([DB_MERCHANTS_PAYMENT].[dbo].[ReceivableCut].[OrgId] = @orgId, 100.0, 1000000.0)))

That query is not issued anywhere in my code, and it runs for the exactly the amount of times I expect my query to run.

My ORM (EFCore) is not issuing this new query, so WHERE is this coming from?!? the major issue is that @orgId is always 63 so that hint is COMPLETELY wrong!!!

• sql-server
• t-sql
• azure-sql-database
• query-optimization
• parameter-sniffing

Share Improve this question Follow edited Feb 3 at 18:02 Thom A 96.3k1111 gold badges6161 silver badges9595 bronze badges asked Feb 3 at 15:18 LeonardoLeonardo 11.4k1414 gold badges7373 silver badges185185 bronze badges 5

• The bottom version is Azure applying OPTIONs to the plan post-facto via Query Store and PSPO learn.microsoft.com/en-us/sql/relational-databases/performance/… . If PSPO is causing issues here then you need to look at the root cause, which is probably a lack of an index ReceivableCut (OrgId, ContractNumber, ReceivableNumber). Remove those nolock hints while you're at it. – Charlieface Commented Feb 3 at 15:35
• 1 @BartMcEndree Looks like OP just doesn't understand where the option line is coming from, but it's a normal feature of PSPO – Charlieface Commented Feb 3 at 15:36
• @Charlieface is there a way to disable it? and guarantee that this is in fact being forced in by PSPO? or you are sure and seen this many times before – Leonardo Commented Feb 3 at 17:41
• 1 brentozar.com/archive/2022/08/… you probably don't want to disable it, you probably want to fix your query. The query plan option itself is not an issue, it will only be used if the parameter value actually matches (which is what PSPO is for). The option is a red herring, what you need to look at is the actual query plan and why it's slow. But for that, we need at a minimum all relevant table and index definitions, and please share the query plan via pastetheplan.com – Charlieface Commented Feb 3 at 17:44
• Disable automatic plan correction using this command ALTER DATABASE dbname SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = OFF ); Run your query again and check if the OPTION (PLAN PER VALUE...) disappears. If it does, then Azure was dynamically altering your plan. – Pratik Lad Commented Feb 11 at 7:18

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Agreed with @Charlieface, the difference between the queries is due to Azure SQL Database's automated plan correction capability, known as Plan Forcing or automated Tuning. Automatic plan correction is an automatic tuning function that detects execution plan choice regression and automatically resolves the issue by reverting to the last known good plan.

• To Disable automatic plan correction using this command

ALTER DATABASE dbname SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = OFF );