Seeking help to test a use case on sql server memory

Question

This is a question which came out of curiosity to know of how SQL Server handles memory.

Assume I set a very low value for max server memory and I increase my workload like running dbcc checkdb, rebuild large indexes, select * from really big tables and so on. Again, this is only my assumption. Since the memory will not sufficient, some part of data/plans resided in RAM will be flushed out of memory. Now my question is, whether the flushed out data will be placed/pageout to OS pagefile or it spills to tempdb. Can anybody provide me same use case/test queries to test this behaviour and confirm(DMV query) the "before" and "after" effect to tell,

1. sql server data has been page out ?
2. how much data in bytes/KB it has been paged out?
3. sql server data has been spilled to tempdb ?
4. how much amount of data has been spilled out?

Basically I am looking for a proper methodology(step-by-step process) to test this behavior. I am getting ideas but was unable to implement properly.

Answer 1

SQL Server does not page, not in the sense the OS pages.

Data does not need to be paged becase the memory is just a cache of the data files. Data can simply be removed from memory and read back when needed.

Caches (eg. Procedure cache) are not paged either. Entries are removed and, if needed, they can be recreated from scratch (eg. compile).

The closest thing to paging are spills. These are operators that need intermediate data that doesn't fit in memory. They spill to tempdb. The typical example is the sort operator.

Answer 2

The only way SQL will spill to page file is if you set max memory to a value that is more than the RAM installed in the server.