July 25, 2016 at 1:29 pm
Hey guys,
I'm having trouble figuring out if one of our production SQL Server environments is lacking memory. Currently this server has 16GB of memory, and this instance has a max memory setting of 14GB. I'm monitoring the PLE from Spotlight and DMVs, and it's regularly dropping to 50-150 seconds during our busy production hours. I haven't been able to correlate these drops to any specific queries. We're not doing any index rebuilds or DBCC CHECKDB runs during this time either. Any suggestions on where to start troubleshooting this? Should I just throw more physical memory at this server? Below is a graph of the last two hours of our PLE.
July 25, 2016 at 1:38 pm
Nothing unusual as such, PLE is only one of the memory related counters and there can be many reasons for it to drop on an sparse memory resource instance like yours.
Questions, any drop in the buffer pool or other indications of memory pressure? Any SSIS or Agent Jobs running?
😎
July 25, 2016 at 1:40 pm
Just to give you a benchmark, my new laptop has as much memory as your production server... 🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 26, 2016 at 7:03 am
Rorz,
First, are you using Lock Pages in Memory or the default setting? If the OS is handling memory management then you need to monitor Available MB of memory to see if the OS had enough memory for operations outside of SQL. If your Available MB of memory dips down when PLE goes down then the OS is reclaiming memory regardless of your max memory setting. If this is the case, lower max memory setting so the OS has enough memory.
If this is not the case see the last paragraph of the information below to calculate how much PLE you should have and if you can't achieve this, then you will have to look into ways to reduce the memory requirements of your databases or increase the RAM on the server.
Here is some information on Max and Min memory settings, Lock pages in memory and Page Life Expectancy:
The SQL Maximum server memory setting is used to allocate/limit the maximum size of the buffer pool memory that is utilized by SQL for requests for memory that require <=8 KB pages such as data pages, query plan cache and working sets. Not all SQL features use the buffer pool and will use memory outside of the buffer pool. For SQL 2008 R2 the list of these features are: SQL Common Language Runtime, BACKUP threads, XML, Extended stored procedures, Linked Server memory, Thread Stack memory and Memory for Database mail.
The SQL Minimum server memory setting is used to keep SQL buffer pool memory from being paged out to disk due to either OS memory pressure alerts or the SQL voluntary memory paging operation.
The Lock Pages in Memory setting changes buffer pool memory allocation to use AWE allocation instead of the default SQL Virtual memory allocation. AWE memory is not allowed to be paged to disk/the OS page file. This will keep SQL from paging SQL buffer pool memory out to the OS page file due to OS memory pressure issues but will not stop SQL from performing voluntary memory paging operations in which modified database pages in memory are written back out to their respective database file.
1. The benefit of using Lock Pages in Memory is to keep SQL performance from being affected by OS memory pressure and paging out large portions of the buffer pool to disk.
2. The negative affect of using Lock Pages in Memory is that OS memory pressure issues can go unnoticed and unresolved due to their seeming to be no problems occurring in SQL.
Page life expectancy can indicate if SQL has enough memory available to perform optimally. Lower values during regular operations suggest a need to install more memory. A realistic value for expected Page life can be determined by performing this equation (DataCacheSizeInGB/4GB *300). This value is used as a baseline but realistically memory does not need to be added until it goes below 180 seconds.
o DataCacheSizeinGB can be determined by running the query below and adding up the total used for all databases. This value will grow in size as the SQL memory buffer is used and should be run after the buffer has time to fill.
? SELECT count(*)*8/1024/1024 AS 'Data Cache Size(GB)'
,CASE database_id
WHEN 32767 THEN 'RESOURCEDB'
ELSE db_name(database_id)
END AS 'DatabaseName'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Data Cache Size(GB)' DESC
Hope this helps!!
Jon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply