5

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%?

0

1 Answer 1

5

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.