November 27, 2019 at 3:23 am
As I understand SQL server instance will reserve a chunk of physical memory once it's started thus I will always see from my Task Manager window that the memory utilization is always high.
How do I determine that my SQL instance is running out of memory since I cannot use Task Manager as a reference point.
November 27, 2019 at 10:05 am
In management studio you have a number of reports that can show you this
I like the performance dashboard
look out for the buffer cache hit ratio - 100% is good, another figure to look out for (you might have to use perfmon for this) is Page life expectancy - PLE measures how long a block of data stays in memory.
these 2 are the main figures to look at for memory pressure.
MVDBA
November 27, 2019 at 11:12 am
Page File usage is something which you should monitor to find out if your instance is running out of memory. You can use the below query.
SELECT (total_page_file_kb / 1024) AS Total_Page_File_Size_In_MB
, ((total_page_file_kb - available_page_file_kb) / 1024) AS Used_Page_File_Size_In_MB
, (total_physical_memory_kb / 1024) AS Total_Physical_Memory_Size_In_MB
, ((total_physical_memory_kb - available_physical_memory_kb) / 1024) AS Used_Physical_Memory_Size_In_MB
, CEILING(((total_page_file_kb * 1.00) / (total_physical_memory_kb * 1.00)) * 100) AS [Percentage_Of_Total_Page_File_Vs_Used_Physical_Memory]
, CEILING(((total_physical_memory_kb * 1.00 - available_physical_memory_kb * 1.00) / 1024)
/ ((total_page_file_kb * 1.00 - available_page_file_kb * 1.00) / 1024)) * 100 AS [Percentage_Of_Used_Page_File_Vs_Used_Physical_Memory]
, system_memory_state_desc
FROM sys.dm_os_sys_memory
Putting here the extracts of the reference articles :
BuckWoody in his blog says
“A “Page File” is simply a file on the hard drive that handles situations where the system wants to move (or “page out”) sections of memory. There are several situations that cause this, but the one you’re most concerned about is when the system is out of physical memory. If the system runs out of memory it can “borrow” some storage from the hard drive to release some memory until it needs that data again.
And that’s exceptionally bad. The reason is that hard drives are amazingly slow in comparison with solid-state memory access. So you REALLY slow down a SQL Server when this happens. In fact, if it happens a lot, that’s a sure sign you need to add more physical RAM.”
Milena Petrovic in his blog says
“Frequent paging and using a large percentage of the paging file requires reducing system workload or adding more memory.
The threshold you should not reach depends on the size of the paging file, but shouldn’t be more than 70%. As a rule of thumb, you can set the paging file to be 50% of total RAM”
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply