Thanks much Ken, I was search the SQLCAT page trying to find the exact explaination after looking at the counters like 3x times it defintely looks like the commit total is what is being shown, it kinda tipped me off when performance monitor only shows me a 25% of a 2GB paging file being used but Task Manager shows 30GB Page file usage.
Happy holidays everyone Z Edward E. Ziots CISSP, Network +, Security + Network Engineer Lifespan Organization Email:[email protected] Cell:401-639-3505 From: Ken Schaefer [mailto:[email protected]] Sent: Wednesday, December 29, 2010 8:43 PM To: NT System Admin Issues Subject: RE: Brain teaser with SQL Server 2005 and high page file usage. Need a sounding board http://blogs.technet.com/b/perfguru/archive/2008/01/08/explanation-of-pa gefile-usage-as-reported-in-the-task-manager.aspx Well known problem with Task Manager up to XP and Win2k3 Server (it's changed in Vista/Win2k8 Server onwards) PF Usage as reported in task manager in Windows XP and Windows Server 2003 is actually the system commit total. This number represents potential page file usage, not actual page file usage. It is how much page file space would be used if all the private committed virtual memory in the system had to be paged out all at once. Cheers Ken From: Ziots, Edward [mailto:[email protected]] Sent: Thursday, 30 December 2010 1:51 AM To: NT System Admin Issues Subject: RE: Brain teaser with SQL Server 2005 and high page file usage. Need a sounding board 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:[email protected] Cell:401-639-3505 From: Kennedy, Jim [mailto:[email protected]] 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:[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] 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 ~ 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 ~ 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 ~ 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
