January 10, 2017 at 8:45 pm
My laptop with 8G memory installs SQL Server 2014 EXPRESS for 64bit. A workstation with 32G memory also installs SQL Server 2014 EXPRESS. Both computers have the same database, on which I make a data analysis.
I run a query in the workstation, then It shows a Error "There is insufficient memory available in the buffer pool". When I run the same query in the laptop, however, It works well.
Both SQL Server 2014 use default settings.
In addtion, Task Manager in Window 7 shows only 5G memory are used when the workstation show the error.
Please help me find any cue leading the workstation being out of memory.
January 11, 2017 at 7:12 am
qjt (1/10/2017)
My laptop with 8G memory installs SQL Server 2014 EXPRESS for 64bit. A workstation with 32G memory also installs SQL Server 2014 EXPRESS. Both computers have the same database, on which I make a data analysis.I run a query in the workstation, then It shows a Error "There is insufficient memory available in the buffer pool". When I run the same query in the laptop, however, It works well.
Both SQL Server 2014 use default settings.
In addtion, Task Manager in Window 7 shows only 5G memory are used when the workstation show the error.
Please help me find any cue leading the workstation being out of memory.
The mere difference in RAM size will have the OS working rather differently, as it has a lot more "room to play with". It might choose to use a much larger fragment of the total, leaving SQL Server without quite as much resource to work with... but without SQL Profiler showing you exactly what's going on, that's just a mildly educated guess. You also made no mention of hard drive configuration or size for both machines. It's also possible that you "think" you have an "identical" install, but actually don't. Without a lot more detail, it's hard to know.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 11, 2017 at 7:52 am
Thanks, SSCrazy
I run a command "DBCC MEMORYSTATUS "
It shows a list of use of memory
Available Physical Memory28611772416
Available Virtual Memory8778531528704
Available Paging File62421553152
Working Set2031136768
Percent of Committed Memory in WS100
Page Faults522152
System physical memory high1
System physical memory low0
Process physical memory low0
Process virtual memory low0
VM Reserved16753168
VM Committed1950752
Locked Pages Allocated0
Large Pages Allocated0
Emergency Memory1024
Emergency Memory In Use16
Target Committed28059488
Current Committed1950752
Pages Allocated114648
Pages Reserved0
Pages Free1733072
Pages In Use177736
Page Alloc Potential31623496
NUMA Growth Phase0
Last OOM Factor9
Last OS Error0
Can you find some cues from this list?
January 11, 2017 at 8:05 am
qjt (1/11/2017)
Thanks, SSCrazyI run a command "DBCC MEMORYSTATUS "
It shows a list of use of memory
Available Physical Memory28611772416
Available Virtual Memory8778531528704
Available Paging File62421553152
Working Set2031136768
Percent of Committed Memory in WS100
Page Faults522152
System physical memory high1
System physical memory low0
Process physical memory low0
Process virtual memory low0
VM Reserved16753168
VM Committed1950752
Locked Pages Allocated0
Large Pages Allocated0
Emergency Memory1024
Emergency Memory In Use16
Target Committed28059488
Current Committed1950752
Pages Allocated114648
Pages Reserved0
Pages Free1733072
Pages In Use177736
Page Alloc Potential31623496
NUMA Growth Phase0
Last OOM Factor9
Last OS Error0
Can you find some cues from this list?
That doesn't really say much. It doesn't tell me anything about what other differences there might be between the two machines, nor does it tell me what else is installed on each. Nor does it show the same values from the other machine, nor do you even say which machine that came from. Given the values, my guess is that it's from the workstation as opposed to the laptop. Again, however, just that info isn't really much to go on. There are too many other factors to consider.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 27, 2017 at 10:06 am
I am also working on a similar issue but with a little more complication:
DBAs installed SQL Server Standard on machines with 96GB and 128GB memory
Allowed SQL Server to take as much memory as possible.
Now index builds give 802 error but NOT always!.
Same index builds are fine on machines with 32GB and 24GB memory.
Its strange that the machine with more memory gives the error.
On analysis I see the machines have two memory nodes (0,1,64) compared to machines where it works with only 24GB and 32GB which have only one memory node (0,64).
locked_page_allocations_kb and pages_kb from the DMV sys.dm_os_memory_nodes show more than 64GB allocated
on 128GB machine it shows
node locked_page_allocations_kb pages_kb
0 55GB 17GB
1 57GB 55GB
on 96GB machine it shows
node locked_page_allocations_kb pages_kb
0 39GB 38GB
1 39GB 39GB
I know that folks will say - reduce the max memory to < 64GB and retry.
What I want to know is why the error happens only sometimes and not always.
October 27, 2017 at 10:10 am
rkrish999 - Friday, October 27, 2017 10:06 AMI am also working on a similar issue but with a little more complication:DBAs installed SQL Server Standard on machines with 96GB and 128GB memory
Allowed SQL Server to take as much memory as possible.
Now index builds give 802 error but NOT always!.
Same index builds are fine on machines with 32GB and 24GB memory.
Its strange that the machine with more memory gives the error.
On analysis I see the machines have two memory nodes (0,1,64) compared to machines where it works with only 24GB and 32GB which have only one memory node (0,64).
locked_page_allocations_kb and pages_kb from the DMV sys.dm_os_memory_nodes show more than 64GB allocated
on 128GB machine it shows
node locked_page_allocations_kb pages_kb
0 55GB 17GB
1 57GB 55GBon 96GB machine it shows
node locked_page_allocations_kb pages_kb
0 39GB 38GB
1 39GB 39GBI know that folks will say - reduce the max memory to < 64GB and retry.
What I want to know is why the error happens only sometimes and not always.
As you posted in a SQL Server 2014 forum is it safe to assume that SQL Server 2014 was installed?
October 27, 2017 at 10:33 am
yes its SQL Server 2014 Standard
October 27, 2017 at 10:45 am
rkrish999 - Friday, October 27, 2017 10:33 AMyes its SQL Server 2014 Standard
Not knowing what all else may be running on each of the servers, I would consider setting the max memory for SQL Server about 20% lower than memory installed on each server and see if the problem persists.
You need to be sure the OS has enough memory to do its job. Starving the OS of memory can cause a variety of issues.
October 27, 2017 at 11:11 am
Thanks we will try that.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply