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


350GB virtual memory usage only 163MB RAM SQL is limited to 32GB memory.


350GB virtual memory usage only 163MB RAM SQL is limited to 32GB memory.

Author
Message
gatorx
gatorx
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 37
I have a SQL server that I have limited the amount of memory to 32GB with the only database being 700GB. I start the SQL Service, and the page file grows exponentially, resource monitor shows it is all SQL is using the memory (confirmed by stopping SQL page file size significantly decreases). The problem is the page file keeps filling up locking up the server. How do I limit the amount of page file SQL uses? Has anyone else experienced this?

Joe Torre
Joe Torre
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6188 Visits: 1141
Make the max memory setting low enough to allow other required system operations. I'd recommend at least 4GB reserved depending on what services are running other that SQL Server.
gatorx
gatorx
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 37
I have 70GB of physical memory in the machine, which is not being used much. However it is filling up the page file on the physical disk.

GilaMonster
GilaMonster
SSC Guru
SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859488 Visits: 48575
SQL doesn't use the page file. If it ever gets paged out, something has gone *seriously* wrong (and there will be messages in the error log stating that it's been paged out)

If the page file is too large (and you've changed the defaults for its size), then look at other stuff on the server than SQL. It's not going to be the SQL Server doing what you describe.
p.s. Don't use Task Manager to check SQL's memory usage. It lies.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


gatorx
gatorx
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 37
I agree something is wrong, but it is SQL that is paging out, I start the SQL Server service and the page file grows exponentially and essentially crashes the OS, I stop the SQL service and everything goes back to normal and the page file drops to 70GB. So SQL is paging, I am just not sure how or why, at this point all ideas are welcome Smile.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)

Group: General Forum Members
Points: 365916 Visits: 41902
gatorx - Thursday, December 28, 2017 11:18 AM
I agree something is wrong, but it is SQL that is paging out, I start the SQL Server service and the page file grows exponentially and essentially crashes the OS, I stop the SQL service and everything goes back to normal and the page file drops to 70GB. So SQL is paging, I am just not sure how or why, at this point all ideas are welcome Smile.


Are you seeing this in the error logs?
Gail did say you would see messages to this affect if SQL were paging.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)SSC Guru (859K reputation)

Group: General Forum Members
Points: 859488 Visits: 48575
gatorx - Thursday, December 28, 2017 11:18 AM
I agree something is wrong, but it is SQL that is paging out, I start the SQL Server service and the page file grows exponentially and essentially crashes the OS, I stop the SQL service and everything goes back to normal and the page file drops to 70GB. So SQL is paging, I am just not sure how or why, at this point all ideas are welcome Smile.


The thing is that SQL does not use the page file itself. The only way it can affect the page file is if the OS forces SQL's memory out to disk. If it does, there will be error messages in the log stating so. Are there?
If not, then what you may be seeing is a response to the SQL service. It uses memory and, as a result, the OS writes something else into the page file.

Have you checked the perfmon Process counters to see what processes are using the page file?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


gatorx
gatorx
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 37
I am not seeing any SQL errors about paging, I am seeing out of memory errors from SQL and other windows components about being out of memory, which I assume is because the page file is full. I have been working with SQL for more than 15 years and never experienced this behavior.
gatorx
gatorx
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 37
GilaMonster - Thursday, December 28, 2017 11:38 AM
gatorx - Thursday, December 28, 2017 11:18 AM
I agree something is wrong, but it is SQL that is paging out, I start the SQL Server service and the page file grows exponentially and essentially crashes the OS, I stop the SQL service and everything goes back to normal and the page file drops to 70GB. So SQL is paging, I am just not sure how or why, at this point all ideas are welcome Smile.


The thing is that SQL does not use the page file itself. The only way it can affect the page file is if the OS forces SQL's memory out to disk. If it does, there will be error messages in the log stating so. Are there?
If not, then what you may be seeing is a response to the SQL service. It uses memory and, as a result, the OS writes something else into the page file.

What in the OS could cause it to page SQL, by the way this is SQL 2012 on Server 2012, when there is 60GB of free RAM?

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)SSC Guru (365K reputation)

Group: General Forum Members
Points: 365916 Visits: 41902
gatorx - Thursday, December 28, 2017 11:42 AM
GilaMonster - Thursday, December 28, 2017 11:38 AM
gatorx - Thursday, December 28, 2017 11:18 AM
I agree something is wrong, but it is SQL that is paging out, I start the SQL Server service and the page file grows exponentially and essentially crashes the OS, I stop the SQL service and everything goes back to normal and the page file drops to 70GB. So SQL is paging, I am just not sure how or why, at this point all ideas are welcome Smile.


The thing is that SQL does not use the page file itself. The only way it can affect the page file is if the OS forces SQL's memory out to disk. If it does, there will be error messages in the log stating so. Are there?
If not, then what you may be seeing is a response to the SQL service. It uses memory and, as a result, the OS writes something else into the page file.

What in the OS could cause it to page SQL, by the way this is SQL 2012 on Server 2012, when there is 60GB of free RAM?


What are your min and max memory setting for SQL Server?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
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