0

I was wondering if SQL server (or other RDBMS for that matter) considers what it already has in memory when creating a query plan, e.g.:

There are 2 indexes that are about equally good in serving the request but one of them is already either fully or partially in-memory, while the other one is not.

Is this something that planner takes into account or loading index from disc is not considered too important or just hard to implement such a feature properly?

Ilya Chernomordik
  • 27,817
  • 27
  • 121
  • 207

1 Answers1

1

Speaking only of SQL Server, the optimizer does not consider whether index pages are cached when generating the plan. The cache is managed independently using an LRU-K algorithm so the most recently used data, with indexes weighted higher.

Unless the indexes candidates are identical in all respects (redundant indexes are a bad thing), there is no "about equally good". The tiebreaker is the narrowest one in my experience.

I speculate it wouldn't be hard to implement but would add significant compilation costs without value in real-world workloads.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • What I meant was more like there are 2 indexes, one is better, but the other one is already in memory. That means that in fact "worse" index will be faster at least for the first query. But as you say SQL Server will not respect cache in this regard. – Ilya Chernomordik Mar 31 '21 at 12:02
  • Agreed it might be faster for the first execution of an individual query but the added compilation cost would likely be detrimental to most workloads. – Dan Guzman Mar 31 '21 at 12:07