Domain 1 of 5 · Chapter 2 of 3

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 scopeWhat you resizeExample setting or commandWhat resizing this scope does not fix
Single database (Azure SQL Database)Service objective (DTUs or vCores), maximum data size, or serverless minimum and maximum vCoresaz sql db update ... --service-objective S2A 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 maximumMax vCores per databaseA 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 hardwareSet-AzSqlInstance ... -Edition BusinessCriticalStructural 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 poolsHost caching read-only for data disks, none for log disksFaster 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 partitionALTER TABLE ... REBUILD WITH (DATA_COMPRESSION = PAGE)A CPU shortage: compression adds CPU work

Decision tree

Limit inside onetable or index?Table or index(all platforms)YesNoRuns on a VMyou manage?SQL Server VMsize, disksYesNoResources set fora whole instance?Managed instancevCores, storageYesNoDatabases shareone budget?Elastic poolshared budgetYesNoSingle databaseserverless orprovisioned

Cheat sheet

  • Serverless minimum and maximum vCores bound elasticity and active compute cost
  • Serverless auto-pause requires both idleness and compatible feature settings
  • Provisioned SQL Database scaling must fit the target's performance and storage boundaries
  • Elastic pool sizing must accommodate concurrent compute demand and allocated storage
  • Pool capacity and per-database limits govern different resource boundaries
  • Business Critical provides local-storage performance and persistent In-Memory OLTP support
  • Hyperscale separates large database storage growth from compute scaling
  • Read-intent routing offloads reads but does not guarantee immediate visibility of primary writes
  • Memory-optimized table durability determines whether rows survive restart or failover
  • In-Memory OLTP object choice should match the source of processing overhead
  • Managed Instance scaling is an asynchronous instance-level management operation
  • Managed Instance compute and reserved storage require separate capacity decisions
  • Next-gen General Purpose makes instance I/O configurable without removing compute or feature limits
  • Instance pools reserve dedicated compute for separate small managed instances on shared infrastructure
  • Stopping an eligible General Purpose managed instance removes idle compute and license charges
  • Flexible memory increases eligible Managed Instance RAM without increasing vCores
  • Managed Instance structural limits require more than spare reserved storage
  • SQL VM storage performance is bounded by both the disks and the VM
  • SQL data and transaction-log disks need different caching and acceleration policies
  • Striped SQL VM storage needs its layout planned before the pool is created
  • SQL VM sizing must satisfy workload needs and supported scaling boundaries
  • Ephemeral local storage suits tempdb only when startup provisioning is reliable
  • Azure Elastic SAN suits consolidated SQL VM storage better than an isolated SQL workload
  • Partition functions assign rows while partition schemes assign storage
  • Aligned partitioned indexes require compatible boundaries and uniqueness keys
  • Sliding-window maintenance should split and merge empty boundary partitions
  • Partition switching reassigns existing storage rather than copying rows
  • Partition-level lock escalation requires the AUTO table option
  • Page compression adds prefix and dictionary savings to row compression
  • Compression settings belong to individual objects and partitions
  • Columnstore archival compression favors infrequently accessed data
  • Compression estimates predict storage savings rather than workload speed

Unlock with Premium — includes all practice exams and the complete study guide.

References

  1. https://learn.microsoft.com/en-us/azure/azure-sql/database/single-database-scale
  2. https://learn.microsoft.com/en-us/azure/azure-sql/database/scale-resources
  3. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-storage
  4. https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu
  5. https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview
  6. https://learn.microsoft.com/en-us/azure/azure-sql/database/in-memory-oltp-overview
  7. https://learn.microsoft.com/en-us/azure/azure-sql/database/read-scale-out
  8. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  9. https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-sql-database-vcore
  10. https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview
  11. https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-billing
  12. https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-create-configure
  13. https://learn.microsoft.com/en-us/cli/azure/choose-the-right-azure-command-line-tool
  14. https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-auto-scaling
  15. https://learn.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-auto-pause-resume
  16. https://learn.microsoft.com/en-us/cli/azure/sql/db
  17. https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy
  18. https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage
  19. https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale
  20. https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-dtu-single-databases
  21. https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-vcore-elastic-pools
  22. https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale-replicas
  23. https://learn.microsoft.com/en-us/azure/azure-sql/database/hyperscale-named-replica-configure
  24. https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/overview-and-usage-scenarios
  25. https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison
  26. https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/introduction-to-memory-optimized-tables
  27. https://learn.microsoft.com/en-us/powershell/module/az.sql/set-azsqlinstance
  28. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/management-operations-overview
  29. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits
  30. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/service-tiers-managed-instance-vcore
  31. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/service-tiers-next-gen-general-purpose-use
  32. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server
  33. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/instance-pools-overview
  34. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/instance-stop-start-how-to
  35. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-vm-size
  36. https://learn.microsoft.com/en-us/azure/virtual-machines/disks-types
  37. https://learn.microsoft.com/en-us/azure/virtual-machines/premium-storage-performance
  38. https://learn.microsoft.com/en-us/sql/relational-databases/partitions/create-partitioned-tables-and-indexes
  39. https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes
  40. https://learn.microsoft.com/en-us/sql/t-sql/functions/partition-transact-sql
  41. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-partition-function-transact-sql
  42. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-partition-scheme-transact-sql
  43. https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression
  44. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql
  45. https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/row-compression-implementation
  46. https://learn.microsoft.com/en-us/sql/relational-databases/data-compression/page-compression-implementation
  47. https://learn.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql
  48. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql