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 idea's but was able to implement properly!!

Any inputs will be of great help.

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.