SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with memory - pagefile use


Problem with memory - pagefile use

Author
Message
antoninSQL
antoninSQL
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 78
Hi everybody,

I have a server with a problem of memory. This is that I have collect on the server.

-Server :
--Task Manager
Memory Virtual Machine : 6 Go
Memory use : 5.1 Go
Last reboot : 272 days

--Perfmon :
Pagefile use : 68 % (avg)
Available Memory : 941 Mo


-SQL Server
--SQL Server 2012 SP1 / Enterprise Edition: Core-based Licensing (64-bit)

--Configuration Instance :
min server memory (MB) 500 500
max server memory (MB) 4000 4000

--Memory_usedby_Sqlserver_MB : 2756
--Reporting services MB : 977


My question :
I can see that there is only 941 Mo available so I can deduct that windows use pagefile because there is no enough memory (WIndows keep a little of RAM). Right ??
Why there is no more memory now because there is enough memory last week ??

- Is it necessary to reboot in order to release memory (RAM) ?

Thank you

Anthony

sgmunson
sgmunson
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89709 Visits: 6914
There really isn't enough information here to draw any conclusions. You appear to have a 6 Gb virtual machine, with only 4 GB allocated to SQL Server, which for most business workloads, would likely be highly inadequate. However, as you haven't said what kind of workload that SQL instance is supposed to be able to handle, we have no idea whether that quantity is inadequate or not. Mind you, it would have to be really small to fit in just 4 Gb. You also appear to be saying that Windows is suddenly using the paging file, and I have to guess that means that your workload is slowing down. As you didn't provide any quantification of just how much slow down you're seeing, or exactly why the use of the paging file is a problem, I don't know what to suggest. However, seeing that your last re-boot was 272 days ago, it would probably be a good idea to re-boot the machine anyway, and see if the problem remains. If that fails to resolve the issue, then find out what changed in the application, or in the database, that might have started the slowdown. Either way, if the problem continues, you'll need to provide a LOT more detail.

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
antoninSQL
antoninSQL
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 78
Thank you for your reply.

Sorry, this is a test server

I do not know if there is a slowdown for the database because I do not know how to measure the workload.

I'm just wondering why windows uses the swap file now when it was not the case before?

I'm going to restart the server and I'll let you know

thank you

anthony
antoninSQL
antoninSQL
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 78
sgmunson - Wednesday, February 28, 2018 9:05 AM
There really isn't enough information here to draw any conclusions. You appear to have a 6 Gb virtual machine, with only 4 GB allocated to SQL Server, which for most business workloads, would likely be highly inadequate. However, as you haven't said what kind of workload that SQL instance is supposed to be able to handle, we have no idea whether that quantity is inadequate or not. Mind you, it would have to be really small to fit in just 4 Gb. You also appear to be saying that Windows is suddenly using the paging file, and I have to guess that means that your workload is slowing down. As you didn't provide any quantification of just how much slow down you're seeing, or exactly why the use of the paging file is a problem, I don't know what to suggest. However, seeing that your last re-boot was 272 days ago, it would probably be a good idea to re-boot the machine anyway, and see if the problem remains. If that fails to resolve the issue, then find out what changed in the application, or in the database, that might have started the slowdown. Either way, if the problem continues, you'll need to provide a LOT more detail.


Hi,

Thank you for your reply

I have reboot my server.

Memory see on task manager :
--Sqlserver.exe : 135 MB now (2756 before restart)
--Reporting services MB : 73 MB (977 before restart)

Why SQL server.exe and reporting services use now less than before restart.

Thank you
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)SSC Guru (156K reputation)

Group: General Forum Members
Points: 156726 Visits: 23025
Don't trust the task manager, it's a blatant liar!
Cool

What is the output of this code?

SELECT
OSM.total_page_file_kb
,OSM.available_page_file_kb
,OSM.total_page_file_kb
,OSM.available_page_file_kb
,OSM.system_cache_kb
,OSM.kernel_paged_pool_kb
,OSM.kernel_nonpaged_pool_kb
,OSM.system_memory_state_desc
FROM sys.dm_os_sys_memory OSM;

