SQL Server 2016 SP1: Know your limits

With the recent announcement of SQL Server 2016 SP1, we announced the consistent programmability experience for developers and ISVs, who can now maintain a single code base and build intelligent database applications which scale across all the editions of SQL Server. The processor, memory and database size limits does not change and remain as–in all editions as documented in the SQL Server editions page. We have made the following changes in our documentation to accurately reflect the memory limits on lower editions of SQL Server. This blog post is intended to clarify and provide more information on the memory limits starting with SQL Server 2016 SP1 on Standard, Web and Express Editions of SQL Server.

Features SQL Server 2016 SP1 Enterprise SQL Server 2016 SP1 Standard SQL Server 2016 SP1 Web SQL Server 2016 SP1 Express SQL Server 2016 SP1 Developer
Scale Maximum number of cores Unlimited 24 cores 16 cores 4 cores Unlimited
Memory: Maximum memory utilized per instance buffer pool size per instance Operating system max 128 GB 64 GB 1410 MB Operating system max
*(NEW) Memory: Maximum columnstore cache Operating system max 32 GB 16 GB 352 MB Operating system max
*(NEW) Memory: Maximum in-memory data Operating system max 32 GB 16 GB 352 MB Operating system max
Maximum database size 524 PB 524 PB 524 PB 10 GB 524 PB

Understanding the Memory limits for lower editions of SQL Server

In the past, the memory limits defined in table above in the SQL Server editions page for lower editions were referred to as Maximum memory utilized per instance which did not accurately reflect the true limits. The memory limit defined for lower editions of SQL Server is essentially Maximum buffer pool memory limit. In simpler terms, buffer pool memory in SQL Server is a memory cache responsible for caching data and index pages for the entire instance. The rest of the caches in the SQL Server memory (procedure cache, thread stack, backup buffers etc) consumes memory outside buffer pool memory. The memory consumed by caches outside buffer pool is not restricted by above memory limits and can grow up to limits defined by "max server memory". This is not specific to SQL Server 2016 SP1 and is also applicable to earlier releases of SQL Server as well.

Essentially, SQL Server in lower editions can consume and utilize memory outside the maximum buffer pool memory limit defined in the table above due to memory consumed by other caches.

What changed in SQL Server 2016 SP1?

On all lower editions of SQL Server 2016 starting with SP1, we have memory quota limits for In-Memory OLTP data and Columnstore segment cache which is in addition to the maximum buffer pool memory limits described earlier. The following table lists those limits:

The limits for In-Memory OLTP data is per database. If you would like to estimate memory size required for your data, please refer to Estimate Memory Requirements for Memory-Optimized Tables.

The limits for Columnstore segment cache is per SQL Server instance across all the databases in the instance.

Like earlier versions of SQL Server, there are still no quota limits defined for caches other than buffer pool, In-Memory OLTP data and Columnstore segment cache. The memory consumed by other caches will still be solely restricted by max server memory (if capped) on SQL Server or memory on the server if max server memory is uncapped.

For example: A Standard Edition of SQL Server has buffer pool memory limited to 128GB, so the data and index pages cached in buffer pool is limited by 128GB. Starting with SQL Server 2016 SP1, you can have an additional 32GB of memory for Columnstore segment cache per instance and an additional 32GB of memory quota for In-Memory OLTP per database. In addition, there can be memory consumed by other memory consumers in SQL Server which will be limited by "max server memory" or total memory on the server if max server memory is uncapped.

Although the buffer pool, In-Memory OLTP data and Columnstore segments memory are restricted by memory quota in lower editions, if the max server memory is capped lower than the limits defined earlier, SQL Server will honor the max server memory limits defined on the instance.

Enterprise Edition of SQL Server

SQL Server Enterprise Edition continues to be our highly differentiated edition of SQL Server when customers are ready to take their business to the next level where performance, scale and availability of the database is important.

For Enterprise Edition of SQL Server, there are no processor, memory, virtualization or scale limitations as defined in SQL Server editions page. With Windows Server 2016, SQL Server 2016 now supports up to 24TB, 128TB VAS space and 512 logical processors. The following table lists the new logical processor and memory limits supported by Windows Server 2016. For more details on Windows Server 2016 scale limits, please refer to our Windows Server blog.

 

The following Error log captured from Windows Server 2016 with 24TB of RAM and 16 sockets with 24 cores per socket (384 Logical processors) shows how SQL Server 2016 scales with Windows Server 2016.

2016-09-08 08:25:28.73 Server      Microsoft SQL Server 2016 (SP1-CTP) - 13.0.3645.0 (X64)
Apr 29 2016 23:23:58
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows Server 2016 Datacenter 6.3 <X64> (Build 14393: )
2016-09-08 08:25:28.73 Server      UTC adjustment: -7:00
2016-09-08 08:25:28.74 Server      (c) Microsoft Corporation.
2016-09-08 08:25:28.74 Server      All rights reserved.
2016-09-08 08:25:28.74 Server      Server process ID is 14864.
2016-09-08 08:25:28.74 Server      Authentication mode is MIXED.
2016-09-08 08:25:28.74 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2016-09-08 08:25:28.75 Server      The service account is 'SQL\Administrator'. This is an informational message; no user action is required.
2016-09-08 08:25:28.75 Server      Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
-T 834
2016-09-08 08:25:28.78 Server      SQL Server detected 16 sockets with 24 cores per socket and 24 logical processors per socket, 384 total logical processors; using 384 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2016-09-08 08:25:28.78 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2016-09-08 08:25:28.78 Server      Detected 25165643 MB of RAM. This is an informational message; no user action is required.
2016-09-08 08:25:28.78 Server      Using large pages in the memory manager.

For detailed feature differentiation across editions of SQL Server 2016 SP1, please refer to Editions and Supported Features for SQL Server.

SQL Server 2016 SP1 is a preferred choice of database engine for businesses of all sizes. If you are Developer/ISV, you can start developing on free Developer Edition of SQL Server 2016. If you are a small business, you can start with the free Express Edition of SQL Server. As your business grows and perf, scale and availability requirements of database grows, you can upgrade to Standard Edition of SQL Server. When running mission critical OLTP workloads, high velocity IOT transactions, and high volume data warehousing with advanced analytics, you can choose to upgrade to Enterprise Edition of SQL Server with Windows Server 2016 on modern hardware or migrate to Azure.

These are exciting times to be part of the SQL Server journey which supports businesses of all sizes and allows developers/ISVs to build applications which scale across all editions and cloud. SQL Server v.Next further allows customers to deploy SQL Server on the platform of their choice: Linux, Windows, Docker, on-premises or in the cloud.

Parikshit Savjani
Senior Program Manager (@talktosavjani)