question

cstephen avatar image
cstephen asked

We are facing this a singnificant part of sql server process memory error in our server?

We are using sql server 2005 64 bit server edition.we are using mirroring,we faced the below issue on principal error.after browse the internet we found it the solution on this link 1) http://blogs.msdn.com/b/karthick_pk/archive/2012/06/22/a-significant-part-of-sql-server-process-memory-has-been-paged-out.aspx Error: A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1509 seconds. Working set (KB): 474328, committed (KB): 999160, memory utilization: 47%. Kindly advice us. either we go for the above we page link solution. thanks stephen
sqlservermemory-utilization
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I'd start from narrowing down the cause of the page out before modifying the registry. Something is causing your SQL Server to trim the working set.
1 Like 1 ·
Please post the value in use for your max server memory. This query will return it: select * FROM sys.configurations where name like 'max server memory (MB)' Please also post the total RAM installed in the server.
1 Like 1 ·
Also are you in current service pack? Can you post the result of SELECT @@vERSION ?
0 Likes 0 ·
And, are you dealing with a VM or a physical box?
0 Likes 0 ·
@DaniSQL: 1) Max Server memory(MB): 2147483647 @sdoubleday: 1) Microsoft SQL Server 2005 - 9.00.5057.00 (X64) Mar 25 2011 13:33:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)
0 Likes 0 ·
Show more comments

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
Have you looked through this Microsoft Support article? http://support.microsoft.com/kb/918483/en-gb - Something else to consider: SQL Server hasn't been told to limit the amount of memory it can use, and it has used so much that Windows has had to page some out. There is a configuration setting in SQL Server that tells it how much memory it's allowed. This is currently set to 2147483647MB, which is, I'm guessing, significantly more memory than you've got. SQL Server likes lots of memory - this allows it to cache data in memory so it doesn't have to keep getting it from disk, giving faster response times to queries, particularly those that make use of frequently-accessed data. And SQL Server will cheerfully use all the memory it can, up to the limit that it has been given. Unfortunately, Windows likes to have some memory available to do other stuff, such as running backup programs, anti-virus stuff, or some other services that you may have installed on that server. This can cause conflicts - and has, given your error message above. First thing you need to do is to restrict the amount of memory that SQL Server can use. However, how low do you go? There are various thoughts here, but the best advice I've seen is in this [blog post by Jonathan Kehayias][1] (of SQLSkills.com). Ignore the title of the post, and check the advice in the third paragraph, which indicates that you need to reserve (for Windows) the following amount of memory: * 1 GB absolutely, plus * 1 GB for every 4 GB RAM installed between 4GB & 16GB * 1 GB for every 8 GB RAM installed above 16GB. So, for a server with 32GB RAM, you need to reserve 1 + 3 + 2 = 6 GB, allowing 26 GB for SQL Server. Now you need to tell SQL Server this. You can either do that by using the SQL Server Management Studio GUI (in Object Explorer, right-click on the server name, select properties, and have a rummage round there), or by using the [`sp_configure` stored procedure][2]: exec sp_configure 'max server memory', 26624 -- = 26 * 1024 (this SP takes memory in MB, not GB) reconfigure (The [`reconfigure` command][3] tells SQL Server to check its configuration and apply these new settings. This particular setting won't need a reboot.) [1]: https://www.sqlskills.com/blogs/jonathan/wow-an-online-calculator-to-misconfigure-your-sql-server-memory/ [2]: http://msdn.microsoft.com/en-us/library/ms188787(v=sql.90).aspx [3]: http://msdn.microsoft.com/en-us/library/ms176069(v=sql.90).aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@thomasruston: thanks for the information.we will implement that solution and let you know the results. And currently our server has 7.50 GB Ram without any other application installed except MSSQL SERVER 2005 Shall we use this below configuration settings. Since we will use 2 GB for OS and remaining 5.5 for Sql server. So exec sp_configure 'max server memory', 5632-- = 5.5 * 1024 (this SP takes memory in MB, not GB) reconfigure Regards Stephen
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.