Why does MSSQL choose scan in execution plan?

Question

Here is my query (It is a Microsoft Axapta Query):

(@P1 bigint)
SELECT TOP 1 T1.JOURNALNUM,T1.LINENUM,T1.ACCOUNTTYPE,T1.COMPANY,T1.TXT,
T1.AMOUNTCURDEBIT,T1.CURRENCYCODE,T1.EXCHRATE,T1.TAXGROUP,
T1.CASHDISCPERCENT,T1.QTY,T1.BANKNEGINSTRECIPIENTNAME,
-- *Snipped lots of columns in T1* --
T1.MODIFIEDDATETIME,T1.RECVERSION,T1.PARTITION,T1.RECID 
FROM LEDGERJOURNALTRANS T1 
WHERE (((PARTITION=123123123) AND (DATAAREAID=N'test')) AND (REVRECID=@P1))

Current execution plan :

enter image description here

Current plan

Actually, there is a appropriate index on table.

Index columns : (PARTITION,DATAAREAID,REVRECID)

Fragmentation : enter image description here

I tried index force. This execution plan (index seek+key lookup) is faster than after plan (index scan):

Force Index Plan

enter image description here

enter image description here

And I tried to :

• UPDATE STATISTICS

• Changed it the column order, for example (REVRECID,PARTITION,DATAAREAID)

Why does MSSQL choose clustered index?

• Thomas Franz: are the data equally distributed or do you have e.g. 1 mio rows for partition 1 and only a few rows for the other partition numbers? If yes (and you have parameterization on your DB enabled) it could be a parameter sniffing problem, if the very same query (with other parameter values) was executed before. You can verify this by adding spaces, aliases etc. to your query (so it gets a new execution plan)
• Yunus UYANIK: I updated my question. This is a axapta query. @P1 is variable. I run query with parameter value in execution plan. declare @P1 bigint=5639064671
• Randi Vertongen: Seems like a estimation issue, probably due to the ~240 columns selected. Why are so many columns returned as a result? Could you add the query plan to pastetheplan. This will help in getting a more complete answer.
• Yunus UYANIK: I tried OPTION(RECOMPILE). But same plan again. And @RandiVertongen I added plan in my question.
• Randi Vertongen: Awesome, thanks. Is there a possibility to add the plan with the forced index aswell? That might show the estimation differences

Answer 1

Estimates, a huge amount of columns selected and predicate pushdown

The query's estimates are not accounting for the residual predicate on the scan being of a higher cost than the seek + key lookup to get all these extra columns from the clustered index. This results in the clustered index scan + residual predicate being chosen instead of the index seek.

My version is Microsoft SQL Server 2016 (RTM-GDR)

These estimates on predicate pushdown where improved in SQL server 2016 SP1

Update to improve diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2016

To improve diagnostics for the scenario that's described in the "Symptoms" section, SQL Server 2016 Service Pack 1 (SP1) introduces a new showplan XML attribute, Estimated Rows Read. This attribute provides the estimated count of rows will be read by the operator before the residual predicate is applied. This update is a complement to KB 3107397.

This adds the EstimatedRowsRead="" to the query plan XML, in your case this would be close or matching the residual predicate if the scan is chosen.

This should fix your issue

---

Residual predicate example

enter image description here

enter image description here

Reading 1.2M rows to return 0

Index scan query Estimated total cost

EstimatedTotalSubtreeCost="0.00449281">

Index seek query Estimated total cost

EstimatedTotalSubtreeCost="0.00672858">

Which is higher than the index scan estimations due to not accounting for the residual predicate, and that is why the lesser performing plan was chosen.

---

The main solution

The main solution would be upgrading to at least SP1 to add the:

Update to improve diagnostics for query execution plans that involve residual predicate pushdown in SQL Server 2016

You should patch sooner and more often, since SP2 CU6 is out as of March 19, 2019, this would be a far better choice.

Another side note, SP1 for SQL Server 2016 adds many additional features such as In memory OLTP, Compression, Columnstore indexes, ....

---

Other workarounds that may or may not be worth mentioning

• Selecting less columns if they are not needed
• Adding all these columns to the NC index's included columns
• You could try disabling row goals with OPTION(QUERYTRACEON 4138) (maybe)
• Using the WITH(INDEX)) Hint

---

Comparison with SQL Server 2016 SP1

when running a query alike yours, forcing the clustered index to be used on a SQL2016 SP1 version: enter image description here

The estimated subtreecost is much higher.

EstimatedTotalSubtreeCost="93.6951"

Where your estimated subtreecost for the clustered index scan

<RelOp AvgRowSize="4788" EstimateCPU="1.36996" EstimateIO="185.267" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00448209" TableCardinality="1245280">

Is low

EstimatedTotalSubtreeCost="0.00448209"

With the main difference being

EstimatedRowsRead="1000000"

shown when executing the query on the SQL 2016 with SP1 applied.

And when testing with the NC index specified

CREATE INDEX IX_PARTITION_DATAAREAID_REVRECID
ON dbo.LEDGERJOURNALTRANS(PARTITION,DATAAREAID,REVRECID);
<RelOp AvgRowSize="980" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1000000">

the EstimatedTotalSubtreeCost for the index seek (not total for entire plan) is also low:

 EstimatedTotalSubtreeCost="0.0032831

and the total estimated subtree cost for my test query is very close to yours

EstimatedTotalSubtreeCost="0.00657048">

• Yunus UYANIK: I can't change query, 'cause it is axapta query. I am gonna update my version, and I will try. Thank u Randi.
• Yunus UYANIK: I upgraded to SP2 and SQL Server chose my force index. Thanks for help Randi.
• Randi Vertongen: @YunusUYANIK awesome, no problem! Thanks for the update =)