5 Minor formatting improvements; added comments Q & A to enable seach, and before comments clean-up.
source | link

This MSDN page states the memory settings only affect query memory:   

https://msdn.microsoft.com/en-us/library/hh510189.aspxResource Governor Resource Pool

MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENTMIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of memory.

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, thisSQL Server Page Life Expectancy MSDN blog:

Page Life Expectancy (PLE) is the best indication of how volatile your Buffer Pool is (BP). It's a PerfMon counter, found in the SQL Server:Buffer Manager PerfMon object. There is also the Buffer Node:Page Life Expectancy counter which should be considered for NUMA systems, using the same logic per node as one would on a non-NUMA system.

Volatility is measured by taking the average "life" of a page within the Buffer Pool (in seconds). If a page is overwritten or aged out, it starts a whole new life.

So if lots of pages are being overwritten with new data very often, the average PLE will be low, and our BP volatility will be high.

Conversely if most pages in our BP remain there for a long time without being overwritten, the average PLE will be high, and the BP volatility will be low.

So why do we care about BP volatility and PLE? What can knowing the BP volatility do for us? What is a "good" and "bad" PLE figure?

PLE can be a measure of how much physical IO your SQL Server is doing. Hopefully I've got your attention, because physical IO is a major performance concern, both for reading and writing.

From questions in the comments here:

What is "query memory" and how is it different from the cache?

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.

It sounds like the answer to my question is that the PLE will not be protected by the Resource Governor at all. Does that sound right?

Correct. Page Life Expectancy is all about how long data pages stay in memory.

This MSDN page states the memory settings only affect query memory:  https://msdn.microsoft.com/en-us/library/hh510189.aspx

MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of memory.

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 this MSDN blog:

Page Life Expectancy (PLE) is the best indication of how volatile your Buffer Pool is (BP). It's a PerfMon counter, found in the SQL Server:Buffer Manager PerfMon object. There is also the Buffer Node:Page Life Expectancy counter which should be considered for NUMA systems, using the same logic per node as one would on a non-NUMA system.

Volatility is measured by taking the average "life" of a page within the Buffer Pool (in seconds). If a page is overwritten or aged out, it starts a whole new life.

So if lots of pages are being overwritten with new data very often, the average PLE will be low, and our BP volatility will be high.

Conversely if most pages in our BP remain there for a long time without being overwritten, the average PLE will be high, and the BP volatility will be low.

So why do we care about BP volatility and PLE? What can knowing the BP volatility do for us? What is a "good" and "bad" PLE figure?

PLE can be a measure of how much physical IO your SQL Server is doing. Hopefully I've got your attention, because physical IO is a major performance concern, both for reading and writing.

This MSDN page states the memory settings only affect query memory: 

Resource Governor Resource Pool

MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of memory.

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:

Page Life Expectancy (PLE) is the best indication of how volatile your Buffer Pool is (BP). It's a PerfMon counter, found in the SQL Server:Buffer Manager PerfMon object. There is also the Buffer Node:Page Life Expectancy counter which should be considered for NUMA systems, using the same logic per node as one would on a non-NUMA system.

Volatility is measured by taking the average "life" of a page within the Buffer Pool (in seconds). If a page is overwritten or aged out, it starts a whole new life.

So if lots of pages are being overwritten with new data very often, the average PLE will be low, and our BP volatility will be high.

Conversely if most pages in our BP remain there for a long time without being overwritten, the average PLE will be high, and the BP volatility will be low.

So why do we care about BP volatility and PLE? What can knowing the BP volatility do for us? What is a "good" and "bad" PLE figure?

PLE can be a measure of how much physical IO your SQL Server is doing. Hopefully I've got your attention, because physical IO is a major performance concern, both for reading and writing.

From questions in the comments here:

What is "query memory" and how is it different from the cache?

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.

It sounds like the answer to my question is that the PLE will not be protected by the Resource Governor at all. Does that sound right?

Correct. Page Life Expectancy is all about how long data pages stay in memory.

4 added 286 characters in body
source | link

This MSDN page states the memory settings only affect query memory: https://msdn.microsoft.com/en-us/library/hh510189.aspx

MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of memory.

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 this MSDN blog:

Page Life Expectancy (PLE) is the best indication of how volatile your Buffer Pool is (BP). It's a PerfMon counter, found in the SQL Server:Buffer Manager PerfMon object. There is also the Buffer Node:Page Life Expectancy counter which should be considered for NUMA systems, using the same logic per node as one would on a non-NUMA system.

Volatility is measured by taking the average "life" of a page within the Buffer Pool (in seconds). If a page is overwritten or aged out, it starts a whole new life.

So if lots of pages are being overwritten with new data very often, the average PLE will be low, and our BP volatility will be high.

Conversely if most pages in our BP remain there for a long time without being overwritten, the average PLE will be high, and the BP volatility will be low.

So why do we care about BP volatility and PLE? What can knowing the BP volatility do for us? What is a "good" and "bad" PLE figure?

PLE can be a measure of how much physical IO your SQL Server is doing. Hopefully I've got your attention, because physical IO is a major performance concern, both for reading and writing.

This MSDN page states the memory settings only affect query memory: https://msdn.microsoft.com/en-us/library/hh510189.aspx

MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of memory.

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.

This MSDN page states the memory settings only affect query memory: https://msdn.microsoft.com/en-us/library/hh510189.aspx

MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of memory.

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 this MSDN blog:

Page Life Expectancy (PLE) is the best indication of how volatile your Buffer Pool is (BP). It's a PerfMon counter, found in the SQL Server:Buffer Manager PerfMon object. There is also the Buffer Node:Page Life Expectancy counter which should be considered for NUMA systems, using the same logic per node as one would on a non-NUMA system.

Volatility is measured by taking the average "life" of a page within the Buffer Pool (in seconds). If a page is overwritten or aged out, it starts a whole new life.

So if lots of pages are being overwritten with new data very often, the average PLE will be low, and our BP volatility will be high.

Conversely if most pages in our BP remain there for a long time without being overwritten, the average PLE will be high, and the BP volatility will be low.

So why do we care about BP volatility and PLE? What can knowing the BP volatility do for us? What is a "good" and "bad" PLE figure?

PLE can be a measure of how much physical IO your SQL Server is doing. Hopefully I've got your attention, because physical IO is a major performance concern, both for reading and writing.

3 added 286 characters in body
source | link

This MSDN page states the memory settings only affect query memory: https://msdn.microsoft.com/en-us/library/hh510189.aspx

MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of memory.

These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, notabove indicates that buffer pool memory (for example, data and index pages). Setting a minimum memory value for ais excluded from resource pool means thatlimitations. Therefore, if you are ensuringintending on performing some actions that may cause the percentage of memory specified willentire buffer pool to be available for any requests that might runemptied in this resource pool. This is an important differentiator comparedorder to MIN_CPU_PERCENTrun your process, because in this case memoryyou may remain in the given resource pool even when the pool does not have any requests in the workload groups belongingwant to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool meansensure that when requests are running in this pool, they will never get more than this percentage of memoryprocess is ran during off-hours.

This MSDN page states the memory settings only affect query memory: https://msdn.microsoft.com/en-us/library/hh510189.aspx

MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of memory.

This MSDN page states the memory settings only affect query memory: https://msdn.microsoft.com/en-us/library/hh510189.aspx

MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT

These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages). Setting a minimum memory value for a pool means that you are ensuring that the percentage of memory specified will be available for any requests that might run in this resource pool. This is an important differentiator compared to MIN_CPU_PERCENT, because in this case memory may remain in the given resource pool even when the pool does not have any requests in the workload groups belonging to this pool. Therefore it is crucial that you be very careful when using this setting, because this memory will be unavailable for use by any other pool, even when there are no active requests. Setting a maximum memory value for a pool means that when requests are running in this pool, they will never get more than this percentage of memory.

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.

2 added 170 characters in body
source | link
1
source | link