PF usage Problem

Question

Post reply

PF usage Problem

MasterDB

Hall of Fame

Points: 3054
More actions
November 1, 2011 at 12:48 am

#259421

Hi all,
one of my production server SQL Server is consuming more memory i.e., almost 32GB.
AWE Enabled,
MAX server memory set to 40GB,
but the PF usage is continuously increasing and after reaching very high PF usage we not able to connect SQL Server.
can anyone help me to fix this issue.. and what are the other things i need to do on that server..?
TIA...

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Usman Butt Hall of Fame Points: 3186 More actions · Answer 1

Can you please let us know which Windows Server and SQL Server are you using? Moreover, is your server on a VM?

dm_tapas Hall of Fame Points: 3515 More actions · Answer 2

Find, where the memory is leaking ?

Execute :

DBCC memorystatus

And look for

Cachestore_SQLCP(node 0)

find the memory usage in " SINGLEPAGE ALLOCATOR

& MULTIPAGE ALLOCATOR

dm_tapas Hall of Fame Points: 3515 More actions · Answer 3

Look for open transaction if, any.

Run DBCC freeproccache -- to release proc.

dm_tapas Hall of Fame Points: 3515 More actions · Answer 4

Also write the version of SQL as well as Windows server.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

How much ram available for the OS. PF is mostly used by the OS, not sql server.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

How much memory total on the server?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MasterDB Hall of Fame Points: 3054 More actions · Answer 7

Hi all,

First i'd like to say thanks for your inputs.

OS:windows 2003 server Enterprise

SQL: 2005 STD Edition sp2

RAM:32GB

Hard Disk:around 300GB

Ninja:i've seen in Task manager-->Process-->Mem Usage by SQLserver.exe is 31,343,5676 K

dm_tapas: Previously i ran the queries like

DBCC freessytemcache

Dbcc freeproccache.

This two didn't helped me to decrease the PFusage..

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

PF usage is through the roof because Windows is starving.

You need to set the max memory of sql server low enough so that the OS will have 4-5 GB of ram for itself.

I wouldn't go above 28 GB max memory for this server.

MasterDB Hall of Fame Points: 3054 More actions · Answer 9

HI,

I ran the query DBCC Memorystatus

Cachestore_SQLCP(total)

Single page allocator:2613840

Multi page allocator:4272

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Ninja's_RGR'us (11/1/2011)
PF usage is through the roof because Windows is starving.
You need to set the max memory of sql server low enough so that the OS will have 4-5 GB of ram for itself.
I wouldn't go above 28 GB max memory for this server.

MasterDB Hall of Fame Points: 3054 More actions · Answer 11

Ninja:i set max server memory for SQL is 40722MB

Should i decrease it or increase?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

MasterDB (11/1/2011)
Ninja:i set max server memory for SQL is 40722MB
Should i decrease it or increase?

You can't assign memory that isn't on the machine. You need to decrease it to 28GB.

MasterDB Hall of Fame Points: 3054 More actions · Answer 13

you mean to say right click on instance-->properties-->memory-->max server memory to 28 gb ryt?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

http://msdn.microsoft.com/en-us/library/ms178067.aspx