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

LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
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?
Scott PletcherSenior DBACommented:
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.
mlcktmguyAuthor Commented:
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.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Scott PletcherSenior DBACommented:
Interesting.  Looks like the box has enough RAM.  What is the 'max memory' setting?

EXEC sp_configure 'max server memory'
arnoldCommented:
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.
mlcktmguyAuthor Commented:
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.
Memoery Usage
arnoldCommented:
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.
Scott PletcherSenior DBACommented:
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.
mlcktmguyAuthor Commented:
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.
arnoldCommented:
usually, you need to turn on advanced options,

The difficulty is that your setup is a developer mode, not test for production mode. dealing with a setup that is "equivalent" to the production in terms of setup hardware/configuration.

Use explain on the query you run and post it here.
the swaping might be a consequence that your DB structure, configuration would greatly benefit from additional indexes, etc. that will speed up the processing without the need of complete table scans which might be what triggers the swapping. The behavior of SQL on a workstation/desktop versus on a server where the handler is designed differently....
your other application would get resource allocation where on a server those same user applications might not be as preferred.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
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' "
arnoldCommented:
yes,
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure
 See https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-configure-transact-sql

exec sp_configure 'max server memory', the_max_value

Commonly, as your image points, SQL is permitted to take as much space your system has, the 2TB is what the maximum number reflects in the image.

The swapping notice deals with what you have and what tests you are running requires the SQL server to move large amounts of data out to respond to your other queries, optimizing the SQL server queries, as well as using the tunning advisor to see whether you should add index, statistics to help the execution plan for the queries....
mlcktmguyAuthor Commented:
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
Config01Congig02
arnoldCommented:
IMHO, the settings are default and straight forward. Your existing setup deals with contention within the SQL not something to be cured externally.
i.e. when I drive there are times the car jars.
Putting forth the make model of my car, etc. and even a region where I drive, will not provide a reader a way to diagnose the issue, i.e. the car jars when I hit a pot-hole.

in your case, you are developing something and running queries against the DB. Based on the reported errors, it seems the sequence of queries require a large amount of memory so from query1 to query2 all the results from query1 have to be swapped out to disk, to make room for query2
i.e. following each execution of a query,

https://docs.microsoft.com/en-us/sql/tools/overview-sql-tools
It is a Graphical tool part of the SSMS tools. There should be a profiler that you would capture a workload. Then the tunning advisor uses the captured data to analyze and provide suggestions.

The report for the SPIds that live about 3 hours, loadinto memory 680MB of data while the resulting data sets are a small fraction, 12-15%.

run explain select somefield from yourtable.
See whether it scans the entire record set, or it relies on an index to provide a response.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.