May 19, 2010 at 5:15 pm
On the SQL machine, the PF usage is about 7-8 Gb, The following are the specifications of the machine:
Microsoft windows server 2003, standard x64 Edition, service pack 2
Intel (R) Xeon(R) CPU E5440@2.83GHz 2.83GHz,16.0 GB of RAM.
Using the "dbcc memorystatus" I get the following results:
Memory Manager:
VM reserved = 16975928 KB
VM committed = 6154808 KB
AWE = 0
Reserved memory = 1024
Buffer Counts :
Committed = 734976
Target = 1761862
Hashed = 323023
Stolen = 1582825
Min free = 512
Visible = 1761862
Available = 1597584
The system becomes slow, only a restart brings the PF usage down but it gets higher later on.
I only have one instance running on this server.
Please suggest a way to reduce this PF usage, Kindly let me know if you need any other information. Your quick response is highly appreciated.
Thanks &
Best Regards
May 19, 2010 at 6:11 pm
That is normal - it is not actually using the page file and just reporting the memory usage that SQL Server is taking as PF usage.
What are the min/max settings for SQL Server memory?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 19, 2010 at 6:19 pm
Thank you for your reply,
In the task manager under processes tab it showed that sql service is using about 6.67 gb of ram alone
May 19, 2010 at 6:51 pm
That is normal and the way SQL Server works. It will take as much memory as is needed, up to the max memory setting for that instance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 20, 2010 at 11:31 am
Today, checked the task manager and i saw that sql server service was using 8gb alone,
the min value in mb for memory is set to 0
and the max value is 2147483647. I dont think this is normal because it is affecting production. and other applications on other machines that use this database
May 20, 2010 at 1:58 pm
wmalik001 (5/20/2010)
Today, checked the task manager and i saw that sql server service was using 8gb alone,the min value in mb for memory is set to 0
and the max value is 2147483647. I dont think this is normal because it is affecting production. and other applications on other machines that use this database
Why do you think this is affecting other applications? Are there other applications on this server? If so, then you need to identify what memory requirements those other systems need and limit SQL Server so those other applications will have access to that memory.
Because you have not set the max (that is the default value you have specified) - SQL Server will take all of the memory that is available on the system.
This is normal - for the way you have configured SQL Server. If you don't want SQL Server to use all of the memory on the system, set the max memory value to limit how much SQL Server will take.
On a system with 16GB of memory, and SQL Server is only taking 8GB - I really don't see any issues with SQL Server causing problems for other applications.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 20, 2010 at 2:52 pm
yes i understand, but the same database on a machine with 4 gb ram only uses about 1-2gb of ram, why is sql server using so much of the ram and the page file is going high?
Secondly there are no other applications that are installed on this server, other applications make transactions in this database but are installed on different machines.
Last thing, could you please let me know the value i should write for max and min,
currently for min it is 0
and for max it is 2147483647 MB
And if reducing a max value, will it affect the PF usage? Will it reduce the Page file usage?
May 20, 2010 at 3:04 pm
This is from the Data base logs:
05/20/2010 05:33:14,spid1s,Unknown,A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 37604<c/> committed (KB): 105592<c/> memory utilization: 35%.
May 20, 2010 at 3:51 pm
wmalik001 (5/20/2010)
This is from the Data base logs:05/20/2010 05:33:14,spid1s,Unknown,A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 37604<c/> committed (KB): 105592<c/> memory utilization: 35%.
For a detailed description of this error see the following link:
http://support.microsoft.com/kb/918483
Most likely your problem can be resolved by setting the max memory option to something like 12GB. This will leave 4GB for the OS and other applications.
/SG
May 20, 2010 at 4:02 pm
Thanks for the link,
I already read that, but i first want to try reducing the max value,
so you are suggesting I should make it to 12gb that is equal to 12,288MB?
So i will remove this value in max 2147483647 MB and type 12,288MB correct?
May 20, 2010 at 4:28 pm
wmalik001 (5/20/2010)
Thanks for the link,I already read that, but i first want to try reducing the max value,
so you are suggesting I should make it to 12gb that is equal to 12,288MB?
So i will remove this value in max 2147483647 MB and type 12,288MB correct?
Yes, but you should probably just write 12288 without the comma 😎
May 20, 2010 at 7:54 pm
Before you set the max memory to 12GB, make sure you have upgraded to SP3 CU4 and enabled the lock pages in memory setting. If you don't, then you will still see those error messages and the system will still page out SQL Server memory.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 21, 2010 at 10:15 am
Hey Jeff,
The SQL server that i am using, its version is as below:
Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
Feb 10 2007 00:59:02
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
****************************************************************
Should i First update it to sp3?
May 21, 2010 at 10:59 am
Are you running SQL Server standard edition or enterprise? You should uprade to sp2 cu4 or later if running standard
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 21, 2010 at 12:06 pm
Standard edition 64 bit
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply