January 23, 2009 at 11:36 am
Hi,
We are using SQL Server 2005 Development Edition in 64 Bit. My system always using High RAM 25 GB out of 32 GB.
Awe is already enabled. When we check SP_who2 Active it shows checkpoint & Log Writter is in suspended stage in Master Database.
All the database are placed in E Drive.
Total Capacity in E Drive is 40 GB. Free space available in E Drive is 8 GB.
Please suggest how to find out why it uses more memory?
Regards,
S.Balavenkatesh
January 23, 2009 at 12:04 pm
Hi there,
SQL is always going to grab as much memory as it can. The SQL OS Buffer pool will grab as much as possible, it will then page out needed memory to the OS when the OS becomes low.
So what you are seeing is normal. you can back of the max, min memory if needed.
good luck
January 23, 2009 at 12:09 pm
the Checkpoint is always suspended state. System always use 25 GB RAM. It is not a production system. It is a development system. No active users are there. How to change as normal;
Regards,
S.Balavenkatesh
January 25, 2009 at 11:38 pm
>>My system always using High RAM 25 GB out of 32 GB
As explained earlier, this is normal behavior for SQL Server. SQL grabs as much memory as needed and will NOT release it unless another application on the same server tries to grab it.
>>the Checkpoint is always suspended state.
This is also normal behavior. Checkpoint periodically runs in the background and flushes the dirty pages from cache to the disk and if there is not much (write) activity then checkpoint will always be in suspended state. you may want to read more information here.
>>How to change as normal;
What is your expected behavior? All the items you raised are normal behaviors.
If you want to know how the 25GB is being used, you can check that information using the code below.
select
objtype,
count(*)as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts)as avg_use_count
from sys.dm_exec_cached_plans
group by objtype
Check adhoc sql cache is bigger than procedure cache and clear only adhoc sql cache
[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]
January 26, 2009 at 6:27 am
As was mentioned earlier, SQL Server will allocate as much RAM as it can. This can often cause problems because SQL neither knows nor cares about other processes on a server.
You should always set a MIN and MAX memory limit on your SQL Server. You should also enable the Lock Pages In Memory privilege for the service account running the SQL Server database engine service. This will allow Windows to page out memory without interferring SQL and allow SQL to manage it's own memory pages without interference.
Also AWE is not available on a 64 bit system. It simply isn't necessary.
cheers
--
Andrew Hatfield
January 27, 2009 at 7:18 am
just take the properties of the database server in ssms and in options set max memory to e.g. 10 gb and it will acquire not more than that. if sql gets out of memory it will simply flush out old pages from the cache will continue to work smoothly. so just chill.
January 27, 2009 at 10:26 am
please check Slava's post on the 64 bit and AWE, according to him it is still used
January 27, 2009 at 1:29 pm
>>As was mentioned earlier, SQL Server will allocate as much RAM as it can. This can often cause problems because SQL neither knows nor cares about other processes on a server.
But it does get notified of memory issues from the OS and has mechanisms in place to respond to those.
To the original poster: is there an actual problem here with something, or are you just concerned that sql takes 25GB of RAM? That, in isolation, is certainly not a cause for concern. It is by design. Are things running slowly? Are there lots of other stuff on the box that are having problems?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 27, 2009 at 3:27 pm
TheSQLGuru (1/27/2009)
>>As was mentioned earlier, SQL Server will allocate as much RAM as it can. This can often cause problems because SQL neither knows nor cares about other processes on a server.But it does get notified of memory issues from the OS and has mechanisms in place to respond to those.
Very basically, SQL Server will receive a notification from the OS to release unused pages as the OS pages RAM out to disk. If Lock Pages is enabled, then SQL Server is not forced to honour those requests.
--
Andrew Hatfield
January 28, 2009 at 11:29 am
My system is running under local account. There is no entry in Locked Pages memory. Is it ok? for that memory management.
Regards,
Balavenkatesh
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply