SQL Server Performance

Seeking help to test a use case on sql server memory

Discussion in 'ALL SQL SERVER QUESTIONS' started by Frank.svs, May 19, 2014.

  1. Frank.svs New Member

    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.

Share This Page