SELECT
ISNULL(DB_NAME(OBD.database_id),'ResourceDB') AS DBNAME
,OBD.page_type AS page_type
,SUM(OBD.row_count) AS SUM_row_count
,SUM(OBD.free_space_in_bytes) AS SUM_free_space_in_bytes
,SUM(OBD.read_microsec) AS SUM_read_microsec
FROM sys.dm_os_buffer_descriptors OBD
GROUP BY DB_NAME(OBD.database_id)
,OBD.page_type
ORDER BY DB_NAME(OBD.database_id) ASC
,OBD.page_type ASC;


John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)

Group: General Forum Members
Points: 130759 Visits: 19090
antoninSQL - Wednesday, February 28, 2018 10:58 PM
Memory see on task manager :
--Sqlserver.exe : 135 MB now (2756 before restart)
--Reporting services MB : 73 MB (977 before restart)

Why SQL server.exe and reporting services use now less than before restart.

Regardless of how accurate those figures are, SQL Server will only grab the memory it needs to start. It'll take more and more as and when it needs it (for example to cache data or plans) until it reaches either the max server memory or the available memory on the computer. It'll give back memory when requested by the operating system, subject to the value of min server memory. That's a basic overview - there may be circumstances under which it will grab more memory than expected, especially in older versions. I'm not sure whether or not Reporting Services behaves in the same way.

John

antoninSQL
antoninSQL
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 78
Eirikur Eiriksson - Thursday, March 1, 2018 1:32 AM
Don't trust the task manager, it's a blatant liar!
Cool

What is the output of this code?

SELECT
OSM.total_page_file_kb
,OSM.available_page_file_kb
,OSM.total_page_file_kb
,OSM.available_page_file_kb
,OSM.system_cache_kb
,OSM.kernel_paged_pool_kb
,OSM.kernel_nonpaged_pool_kb
,OSM.system_memory_state_desc
FROM sys.dm_os_sys_memory OSM;

SELECT
ISNULL(DB_NAME(OBD.database_id),'ResourceDB') AS DBNAME
,OBD.page_type AS page_type
,SUM(OBD.row_count) AS SUM_row_count
,SUM(OBD.free_space_in_bytes) AS SUM_free_space_in_bytes
,SUM(OBD.read_microsec) AS SUM_read_microsec
FROM sys.dm_os_buffer_descriptors OBD
GROUP BY DB_NAME(OBD.database_id)
,OBD.page_type
ORDER BY DB_NAME(OBD.database_id) ASC
,OBD.page_type ASC;



Hi

This is output after reboot
---------------
total_page_file_kb
7339512
available_page_file_kb
5600084
total_page_file_kb
7339512
available_page_file_kb
5600084
system_cache_kb
1293788
kernel_paged_pool_kb
170576
kernel_nonpaged_pool_kb
55228
system_memory_state_desc
Available physical memory is high

-----

Thank you
antoninSQL
antoninSQL
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 78
John Mitchell-245523 - Thursday, March 1, 2018 1:54 AM
antoninSQL - Wednesday, February 28, 2018 10:58 PM
Memory see on task manager :
--Sqlserver.exe : 135 MB now (2756 before restart)
--Reporting services MB : 73 MB (977 before restart)

Why SQL server.exe and reporting services use now less than before restart.

Regardless of how accurate those figures are, SQL Server will only grab the memory it needs to start. It'll take more and more as and when it needs it (for example to cache data or plans) until it reaches either the max server memory or the available memory on the computer. It'll give back memory when requested by the operating system, subject to the value of min server memory. That's a basic overview - there may be circumstances under which it will grab more memory than expected, especially in older versions. I'm not sure whether or not Reporting Services behaves in the same way.

John


Thank you for this information John

anthony
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search