December 10, 2010 at 9:14 am
Task manager showing memory occupaied by SQL Server 27GB,
But in Reports memory consuption showing
CACHESTORE_SQLCP:1529560 KB
OBJECTSTORE_LOCK:134624 KB
CAHCESTORE_OBJCP:67096 KB
MEMEORYCLEARK_SQLGENERAL:24776 KB
MEMEORYCLEARK_SOSNODE:23936 KB
OTHER:34920 KB
I have below environment
1.Cluster Environment(Windows 2003 64 bit SP2)
2.Active/Passive cluster SQL Server 2005 RTM
3.Memory size in active node 32 GB
4.Min memory configured:8GB
5.Max memory configured:2147483647 MB
6.Present accupaied by SQL Server 27GB (it is more then 80% )
I want retain accupaied memery from SQL Service,Is it possible ,If possible how to do ?
Regards
December 10, 2010 at 9:38 am
Yes , You can do it . Assign Lock Pages in Memory privilege to the SQL Server Engine Service
Ctrl Panel --> Administrative Tools --> Local Security Policy --> User Rights Assignment --> Lock Pages in Memory
To find the mem used by SQL Server, use the Perfmon counters
SQLServer:Memory Manager -- Target Server Memory (KB)
SQLServer:Memory Manager -- Total Server Memory (KB)
Is SQL Server 32 bit or 64 bit ?
How many SQL Server instances are running on the Cluster ?
What is the Page file size on the server ?
Always Set the Max Server Memory. If this is a dedicated box for SQL Server, then you can leave 4 GB for OS and assign the rest as Max Memory. If using Multiple SQL instances, mem has to be shared between the instances.
Thank You,
Best Regards,
SQLBuddy
December 10, 2010 at 9:45 am
Version is " Microsoft SQL Server 2005 - 9.00.1399.06 (X64)"
OS "Windows 2003 64 bit SP2
MEMEORYCLERK_SQLBUFFERPOLL
Virtual memory Reserved is :33 GB
Virtual memory commited 25GB
Only one Instance running..with 70,30,50,12 GB Database size's
December 10, 2010 at 2:13 pm
snivas (12/10/2010)
Version is " Microsoft SQL Server 2005 - 9.00.1399.06 (X64)"OS "Windows 2003 64 bit SP2
MEMEORYCLERK_SQLBUFFERPOLL
Virtual memory Reserved is :33 GB
Virtual memory commited 25GB
Only one Instance running..with 70,30,50,12 GB Database size's
On x64 hardware running the x64 version of SQL Server you really need to set the max memory (no more than 28GB with 32GB available). You also should make sure you have set the locked pages in memory right for the service account running SQL Server.
And finally, you need to upgrade this system as soon as possible to at least SP3 - and if this is the Standard Edition you will need CU4 to be able to use the locked pages in memory feature.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 11, 2010 at 8:01 am
Thanks Williams
I updated maximum memory to 24GB,is it required to restart the service ??
If yes ,,,How to do in Cluster environment
December 13, 2010 at 11:45 am
Setting Max Memory doesn't need restart.
Thank You,
Best Regards,
SQLBuddy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply