I have a query of the following form
exec sp_executesql N' UPDATE table SET column1 = 1, modify_date=N''2020-02-12 04:55:59.000'' WHERE (column5=@P1 AND column6=@P2 )',N'@P1 nvarchar(36),@P2 int',N'458986156148',87
I can't change anything about this query itself. It comes from an application.
Each time it is run, the update values change, which causes a new query plan to be generated instead of the old plan being reused.
The forced parameterization is activated, but does not seem to have an effect on this query, probably because of the way it is executed.
When executing the query itself, I see a total of 4 readings for the update. In this case, the execution itself did not cause an update, since there are no rows returned for the where clause.
When using the profiler, I see that the time between the incoming query and the start is almost all the time of the query (1 second). The actual execution is almost immediate.
The profiler shows 455,000 readings! that are not shown in the execution of the query itself.
So now i wonder
- Can I force the query to use the same plan here? A plan guide seems only usable for the same query or for a query that is affected by forced parameterization.
- Can we increase the compilation speed of this query? Where do the 455,000 readings come from? There are many statistics in that table (+ – 100) but 455,000 readings seem quite excessive.
This is in SQL Server 2019, there are no cumulative updates yet. I scanned the CU change log for something that might have to do with this.