April 24, 2009 at 1:29 pm
New server, currently 8GB of RAM, AWE enabled, 6GB allocated for SQL, 2GB for OS. I swear, over the years I thought I found some guidelines on how to size the pagefile based on how much RAM is allocated. My pagefile is currently 2GB. Should I increase the size, to say 4GB. The server is currently being built for production and I'd like to tune as much as I can before we go live. SQL is the only app running on the server. Any advice is appreciated. Thanks.
-- You can't be late until you show up.
April 24, 2009 at 1:39 pm
The pagefile really shouldn't matter - since you really don't want SQL Server to be paging out to it anyways.
I would leave it alone.
What edition are you installing? Is it x86 or x64? If x64 and Standard edition you might have some problems with that much memory being allocated to SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 24, 2009 at 2:12 pm
OS is windows 2003 EE, SQL is x86 SE, awe-enabled. So leaving the pagefile at 2GB appears to be fine? Our network guys want to push the pagefile to 8GB, to match the RAM, but I told them not to until I got some advice as I've never changed the default sizes on any SQL2000 servers before. I don't want to go into production and immediately begin having issues. Any other recommendations you can think of?
-- You can't be late until you show up.
April 24, 2009 at 2:43 pm
They can put the page file at whatever size they want. SQL Server shouldn't be using it - and if you are you have bigger problems.
If you are migrating databases, remember to perform you post upgrade steps.
DBCC CHECKDB(database) WITH DATA_PURITY; --
Rebuild indexes
Update statistics
etc...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 24, 2009 at 3:03 pm
If I rebuild indexes, doesn't that also update my statisics like SQL 2000? Did not know about WITH DATA_PURITY but just googled it to see what it does. One other question now, whenever I run DBCC CheckDB on SQL2005, should my scheduled maintenace jobs always run with "WITH DATA_PURITY" (I'm guessing it is) or is this more of a one time thing due to migration?
-- You can't be late until you show up.
April 25, 2009 at 8:19 am
Jeffrey Williams (4/24/2009)
What edition are you installing? Is it x86 or x64? If x64 and Standard edition you might have some problems with that much memory being allocated to SQL Server.
What problems??
April 25, 2009 at 9:07 am
SQL Server 2005 x64 Standard will show memory pressure issues. I am not sure where the cutoff is for it, but I have seen some installations with 8GB of memory have to set max memory at 4GB and others with 16GB can't set more than 12GB.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 27, 2009 at 10:17 am
Page file size is matter in 64 bit of SQL 2005. SQL may not use it but require the size is at least 1.5 times of RAM size. Otherwise, SQL Server will show memory pressue. Please see the KB article
http://support.microsoft.com/default.aspx/kb/889654/
Thanks,
William
April 27, 2009 at 10:36 am
liuwilli (4/27/2009)
Page file size is matter in 64 bit of SQL 2005. SQL may not use it but require the size is at least 1.5 times of RAM size. Otherwise, SQL Server will show memory pressue. Please see the KB articlehttp://support.microsoft.com/default.aspx/kb/889654/
Thanks,
William
William - first, this article is a good article for determining page file size for servers that are running services that will need to access the page file.
Second, the recommendation in this article is that for a server running SQL Server - the page file size should be 1% larger than the available memory, and shows that a system with 32GB of memory should have a page file size of 32.32GB.
And finally, I disagree with this recommendation - as I have found that if SQL Server is using the page file, and using that much - you have many more problems than memory pressure in SQL Server.
If you are having significant issues on the server and need to be able to capture a crash dump, then yes - you need to set the page file large enough to capture everything in memory. But, normal operations shouldn't use the page file at all - and not setting will not (by itself) cause memory pressure in SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 27, 2009 at 11:01 am
Jeffery,
Right, SQL Server shouldn't use page file at all. But I have seen in my 64 bit environment, SQL Server not release memory back to system even it is not using it. I forgot where I saw an article said that is because page file size is too small. This may not impact the performance of SQL Server itself, but it will impact other applications or components (MSDTC etc.) in that box.
Thanks,
William
April 27, 2009 at 11:09 am
William,
Yes - if you have other applications running on the server - you need to size the page file according to the requirements for those applications. I have never seen MSDTC need 32GB of page file, but then again - I have not run a system that uses lots of distributed transactions.
SQL Server will not release memory unless it has been configured to do so. If you followed best practice, you set the lock pages in memory option and set the max memory for SQL Server. Basically, you just told SQL Server that once it reaches max memory, keep it and *never* release it for any reason.
That is one of the reasons why the recommendation is to make sure you set the max memory to a reasonable size on x64 platforms. The general guidelines have you allocating no more than 28GB of memory for a system with 32GB (and, sometimes a lot less).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply