sql-serverindexingquery-planner

Does SQL Server consider which index is already in memory (cached) in query plan?


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?


Solution

  • 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.