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 (21)

  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).

              1. Andrew S says:

                Pedro,
                Not every company has the luxury of having a cut and dry distinction between Developers and DBAs. Also, sometimes DBA type tasks are built into applications to prevent the need to run them separately.

                As for your initial question “Hi Stephen, can you elaborate on why that would be useful?”, it would take you less than 1 minute to search the web to find hundreds (thousands?) of posts where people lament the fact that they were bitten by the lack of this ability. So, my answer to you is “because your customers have said so, over and over again for many years.” Or you could take the approach of “Maybe if we ignore our customers they will go away.”

                Regards,
                Andrew

  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/

  5. Andrew says:

    I am pretty sure the answer to this question is going to be “No, you cannot do that” but I want to make sure I am not being too restrictive in my interpretation. The license says “not for production environments or for use with production data”. That means that even though one can install it on a test/dev server, one has to use mock data, i.e. one cannot simply restore a production backup to test/dev in order to have a solid understanding of performance. Is that correct?

    I realize that many people will reply “you should not do that anyway because of security, blah blah blah” but if the data is not PCI or HIPAA protected or in some other way legally sensitive and your legal counsel is OK with it, it sure beats having to mock 10s or 100s of GB of data which won’t have the same distribution statistics as the real thing. Also, reality check, regardless of how careful your application is to avoid data that doesn’t make sense from a business standpoint, it WILL get into your database and mess with distribution statistics even more. Mocked (generated) data won’t be like that because whatever mechanism you use to generate the data will be instructed to generate “clean” data.

    To be sure, my second paragraph is not a complaint, just trying to stave off a flurry of comments about why one should not do that. The question to which I would appreciate an answer is described in the first paragraph.

    Regards,
    Andrew

  6. The limit of the SQL SERVER Express version is 10GB, however there is no restriction for databases model, master, etc.

    So these databases can exceed the limit of 10GB without breaking the terms of the Express version?

    1. Microsoft does not recommend creating users objects in systems databases (other than the ones created on TempDB). Creating user objects in the master database may create unwarranted and possibly unstable interference with this system database. If you do, master must be backed up more frequently. When you put user objects into master, you may be able to access them without database context. However, this is essentially unintended behavior – SQL Server will check for some objects (depending on the object name and the version of SQL Server) in the master database before checking the current database context. Do not design your processes around this behavior.

  7. jeremy roe says:

    Please modernize SQL 2016 SP1 Standard to:
    a. 256GB of Memory (512GB would be ideal)
    b. 32 cores (24 is not to common of a configuration and next year 32 core will become a standard)

    128GB is too low and has been in place for over 5 years. There are free alternatives organizations are turning to due to the high pricing and limitations. Thank you for considering.

Skip to main content