Will index be fully loaded into memory

Question

I am wondering whether the index will be fully loaded into memory or not?

Answer 1

The answer to this is in several parts:

1. The size of the index.
2. What parts are accessed.
3. Memory pressure.

I had a huge index on a table that took SQL Server a full 3 minutes to return the first result. Second query (different request) was about one minute. Third query was 20 seconds. Eventually all results came in 0.001 seconds.

The point? SQL Server cached the index in memory as it was being read.

However, if I had put other memory pressure on SQL Server, it would have dropped out those cached index pages by order of lowest reads.

Answer 2

No, it's treated like any other data stored on disk. It's loaded into memory disk page by disk page. And a page stays in memory as long as it's regularly accessed.