October 15, 2015 at 9:04 am
Hello,
We have windows 2012 server with 3TB of ram, where SQL server 2014 instance is hosted. curretnly Virtual memory is set to 60GB...we have an environment where we see virtual memory spikes whenever some sql jobs are running...what are best practices for setting virtual memory and based on our situation...is 60GB ok for virtual memory?
Appreciate your valuable inputs.
October 15, 2015 at 9:15 am
What exactly do you mean 'virtual memory is set to 60GB'?
And what exactly are you looking at when you say a 'virtual memory spike'?
And 3 Terabytes of RAM????
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
October 15, 2015 at 9:18 am
Yes...Virtual memory is currently set to 60 GB on windows server.....n Yes, server has 3TB ram..this is datawarehouse env where lot of cube jobs run on daily basis and that's y we needed 3TB RAM
October 15, 2015 at 12:10 pm
Virtual memory set to 60GB where? What, exactly do you mean (because virtual memory is fixed on a windows machine based on whether it's 32 or 64 bit, it's not custom set). And what do mean by "virtual memory spikes"?
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
October 15, 2015 at 1:46 pm
Hi Gail,
am sorry that I made it look confusing...Yes, you are right...am talking about virtual memory setting on windows machine which is 64bit and I think we can set it with custom setting as well. Right now, it's the default value which is 60 GB.(Can you also tell how this virtual memory setting is calculated or set ?)
n Spikes in virtual memory...we are seeing it in our monitoring tool, when the cube jobs run on sql instance.
October 16, 2015 at 2:32 am
Isn't the rule of thumb for a Virtual Memory/Page file to be set to 1.5 x amount RAM?
October 16, 2015 at 2:44 am
Justin Manning SA (10/16/2015)
Isn't the rule of thumb for a Virtual Memory/Page file to be set to 1.5 x amount RAM?
It was, about 10 years ago....
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
October 16, 2015 at 2:47 am
Agnii (10/15/2015)
Yes, you are right...am talking about virtual memory setting on windows machine which is 64bit and I think we can set it with custom setting as well. Right now, it's the default value which is 60 GB.(Can you also tell how this virtual memory setting is calculated or set ?)
You're still not being clear. In Windows one does NOT set the amount of virtual memory, as virtual memory size is a property of the processor architecture.
Are you talking about page file size?
Are you talking about SQL's max server memory?
Are you talking about something else?
n Spikes in virtual memory...we are seeing it in our monitoring tool, when the cube jobs run on sql instance.
I can't see your screen or your monitoring tool. I can't help if you cannot be specific about exactly what is being shown.
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
October 16, 2015 at 3:02 am
GilaMonster (10/16/2015)
Justin Manning SA (10/16/2015)
Isn't the rule of thumb for a Virtual Memory/Page file to be set to 1.5 x amount RAM?It was, about 10 years ago....
Oh? But is it still relevant?
October 16, 2015 at 3:10 am
Justin Manning SA (10/16/2015)
GilaMonster (10/16/2015)
Justin Manning SA (10/16/2015)
Isn't the rule of thumb for a Virtual Memory/Page file to be set to 1.5 x amount RAM?It was, about 10 years ago....
Oh? But is it still relevant?
For the OP, that would be a 4.5TB page file.....
It hasn't been relevant since Windows started doing minidumps or kernal dumps on fatal errors instead of a full memory dump. I think that was server 2003 where that change came and XP I think on the client side.
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
October 16, 2015 at 3:24 am
GilaMonster (10/16/2015)
Justin Manning SA (10/16/2015)
GilaMonster (10/16/2015)
Justin Manning SA (10/16/2015)
Isn't the rule of thumb for a Virtual Memory/Page file to be set to 1.5 x amount RAM?It was, about 10 years ago....
Oh? But is it still relevant?
For the OP, that would be a 4.5TB page file.....
It hasn't been relevant since Windows started doing minidumps or kernal dumps on fatal errors instead of a full memory dump. I think that was server 2003 where that change came and XP I think on the client side.
Fair enough.. but surely a 60GB file is too small?
Thank you for the new info. Will go and read up a little on the subject
October 16, 2015 at 3:35 am
Justin Manning SA (10/16/2015)
Fair enough.. but surely a 60GB file is too small?
Assuming that the OP is talking about the page file, probably not, unless the server's crashing often and the Windows minidumps are needed for debugging purposes.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply