Applies to: Azure SQL Managed Instance
This article provides an overview of the technical characteristics and resource limits for Azure SQL Managed Instance, and provides information about how to request an increase to these limits.
Hardware configuration characteristicsSQL Managed Instance has characteristics and resource limits that depend on the underlying infrastructure and architecture. SQL Managed Instance can be deployed on multiple hardware generations.
Hardware generations have different characteristics, as described in the following table:
Feature Standard-series (Gen5) Premium-series Memory optimized premium-series CPU Intel® E5-2673 v4 (Broadwell) 2.3 GHz, Intel® SP-8160 (Skylake), and Intel® 8272CL (Cascade Lake) 2.5-GHz processors Intel® 8370C (Ice Lake) 2.8-GHz processors Intel® 8370C (Ice Lake) 2.8-GHz processors Number of vCores1 Deploying a 2-vCore instance is only possible inside an instance pool.
2 Dependent on the number of vCores.
3 The following regions can provide 16 TB of storage, while other regions limit available storage to 5.5 TB.
4 16 TB for classic General Purpose. 32 TB only for Next-gen General Purpose service tier (preview)
Note
If your workload requires storage sizes greater than the available resource limits for Azure SQL Managed Instance, consider the Azure SQL Database Hyperscale service tier.
Regional supports for memory optimized premium-series hardware and for premium-series hardware with 16-TB storageSupport for the premium-series hardware with 16-TB storage has the same availability as support for the memory optimized premium-series hardware. To learn more, review Regional supports for memory optimized premium-series hardware and for premium-series hardware with 16-TB storage.
In-memory OLTP available spaceThe amount of In-memory OLTP space in Business Critical service tier depends on the number of vCores and hardware configuration. The following table lists the limits of memory that can be used for In-memory OLTP objects.
vCores Standard-series (Gen5) Premium-series Memory optimized premium-series 4 vCores 3.14 GB 4.39 GB 8.79 GB 6 vCores - 6.59 GB 15.32 GB 8 vCores 6.28 GB 8.79 GB 22.06 GB 10 vCores - 12.11 GB 30.94 GB 12 vCores - 15.43 GB 39.82 GB 16 vCores 15.77 GB 22.06 GB 57.58 GB 20 vCores - 28.70 GB 75.34 GB 24 vCores 25.25 GB 35.34 GB 93.09 GB 32 vCores 37.94 GB 53.09 GB 128.61 GB 40 vCores 52.23 GB 73.09 GB 164.13 GB 48 vCores - 95.34 GB 199.64 GB 56 vCores - 117.58 GB 244.13 GB 64 vCores 99.9 GB 139.82 GB 288.61 GB 80 vCores 131.68 GB 184.30 GB 288.61 GB 96 vCores N/A 184.30 GB 288.61 GB 128 vCores N/A 184.30 GB 288.61 GB Service tier characteristicsSQL Managed Instance has two service tiers: General Purpose and Business Critical. You can choose to use the upgraded Next-gen General Purpose service tier (preview).
Important
The Business Critical service tier provides an additional built-in copy of the SQL Managed Instance (secondary replica) that can be used for read-only workload. If you can separate read-write queries and read-only/analytic/reporting queries, you're getting twice the vCores and memory for the same price. The secondary replica might lag a few seconds behind the primary instance, so it's designed to offload reporting/analytic workloads that don't need exact current state of data. In the following table, read-only queries are the queries that are executed on secondary replica.
Number of vCores Hardware generation General Purpose Next-gen General Purpose Business Critical Standard-series (Gen5) 21 , 4, 8, 16, 24, 32, 40, 64, 80 4, 8, 16, 24, 32, 40, 64, 80 4, 8, 16, 24, 32, 40, 64, 80 Premium-series 21 , 4, 8, 16, 24, 32, 40, 64, 80 4, 6, 8, 10, 12, 16, 20, 24, 32, 40, 48, 56, 64, 80, 96, 128 4, 6, 8, 10, 12, 16, 20, 24, 32, 40, 48, 56, 64, 80, 96, 128 Memory optimized premium-series 4, 8, 16, 24, 32, 40, 64, 80 4, 6, 8, 10, 12, 16, 20, 24, 32, 40, 48, 56, 64, 80, 96, 128 4, 6, 8, 10, 12, 16, 20, 24, 32, 40, 48, 56, 64, 80, 96, 1281 Deploying a 2-vCore instance is only possible inside an instance pool.
Max memory Hardware generation General Purpose Next-gen General Purpose Business Critical Standard-series (Gen5) 20.4 GB - 408 GB1 The memory-to-vCore ratio is only available up to 80 vCores for premium-series hardware, and 64 vCores for memory optimized premium-series. Maximum memory is capped at 560 GB for premium-series vCores above 80, and 870.4 GB for memory optimized premium-series vCores above 64.
Max instance storage size (reserved) Hardware generation General Purpose Next-gen General Purpose Business Critical Standard-series (Gen5) - 2 TB for 4 vCores1 Only the major regions can provide 16 TB of storage for the premium-series hardware for these CPU vCore numbers. Smaller regions limit available storage to 5.5 TB.
2 Only the major regions can provide 8 TB of storage for the premium-series memory optimized hardware for these CPU vCore numbers. Smaller regions limit available storage to 5.5 TB.
Feature comparison Feature General Purpose Next-gen General Purpose Business Critical Max database size Up to currently available instance size (depending on the number of vCores). Up to currently available instance size (depending on the number of vCores). Up to currently available instance size (depending on the number of vCores). Maxtempdb database size Limited to 24 GB/vCore (96 - 1,920 GB) and currently available instance storage size.
tempdb space.
tempdb space.
tempdb files 128 128 128 Max number of databases per instance 100 user databases, unless the instance storage size limit has been reached. 500 user databases 100 user databases, unless the instance storage size limit has been reached. Max number of database files 280 per instance, unless the instance storage size or Azure Premium Disk storage allocation space limit has been reached. 4,096 files per database 32,767 files per database, unless the instance storage size limit has been reached. Max data file size Maximum size of each data file is 8 TB. Use at least two data files for databases larger than 8 TB. Up to currently available instance size (depending on the number of vCores). Up to currently available instance size (depending on the number of vCores). Max log file size Limited to 2 TB and currently available instance storage size. Limited to 2 TB and currently available instance storage size. Limited to 2 TB and currently available instance storage size. Data/Log IOPS (approximate) 500 - 7500 per file
1 This is an average range. Although the vast majority of IO request durations will fall under the top of the range, outliers which exceed the range are possible.
Additional considerationsCurrently available instance storage size is the difference between reserved instance size and the used storage space.
Both data and log file size in the user and system databases are included in the instance storage size that is compared with the max storage size limit. Use the sys.master_files system view to determine the total used space by databases. Error logs aren't persisted and not included in the size. Backups aren't included in storage size.
Throughput and IOPS in the General Purpose tier also depends on the file size, and isn't explicitly limited by the SQL Managed Instance.
Max instance IOPS depend on the file layout and distribution of workload. As an example, if you create 7 x 1-TB files with max 5 K IOPS each and seven small files (smaller than 128 GB) with 500 IOPS each, you can get 38500 IOPS per instance (7x5000+7x500) if your workload can use all files. Some IOPS are also used for autobackups.
You can create another readable replica in a different Azure region using failover groups
Names of tempdbfiles can't have more than 16 characters.
Find more information about the resource limits in SQL Managed Instance pools in this article.
IOPSFor the Next-gen General Purpose and Business Critical service tiers, available IOPS are dictated by the number of vCores:
The following table lists the max IOPS available to each service tier based on the number of vCores:
Number of vCores Next-gen General Purpose Business Critical 4 6,400 16,000 6 9,600 24,000 8 12,800 32,000 10 16,000 40,000 12 19,200 48,000 16 25,600 64,000 20 32,000 80,000 24 38,400 96,000 32 51,200 128,000 40 64,000 160,000 48 76,800 192,000 56 80,000 224,000 64 80,000 256,000 80 80,000 320,000 96 80,000 320,000 128 80,000 320,000 File IO characteristics in General Purpose tierIn the General Purpose service tier, every database file gets dedicated IOPS and throughput that depend on the file size. Larger files get more IOPS and throughput. IO characteristics of database files are shown in the following table:
File size >=0 and <=129 GiB >129 and <=513 GiB >513 and <=1025 GiB >1025 and <=2049 GiB >2049 and <=4097 GiB >4097 GiB and <=8 TiB IOPS per file 500 2300 5000 7500 7500 7500 Throughput per file 100 MiB/s 150 MiB/s 200 MiB/s 250 MiB/s 250 MiB/s 250 MiB/sIf you notice high IO latency on some database file or you see that IOPS/throughput is reaching the limit, you might improve performance by increasing the file size.
There's also an instance-level limit on the max log write throughput (see the previous table for values, for example 22 MiB/s), so you might not be able to reach the max file throughout on the log file because you're hitting the instance throughput limit.
Data and log storageThe following factors affect the amount of storage used for data and log files, and apply to General Purpose and Business Critical tiers.
tempdb uses local SSD storage, and this storage cost is included in the vCore price.tempdb shares local SSD storage with data and log files, and tempdb storage cost is included in the vCore price.Important
In both service tiers, you're charged for the maximum storage size configured for a managed instance.
To monitor total consumed instance storage size for SQL Managed Instance, use the storage_space_used_mb metric. To monitor the current allocated and used storage size of individual data and log files in a database using T-SQL, use the sys.database_files view and the FILEPROPERTY(... , 'SpaceUsed') function.
Tip
Under some circumstances, you might need to shrink a database to reclaim unused space. For more information, see DBCC SHRINKFILE.
Backups and storageStorage for database backups is allocated to support the point-in-time restore (PITR) and long-term retention (LTR) capabilities of SQL Managed Instance. This storage is separate from data and log file storage, and is billed separately.
PITR: In General Purpose and Business Critical tiers, individual database backups are copied to read-access geo-redundant (RA-GRS) storage automatically. The storage size increases dynamically as new backups are created. The storage is used by full, differential, and transaction log backups. The storage consumption depends on the rate of change of the database and the retention period configured for backups. You can configure a separate retention period for each database between 1 to 35 days for SQL Managed Instance. A backup storage amount equal to the configured maximum data size is provided at no extra charge.
LTR: You also have the option to configure long-term retention of full backups for up to 10 years. If you set up an LTR policy, these backups are stored in RA-GRS storage automatically, but you can control how often the backups are copied. To meet different compliance requirements, you can select different retention periods for weekly, monthly, and/or yearly backups. The configuration you choose determines how much storage is used for LTR backups. For more information, see Long-term retention - Azure SQL Database and Azure SQL Managed Instance.
Note
The flexible memory feature is currently in preview.
By default, the amount of memory allocated to Azure SQL Managed Instance is a static value determined by the selected number of vCores. The flexible memory feature for the Next-gen General Purpose service tier allows you to change the amount of memory allocated to your managed instance without changing the number of vCores. This feature is useful for workloads that require more memory than the default allocation for a given number of vCores.
The flexible memory feature is currently only available to locally redundant instances in the Next-gen General Purpose service tier on Premium-series hardware.
You can change the amount of memory allocated to your managed instance at any time for new and existing instances by using the Azure portal, or the REST API. The memory allocation change is applied to all databases in the instance and performs a failover of the instance as the final operation step. Check management operations duration to determine the estimated time for the operation to complete.
Use the Compute + storage blade in the Azure portal to change the memory allocation, or the properties.memorySizeInGB value of the Managed Instance - Create or Update REST API call starting with the 2024-08-01-preview version. The memory allocation is specified in gigabytes (GB).
When allocating memory, you can choose between a minimum and maximum value, and also determine the ratio of memory to vCores. The minimum and maximum values are determined by the number of vCores selected for the instance. The memory-to-vCore ratio is a percentage of the total memory allocated to the instance.
The following table shows the minimum and maximum memory values for the flexible memory feature:
vCores Min RAM (GB) Max RAM (GB) Supported API RAM values Min ratio Max ratio Supported ratios 4 28 48 28, 32, 40, 48 7 12 7, 8, 10, 12 6 42 72 42, 48, 60, 72 7 12 7, 8, 10, 12 8 56 96 56, 64, 80, 96 7 12 7, 8, 10, 12 10 70 120 70, 80, 100, 120 7 12 7, 8, 10, 12 12 84 144 84, 96, 120, 144 7 12 7, 8, 10, 12 16 112 192 112, 128, 160, 192 7 12 7, 8, 10, 12 20 140 240 140, 160, 200, 240 7 12 7, 8, 10, 12 24 168 288 168, 192, 240, 288 7 12 7, 8, 10, 12 32 224 384 224, 256, 320, 384 7 12 7, 8, 10, 12 40 280 480 280, 320, 400, 480 7 12 7, 8, 10, 12 48 336 480 336, 384, 480 7 10 7, 8, 10 56 392 448 392, 448 7 8 7, 8 64 448 448 448 7 7 8 80 560 560 560 7 7 8 96 560 560 560 5.83 5.83 5.83 128 560 560 560 4.38 4.38 4.38 PricingWhen using the flexible memory feature, consider the following:
Billable memory = Total memory - default memory.For example, if you have a pay-as-you-go 4 vCore instance with 40 GB of memory, you are charged for:
SQL Managed Instance can be created only in supported regions. To create a SQL Managed Instance in a region that is currently not supported, you can send a support request via the Azure portal.
Supported subscription typesSQL Managed Instance currently supports deployment only on the following types of subscriptions:
Note
For the latest information on region availability for subscriptions, first check select a region.
Supported subscription types can contain a limited number of resources per region. SQL Managed Instance has two default limits per Azure region (that can be increased on-demand by creating a special support request in the Azure portal) depending on a type of subscription type:
Note
These limits are default settings and not technical limitations. The limits can be increased on-demand by creating a special support request in the Azure portal if you need more instances in the current region. As an alternative, you can create new instances of SQL Managed Instance in another Azure region without sending support requests.
The following table shows the default regional limits for supported subscription types (default limits can be extended using a support request):
Subscription type Default limit for SQL Managed Instance subnets Default limit for vCore units 1 CSP 16 (30 in some regions2) 960 (1440 in some regions2) EA 16 (30 in some regions2) 960 (1440 in some regions2) Enterprise Dev/Test 6 320 Pay-as-you-go 6 320 Pay-as-you-go Dev/Test 6 320 Azure Pass 3 64 BizSpark 3 64 BizSpark Plus 3 64 Microsoft Azure Sponsorship 3 64 Microsoft Partner Network 3 64 Visual Studio Enterprise (MPN) 3 64 Visual Studio Enterprise 3 32 Visual Studio Enterprise (BizSpark) 3 32 Visual Studio Professional 3 32 MSDN Platforms 3 321 In planning deployments, please take into consideration that Business Critical (BC) service tier requires four (4) times more vCore capacity than General Purpose (GP) service tier. For example: 1 GP vCore = 1 vCore unit and 1 BC vCore = 4 vCore. To simplify your consumption analysis against the default limits, summarize the vCore units across all subnets in the region where SQL Managed Instance is deployed and compare the results with the instance unit limits for your subscription type. Max number of vCore units limit applies to each subscription in a region. There's no limit per individual subnets except that the sum of all vCores deployed across multiple subnets must be lower or equal to max number of vCore units.
2 Larger subnet and vCore limits are available in the following regions: Australia East, East US, East US 2, North Europe, South Central US, Southeast Asia, UK South, West Europe, West US 2.
Important
In case your vCore and subnet limit is 0, it means that default regional limit for your subscription type isn't set. You can also use quota increase request for getting subscription access in specific region following the same procedure - providing required vCore and subnet values.
Request a quota increaseIf you need more instances in your current regions, send a support request to extend the quota using the Azure portal. For more information, see Request quota increases for Azure SQL Database and SQL Managed Instance.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4