Condividi tramite

SQL Server 2016 SP1: Know your limits

  • Articolo

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

  • Anonymous
    December 09, 2016
    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.thanksStephen
    • Anonymous
      December 09, 2016
      Hi Stephen, can you elaborate on why that would be useful?
      • Anonymous
        December 12, 2016
        The comment has been removed
        • Anonymous
          December 12, 2016
          The comment has been removed
      • Anonymous
        December 12, 2016
        The comment has been removed
        • Anonymous
          December 12, 2016
          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.
          • Anonymous
            December 12, 2016
            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
            • Anonymous
              December 12, 2016
              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).
              • Anonymous
                February 24, 2017
                The comment has been removed
  • Anonymous
    December 09, 2016
    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.
    • Anonymous
      December 12, 2016
      I'll refer to this connect request from 2009 with 162 voteshttps://connect.microsoft.com/SQLServer/Feedback/Details/496380but with the SP1 changes I think we're much closer now :-)
  • Anonymous
    December 12, 2016
    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.
    • Anonymous
      December 12, 2016
      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 !!!
      • Anonymous
        December 12, 2016
        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 ?
        • Anonymous
          December 12, 2016
          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 + 232GB = 224GB). Now if other caches like plan cache, CLR, backup buffers need 20GB of memory, total memory required will be (128GB + 32GB+ 232GB+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.
  • Anonymous
    December 12, 2016
    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?
  • Anonymous
    February 24, 2017
    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
  • Anonymous
    March 21, 2017
    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?
    • Anonymous
      May 03, 2017
      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.
  • Anonymous
    June 14, 2017
    The comment has been removed
    • Anonymous
      September 26, 2018
      Just want to check if there is any plan to change Standard Edition memory limit in SQL SERVER 2019?Thanks,Vikas Rana
      • Anonymous
        September 26, 2018
        Please stay tuned to our release announcements for SKU related features in the future.
        • Anonymous
          September 27, 2018
          Sure, thanks Amit.
  • Anonymous
    November 16, 2018
    Hi Parikshit Savjani,I'm using SQL Server 2016 Developer Edition and I am seeing a maximum of 1GB of memory used locally, even when I do:sp_configure 'min server memory (MB)', 2048;GORECONFIGURE;GOI've tried to find an exact explanation of this behavior. The only thing I can find online is that the Windows Internal Database uses SQL Server Express Edition, but SELECT SERVERPROPERTY('Edition') says I am on Developer Edition (64-bit)This caught me off guard big time. I still cannot make sense of it, but this is the ONLY blog post I can find that claims or discusses word-for-word that Developer Edition is only limited to OS available memory.
    • Anonymous
      November 17, 2018
      Hi John!Setting min server memory does not cause SQL Server to automatically grab that much memory. When you start SQL Server, it will acquire memory as-needed until it reaches either max server memory, or until the available memory on the server falls below a certain threshold. If there is memory pressure on the server, SQL Server will trim its memory footprint to give memory back to the OS to prevent paging, but it will never trim below min server memory.The amount of memory SQL Server acquires is based on the workload running on the server. If you don't have any activity on the server, it's not going to take that much memory. Since this is a Developer instance, it's possible that it just doesn't need more than 1GB of memory. Other reasons for not acquiring more memory would be other processes on the server using up memory so there isn't enough available for SQL Server, or if you're on a VM, the host could be overcommitted and the VM can't get as much memory as it's been configured to take.Hope that helps!