SQL Server – Resolve Slow Query Compilation and High Reads

query-performancesql serversql-server-2019

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.

Best Answer

Forced parametrization is on but doesn't seem to have an effect on this query, probably by the way it is executed.

That's correct, forced paramterization doesn't work here because the query has "already been parameterized on the client-side application" (source). In other words, the problem is that there are two actual parameters, so the embedded date and int parameters can't be forced.

On to what's going on with the reads / CPU...

Auto Created / Updated Stats

As far as the reads being shown in profiler, those do include statistics updates and creation. That would explain the problem if it only happened occasionally. Since you mentioned these long compile times and high reads occur on each execution, it doesn't seem like that's the culprit (unless this application is doing something really unusual, like aggressively deleting auto-stats).

Version Store Reads

There are other kinds of reads that are not reported in SET STATISTICS IO ON, which I talked about on my blog. These include version store reads, so if you have SNAPSHOT or READ COMMITTED SNAPSHOT enabled on this database, that could explain some of the CPU / read discrepancy. Especially if you have long-running transactions.

Additional Troubleshooting

It might be helpful if you could share an "Actual Execution Plan" for the query in question while reproducing the issue. There could be clues in addition to the information you've already provided.

Plan Forcing

As far as I can tell, there isn't any way to prevent a plan from compiling each time one of these queries comes in (as you noted, plan guides won't work, and neither will Query Store plan forcing).

Ad-Hoc Workloads

As a side note, since you have these embedded parameters, your workload could probably benefit quite a bit from the optimize for ad hoc workloads server-level configuration option. It will keep your plan cache from being bloated, which might also be contributing to CPU issues (as SQL Server has to evict items from the plan cache over time due to all the "new" plans coming in for each query).

Related Solutions

Sql-server – Query runs slow in test site on first execution. Why

Well, the first time you run the query, it has to load it from disk; subsequently it uses memory, which is much faster than disk. If you clear the buffers, you essentially flush all of that data from memory, so next time you request the data, it has to load it from disk again. Think of disk vs. memory as turtle vs. hare. Outside of test scenarios, you want your queries generally to be running from a warm cache, and shouldn't be purposefully clearing out the buffer pool using DBCC DROPCLEANBUFFERS;.

SQL Server Query Performance – Optimizing Group By with Many Columns

The non-clustered index you have tested is not the best for this query. It can be used for the WHERE clause and for doing an index scan instead of a full table scan but it cannot be used for the GROUP BY.

The best possible index would have to be a partial index (to filter the unwanted rows from the WHERE clause), then have all the columns used in the GROUP BY and then INCLUDE all the other columns used in the SELECT:

CREATE INDEX special_ix 
  ON dbo.Commissions_Output
    ( company, location, account, 
      salesroute, employee, producttype, 
      item, loadjdate, commissionrate ) 
INCLUDE 
  ( [Extended Sales Price], [Delivered Qty] ) 
WHERE 
  ( [Extended Sales Price] <> 0 ) ;