June 30, 2014 at 10:09 am
We have a SQL 2012 SP1 running 64bit in Window 2008. We are reviewing a possible memory leak in the server. Checking the SQL Log files, we noticed the following messages:
014-05-24 05:35:50.98 spid79 Error: 701, Severity: 17, State: 123. 2014-05-24 05:35:50.98 spid79 There is insufficient system memory in resource pool 'default' to run this query.
2014-05-24 05:35:51.23 spid95 Error: 701, Severity: 19, State: 1. 2014-05-24 05:35:51.23 spid95 There is insufficient system memory in resource pool 'default' to run this query.
2014-05-24 05:35:51.23 spid95 Error: 701, Severity: 19, State: 1. 2014-05-24 05:35:51.23 spid95 There is insufficient system memory in resource pool 'default' to run this query.
2014-05-24 05:35:51.23 spid95 Error: 701, Severity: 19, State: 1. 2014-05-24 05:35:51.23 spid95 There is insufficient system memory in resource pool 'default' to run this query.
2014-05-24 05:35:51.23 spid95 Error: 701, Severity: 19, State: 1. 2014-05-24 05:35:51.23 spid95 There is insufficient system memory in resource pool 'default' to run this query.
2014-05-24 05:35:51.23 spid95 Error: 701, Severity: 19, State: 1. 2014-05-24 05:35:51.23 spid95 There is insufficient system memory in resource pool 'default' to run this query.
2014-05-24 05:35:51.24 spid95 Error: 701, Severity: 19, State: 1. 2014-05-24 05:35:51.24 spid95 There is insufficient system memory in resource pool 'default' to run this query.
2014-05-24 05:37:00.84 spid96 DBCC TRACEON 2861, server process ID (SPID) 96. This is an informational message only; no user action is required.
2014-05-24 05:40:22.06 spid65 Failed allocate pages: FAIL_PAGE_ALLOCATION 1 2014-05-24 05:40:22.06 spid65
Process/System Counts Value ---------------------------------------- ---------- Available Physical Memory 14477295616
Available Virtual Memory 8539624042496
Available Paging File 47662735360
Working Set 1311162368
Percent of Committed Memory in WS 100
Page Faults 18727143
System physical memory high 1
System physical memory low 0
Process physical memory low 1
Process virtual memory low 0
2014-05-24 05:40:22.06 spid65
Memory Manager KB ---------------------------------------- ---------
Reserved 249818876
VM Committed 913296
Locked Pages Allocated 14186600
Large Pages Allocated 165888
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 15099904
Current Committed 15099896
Pages Allocated 13122664
Pages Reserved 1551872
Pages Free 232624
Pages In Use 14817160
Page Alloc Potential -472248
NUMA Growth Phase 2
Last OOM Factor 1
Last OS Error 0
2014-05-24 05:40:22.06 spid65
Memory node Id = 0 KB ---------------------------------------- ----------
VM Reserved 249818812
VM Committed 913276
Locked Pages Allocated 14186600
Pages Allocated 13122664
Pages Free 232624
Target Committed 15099880
Current Committed 15099880
Foreign Committed 0
Away Committed 0
Taken Away Committed 0 2
014-05-24 05:40:22.06 spid65
Memory node Id = 64 KB ---------------------------------------- ----------
VM Reserved 0
VM Committed 20
Locked Pages Allocated 0
2014-05-24 05:40:22.06 spid65
MEMORYCLERK_SQLGENERAL (node 0) KB ---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 50904
2014-05-24 05:40:22.06 spid65
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB ---------------------------------------- ---------- VM Reserved 12695676 VM Committed 65536 Locked Pages Allocated 340960 SM Reserved 0 SM Committed 0 Pages Allocated 1603648 2014-05-24 05:40:22.06 spid65
MEMORYCLERK_SQLQUERYEXEC (node 0) KB ---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 224
2014-05-24 05:40:22.06 spid65
MEMORYCLERK_SQLOPTIMIZER (node 0) KB ---------------------------------------- ---------- V
M Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 11001888
2014-05-24 05:40:22.06 spid65
MEMORYCLERK_SQLUTILITIES (node 0) KB ---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 784
2014-05-24 05:40:22.06 spid65
MEMORYCLERK_SQLSTORENG (node 0) KB ---------------------------------------- ----------
VM Reserved 98880
VM Committed 0
Locked Pages Allocated 98880
SM Reserved 0
SM Committed 0
Pages Allocated 37984
2014-05-24 05:40:22.06 spid65
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB ---------------------------------------- ----------
2014-06-22 00:36:41.36 spid14s FlushCache: cleaned up 115225 bufs with 9490 writes in 114053 ms (avoided 227287 new dirty bufs) for db 38:0
2014-06-22 00:36:41.38 spid14s average throughput: 7.89 MB/sec, I/O saturation: 11407, context switches 21541
2014-06-22 00:36:41.38 spid14s last target outstanding: 46, avgWriteLatency 15 2014-06-22 00:39:17.25 spid14s FlushCache: cleaned up 148718 bufs with 8848 writes in 90018 ms (avoided 4920 new dirty bufs) for db 38:0
2014-06-22 00:39:17.25 spid14s average throughput: 12.91 MB/sec, I/O saturation: 9322, context switches 18881
2014-06-22 00:39:17.25 spid14s last target outstanding: 192, avgWriteLatency 36 2014-06-22 00:44:20.11 spid14s FlushCache: cleaned up 142962 bufs with 7858 writes in 125304 ms (avoided 267545 new dirty bufs) for db 38:0 2014-06-22 00:44:20.11 spid14s average throughput: 8.91 MB/sec, I/O saturation: 9664, context switches 18276
2014-06-22 00:44:20.11 spid14s last target outstanding: 352, avgWriteLatency 50
The SQL Instance is running with Log Shipping, taking a backup of tranlog every 15 mins. Server memory is at 32000 (MB) with 8 processor. Currently the server is at 94% of Memory Utlization. SQL is pinned to minimum 25600MB to maximum 27648MB.
Can anyone help me in determining if there is a possible memory leak issue? What other things I can check to verify if there is an issue with the memory or IO?
June 30, 2014 at 1:42 pm
That's caused by a long running checkpoint operation, where the data gets written to disk from memory. It's not an indication of a memory leak. It's an indication of large, possibly slow, writes to disk. You need to look to your disks and disk sub-system to understand what's happening. I would also strongly suggest capturing wait statistics. You can use sys.dm_os_wait_stats for that. You need to know why the server is running slow and that's what that defines. For other metrics that show how performance is behaving, I suggest reading this article[/url].
"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
June 30, 2014 at 6:18 pm
They rewrote the memory management system for SQL 2012 and there were definitely bugs. I would check the post SP1 CUs and/or SP2 for additional issues that may not be fixed in SP1. Honestly you should probably migrated to newer build sooner rather than later - there are a LOT of REALLY important bug fixes post SP1!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 30, 2014 at 7:14 pm
Be careful with the SPs in 2012. Both SP1 and SP2 have a bug that can cause data loss when performing index rebuilds online.
There are CUs (CU9 for SP1 and via this download for sp2 http://blogs.msdn.com/b/sqlreleaseservices/archive/2014/06/20/fix-for-index-corruption-issue-now-available-for-sql-server-2012-service-pack-2.aspx) that you must apply if you install the SP and you are on Enterprise edition.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 1, 2014 at 7:59 am
SQLRNNR (6/30/2014)
Be careful with the SPs in 2012. Both SP1 and SP2 have a bug that can cause data loss when performing index rebuilds online.There are CUs (CU9 for SP1 and via this download for sp2 http://blogs.msdn.com/b/sqlreleaseservices/archive/2014/06/20/fix-for-index-corruption-issue-now-available-for-sql-server-2012-service-pack-2.aspx) that you must apply if you install the SP and you are on Enterprise edition.
SQL 2012 SP2 has a hotfix to fix that bug too (as does SQL 2014, which is also exposed to it!): http://support.microsoft.com/kb/2969896
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply