August 25, 2015 at 10:53 pm
We encounter the following memory error 3-5 times per day on a SQL 2012 11.0.5058.0 (X64) instance:
Failed allocate pages: FAIL_PAGE_ALLOCATION 1
Question: Does the following excerpt of the MEMORYSTATUS dump identify the origin of my issue? I'm interested to know if the large size of CACHESTORE_PHDR indicates a memory leak.
Thanks
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 446242426880
Available Virtual Memory 8543012360192
Available Paging File 472072433664
Working Set 780689408
Percent of Committed Memory in WS 100
Page Faults 1930383
System physical memory high 1
System physical memory low 0
Process physical memory low 1
Process virtual memory low 0
Memory Manager KB
---------------------------------------- ----------
VM Reserved 245277964
VM Committed 1379428
Locked Pages Allocated 19592104
Large Pages Allocated 1271808
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 20971536
Current Committed 20971536
Pages Allocated 19005616
Pages Reserved 9528
Pages Free 130112
Pages In Use 19923152
Page Alloc Potential -208
NUMA Growth Phase 2
Last OOM Factor 6
Last OS Error 0
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------- ----------
VM Reserved 207962964
VM Committed 1048576
Locked Pages Allocated 429968
SM Reserved 0
SM Committed 0
Pages Allocated 956904
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4344
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 310152
CACHESTORE_PHDR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16576296
August 25, 2015 at 11:22 pm
Quick questions, what are the memory specs (hardw.,vm,provisioning)? What are the sql server's mem configs?
😎
August 31, 2015 at 9:44 am
Physical - Not VM
Model : ProLiant DL580 G7
CPU : 4x10
Memory : 512 GB
OS : Windows 2008R2 ENT
Max Server Memory = 20 GB
Since posting on this site, I increased the memory allocation for this SQL instance to 40 GB. This eliminated the FAIL ALLOCATION event, and prevented SQL from hanging. Tracing the CACHESTORE_PHDR clerk yielded something interesting. It briefly grows from 38 MB to 17GB and then shrinks to less than 1 MB.
Timestamp size_kb
================ =======
2015-08-31 02:20:45.651 328
2015-08-31 02:20:30.649 17075664
2015-08-31 02:20:15.648 12631408
2015-08-31 02:20:00.642 38472
PHDR grows likes this 4-times per day at the same times each day. The following message appears in the SQL log each time PHDR grows large:
"The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions..."
Questions:
How may I identify the offending query?
Is this expected SQL behavior?
September 2, 2015 at 12:33 am
What else is running on this server apart from the SQL Server? Why such a meagre memory configuration if the server has 512Gb of RAM? Are you RDP'ing on to the box? What would you run in such sessions? Is the server running SSIS, SSRS or SSAS?
😎
September 2, 2015 at 5:52 am
It's a big shared environment with 9 other instances of SQL Server are running. Approximately 200 GB of physical memory remains unallocated at this time. No BI tools or workloads are supported by this cluster.
September 2, 2015 at 5:56 am
Steve Cornwell-278514 (9/2/2015)
It's a big shared environment with 9 other instances of SQL Server are running. Approximately 200 GB of physical memory remains unallocated at this time. No BI tools or workloads are supported by this cluster.
What are the memory configurations of the other instances? Does the sum of the max memory for all of them exceed the physical memory?
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply