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 cannot change anything about this query itself. It comes in from an application.
Every time it is executed the update values change, causing a new query plan to be generated rather than the previous plan being reused.
Forced parametrization is on but doesn't seem to have an effect on this query, probably by the way it is executed.
When executing the query itself I see a total of 4 reads for the update. In this case execution itself didn't cause an update as there are no rows returned for the where clause.
Using the profiler I see that the time between the query coming in and starting is almost the entire time of the query (1 second). Actual execution is almost immediate.
The profiler shows 455.000 reads!! which are not shown in the query execution itself.
So now I'm wondering
- 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 parametrization.
- Can we increase the compilation speed of this query? Where do the 455.000 reads come from? There are a lot of statistics on that table (+- 100) but 455.000 reads seems rather excessive.
This is on SQL Server 2019, no cumulative updates yet. I scanned the CU changelog for anything that might have to do with this.
edit/ Further investigation showed that there are a lot of locks on other tables during compilation time. I have 300 tables having a foreign key referencing the primary key in my table where I want to insert.
All foreign key relationships are trusted. Would there be any way to prevent these checks during compilation phase?
edit 2/ The dependencies are no foreign key constraints but views on the table. All views using that table have the SCH-S lock on them during execution which is expected. It's not clear if this is also causing the reads...
edit 3/ Apparently the 455.000 reads are done by scanning the sys.sysmultiobjrefs system table more than a million times. This doesn't seem proper behavior.