How can I Debug a Buffer Issue?

I have a production "Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)" that is showing weird buffer and page life expectancy (PLE) symptoms.

I am running this every minute on my server (to track this issue):

SELECT @ple = CAST([cntr_value] AS VARCHAR(20))
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

SELECT @usedBufferPages = CAST(COUNT(*) /128 AS VARCHAR(20)) 
FROM sys.dm_os_buffer_descriptors

DECLARE @StartDate VARCHAR(8) = Convert(VARCHAR(8), GETDATE(), 14)
RAISERROR ('%s. PLE at %s and Used Buffers at %s at %s ', 0, 
            1,@runCountString ,@ple, @usedBufferPages, @StartDate) WITH NOWAIT

This is some example output:

1. PLE at 858 and Used Buffers at 7290 at 09:51:42
2. PLE at 918 and Used Buffers at 7342 at 09:52:42
3. PLE at 978 and Used Buffers at 7408 at 09:53:43
4. PLE at 1039 and Used Buffers at 7547 at 09:54:43
5. PLE at 1100 and Used Buffers at 7697 at 09:55:44
6. PLE at 1160 and Used Buffers at 7901 at 09:56:45
7. PLE at 1221 and Used Buffers at 7961 at 09:57:46
8. PLE at 1282 and Used Buffers at 8012 at 09:58:46
9. PLE at 11 and Used Buffers at 313 at 09:59:46
10. PLE at 31 and Used Buffers at 966 at 10:00:46
11. PLE at 90 and Used Buffers at 1580 at 10:01:47
12. PLE at 151 and Used Buffers at 3072 at 10:02:47
13. PLE at 211 and Used Buffers at 3152 at 10:03:47
14. PLE at 271 and Used Buffers at 3729 at 10:04:47

At item #24 SQL Server reports the PLE going from 1,282 to 11. SQL Server also reports that the used buffers go from 8,012 to 313.

First I looked for poor running queries, and I found a fixed a few (had no effect on the issue). But, I am not finding any problem queries that correlate to the times that I have PLE/Buffer issues. Also, if it was a poor running query, then I would think the Buffers would be full of that query's data, not empty/missing/errored.

Next I thought that the Virtual Machine was getting its memory rest

Your buffer pool is only 13GB and your databases are 383 GB and 378 GB which you have classified as being OLTP - small transactions running too frequently.

The above situation, if I have to imagine is like below :

(source : Google Photos)

You have to understand how SQL Server stores information :


SQL Server stores information in memory in a structure called a memory cache. The information in the cache can be data, index entries, compiled procedure plans, and a variety of other types of SQL Server information. To avoid re-creating the information, it is retained the memory cache as long as possible and is ordinarily removed from the cache when it is too old to be useful, or when the memory space is needed for new information. The process that removes old information is called a memory sweep. The memory sweep is a frequent activity, but is not continuous.

You are for sure experienceing memory starvation due to sheer amount of database size and your inadequate buffer pool. Refer to - How to determine ideal memory for instance?

Collect wait stats and check for performance issues that arises from wasted buffer pool memory

Recommendation:

Add more memory to server instance and separate the two databases on different VMs with adequate memory.

StackExchange Database Administrators Q#115671, answer score: 9