I have several large tables that I need to copy into partitioned copies on a transactional database.
I am worried about the hit that this will cause to performance of my production applications.
In an attempt to mitigate this, I am looking into Resource Governor. I am going to set my script to use a resource pool that is limited to a max of 5% CPU and Memory.
The only part about this that I am not sure of is the effect on PLE.
As I understand it, the select from the table will read the value in to the cache. This will force out something else that was in the cache. (The basic concept of PLE.) But will the "forced out" items be limited to the 5% I have specified?
Meaning after I have used the 5% of the memory, will it only force out cached items in the same 5%? (Leaving the other 95% unaffected.)
Another way of asking this is: Is the memory limit of 5% the same as saying a cache limit of 5%?
Best Answer
This MSDN page states the memory settings only affect query memory:
Resource Governor Resource Pool
The above indicates that buffer pool is excluded from resource pool limitations. Therefore, if you are intending on performing some actions that may cause the entire buffer pool to be emptied in order to run your process, you may want to ensure that process is ran during off-hours.
The following is an excerpt from the MSDN blog, SQL Server Page Life Expectancy:
From questions in the comments here:
Query memory is used for execution of queries. It's temporarily used for things like sorts, bitmap creates, hash tables, etc. Cache contains in-memory table data.
Correct. Page Life Expectancy is all about how long data pages stay in memory.