SQL is funny with page files. What you are seeing is its potential use of the page file, not what it is actually using at that given moment.
From: Ziots, Edward [mailto:[email protected]] Sent: Wednesday, December 29, 2010 9:29 AM To: NT System Admin Issues Subject: Brain teaser with SQL Server 2005 and high page file usage. Need a sounding board I have a SQL 2005 32bit 2-node cluster, each server has 36GB of RAM and both servers are running Windows 2003 R2 Enterprise Edition. On my primary node which is also holding the SQL Group ( SQL server) and its associated resources, the performance monitor is showing my Page-file at 30.8GB, but the page-file is only set to a 2GB minimum and a 4GB Maximum ( I know I know its supposed to be 1.5X Memory and 2x Memory) SO when I look at the following counters I see this: http://support.microsoft.com/kb/2267427/en-us Memory_Committed Bytes: 31GB which is the same of what I am seeing in the Task Manager. Process, Working Set, _Total: 817,385,472 ( So like 817MB)n which is basically a multiple of 4096 ( as expected, 199557 4096K pages) Paging File, %Pagefile%^ Usage, in use: 25% ( so this doesn't jive with a reading of 31GB in Task manager, but does jive with a calculation from Process (combined processes, and there pagefile usage of 1.2GB) Memory Pages/Sec: 0 ( basically was dead quiet) Memory Pages Output/Sec: 0 (basically no pages going out to the disk, which is expected, since I shouldn't be seeing memory pressure with 32GB of RAM in the server) Memory Pages Input/sec: .8 ( Again not many pages that needed taken from the disk to satisfy memory constrains. Memory, Available Mbytes: 5.1GB ( which is about right, since we set the min and max of 0-32GB in SQL Server which leaves about 5GB for the OS) >From the article: Even if the Committed Bytes value is greater than the installed RAM, a Pages Output/sec value that is low or zero most of the time indicates that there is not a significant performance problem that is caused by not enough RAM. The committed bytes is close to the physical ram, but the pages output/sec is virtually nil, therefore I don't see this as a memory constraint. Also when I look at the Process Page File Bytes ( Total) I get 938MB, which is about 22-25% of the maximum of 4096 which is the maximum of the paging file. So does anyone have an idea, why in the heck I would be seeing 31GB for PF usage in the Task Manager, when the Performance Monitor counters simply do not support that case? TIA, EZ Edward E. Ziots CISSP, Network +, Security + Network Engineer Lifespan Organization Email:[email protected] Cell:401-639-3505 ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected]<mailto:[email protected]> with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin
