0

We have some of our queries that are ten times slower than with the old CE ... there are a lot of sites explaining this "problem".

But here we have an example where all the time is passed is the "SQL Server parse and compile time" part ...

Plans are the same : 8% of index seek on table "T" and 92% of Clustered index update

UPDATE T
SET Value = 0.703645756
WHERE Col1 = '05/01/2022'
AND Col2 = '57XGOXYBT4OMXIFI'
AND Col3 = 372
AND Col4 = 'XX7R78OLRVJX9J2U'
AND Col5 <> 0.703645758

SQL 2008 :

SQL Server parse and compile time: CPU time = 78 ms, elapsed time = 83 ms. Table 'T'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.

(1 row affected)

Completion time: 2022-06-23T12:17:27.7728479+02:00

SQL 2016 : SQL Server parse and compile time: CPU time = 1078 ms, elapsed time = 1108 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. Table 'T'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server parse and compile time: CPU time = 153 ms, elapsed time = 153 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 157 ms, elapsed time = 155 ms.

(1 row affected)

Completion time: 2022-06-23T12:18:47.7376589+02:00

All the solutions to come back to the old CE are working (compatibility level, force CE, query hing, ...) but do you have encountered same thing ?

Could Microsoft make something to this ? Thanks

steevi2307
  • 9
  • 2
  • Is T a table or a view? If it is a view does it have WITH SCHEMABINDING? – Aardvark Jun 23 '22 at 10:50
  • High compile times are usually caused by some exceptionally complex code (even if it;s not in the actual query) such as partitioning or a large `IN` clause or a complex view. Please give a [mcve] containing your *actual* query and the table *and index* definitions. – Charlieface Jun 23 '22 at 11:18
  • Hi, it's a table, not a view ; we reproduce this even with small data name rows =39535 reserved =34328 KB data = 10344Kb index_size = 23632Kb unused = 352 Kb This table have one clustered index and 5 other indexes (2 filtered) and 6 triggers ... (2 for insert, 2 for update and 2 for delete) – steevi2307 Jun 24 '22 at 11:19
  • Something very strange also is that the query was used in a loop for update record by record (and so now 780ms for each update) ... we change that to update all records in one time (+/- 5000) and it's the same time ... so we will do that for the moment – steevi2307 Jun 24 '22 at 11:55

0 Answers0