March 27, 2013 at 5:37 pm
I have an SQL Server 2008R2 server instance that is hitting the RAM ceiling and crippling the server.
- Server has 12GB RAM
- Task Manager shows more than 9.5GB of RAM being used on SQLServr.exe, I am getting time-out errors on C# queries, and connecting to the server via SSMS
- Management won't allow for increased RAM on this server
- Restarting the SQL Server service fixes the issue, but the RAM slowly climbs back up
- Online SQL Queries are showing that my databases are using approximately 2.5GB of RAM for buffer pages all added up
- This issue has happened approximately once a week for the last few weeks
Could anyone please direct me on how to better monitor / see where this memory usage is going? I'd appreciate some assistance on this.
March 27, 2013 at 11:05 pm
That is actually normal, and is not anything to worry about once you understand that SQL Server will use as much memory as you allow. See, serving data from memory is more efficient than serving from disk so SQL Server attempts to keep as much of your data in memory as possible.
An important thing for you to do right away is to set the "max server memory" server configuration to a sensible value that leaves enough memory for the Windows and other non-buffer pool SQL Server needs. Please read chapter 4 of this book (buy from Amazon or free eBook download) for details on memory management
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 28, 2013 at 4:46 am
If restarting the server clears up what's going on, I'm not sure that you're seeing memory problems. I'd suggest hitting the sys.dm_os_ring_buffers[/url] to determine if you're getting out of memory alerts. If not, while you may be experiencing memory pressure, causing paging, etc., you're not actually literally running out of memory.
It sounds more like you may be experiencing other issues such as bad parameter sniffing or something else. Have you looked at blocked processes, wait stats, query execution times?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 28, 2013 at 4:56 am
On top of the previous replies you may want to double check the need for this instance to enable ad hoc workload optimization.
( just to prevent cache spill ram for sqlplans that will only get used once. )
exec sp_configure 'optimize for ad hoc workloads', 1
reconfigure
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply