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

Question

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

gatorx

SSC Veteran

Points: 222
More actions
December 27, 2017 at 11:39 am

#334177

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?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

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 SSC Veteran Points: 222 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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 SSC Veteran Points: 222 More actions · Answer 4

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 :).

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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 :).

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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 :).

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 SSC Veteran Points: 222 More actions · Answer 7

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 SSC Veteran Points: 222 More actions · Answer 8

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 :).
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 SSC Guru Points: 442467 More actions · Answer 9

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 :).
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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

gatorx - Thursday, December 28, 2017 11:42 AM
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?

Good question. That's why I asked about perfmon, to identify what's being paged out, because if you're not seeing messages in the log, then it's not going to be SQL being paged out.

Is this a VM?

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

shahgols SSCrazy Eights Points: 9874 More actions · Answer 11

shahgols

SSCrazy Eights

Points: 9874

December 29, 2017 at 8:25 am

#1973833

What's the PLE value when the paging is occurring?