Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Crippling high RAM usage, Googled SQL buffer-related usage doesn't show the issue Expand / Collapse
Author
Message
Posted Wednesday, March 27, 2013 5:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 9:01 PM
Points: 10, Visits: 103
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.
Post #1436227
Posted Wednesday, March 27, 2013 11:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:57 AM
Points: 7,081, Visits: 12,575
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

https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1436261
Posted Thursday, March 28, 2013 4:46 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 15,518, Visits: 27,899
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 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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1436381
Posted Thursday, March 28, 2013 4:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1436386
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse