December 11, 2008 at 6:50 pm
Hi,
we are getting free space in virtual memory is less.To resove this We are thinking of increase the page file size to 1.5 times the Physical memory.
We have active\active\passive cluster setup with sql server 2005.I have couple of questions.
1.Can we put this page file C drive?
2.Can we put in any clustered drive?
3.Do we need separate drive to put the page file?
4If we put page file in separate drive, it need to be make as clustered drive or can we keep as normal drive?
5.We have 3 instances on one of the nodes,So do we need to create the pagefile for each instance or only server level?
and finally increasing the page file to 1.5 times physical memory is a good idea or not?
Thanks for your suggestions
December 11, 2008 at 8:31 pm
Good questions:
1.You can can certainly put the pagefile on the system drive, however this is not best practice, you should put it on another permanent drive on the server. It is best to separate the system files and the pagefile.
2.Don't put the pagefile on a clustered drive, if that drive fails over to another system you are going to have a bluescreen on that node, impacting other instance on that node.
3.A separate drive is recommended, one that persists on the system.
4.Do not cluster it, keep it local.
5.One pagefile for each physical node. The pagefile is related to the physical hardware and OS, not to the SQL Server instance.
Increasing the pagefile to 1.5 x physical memory is good practice, also set the minimum and maximum size of the pagefile to be the same, this helps keep it as contiguous as possible, and helps prevent impact as the file grows.
December 11, 2008 at 11:23 pm
Thank You.....
December 12, 2008 at 5:23 am
Just a follow up from another topic, here is a link to a Microsoft KB article that will help to accurately size your pagefile, rather than use a generic value.
December 13, 2008 at 5:07 am
madhu.arda (12/11/2008)
getting free space in virtual memory is less.
it would be more important to try and ascertain what is causing the paging
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 13, 2008 at 11:42 pm
hi,
I followed the method2 to calculate the pageing file size, which says,Note the maximum value for the Page File Bytes Peak counter, and then multiply the value by 0.70. The sum of the equation is the size to set for your page file
From the system monitor I got the max value for the Page File Bytes Peak as 1.7276e+010.
could you plz tell me to calculate this into GB.
December 14, 2008 at 4:58 am
madhu.arda (12/13/2008)
From the system monitor I got the max value for the Page File Bytes Peak as 1.7276e+010.could you plz tell me to calculate this into GB.
i havent done this i left school but here goes
the value is in bytes so move the decimal place 10 places to the right gives
17276000000
then divide by 1073741824 to get GB's
result = 16GB's
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 14, 2008 at 8:45 am
Perry Whittle (12/14/2008)
madhu.arda (12/13/2008)
From the system monitor I got the max value for the Page File Bytes Peak as 1.7276e+010.could you plz tell me to calculate this into GB.
i havent done this i left school but here goes
the value is in bytes so move the decimal place 10 places to the right gives
17276000000
then divide by 1073741824 to get GB's
result = 16GB's
Kudos Perry, much kudos. 😎
December 14, 2008 at 12:24 pm
Nicholas Cain (12/14/2008)
Kudos Perry, much kudos. 😎
thanks m8tey, at least i learnt something at school all those years ago.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 15, 2008 at 8:07 am
Personally, I've always tried to avoid situations where there is alot of virtual memory page swaps for servers where SQL Server was running on them. This is really a hidden way to drain performance of SQL Server, as you're creating 2 extra disk I/O operations every time the OS swaps virtual memory. Once to write that data to the disk, then another to read it back in later.
This will work against SQL Server's own memory management, which has its own least recently used algorithm, and knows what pages are already saved to disk vs. dirty pages to more intelligently determine when it can merely overwrite pages in the buffer cache with other data that is being requested. Your buffer cache hit ratio will also be skewed because SQL Server will be thinking it's getting data from memory, when actually an indeterminate amount of those logical reads will be physical reads hitting the OS page file. Considering DBA's try to tune SQL Server cache to limmit the amount of disk activity, why would you create a situation that guarantees more disk activity?
December 15, 2008 at 11:53 am
you mean to say that do not set the page file size to 1.5 times the physical memory or do not use separate drive to keep the page file?
December 15, 2008 at 12:44 pm
What I'm saying is that it could be a concern if there are a lot of virtual memory swaps occurring. Making a larger virtual memory swap file will not improve SQL Server performance, and could harm performance if too many pages are getting swaped to disk by the operating system.
What do you have the "Maximum Server Memory" setting at for SQL Server? Compare this value to your physical RAM on the computer. Most people set this so that SQL Server will leave 1-2 Gig of RAM for the OS, or maybe even more if there are other things running on this computer besides SQL Server.
Also, is your operating system and SQL Server 64 bit?
December 18, 2008 at 6:45 pm
Thanks Chris,
We have the default settings for SQL Server memory.
Min: 0
Max: 2147483647MB
We have windows 2003 R2 EE x64 with SP2 and SQL Server 2005 EE x64 with SP2
December 18, 2008 at 11:42 pm
yes if u will change the disk for this ..there will be an impact on ur performance... bcoz we hav did that to 1 of our server and the performance went down......
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply