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)

Comments (16)

  1. Now that the editions are so much more closely aligned can we have a switch for the Developer Edition which makes it act like Standard Edition (Memory limits, scalability features) as an alternative to it being equivalent to Enterprise Edition.

    thanks
    Stephen

    1. Hi Stephen, can you elaborate on why that would be useful?

      1. Richard says:

        Pedro Lopes, I think it would be useful so you can have one local developer instance which you can use to develop against multiple applications which have difference SQL editions behind them (e.g. one app has Enterprise Edition and another app has standard). This way you don’t have to remember what the differences are as the sql engine handles that for you.

        1. Hello Richard, the programmability surface area being the same, differing only in scale, a dev already can develop the same code base for Express (only CDC doesn’t apply to this one), standard and enterprise editions from a functional perspective.

      2. Peter says:

        Pedro isn’t that fairly obvious lets say you have Standard in production and you have 5 developers that are using the Developer Edition.
        Now they are testing and developing against Developer Edition and thats basically the same thing as developing against a Enterprise Edition.

        They might miss that feature X is missing in Standard and develop against it and then when they realize it they will have to start all over and find a solution that works with Standard..

        Now if there was a switch to limit Developer to the same feature set as Standard Edition that would never happen..

        1. Hello Peter, the programmability surface area being the same, a dev already can develop the same code for Express, Standard and Enterprise editions from a functional perspective. As you might see at https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released only CDC is not enabled in Express, but that is not a dev feature that precludes app development.

          1. Tim says:

            I think what Stephen is asking for with the switch to disable enterprise features on a developers laptop are to disallow things such as the TSQL that allows an online index rebuild (enterprise only) . Running the TSQL for this will work in developer edition and fail in
            Standard edition. Resource Governor and Transparent database encryption are also not supported in Standard edition but a developer / documentation writer in writing some change scripts to enable TDE will like to know the exact error messages that are produced when these scripts are run in the wrong edition.
            There is a DMV sys.dm_db_persisted_sku_features that lists some of these

            1. Thanks for the feedback Tim. I understand where you’re coming from and the scenario you describe. For clarification, the changes introduced with SQL 2016 SP1 do not cover DBA type of tasks/features as they are not tied to app dev, like operations (RG, TDE) and database maintenance (Online Indexing).

  2. Stephen, Great feedback. So this will allow you use the same developer instance to simulate the behavior or standard or even express edition when u are developing. You can use Resource Governor to limit cores, memory on to simulate the behavior of edition. I understand the memory cap for in-memory or columnstore is not available yet but wondering it can be a feasible workaround. Would you mind filing a connect feedback for this.

    1. I’ll refer to this connect request from 2009 with 162 votes

      https://connect.microsoft.com/SQLServer/Feedback/Details/496380

      but with the SP1 changes I think we’re much closer now 🙂

  3. Gordon Feeney says:

    Hi Parikshit, I’m somewhat confused by the above. I was always under the impression that the Buffer Pool (of which the Buffer Cache is a constituent part) contains the the data cache, plan cache and several other smaller caches, the most important ones being the buffer cache (also referred to as data cache) and procedure cache. Indeed BOL seems to bear that out: ‘max server memory controls the SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and clr memory (essentially any memory clerk found in sys.dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked server providers other than SQL Server, and any memory allocated by a non SQL Server DLL are not controlled by max server memory.’

    However the article above seems to go against that interpretation by stating that ‘the rest of the caches in the SQL Server memory (procedure cache, thread stack, backup buffers etc) consumes memory outside buffer pool memory’. Could you clarify or am I misunderstanding something fundamental here?

    Regards,

    Gordon Feeney.

    1. Hi Gordon, Let me try to simplify this for you to clear some of your confusion. Prior to SQL Server 2012, “max server memory” limited only the buffer pool memory and any cache (plan, CLR etc) which required less than 8KB of contiguous memory, it was stolen from buffer pool (single page allocation) but if any internal consumer required contiguous memory greater than 8KB (multi page allocation), it was outside buffer pool and also outside max server memory limits. Starting SQL Server 2012, we made changes in memory management in SQL Server such that, Buffer pool is no longer a donor of single page allocation or in other words, buffer pool memory is dedicated cache for data and index pages. Plan cache, CLR, Columnstore, In-Memory or any internal consumer which requires memory within SQL Server, it allocated from SQL memory but it doesn’t come from buffer pool but comes from SQL memory limited by max server memory. The only memory which doesn’t honor max server memory is worker threads and memory allocated by 3rd party dlls which doesn’t go through SQL memory clerks. Hope this clarifies !!!

      1. Alexandre Araujo says:

        Hello Parikshit, in situations with SQL2016 SP1 STD and columnstores reachs 32 GB, so it is not comes from buffer pool but from internal consumers. My question is about when columnstore`s memory reachs 32 GB outside buffer pool, the BPool lost 32 GB from 128 GB ?

        1. Hi Alexandre, The Columnstore memory is outside buffer pool and will not steal any memory from 128GB limit set for buffer pool provided you have sufficient RAM on your server. For example – if you have 256GB RAM on your server and max server memory is set to 230GB, in this case, buffer pool memory can go up to 128GB, while Columnstore cache can be go up to 32GB outside 128GB so total sql server memory can grow up to (128GB + 32GB)= 160GB. Now, if you have In-memory oltp for 2 of your database, your server memory can grow up to (128GB + 32GB + 2*32GB = 224GB). Now if other caches like plan cache, CLR, backup buffers need 20GB of memory, total memory required will be (128GB + 32GB+ 2*32GB+20 = 244GB) but your max server memory is limited to 230GB. In this case, there will be internal memory pressure where target server memory will be lowered below total server memory and some of the internal caches will be flushed (LRU) to accommodate the memory required.

  4. Kevin Boles says:

    The top chart should have “per database” for the in memory limit row. It completely fails to portray that, which is only clarified later in the article (thankfully in both text and chart).

    Also, I thought procedure cache and other memory needs were stolen from the buffer pool and at least for 2012+ (with it’s rewritten memory management system) were thus capped by and part of that same RAM limit?

    1. Hi Kevin, MSDN page is updated to accurately reflect per database and per instance memory limits.https://msdn.microsoft.com/en-us/library/cc645993(v=sql.130).aspx

      Start 2012+ – procedure cache is stolen from SQL Server memory controlled by max server memory but not from buffer pool. I recommend you read the following blog which might help clarify the changes in SQL Server 2012
      https://blogs.msdn.microsoft.com/sqlosteam/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012/

Skip to main content