June 3, 2015 at 8:46 am
I seem to be experiencing a lot of memory pressure on my test SQL 2014 SP1 instance, as compared to SQL 2008 R2. Has anyone seen this behavior ? I have a side by side install of SQL 2008 R2 SP1 and SQL 2014 SP1, on the same hardware. I am running some baseline tests and the exact same workload runs on both instances(never simultaneously). Both instances have been allocated 12 GB of memory for this test. My SQL 2008 R2 completes the workload in approximately 20 hours, but the SQL 2014 SP1 craps out in many of my test cases citing insufficient memory. The box itself has plenty to spare - about 96 GB in total.
I would appreciate any thoughts or comments from people who have seen something similar. Thanks in advance.
June 3, 2015 at 12:07 pm
I've moved a few instances to 2014, and haven't really run into this sort of problem.
Having said that, one thing that is definitely different between the two is what maximum server memory controls. Starting in SQL Server 2012, maximum server memory also controls memory allocations for CLR and what were formerly multi-page allocations.
With that in mind, if the maximum server memory settings on each instance are the same, then the 2014 instance is effectively more limited than the 2008 R2 instance is. Whether that's actually the explanation of your issues is another thing.
Do you have any more detail on the exact errors/problems you're seeing?
Cheers!
June 3, 2015 at 12:39 pm
Hey Jacob ! Thanks for the reply. My tests are very specific(but very involved as well). There are a few thousand use cases that I run through with a powershell script. They are all run serially, one after another, so there is no memory contention between processes. When I run on SQL 2008 R2 for a benchmark, they all run fine and take about 20 hours to run thru all of them. When I run on SQL 2014 SP1 however, I get the error "There is insufficient memory available in the buffer pool" randomly on different use cases amongst the few thousand. The first time I got the error on one use case, I reran that particular use case in a loop a few dozen times to see if I could reproduce the error. I could not.
It's becoming pretty clear that there is some issue with the way SQL 2014 SP1 is handling its memory, but I'm not sure what. I will be running more iterations of my tests with some perfmon counters for both SQL 2008 r2 and sql 2014 and will compare the way its is handling memory.
By the way, I also ran into the CE issue with SQL 2014 as well :-). I am running all of my tests with the database compatibility set to 110.
May 14, 2016 at 6:08 am
I am also experiencing similar problem occasionally with our workload while running in SQL 2014 sp1 getting error: There is insufficient memory available in the buffer pool. I run the DBCC Memorystatus command and compared it with the memorystatus log generated by SQL when error had occurred but there are no major differences. I would appreciate if you can share any further findings.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply