| Author |
Topic |
|
dbthj
Posting Yak Master
143 Posts |
 Posted - 2008-12-24 : 11:33:10
|
| We have a 64 bit SQL 2005 Enterprise Edition instance.It has 16 gig of memory. SQL is using most of it.Task Manager says sqlserver.exe is using 14.9 gigDBCC MEMORYSTATUS says VM RESERVED IS 16.9 GIGVM COMMITTED IS 14.8 GIGI figured the plan cache was overused.DBCC FREEPROCCACHE freed up nothing.sysprocesses says no threads are using any significant memory.Questions:1. How do I figure out where the memory is used and can I flush it (how?)?2. The default for SQL memory maximum is megagajillion gigabytes. Is it a good idea to set this to prevent having SQL paralyze the box?3. Can anybody point me to a paper on Best Practices for 2005 64bit memory management?4. Another thread on this forum mentioned DBCC MEMORYUSAGE.That doesn't do anything on my machine. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2008-12-24 : 13:34:24
|
| PLease Pardon my ignorance, but I don't know what "standard reports" are.I assume they are in Report Manager. I don't know where that is. All instructions I see inBooks Online start with "In Report Manager click on .....". Where is it? |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-24 : 13:36:05
|
| Right click Instance- Reports- Standard Reports. I assume you have applied SP2 or Above for SQL Server ENT Edition. |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2008-12-29 : 16:26:27
|
| Thanks. They hid that little bugger pretty well. That told me what I need for now.For the benefit of onlookers, here's what I found.These two docs are excellent presentations of SQL Memory usage:http://www.sdsqlug.org/presentations/November2006/November_2006_64-bit_SQL_Server.pdf http://charlotte-sql.org/powerpoints/Nov_28_Meeting.ppt Here are a few things I discovered: By default, the maximum forSQL memory usage is about 2 exabytes. I figure that means SQL willsuck the server dry if it can. It tends to allocate a lot more than is currently being used. You can see that in the Standard Report for Memory. Look for MEMORYCLERK_SQLBUFFERPOOL. (This is where I start to get lost) MEMORYCLERK_SQLBUFFERPOOL is reported in the awe_allocated_kb column of the sys.dm_os_memory_clerks table.BOL says this value is the memory that is used only with AWE. HOWEVER, these numbers are big on my x64 SQL instances that neitheruse nor need AWE to access exteneded memory. I figure BOL is notbeing totally accurate on this.My guess is that it is a pretty good idea not to use the defaultfor max memory on a 64 bit system. Seems to me it would be betterto reset the Max to a value somewhat lower than the total memoryof the server. Anybody got a different opinion on that? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
saurabhsrivastava
Posting Yak Master
216 Posts |
Posted - 2008-12-30 : 21:02:35
|
| Yes, Tara is correct leave at least 2 GB for OS in your case. When you increase RAM you might want to leave more for OS and other applications running on system. Also, properly configure pagefile.sys, in most cases it is set as 1.5xRAM which is not optimal configuration for 64-bit systems. Check Ms website for all details on how to set pagefile.sys on 64-bit systems. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-12-30 : 23:52:44
|
| The amount of memory that is being reported as used by SQL Server looks about right for the amount of memory on the system, so it would probably be OK to just leave it alone. This is normal behaviour for SQL Server. Once it allocates memory, it does not release it unless the OS is under memory pressure.Are you seeing some problem on your server?CODO ERGO SUM |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2009-01-05 : 14:55:42
|
| Nope. No problems. Got some alerts based on % of memory used by SQL and I figured it would be a good idea to know something about finding out how the memory is being used and what are Best Practices. |
|
|
|
|
Memory Management 2005 Enterprise 64bit