Skip to main content
corrected the quoted section to match the edited question
Source Link
Andriy M
  • 23.2k
  • 6
  • 59
  • 103

Why does SQL Server use the clustered index plus a sort algorithm instead of of using a non-clustered index even if the execution time is 38% faster in the latter case?

Because SQL Server uses a cost-based optimizer based on statistics, not runtime info.

During the cost estimation process for this query, it does actually evaluate the lookup plan, but estimates it will take more effort. (Note the "Estimated Subtree Cost" when hovering over SELECT in the execution plan). That's not necessarily a bad assumption either - on my test machine, the lookup plan takes 6X the CPU of the sort/scan.

Look to Rob Farley's answer as to why SQL Server might cost the lookup plan higher.

Why SQL Server use the clustered index plus a sort algorithm instead of non-clustered index even if execution time is 38% faster?

Because SQL Server uses a cost-based optimizer based on statistics, not runtime info.

During the cost estimation process for this query, it does actually evaluate the lookup plan, but estimates it will take more effort. (Note the "Estimated Subtree Cost" when hovering over SELECT in the execution plan). That's not necessarily a bad assumption either - on my test machine, the lookup plan takes 6X the CPU of the sort/scan.

Look to Rob Farley's answer as to why SQL Server might cost the lookup plan higher.

Why does SQL Server use the clustered index plus a sort algorithm instead of using a non-clustered index even if the execution time is 38% faster in the latter case?

Because SQL Server uses a cost-based optimizer based on statistics, not runtime info.

During the cost estimation process for this query, it does actually evaluate the lookup plan, but estimates it will take more effort. (Note the "Estimated Subtree Cost" when hovering over SELECT in the execution plan). That's not necessarily a bad assumption either - on my test machine, the lookup plan takes 6X the CPU of the sort/scan.

Look to Rob Farley's answer as to why SQL Server might cost the lookup plan higher.

Source Link
Forrest
  • 4.2k
  • 1
  • 19
  • 31

Why SQL Server use the clustered index plus a sort algorithm instead of non-clustered index even if execution time is 38% faster?

Because SQL Server uses a cost-based optimizer based on statistics, not runtime info.

During the cost estimation process for this query, it does actually evaluate the lookup plan, but estimates it will take more effort. (Note the "Estimated Subtree Cost" when hovering over SELECT in the execution plan). That's not necessarily a bad assumption either - on my test machine, the lookup plan takes 6X the CPU of the sort/scan.

Look to Rob Farley's answer as to why SQL Server might cost the lookup plan higher.