Link to home
Start Free TrialLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

asked on

SQL Server Memory Has Been Paged Out Message

I am fairly new to SQL Server and I'm looking at the log of one of my process that ended in an error.

I see the message about '"A significant part of sql server process memory has been paged out.  This may result in a performance degradation. "

What does this message mean and what can I do to address the situation?


2017-12-24 00:00:17.32 spid27s     This instance of SQL Server has been using a process ID of 2780 since 12/23/2017 9:32:38 AM (local) 12/23/2017 2:32:38 PM (UTC). This is an informational message only; no user action is required.
2017-12-24 05:49:51.26 spid17s     A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 9684, committed (KB): 3315260, memory utilization: 0%.
2017-12-24 09:10:43.57 spid17s     A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 12049 seconds. Working set (KB): 16040, committed (KB): 694180, memory utilization: 2%.
2017-12-24 09:10:48.61 spid17s     A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 12054 seconds. Working set (KB): 106292, committed (KB): 686652, memory utilization: 15%.
2017-12-24 09:11:49.25 spid17s     A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 12115 seconds. Working set (KB): 110876, committed (KB): 686716, memory utilization: 16%.
2017-12-24 09:11:54.29 spid17s     A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 12120 seconds. Working set (KB): 111336, committed (KB): 686676, memory utilization: 16%.
2017-12-24 09:12:54.94 spid17s     A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 12181 seconds. Working set (KB): 115060, committed (KB): 686804, memory utilization: 16%.
2017-12-24 09:12:59.98 spid17s     A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 12186 seconds. Working set (KB): 115356, committed (KB): 686740, memory utilization: 16%.
2017-12-24 09:14:00.64 spid17s     A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 12246 seconds. Working set (KB): 118980, committed (KB): 686788, memory utilization: 17%.
2017-12-24 09:14:05.67 spid17s     A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 12251 seconds. Working set (KB): 118892, committed (KB): 686564, memory utilization: 17%.

Open in new window

Avatar of arnold
arnold
Flag of United States of America image

Make sure the server is configured to prioritize processor and memory for programs(application server) versus for background ...

This is under advanced system settings on the OS configuration.
How much ram available on the system and how much is available to the SQL server?
Those messages indicate that SQL Server needs more RAM.  If the version of SQL you are using supports more RAM, you should add as much as you can afford to add that SQL can actually use.
Avatar of mlcktmguy

ASKER

Thanks for the information.  I now realize I should have included more details in my original post.

This issue is occurring on a stand alone Win 7 Pro machine with 32GB RAM and a i7 quad core 3.2 chip.  

The SQL version is Developer 2014 and SQL is being executed from an  Access 2013 front end.

I do not know how to configure my machine to allocate more ram and possibly processing power to SQL.  If you could explain how to do this I would appreciate it.

Since I am the only user on the machine I could allocate memory and processing power as needed.
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
if you are using ssms, after you connect to the sql server, right click on the top line, for the database server info, under options, you can set parameters for the CPU boost as well as min/max memory use. your issue is this is a workstation, the system will prioritize memory/cpu for applications you are using and potentially deprive as the alert pointed out SQL of memory.  Since this is a development platform, the notices as indicated are informational only.
You do not want to see these notices when in production on a server.
I checked into the parameters suggested and they look OK to me.  However I have a SQl routine running now to re-index every table that has been running for nearly four hours.  I checked the task manager and see that the system memory usage is a flat 6GB while the CPU is being driven a bit harder.  The only other foreground process running is Outlook to check emails.  

I also checked the boxes to 'Automatically set processor affinity and I/o Affinity mask for all processors, whatever that means.

Is there something I can do or should have done in the SQL config to get better utilization out of my computer?  Here's the graphic of my memory config in SQL underneath the task manager display.
User generated image
Not clear what you are asking.

Your situation is
The SQL server data is on a single drive used and accessed by the OS, Page file, and SQL transaction.
You are in a desktop environment with 16GB of ram.

Affinity/cpu boost lets you prioritize Processor and resource at a higher level then average (above average) but be careful with these selections as depending on what you are running on the sql side might render for a time your system unusable when the system/os dedicates as directed (affinity) many resources to the SQL server process.

use SQL tunning advisor.....

Your sql server database might benefit from additional index, stats etc. to improve performance.
I checked into the parameters suggested and they look OK to me.

OK.  If you prefer not to provide the results here, I can't begin to guess what they are, so I can't help any further.  Good luck.
Scott: what else would you like to see?  I included the processor stats and the memory configuration page in the above graphic.

You suggested entering
EXEC sp_configure 'max server memory'

When I did that I got an error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option 'max server memory' does not exist, or it may be an advanced option.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry took some time off over the holidays.  I appreciate the feedback but I don't know how to implement your suggestions.
usually, you need to turn on advanced options,
How do I do this and what am I looking for when I do?  Will this allow me to run "EXEC sp_configure 'max server memory' "
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks you Arnold.  I would be very interested in running SQL running the SQL Tuning Advisor.  How do I run it?

With you instructions I was able to list the configuration parameters of my SQL.  Here they are, let me know if you see anything that needs adjusted
User generated imageUser generated image
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial