July 21, 2020 at 3:04 pm
I friend at another company said they got an out of memory error on their Production SQL server. It has 24 Gig of RAM and a 4 Gig page file. Their hosting company said they can increase the page file to a set value, or let Windows manage it. If Windows manages it, is there a risk Windows will let it balloon to a huge size ?
I am surprised they ran out of memory, since I don't think it's running huge jobs. Mainly a low traffic website.
July 21, 2020 at 3:32 pm
I haven't worried about this too much. Typically this is limited in Windows, unless you are having crash dumps, in which case it can grow large. This pages notes that it tends to be volume/8 in size.
https://docs.microsoft.com/en-us/windows/client-management/determine-appropriate-page-file-size
July 21, 2020 at 5:38 pm
...Production SQL server. It has 24 Gig of RAM and a 4 Gig page file. Their hosting company said they can increase the page file to a set value, or let Windows manage it...
If SQL Server is properly configured, you shouldn't be hitting the OS page file enough for it to really mater. If Windows sends a page of data to the OS page file that SQL Server thinks is in memory, you're adding extra disk write and read time. SQL Server manages its own memory rather well, so here's some questions to ask your friend:
What do you have Max Server Memory set to in SQL Sever configuration? Is there enough memory for the OS above what threshold you've set?
Are there any other programs besides the database engine using this same server? (SSIS, SSRS, and SSAS count as other programs)
How often is the OS swapping to its page file?
What are the waits that SQL Server is experiencing?
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
July 21, 2020 at 6:23 pm
Where would I find this ?
"...How often is the OS swapping to its page file?..."
July 21, 2020 at 8:13 pm
the easiest way to see how often OS is paging is looking at "Pages Input / sec" counter in the "Memory" section of Performance Monitor (perfmon)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply