August 14, 2009 at 12:00 am
Hi,
We have A/P clustered SQL Server 2005 EE(64 bit) with SP3. I'm noticing below message in the error log, saying that memory has been paged out whenever the clustered instance moved to passive node and from passive node to Active node vice versa. Is this normal behavior or do I need to consider has a memory pressure?
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): 35872, committed (KB): 72000, memory utilization: 49%.
August 14, 2009 at 6:13 am
As per Microsoft, This problem occurs because the Windows operating system pages out the working set of the SQL Server process.
These error messages are logged when the working set of a SQL Server process reaches 50 percent or less of the memory that is committed for the SQL Server process. Therefore, you can use these error messages to determine the case in which SQL Server performance decreases significantly because the Windows operating system pages out the working set of the SQL Server process.
When this problem occurs, you may notice that the working set of other applications on the system is also paged out around the same time.
To troubleshoot the problem that occurs because the Windows operating system pages out the working set of the SQL Server process, follow these steps:
Apply the hotfixes
905865 , 920739, 942861 and 938486
If you still encounter this problem, you can prevent the Windows operating system from paging out the buffer pool memory of the SQL Server process by locking the memory that is allocated for the buffer pool in physical memory. You lock the memory by assigning the Lock pages in memory user right to the user account that is used as the startup account of the SQL Server service.
Note For 64-bit editions of SQL Server , only SQL Server Enterprise Edition can use the Lock pages in memory user right. This is applicable for SQL Server 2005 [RTM,SP1,SP2,SP3] and SQL Server 2008 [RTM and SP1]. SQL Server 2008 SP1 Cumulative Update 2 and SQL Server 2005 SP3 Cumulative Update 4 introduces support for SQL Server Standard editions to use the Lock pages in memory user right.
To assign the Lock pages in memory user right, follow these steps:
Click Start, click Run, type gpedit.msc, and then click OK. The Group Policy dialog box appears.
Expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Click User Rights Assignment, and then double-click Lock pages in memory.
In the Local Security Policy Setting dialog box, click Add User or Group.
In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK.
Close the Group Policy dialog box.
Restart the SQL Server service.
After you assign the Lock pages in memory user right and restart the SQL Server service, the Windows operating system no longer pages out the buffer pool memory within the SQL Server process. However, the Windows operating system can still page out the nonbuffer pool memory within the SQL Server process.
You can validate that the user right is used by the SQL Server instance by ensuring the following message is written in the SQL Server Error Log at startup: Using locked pages for buffer pool This message only applies to 64bit editions of SQL Server
For more Information visit:
http://support.microsoft.com/kb/918483
http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx (http://blogs.msdn.com/psssql/archive/2007/10/18/do-i-have-to-assign-the-lock-privilege-for-local-system.aspx)
http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx
For more information about support for locked pages on 64 bit systems, click the following article number to view the article in the Microsoft Knowledge Base:
970070 (http://support.microsoft.com/kb/970070/ ) Support for Locked Pages on SQL Server 2008 Standard Edition 64-bit systems
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 14, 2009 at 7:33 am
Have you /3GB, /USERVA, /PAE or AWE on on your active or passive server?
-LK
August 14, 2009 at 8:10 am
If you call me and say "We have A/P clustered SQL Server 2005 EE(64 bit) with SP3." and then ask me to guess what issue you could be facing , i will blindly say its the SQL paging issue , isn't it ??? 🙂 ...
I have worked on atleast 20-30 such cases in past 2.5 years in Microsoft (no longer wit it now...) ...
The reason is that this issue mostly comes on x64 architecture (A cluster setup will add to it).
If you want me to give you the indepth knowledge on this issue send me a mail @ hi_abhay78@yahoo.co.in .
For now :
------------
As per the error your working set is only 36 MB and 51% has been paged out (100-49) .This is very small and you should not worry if the working set is so small .As soon as its les sthan 50% you will no longer this issue .This errror is fake and if i remember MS released a new patch/CU that had a different algorithm to calculate the working set trimming issue .
There have been great discussion on this inside MS and the Dev refused to change the way OS virtual memory manager works as it might break the code and create many new bugs .
Try to follow http://support.microsoft.com/default.aspx?scid=kb;EN-US;938486
If nothing works then use LIPM ...
Regards
Abhay
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
August 14, 2009 at 8:20 am
forgot to add : before implementing LIPM cap Max memory of SQL Server to 75% of RAM via sp_configure .Else you will be in trouble ..
Regards
Abhay
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
August 14, 2009 at 10:11 am
Hi Sarab, Abhay,
Thanks for the detail explanation. I have noticed this memory paged out only when Cluster instance fail over to other node only so far..and I have the Max memory set to 12 GB for SQL Server instance & left 4 GB for the OS.
thanks
Ram
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply