Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DigitHeads
Starting Member
1 Post |
 Posted - 2010-05-27 : 12:36:09
|
| Our company recently upgraded our server from 32bit 2003 enterprise running SQL server 2005 Standard to 64bit Server 2008 R2 Enterprise and SQL server 2008 Standard. now we seem to have developed a memory issue. we have 20Gb of memory in the server and as the day progresses you can watch the SQL server process (SQLserver.exe) gobble up more and more, to the point where only 2Gb of memory are left and my users start complaining that the system is running slow. it doesnt seem to stop the more memory we give it them more it gobbles up. WHY ? within 3 days after reboot, all my available memory is gone, i have to reboot the server. i rebooted it 5 minutes ago and it has already climbed from 1Gb to 4Gb and it is still going up. PLEASE HELP !!Bill DanielsNetwork Administrator |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-05-27 : 16:49:12
|
There is a bug feature in 64 bit SQL Server where the available memory is evenly divided between data and procedure cache, minus some reserved structures. Under 32 bit this was only true of the first 4 GB of memory; AWE memory above 4 GB was only used for data.It is likely that your procedure cache is filling with ad-hoc execution plans that are not being purged, and therefore there is less memory available for data. You can check this by running DBCC PROCCACHE (NOT FREEPROCCACHE) and looking at the "num proc buffs" column. Multiply that number by 8192 and you'll get the number of bytes used by the procedure cache.There are dynamic management views that will also report memory usage but I'm not familiar enough with them. I believe there is a DBCC command in 2008 R2 that can flush the ad-hoc cache only and preserve the procedure cache. Otherwise you may want to use DBCC FREEPROCCACHE to clear those buffers and see if performance improves. |
 |
|
|
|
|
|
Server Memory