May 19, 2009 at 10:34 am
PF usage is very high in sql server 2005. it is around 8 GB. CPU is very low and also I checked the activity in sql server and there are few normal process are running.
This high PF usage makes the SQL Box very slow. How to check what process is making PF to grow.
appreciated if somebody will help.
May 19, 2009 at 11:59 am
Are you using SQL Server 64bit ??
PF usage will be more due memory issues.
May 19, 2009 at 12:14 pm
SQL Server will use as much memory as you have configured. If you have not set a maximum, it will use as much memory as is available on that system unless you are running the Standard Edition on x86.
The PF Usage you are seeing is normal for a SQL Server system. I would suspect that you have not set min/max memory settings and have found that SQL Server is taking up all of the available memory.
I would recommend setting the max memory to no more than 6GB - and the min memory to no more than 4GB if you set the min at all. You don't need to set the minimum memory unless you have other processes/applications outside of SQL Server that could cause problems for SQL if they take up too much memory.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 19, 2009 at 1:28 pm
Thanks guys,
Yes, it's 64 bit machine.
I am thinking of creating two virtual files. One will be the default on c drive and other will be on D drive and the size will be 6 GB. By doing that it may increase the performance of the server.
I will reboot the server and see if it helps.
thanks again.
May 19, 2009 at 1:33 pm
balbirsinghsodhi (5/19/2009)
Thanks guys,Yes, it's 64 bit machine.
I am thinking of creating two virtual files. One will be the default on c drive and other will be on D drive and the size will be 6 GB. By doing that it may increase the performance of the server.
I will reboot the server and see if it helps.
thanks again.
This won't help because SQL Server should not be paging out to disk. If it is, then you have a bigger problem.
Since this is x64 - verify the minimum and maximum memory settings. Is this Standard or Enterprise Edition? If you are running Standard edition - you need to decrease the maximum memory you allow SQL Server (probably less than 5GB on your system). On an x64 system - if you don't set the maximum memory that SQL Server can take - it is going to take all of the memory available.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 19, 2009 at 2:04 pm
It's a 64 bit Enterprise Edition of sql server and I believe sql serer is paging out to disk. I am not changing the memory but creating two Page File on the SQL Server to see if it increase the performance.
May 19, 2009 at 3:21 pm
If you have not set lock pages in memory, and you have not set the maximum memory - then you could be seeing issues.
Once again - SQL Server should not be paging out to disk. Adding virtual files for paging will not solve any performance issues. If you are running into a memory pressure situation - then it is because you don't have enough memory, or you have not set a maximum for SQL Server and it is using all of the available memory on the server.
Review the min/max settings and verify that you have set the maximum memory for SQL Server.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 19, 2009 at 3:43 pm
This SQL box has 6 GB of total memory.
Mininum memory is 0 and maximum memory is 2147483647 MB which is default.
Should I change maximum memory to 6000 MB and what locks size should be set. right now it's 0
May 19, 2009 at 4:01 pm
You should set the max memory to no more than 4GB (4096MB). This will leave 2GB of memory to the OS.
You can also set the lock pages in memory option in group policy editor (gpedit.msc from command line). Review this document: http://msdn.microsoft.com/en-us/library/ms190730(SQL.90).aspx
After making these changes - you should restart SQL Server, but it is not necessary to reduce the memory. SQL Server will not give up the memory right away if you don't. For the lock pages in memory setting, I am fairly certain that a server reboot is going to be needed. That setting is less important than the max memory setting.
If after making these changes - SQL Server is still very slow then you should invest in adding more memory to the system.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 19, 2009 at 4:52 pm
This SQL box has 6 GB of total memory.
Mininum memory is 0 and maximum memory is 2147483647 MB which is default.
Should I change maximum memory to 6000 MB and what locks size should be set. right now it's 0
May 19, 2009 at 4:53 pm
Thanks,
I will surely do that.
May 19, 2009 at 5:09 pm
After doing all the changes and rebooted the server, it is now taking 100 CPU but paging size is very low. Before the changes, CPU was very little but paging was very high.
May 19, 2009 at 5:18 pm
Now, you need to identify what queries are causing the CPU's to spike and fix them. It definitely sounds like you have an underpowered system that could benefit from more CPU's and more memory.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 19, 2009 at 5:26 pm
Thanks man,
I think it's fine now, some system process were running and taking lots of CPU. I will see tomorrow if there is any improvement because there will be some load running in the night.
Thanks again.
May 19, 2009 at 5:29 pm
Ahh, glad that it settled down for you.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply