When I thought about blogging on this topic I knew that I would be opening a rather controversial issue. Through many years dealing with customer issues and with guidance on many Microsoft SQL Server based products I had to defend old 5 minute in-memory rule and for good reasons. First, lets understand what is 5 minute in-memory rule, why is it controversial with some folks, although it definitely shouldn’t be in my opinion, and why it still matters.

The 5 minute rule was first described long ago in a cult paper, which I believe should be part of every computer science degree (at least it always was in Berkeley, MIT and NYU\Polytechnic) written by Jim Gray and Franco Putzolu long ago – in 1985. The paper is available from Berkeley site at – http://db.cs.berkeley.edu/cs286/papers/fiveminute-tr1986.pdf. Essentially what it states is to cache randomly accessed disk pages that are re-used every 5 minutes or less.The original 5-minute rule was derived from the following cost-benefit computation:

What does it mean in practice for SQL Server? Well, SQL Server always contained great basic performance counter metric on the instance level as part of Buffer Manager object called Page Life Expectancy (commonly shortened to PLE in SQL Server community).

Like all database servers, Microsoft SQL Server thrives on memory. Each instance of Microsoft SQL Server has its own memory address space. The SQL Server address space (Virtual memory allocation by Windows) consists of two main components: executable code and memory pool.

In our case we are mainly interested in memory pool and its largest component – Buffer Cache.

The memory pool is the unit of memory that an instance of SQL Server uses to create and manage data structures relating to client requests. Buffer Cache is the pool of memory pages into which data pages are read to satisfy requests. One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations. SQL Server builds a buffer pool in memory to hold pages read from the database. As it always a lot more performing to serve page from Buffer Cache than from Disk here where 5 minute rule described above and PLE metrics become paramount for SQL Server performance. The minimum recommended value for PLE is just that – 300 seconds (5 minutes). In simple words, if your page stays longer in the buffer pool (area of the memory cache) your PLE is higher, leading to higher performance as every time request comes there are chances it may find its data in the cache itself instead of going to hard drive to read the data. You can find your PLE value also from SQL Server DMV query in addition to Performance Monitor:

SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

As you can see on my legacy SQL Server 2008 instance result is pretty high as a have plenty of memory dedicated to SQL Server Buffer via max_server_memory and not much traffic at all:

So why the controversy? Well, since the 5 minute rule is now close to 30 years old there are some revisions that have been published to this classic.

In September 1997, group from MS Research, including Jim Gray published “The Five-Minute Rule Ten Years Later,and Other Computer Storage Rules of Thumb” technical report – http://db.cs.berkeley.edu/cs286/papers/fiveminute-tr1997.pdf Actually this paper reinforced 5 minute rule stating – “These calculations indicate that the reference interval of Equation (1) is almost unchanged, despite these 10x, 100x, and 1,000x changes. It is still inthe 1-minute to 10-minute range. The 5-minute rule still applies to randomly accessed pages.”

However, as time went by various customers reading various blogs on how PLE is old started questioning classic >300 PLE rule. Statements also from various customers included following – “