October 3, 2009 at 3:17 pm
Has it ever happened that PAGEFILE.SYS was a cause for poor performance on SQL Server? What factors can make this a problem for good performance in the SS Box? Any other thoughts in relation, to this, from a DBA's perspective is what I am looking for.
Dan
October 4, 2009 at 6:38 am
if your server is paging a lot and pagefile is placed on the same drive as system or user databases it can be a real nasty configuration ;/
October 4, 2009 at 5:52 pm
ok. Thanks.
How can I check that SQL Server is not paging regularly, and if it is tuned /configured that that paging is not possible. where are these settings.
October 4, 2009 at 7:56 pm
You can often minimize it by making sure that you set aside enough memory for the OS. This all depends on what else you have running and how much memory you have on the box. Without anything else but SQL you would need to hold out at LEAST 512MB of RAM, but numbers like 768MB and 1GB seem to fit.
Anytime it spends a lot of time paging to disk it will affect performance whether it is on a drive with a database file or not..
CEWII
October 5, 2009 at 2:37 pm
If you are having paging at the OS level you are in trouble NOW.
Memory is so cheap that there is absolutely no reason to have this type of issue anymore. Oh, that also means if you have 32 Gb or RAM you DO NOT need a 32 Gb pagefile. The 'Standard' maximum of 4 Gb is plenty.
I state this because if your are 'paging' with 32 Gb of RAM you are definitely in trouble again !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 6, 2009 at 6:16 am
What kind of activity can cause GB's of paging?
October 6, 2009 at 6:53 am
hard to tell what is causing it....we need more info
1.whats the DB size
2. how much Ram
3. slq server setting min and max --- this is so that the OS has enough memory to operate, other wise SQL will use it all.
4. if you do not have enough RAM you could see GB of paging.
5. You Temp is used a lot in 2005 and if you do not have enough ram then the TEMP DB is going to use page file.
6. also check page life expactancy on the perf mon...this will give you a good indication of how long memory is staying in cache. if this number is below 5000 then you could have a memory shortage...
7. also you do need to set you page file to 1.5 time the amount of RAM, this is needed in case SQL crashes
(this is not for sql to operate on), it will do a complete memory dump. Microsoft will need this in case of a crash, they can comb through the wreckage...and this does happen from time to time.... Brent Ozar has a page on this http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/..there are many others out there as well
October 14, 2009 at 9:11 am
465789psw
Thanks for your input.
The DB is 210GB; Ram 16GB; SQL server Memory min and max (0 and 8GB); TempDB is 24GB right now; Page life expectancy is 600.
Brent has a comment "watch the utilization carefully over time and scale the page file size down as necessary". Can you please help me understand it better.
In one of the servers, I am not able to find the pagefile.sys (I searched for it in all the drives)- what am i missing?
Thanks
October 14, 2009 at 9:31 am
so good news is your pages are staying cache for 600 seconds or 10 min. I would bump th memory up to 10-12 gig from the current 8 although if 600 is the average over the long run then your 8 seems to be working ok. But just to be safe it would not hurt to add the extra. I have 120 gig on production and run 32 gig and set it 25....my page life is really high...which fine by me...better to be safe then sorry and the price of ram is so cheap. If you are on 32 bit then you will need to make some changes, check the web, if you need are then 64 then you are good to go.
The page file can be checked by going to the my computer then right click on propertied then advanced
performance setting.
In theory sql is not suppose to use the page file...he is telling you to scale it back so that you dont have a bunch memory sitting doing nothing...which might be a good idea on a server that is not important... if crashed you spin it back up and away you go....bu on a Production environment set it to 1.5 RAM so in your case 24 gig. this is needed according to Microsoft, so if the server crashes it will do a full memory dump all SQL and the OS which will allow Microsoft to detect what happened..that could be useful!
on on the page file, if you are above 300 and you are then you only using your page as needed...sql can use for a number of reason....IE if you have a hash join, sql will request a memory grant to do the join if est wrong it could spill over to the page file...few other reason wont go into them all..
good luck...adjust the page file and u will need to reboot for it to take effect...
October 15, 2009 at 6:48 am
Thanks for the helpful reco.
So, I will increase the Max Mem to 12GB (why should I not keep it at 16GB, is it because OS needs some?)
and also Page Fize to 24GB.
You said "If you are on 32 bit then you will need to make some changes", can you give me the keywords to search for in google.
What did you mean by 'on on the page file, if you are above 300'. Are you talking about the page life expectancy?
October 15, 2009 at 7:00 am
on couple of the SQL servers, 'No Paging File' is selected for all the drives.
Is that ok?
October 15, 2009 at 7:17 am
link is for setting memory on a 32 bit: http://msdn.microsoft.com/en-us/library/ms175581(SQL.90).aspx
if you don't set this you wont be using your 16 gig of memory.
yes, check page life expectancy. sorry for the typo
and you do need to have the page file turned on, sql needs it! the idea is not to use it, but the reality is SQL needs it...if your box is set up correctly most the time its not needed, but there are times it will be used regardless.ifs its not a production box then follow the guidelines in the previous answer, which was set it and then back off until a bit. but if it is a production box set it to 1.5 X RAM. and put it on a different drive then your OS
October 20, 2009 at 9:08 am
AWE is enabled, but no pagefile.sys is setup for any of the drives. Is this an OK combo?
Perfmon says 'pagefile.sys % Usage' counter is 6 for last 24 hours (last week, it said 35).
how can i know if the OS/DB is paging a lot? is the above counter enough to say that?
October 20, 2009 at 9:16 am
I do have the '/pae' parameter in the Boot.ini file
October 20, 2009 at 10:18 am
if you do not have the pagefile.sys ...this is telling me that you do not have a Page file set up. but you are saying that you are getting readings...little lost...what d you have your page file set to?
as far as paging...you always going to have paging...not all paging is bad.
Page Faults/sec is the average number of pages faulted per second. It is measured in number of pages faulted per second because only one page is faulted in each fault operation, hence this is also equal to the number of page fault operations. This counter includes both hard faults (those that require disk access) and soft faults (where the faulted page is found elsewhere in physical memory.) Most processors can handle large numbers of soft faults without significant consequence. However, hard faults, which require disk access, can cause significant delays.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply