October 26, 2016 at 6:43 am
Hi Friends,
All off sudden in my PROD SQL server memory was reduced to zero(As the minimum memory was set to 0).
Is there any way to find which one caused the SQL server to release the memory?
This is a VM ware OS windows server 2012R2 and dedicated only for SQL server 2014 SP1.
Got the error in the error log msg -> “A significant part of SQL server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 2024356, committed (KB): 45044452, memory utilization: 4%.”
Thanks,
Kesavan
October 26, 2016 at 6:48 am
kesavan.rv (10/26/2016)
Hi Friends,All off sudden in my PROD SQL server memory was reduced to zero(As the minimum memory was set to 0).
Is there any way to find which one caused the SQL server to release the memory?
This is a VM ware OS windows server 2012R2 and dedicated only for SQL server 2014 SP1.
Got the error in the error log msg -> “A significant part of SQL server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 2024356, committed (KB): 45044452, memory utilization: 4%.”
Thanks,
Kesavan
Surely it is the Maximum memory, not the Minimum, which is the important setting here?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 26, 2016 at 7:10 am
So who or what can change your server configuration settings?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 26, 2016 at 7:15 am
kesavan.rv (10/26/2016)
Hi Friends,All off sudden in my PROD SQL server memory was reduced to zero(As the minimum memory was set to 0).
Is there any way to find which one caused the SQL server to release the memory?
This is a VM ware OS windows server 2012R2 and dedicated only for SQL server 2014 SP1.
Got the error in the error log msg -> “A significant part of SQL server process memory has been paged out. This may result in a performance degradation. Duration: 328 seconds. Working set (KB): 2024356, committed (KB): 45044452, memory utilization: 4%.”
Thanks,
Kesavan
The SQL Server is fully dependent on the OS for memory I/O, if something is pressuring the OS then it will eventually page the memory allocated for the SQL Server. It can be hard or almost impossible to detect the cause for this from the SQL Server but it's relatively simple from the OS side. Common causes are i.e. backup agent encrypting and/or deduping backups, RDP sessions which have larges files open in editors, SSIS packages and SSMS open on the server with very large result sets.
😎
October 26, 2016 at 7:25 am
thanks for the reply friends..there is an another view which i got is 'virtual memory ballooning ' an option in VMware..
October 26, 2016 at 8:50 am
kesavan.rv (10/26/2016)
thanks for the reply friends..there is an another view which i got is 'virtual memory ballooning ' an option in VMware..
If the data is being paged out of physical memory, it has to be put somewhere.
So that goes to your swap file.
Use min server memory to guarantee a minimum amount of memory available to the SQL Server Memory Manager for an instance of SQL Server.
Do you have SSIS, SSAS or SSRS running on the same server?
If so, set the Max Mem leaving a couple of GB for the OS and these services.
Don't make the MIN and MAX Memory values the same.
October 26, 2016 at 10:07 am
1) misconfigured/over provisioned virtualization can cause memory flushes
2) I have seen probably a half-dozen different firmware/driver/other bugs over the last 2 decades that caused complete memory flush. Are you patched up on EVERYTHING?!?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply