Why is COMPATIBILITY_LEVEL: 160 using Clustered Index Scan?

Question

Disclaimer: I'm a developer dwelling in databases, so my knowledge is limited

We recently upgraded our MSSQL Server from 2012 to 2022 and in that regard also updated the COMPATIBILITY_LEVEL from 110/2012 to 160/2022 (all indexes have been rebuilt). But I'm seeing some difference in execution plan for a specific table with 475.000 rows where in 110 it runs a key lookup and in 160 it runs a clustered index scan.

This results in a lot more logical reads with the clustered index scan compared to the key lookup.

So, I was wondering if it's a problem or if it's okay? Generally, I'm seeing a higher CPU usage in 160 compared to 110.

Query:

SET STATISTICS TIME ON
SET STATISTICS IO ON

SELECT [e].[Id], [e].[Title], [e].[Level], [e].[PageNumber], [e].[DocumentId], [e].[SortOrder]
FROM [ExternalBookmark] AS [e]
INNER JOIN [Dokument] AS [d] ON [e].[DocumentId] = [d].[Id]
WHERE [d].[AgendaItemId] = 16602592
ORDER BY [e].[SortOrder]

160:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ExternalBookmark'. Scan count 1, logical reads 6630, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Dokument'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

https://www.brentozar.com/pastetheplan/?id=SybVG68Z0

110:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'ExternalBookmark'. Scan count 1, logical reads 15, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Dokument'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

https://www.brentozar.com/pastetheplan/?id=H1rsM68WR

I tried with both FORCE_LEGACY_CARDINALITY_ESTIMATION and DISALLOW_BATCH_MODE hints, but it's still using clustered index scan. I also tried updating statistics without fullscan for the two tables involved (ExternalBookmark and Dokument) but still no luck.

Answer 1

The plans provided are taken with different database states at different points in time, or perhaps on different servers:

• In the hash join plan, Dokument has 16,478,000 rows and ExternalBookmark has 475,588.
• In the nested loops plan, Dokument has 16,051,000 rows and ExternalBookmark has 420,048.

More to the point, the predicate [AgendaItemId] = 16602592 is estimated to return 2.84199 rows in the lookup plan, but 124.309 rows in the scan plan. This suggests the statistics are different in the two cases.

SQL Server is less likely to choose a lookup plan when more than a small number of lookups seem to be required.

This is based on a number of modelling assumptions, including that queries start with a cold cache (so physical reads will be needed, at least initially), and sequential I/O (scan) is larger and cheaper than random I/O (repeated lookups).

These assumptions do not depend so much on compatibility level; they are a general thing. As a practical matter, it means the optimizer tends to switch from a navigational strategy to scans and hashes earlier than one might hope.

The costing model and cardinality estimates therefore explain why SQL Server prefers a scan and hash match in one case, but a seek and lookup plan in the other. Only you are in a position to investigate in detail why the estimates are different.

I was wondering if it's a problem or if it's okay?

Your two plans don't show a huge difference in absolute execution time, but if this is a statement that runs very often, you might choose to enforce the faster plan using Query Store or a hint (like FORCESEEK). Again, only you have the context needed to determine if it is important or not.

In both cases, the AgendaItemId equality predicate matches exactly one row. If this is an undeclared key (i.e. AgendaItemId is unique), you could consider making it a key with a unique index or constraint. With that information, the optimizer will likely naturally choose the seek and lookup plan.

Otherwise, you could consider adding included columns to the idxExternalBookmark_DocumentId index so the lookup is not required. This is not for free, so you would need to balance the competing factors involved in making that decision. Making the index 'covering' would favour the seek as an option for the optimizer.

Finally, if AgendaItemId is not an undeclared key, and a higher number of matches is possible with other predicate values, you might prefer the scan and hash join plan for performance stability.

You might like to watch Why Logical Reads Are A Bad Metric For Query Tuning In SQL Server by Erik Darling.