Configure resources for scale and performance
Unlock the complete study guide + 25 practice exams totaling 1,500 questions.
Bundled into the existing Administering Microsoft Azure SQL Solutions premium course — no separate purchase.
14-day refund guarantee on one-time purchases and your first subscription charge.
Included in this chapter:
- Find the scaling scope before you scale
- Serverless compute: vCore range and billing
- Serverless auto-pause and auto-resume
- Provisioned scaling: a service objective change
- Fit the target: storage, tempdb, and feature limits
- Elastic pools: one shared budget, two kinds of limit
- Azure SQL Database service tiers and Hyperscale
- Read scale-out with ApplicationIntent=ReadOnly
- In-Memory OLTP: table durability and object choice
- Managed Instance scaling is an instance operation
- Managed Instance tiers and Next-gen General Purpose
- Managed Instance memory and flexible memory
- Managed Instance storage and structural limits
- Instance pools for many small managed instances
- Stop and start a General Purpose managed instance
- SQL Server VM size: memory, topology, and licensing
- SQL Server VM disks: two limits on every I/O
- Host caching and Write Accelerator by file role
- tempdb on the ephemeral temporary disk
- Storage Spaces striping and pool expansion
- Table partitioning: function, scheme, and table
- Partitioned indexes: alignment and unique keys
- Boundary maintenance with SPLIT and MERGE
- Partition switching with ALTER TABLE SWITCH
- Partition-level lock escalation
- Row and page compression
- Compression settings per object and partition
- Columnstore archival compression
- The COMPRESS function for selected values
- Estimate compression savings before rebuilding
- Practice scenarios: matching the lever to the limit
Scale levers by scaling scope
| Scaling scope | What you resize | Example setting or command | What resizing this scope does not fix |
|---|---|---|---|
| Single database (Azure SQL Database) | Service objective (DTUs or vCores), maximum data size, or serverless minimum and maximum vCores | az sql db update ... --service-objective S2 | A feature the target tier lacks, such as columnstore below Standard S3 or memory-optimized tables outside Premium and Business Critical |
| Elastic pool (Azure SQL Database) | Pool eDTUs or vCores, pool storage, and per-database minimum and maximum | Max vCores per database | A database at the per-database maximum, if only the pool's eDTUs or vCores are raised |
| Managed instance (SQL Managed Instance) | vCores, memory, reserved storage in multiples of 32 GB, service tier, and hardware | Set-AzSqlInstance ... -Edition BusinessCritical | Structural limits such as 100 user databases or 280 files on classic General Purpose |
| SQL Server VM (Azure Virtual Machines) | VM size, disk type and performance, host caching, and Storage Spaces pools | Host caching read-only for data disks, none for log disks | Faster disks alone cannot bypass the existing VM's uncached IOPS or throughput limit |
| Table or index (all three platforms) | Partition function and scheme, and data compression per heap, index, or partition | ALTER TABLE ... REBUILD WITH (DATA_COMPRESSION = PAGE) | A CPU shortage: compression adds CPU work |
Decision tree
Cheat sheet
Unlock with Premium — includes all practice exams and the complete study guide.
References
- https://learn.microsoft.com/en-us/azure/azure-sql/database/single-database-scale
- https://learn.microsoft.com/en-us/azure/azure-sql/database/scale-resources
- https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-storage
- https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu
- https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/in-memory-oltp-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/read-scale-out
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
- https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-sql-database-vcore
- https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-billing
- https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-create-configure
- https://learn.microsoft.com/en-us/cli/azure/choose-the-right-azure-command-line-tool
- https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-auto-scaling
- https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-auto-pause-resume
- https://learn.microsoft.com/en-us/cli/azure/sql/db
- https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy
- https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage
- https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale
- https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-dtu-single-databases
- https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-vcore-elastic-pools
- https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale-replicas
- https://learn.microsoft.com/en-us/azure/azure-sql/database/hyperscale-named-replica-configure
- https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/overview-and-usage-scenarios
- https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison
- https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/introduction-to-memory-optimized-tables
- https://learn.microsoft.com/en-us/powershell/module/az.sql/set-azsqlinstance
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/management-operations-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/service-tiers-managed-instance-vcore
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/service-tiers-next-gen-general-purpose-use
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/instance-pools-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/instance-stop-start-how-to
- https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-vm-size
- https://learn.microsoft.com/en-us/azure/virtual-machines/disks-types
- https://learn.microsoft.com/en-us/azure/virtual-machines/premium-storage-performance
- https://learn.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes
- https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes
- https://learn.microsoft.com/en-us/sql/t-sql/functions/partition-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-partition-function-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-partition-scheme-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/row-compression-implementation
- https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/page-compression-implementation
- https://learn.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql