The New SQL Server 2016 Edition Limits

Question

The New SQL Server 2016 Edition Limits

Steve Jones - SSC Editor

SSC Guru

Points: 734552
More actions
January 26, 2017 at 12:13 am

#318182

Comments posted to this topic are about the item The New SQL Server 2016 Edition Limits

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

Being on the development, as opposed to administration, side I heavily rely not only on others knowledge and expertise but also analysis of hardware requirements. I am certainly glad that those people that I rely on are helped out with these limits on SE as I am being given access to the Enterprise features.

Is the additional memory restricted to be used for the feature allocated it e.g. are the two caches isolated with separate limits for a server with a 128GB buffer pool cache and a 32GB Columnstore cache?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 2

90% of our SQL Servers here are simply needed to store databases in and query data... basic and simple stuff. As I look to all of the servers we still have running SQL 2008R2 and talk to my boss about upgrading them and attempt to look at the sticker shock of Standard Edition with the number of CORES now with the licensing change it is going to really drive up the cost of upgrading. We don't need the extra features from Enterprise put into Standard Edition.

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 3

Gary Varga - Thursday, January 26, 2017 1:56 AM
Being on the development, as opposed to administration, side I heavily rely not only on others knowledge and expertise but also analysis of hardware requirements. I am certainly glad that those people that I rely on are helped out with these limits on SE as I am being given access to the Enterprise features.
Is the additional memory restricted to be used for the feature allocated it e.g. are the two caches isolated with separate limits for a server with a 128GB buffer pool cache and a 32GB Columnstore cache?

Yes. These are now three caches, essentially. One for buffer pool, one for columnstore cache (32GB), and one per database for In-Memory table data (32GB per). This comes out of the total memory allocation for SQL, so if you set max memory to 128GB, you are sharing that among these three caches.

I don't know how that allocation takes place, but it's shared.

The other thing to be wary of is that there is other memory SQL needs to process work itself. This is cumbersome to track and understand, but part of the memory allocated to Windows is used here.

Gary Varga SSC Guru Points: 82166 More actions · Answer 4

Thanks Steve.

I didn't make this clear but in my scenario if I have only used 1GB for Columnstore caching will I be able to use more than 128GB for the buffer pool cache if max memory is set to 160GB?

Or is the memory allocation isolated to use?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Eric M Russell SSC Guru Points: 125524 More actions · Answer 5

I work for a national retail chain, and we have 2000+ installations of SQL Server Express edition deployed at store locations, and just last year we finally got all the locations standardized on SQL Server 2012. However, I wish now we had waited a few months, because even the lowly Express Edition 2016 has all these cool new features too. In addition to data compression (which is great when you're limited to a 10 GB database size), being able to leverage the new native security features (ie: TDE, data masking, column encryption) rather than relying on 3rd party addons will be a game changer functionally and economically.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Steve Jones - SSC Editor SSC Guru Points: 734552 More actions · Answer 6

Gary Varga - Thursday, January 26, 2017 8:47 AM
Thanks Steve.
I didn't make this clear but in my scenario if I have only used 1GB for Columnstore caching will I be able to use more than 128GB for the buffer pool cache if max memory is set to 160GB?
Or is the memory allocation isolated to use?

Isolated to use. The buffer pool only ever gets to 128GB, whether you use columnstore or not. If you use columnstore, you can only ever cache 32GB, no matter what the memory settings are.

Eric M Russell SSC Guru Points: 125524 More actions · Answer 7

I still recall the day back in 1997 when I first saw that awesome new Sybase server with it's 500 (MB) of memory. It was such a leap forward from the 640 (KB) PCs on which I started coding FoxPro database applications back in 1992.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho