asked
SQL 2012 Uses all RAM then crashes the Machine
I have a SQL 2012 SP1 running on win 2012 R2. I have 80 GB for the machine. No matter WHAT I set the max ram to on SQL, it fills up the entire machine and crashes. I am currently trying to run the following to prevent this from happening, yet I do not think this is really working. EXEC master.dbo.sp_executesql N'DBCC DROPCLEANBUFFERS' EXEC master.dbo.sp_executesql N'DBCC FREESESSIONCACHE' EXEC master.dbo.sp_executesql N'DBCC FREEPROCCACHE' EXEC master.dbo.sp_executesql N'DBCC FREESYSTEMCACHE ([ALL])' EXEC master.dbo.sp_executesql N'DBCC FLUSHPROCINDB( 7 )' A long time search of this has revealed that this happens and the answer is "more ram". My database is 17 Gigs. This appears to be a lot for this. The 2012 server is using a linked server to the 17 gig database. The 2012 server is being hit by Crystal reports that simply use views to the linked server and database (sql 2005 not an issue). Is there some type of formula where you can decide on ram? I have a lot of individals asking why is the database cached 4 times and still wanting more? I have optimized for adhoc queries and this has produced little results.
is sql server the only thing on this machine? what is the current value of "Maximum server memory (in MB)"? The linked server jumps out - linked server memory usage is not limited by max server memory. You might try some of the virtual address space queries in this msdn blog to see if you can identify something outside of the buffer pool that is driving this memory usage -
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/02/16/how-to-find-who-is-using-eating-up-the-virtual-address-space-on-your-sql-server.aspx
So you have 80 GB of RAM on this server, right? Your database it only 17 GB, right? What is your 'max server memory' set to? please run this -- exec sp_configure 'max server memory (MB)' -- and post back. What does Performance Monitor show for "SQLServer:Buffer Manager\Page life expectancy" and "Memory\Available MBytes"?
Page life expectancy Average is 161,286.205 , min = 155 and max = 167 over about 4 hours today. I can go back to previous days. No other apps no CLR. Just Crystal running views. Current Memory perfmon counter disappeared along with a few others but current memory is 42.7 GB in use 37.3 GB available 44/106 GB committed 1.6 GB cached 180 MB paged pool 186 MB non page pool. I have been running the DBCC to try and keep the thing to a minimum growth pattern.
dbcc memorystatus results FYI.. Process/System Counts Value ---------------------------------------- -------------------- Available Physical Memory 39399268352 Available Virtual Memory 140605728710656 Available Paging File 65747001344 Working Set 44200022016 Percent of Committed Memory in WS 100 Page Faults 10861521 System physical memory high 1 System physical memory low 0 Process physical memory low 0 Process virtual memory low 0 (10 row(s) affected) Memory Manager KB ---------------------------------------- ----------- VM Reserved 89393552 VM Committed 4552836 Locked Pages Allocated 0 Large Pages Allocated 0 Emergency Memory 1024 Emergency Memory In Use 16 Target Committed 38633528 Current Committed 4552840 Pages Allocated 158864 Pages Reserved 0 Pages Free 4033880 Pages In Use 492216 Page Alloc Potential 58015872 NUMA Growth Phase 0 Last OOM Factor 0 Last OS Error 0 (16 row(s) affected) Memory node Id = 0 KB ---------------------------------------- ----------- VM Reserved 89393488 VM Committed 4552836 Locked Pages Allocated 0 Pages Allocated 158864 Pages Free 4033880 Target Committed 38633528 Current Committed 4552840 Foreign Committed 0 Away Committed 0 Taken Away Committed 0 (10 row(s) affected) MEMORYCLERK_SQLGENERAL (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 9336 (6 row(s) affected) MEMORYCLERK_SQLBUFFERPOOL (node 0) KB ---------------------------------------- ----------- VM Reserved 2153140 VM Committed 170124 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 26744 (6 row(s) affected) MEMORYCLERK_SQLQUERYEXEC (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 40 (6 row(s) affected) MEMORYCLERK_SQLOPTIMIZER (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 992 (6 row(s) affected) MEMORYCLERK_SQLUTILITIES (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 136 (6 row(s) affected) MEMORYCLERK_SQLSTORENG (node 0) KB ---------------------------------------- ----------- VM Reserved 7680 VM Committed 7680 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8080 (6 row(s) affected) MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 1656 (6 row(s) affected) MEMORYCLERK_SQLCLR (node 0) KB ---------------------------------------- ----------- VM Reserved 3151296 VM Committed 3488 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 5736 (6 row(s) affected) MEMORYCLERK_SQLSERVICEBROKER (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 560 (6 row(s) affected) MEMORYCLERK_SQLHTTP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) MEMORYCLERK_SNI (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 104 (6 row(s) affected) MEMORYCLERK_FULLTEXT (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 296 (6 row(s) affected) MEMORYCLERK_SQLXP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) MEMORYCLERK_BHF (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 32 (6 row(s) affected) MEMORYCLERK_XE_BUFFER (node 0) KB ---------------------------------------- ----------- VM Reserved 4224 VM Committed 4224 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 0 (6 row(s) affected) MEMORYCLERK_XTP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) MEMORYCLERK_HOST (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 792 (6 row(s) affected) MEMORYCLERK_SOSNODE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 26952 (6 row(s) affected) MEMORYCLERK_SOSOS (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 192 (6 row(s) affected) MEMORYCLERK_SOSMEMMANAGER (node 0) KB ---------------------------------------- ----------- VM Reserved 104256 VM Committed 104128 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 0 (6 row(s) affected) MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 24 SM Committed 24 Pages Allocated 0 (6 row(s) affected) MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB ---------------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 192 (6 row(s) affected) MEMORYCLERK_FSAGENT (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) MEMORYCLERK_FILETABLE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) MEMORYCLERK_XE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 1592 (6 row(s) affected) MEMORYCLERK_SQLLOGPOOL (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 2088 (6 row(s) affected) MEMORYCLERK_LWC (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) MEMORYCLERK_FSCHUNKER (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 48 (6 row(s) affected) CACHESTORE_OBJCP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 5040 (6 row(s) affected) CACHESTORE_SQLCP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 17240 (6 row(s) affected) CACHESTORE_PHDR (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 7936 (6 row(s) affected) CACHESTORE_XPROC (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 40 (6 row(s) affected) CACHESTORE_TEMPTABLES (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) CACHESTORE_NOTIF (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) CACHESTORE_VIEWDEFINITIONS (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) CACHESTORE_XMLDBTYPE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) CACHESTORE_XMLDBELEMENT (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) CACHESTORE_XMLDBATTRIBUTE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) CACHESTORE_BROKERTBLACS (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 56 (6 row(s) affected) CACHESTORE_BROKERKEK (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) CACHESTORE_BROKERDSH (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) CACHESTORE_BROKERRSB (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) CACHESTORE_BROKERREADONLY (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 32 (6 row(s) affected) CACHESTORE_BROKERTO (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) CACHESTORE_EVENTS (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) CACHESTORE_SEHOBTCOLUMNATTRIBUTE (node 0) KB ----------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 104 (6 row(s) affected) CACHESTORE_SYSTEMROWSET (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 1280 (6 row(s) affected) CACHESTORE_CONVPRI (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 64 (6 row(s) affected) CACHESTORE_FULLTEXTSTOPLIST (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 32 (6 row(s) affected) CACHESTORE_SEARCHPROPERTYLIST (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) CACHESTORE_COLUMNSTOREOBJECTPOOL (node 0) KB ----------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) CACHESTORE_XML_SELECTIVE_DG (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 8 (6 row(s) affected) USERSTORE_SCHEMAMGR (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 2968 (6 row(s) affected) USERSTORE_DBMETADATA (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 15808 (6 row(s) affected) USERSTORE_TOKENPERM (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 14320 (6 row(s) affected) USERSTORE_OBJPERM (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 3112 (6 row(s) affected) USERSTORE_SXC (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 32 (6 row(s) affected) OBJECTSTORE_LBSS (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 48 (6 row(s) affected) OBJECTSTORE_SNI_PACKET (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 2056 (6 row(s) affected) OBJECTSTORE_SERVICE_BROKER (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 496 (6 row(s) affected) OBJECTSTORE_LOCK_MANAGER (node 0) KB ---------------------------------------- ----------- VM Reserved 65536 VM Committed 65536 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 2104 (6 row(s) affected) OBJECTSTORE_SECAUDIT_EVENT_BUFFER (node 0) KB ------------------------------------------ ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 (6 row(s) affected) OBJECTSTORE_XACT_CACHE (node 0) KB ---------------------------------------- ----------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 72 (6 row(s) affected) Buffer Pool Value ---------------------------------------- ----------- Database 3342 Simulated 0 Target 180480 Dirty 212 In IO 0 Latched 0 Page Life Expectancy 172520 (7 row(s) affected) Procedure Cache Value ---------------------------------------- ----------- TotalProcs 164 TotalPages 3782 InUsePages 537 (3 row(s) affected) Global Memory Objects Pages ---------------------------------------- ----------- Resource 413 Locks 263 XDES 93 DirtyPageTracking 1 SETLS 1 SubpDesc Allocators 1 SE SchemaManager 158 SE Column Metadata Cache 212 SE Column Metadata Cache Store 13 SQLCache 254 Replication 2 ServerGlobal 52 XP Global 2 SortTables 3 (14 row(s) affected) Query Memory Objects (internal) Value ---------------------------------------- ----------- Grants 0 Waiting 0 Available 3662096 Current Max 3662096 Future Max 3662096 Physical Max 3662096 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 (11 row(s) affected) Small Query Memory Objects (internal) Value ---------------------------------------- ----------- Grants 0 Waiting 0 Available 16384 Current Max 16384 Future Max 16384 (5 row(s) affected) Remote Query Memory Objects (internal) Value ---------------------------------------- ----------- Grants 0 Waiting 0 Available 1831048 Current Max 1831048 (4 row(s) affected) Optimization Queue (internal) Value ---------------------------------------- -------------------- Overall Memory 32143089664 Target Memory 31615115264 Last Notification 1 Timeout 6 Early Termination Factor 5 (5 row(s) affected) Small Gateway (internal) Value ---------------------------------------- ----------- Configured Units 4 Available Units 4 Acquires 0 Waiters 0 Threshold Factor 380000 Threshold 380000 (6 row(s) affected) Medium Gateway (internal) Value ---------------------------------------- ----------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 12 Threshold -1 (6 row(s) affected) Big Gateway (internal) Value ---------------------------------------- ----------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 Threshold -1 (6 row(s) affected) Memory Pool Manager Pages ---------------------------------------- ----------- Reserved Current 0 Reserved Limit 4767696 (2 row(s) affected) Memory Pool (internal) Pages ---------------------------------------- ----------- Allocations 61495 Predicted 977019 Private Target 0 Private Limit 0 Total Target 4829191 Total Limit 4829191 OOM Count 0 (7 row(s) affected) MEMORYBROKER_FOR_CACHE (internal) Pages ---------------------------------------- ----------- Allocations 9386 Rate 0 Target Allocations 3861558 Future Allocations 0 Overall 3923717 Last Notification 1 (6 row(s) affected) MEMORYBROKER_FOR_STEAL (internal) Pages ---------------------------------------- ----------- Allocations 7095 Rate 0 Target Allocations 3859267 Future Allocations 0 Overall 3923717 Last Notification 1 (6 row(s) affected) MEMORYBROKER_FOR_RESERVE (internal) Pages ---------------------------------------- ----------- Allocations 0 Rate 0 Target Allocations 3923717 Future Allocations 915524 Overall 3923717 Last Notification 1 (6 row(s) affected) MEMORYBROKER_FOR_COMMITTED (internal) Pages ---------------------------------------- ----------- Allocations 45012 Rate 0 Target Allocations 3897184 Future Allocations 0 Overall 3923717 Last Notification 1 (6 row(s) affected) MEMORYBROKER_FOR_XTP (internal) Pages ---------------------------------------- ----------- Allocations 2 Rate 0 Target Allocations 3852174 Future Allocations 0 Overall 3923717 Last Notification 1 (6 row(s) affected) Memory Broker Clerk (Buffer Pool) Pages ---------------------------------------- ---------------------- Total 3342 Simulated 0 Simulation Benefit 0 Internal Benefit 0 External Benefit 0 Value Of Memory 0 Periodic Freed 0 Internal Freed 0 (8 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator.
15 People are following this question.
