I am starting to get that feeling also,
Doing some more reading here: Troubleshooting Performance Problems in SQL Server 2005 http://technet.microsoft.com/en-us/library/cc966540.aspx It's just things aren't adding up, and I don't want folks bitching about performance and blaming it on memory pressure or something else when it isn't. Z Edward E. Ziots CISSP, Network +, Security + Network Engineer Lifespan Organization Email:ezi...@lifespan.org Cell:401-639-3505 From: Kennedy, Jim [mailto:kennedy...@elyriaschools.org] Sent: Wednesday, December 29, 2010 9:33 AM To: NT System Admin Issues Subject: RE: Brain teaser with SQL Server 2005 and high page file usage. Need a sounding board 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:ezi...@lifespan.org] 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:ezi...@lifespan.org 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 listmana...@lyris.sunbeltsoftware.com 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 listmana...@lyris.sunbeltsoftware.com 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 listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
