Microsoft SQL Server
--
Questions
--
Followers
Top Experts
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?
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%.Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
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?
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.
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.
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.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
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.
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.
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.
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.
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.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
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
When I did that I got an error
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
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
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' "
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
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
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
SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
Microsoft SQL Server
--
Questions
--
Followers
Top Experts
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.