Domain 3 of 5 · Chapter 3 of 3

Configure database solutions for optimal 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:

  • Matching a performance change to its scope
  • Measuring rowstore fragmentation and page density
  • Reorganizing versus rebuilding a rowstore index
  • Online and resumable index rebuilds
  • Maintaining columnstore indexes
  • What a statistics object describes
  • How automatic statistics updates are triggered
  • Updating statistics on demand
  • Choosing the scope of DBCC CHECKDB
  • Detecting damaged pages with PAGE_VERIFY
  • Running integrity checks within resource limits
  • Responding to an integrity error
  • Automatic tuning options by platform
  • How automatic tuning applies and verifies changes
  • When automatic index management does not act
  • Server memory settings
  • Instance-level parallelism settings
  • Optimize for ad hoc workloads
  • How Resource Governor classifies sessions
  • Resource pool CPU and memory limits
  • Workload group request limits
  • Database-scoped MAXDOP and parallelism precedence
  • Database-scoped optimizer switches
  • Clearing cached plans at the smallest scope
  • Delayed transaction durability
  • AUTO_CLOSE and AUTO_SHRINK
  • Scaling the resource that is actually constrained
  • What happens during a scale operation
  • Intelligent query processing as a feature family
  • Replacing fixed row guesses with actual row counts
  • Scalar UDF inlining
  • Query processing feedback features
  • Adaptive joins
  • Batch mode on rowstore
  • Dispatcher plans for parameter-sensitive queries
  • Optimized plan forcing
  • Approximate query processing
  • Performance problems and the change that addresses each

Performance settings by scope

ScopeWhat you changeExampleWhere you can set itReach and side effects
StatementA query hint on one statementOPTION (MAXDOP 2)SQL Server, Azure SQL Database, Managed InstanceOnly that statement; a MAXDOP hint is still capped by the workload group's MAX_DOP
ObjectOne index or one statistics objectALTER INDEX IX_Orders ON dbo.Orders REORGANIZESQL Server, Azure SQL Database, Managed InstanceREORGANIZE does not update statistics; a rebuild needs space for two copies of the index
DatabaseDatabase-scoped configurations, database SET options, and automatic tuningALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4SQL Server 2016 and later, Azure SQL Database, Managed InstanceOne database; the statement clears that database's plan cache
Workload groupResource Governor classifier function, workload groups, and resource poolsCREATE WORKLOAD GROUP ReportGroup WITH (MAX_DOP = 2) USING ReportPoolSQL Server and Managed Instance, not Azure SQL DatabaseSessions the classifier assigns to the group, for the lifetime of each session
InstanceServer configuration options set with sp_configureEXEC sp_configure 'max server memory', 4096; RECONFIGURE;SQL Server; Managed Instance for options such as max degree of parallelismEvery database on the instance; changing options such as max degree of parallelism clears the plan cache
Compute and storageService objective, vCores, and maximum data sizeALTER DATABASE [db1] MODIFY (SERVICE_OBJECTIVE = 'S3')Azure SQL Database and Managed InstanceThe whole database, elastic pool, or managed instance; open connections end at the switchover

Decision tree

Capacity still the limitafter tuning?YesCompute andstorageNoOnly one statementneeds the change?YesStatementNoCause is one index orstatistics object?YesObjectNoOnly one databaseneeds the change?YesDatabaseNoOne workload needsits own limits?YesWorkload groupNoInstance

Cheat sheet

  • Reorganization favors online incremental work while rebuilding recreates the index
  • Page density and fragmentation must be evaluated together before maintenance
  • A resumable rebuild can preserve progress across maintenance windows
  • Columnstore maintenance addresses deleted rows and undersized rowgroups
  • Statistics updates can target the needed object and sampling precision
  • Asynchronous statistics updates let compilation proceed with existing statistics
  • Incremental statistics avoid rescanning unchanged partitions
  • A statistics histogram describes the first column rather than every column in the object
  • Automatic statistics refresh is driven by data changes and query use
  • Frequent physical checks complement rather than replace full integrity checks
  • A known-good restore is preferred to CHECKDB repair
  • Integrity-check execution must account for resource use and platform support
  • Automatic tuning options are not identical across Azure SQL platforms
  • Automatic tuning validates its changes and reverses actions that do not help
  • Automatic index recommendations have workload and safety boundaries
  • SQL Server memory bounds must leave room for the host and other consumers
  • MAXDOP limits parallel task width rather than the number of concurrent queries
  • The cost threshold controls consideration of parallel plans, not elapsed-time limits
  • Optimize for ad hoc workloads reduces the cache footprint of single-use batches
  • Database-scoped MAXDOP localizes a parallelism policy to one database
  • Database-scoped optimizer switches can isolate behavior without lowering compatibility
  • Plan-cache invalidation should evict only the plans that require recompilation
  • Delayed durability is appropriate only when loss of acknowledged commits is tolerable
  • Automatic database resource reclamation needs an operational justification
  • Resource Governor policies follow the session's workload classification
  • A resource pool's CPU maximum under contention differs from its hard cap
  • Workload-group settings constrain requests without changing every database or query
  • Workload-group contention policies distinguish admission limits from relative priority
  • Resource scaling should address the measured constraint rather than any slow query
  • An online Azure SQL scale operation can still interrupt connections at switchover
  • Table-variable deferred compilation uses the initial execution's actual row count
  • Interleaved execution improves downstream planning for eligible multi-statement TVFs
  • Scalar UDF inlining exposes eligible function work to relational optimization
  • Cardinality and parallelism feedback learn different improvements for recurring queries
  • Optimized plan forcing reduces compilation work for recurring forced queries
  • An Adaptive Join selects between hash and nested loops using observed input rows
  • Batch mode on rowstore can accelerate eligible analytical work without a columnstore index
  • Memory-grant feedback adjusts future grants rather than repairing a running execution
  • Parameter Sensitive Plan optimization retains variants for skewed parameter values
  • Optional parameter optimization separates filtered and unfiltered parameter states
  • Approximate aggregates favor analytical responsiveness when exact results are unnecessary

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

References

  1. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option
  2. https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query
  3. https://learn.microsoft.com/en-us/azure/azure-sql/database/scale-resources
  4. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  5. https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store
  6. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes
  7. https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor
  8. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql
  9. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql
  10. https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-data-loading-guidance
  11. https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics
  12. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level
  13. https://learn.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql
  14. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options
  15. https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql
  16. https://learn.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview
  17. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql
  18. https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview
  19. https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview
  20. https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios
  21. https://learn.microsoft.com/en-us/azure/azure-sql/database/logical-servers
  22. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-automatic-tuning-options-transact-sql
  23. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options
  24. https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql
  25. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option
  26. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option
  27. https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide
  28. https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-classifier-function
  29. https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-resource-pool
  30. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-workload-group-transact-sql
  31. https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/tempdb-space-resource-governance
  32. https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing
  33. https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-details
  34. https://learn.microsoft.com/en-us/sql/relational-databases/logs/control-transaction-durability
  35. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-resource-stats-azure-sql-database
  36. https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-logical-server
  37. https://learn.microsoft.com/en-us/azure/azure-sql/database/purchasing-models
  38. https://learn.microsoft.com/en-us/azure/azure-sql/database/single-database-scale
  39. https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-service-objectives-azure-sql-database
  40. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=azuresqldb-current
  41. https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage
  42. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/management-operations-overview
  43. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-operation-status-azure-sql-database
  44. https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining
  45. https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-memory-grant-feedback
  46. https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-cardinality-estimation-feedback
  47. https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-degree-parallelism-feedback
  48. https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins
  49. https://learn.microsoft.com/en-us/sql/relational-databases/performance/optional-parameter-optimization
  50. https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization
  51. https://learn.microsoft.com/en-us/sql/relational-databases/performance/optimized-plan-forcing-query-store
  52. https://learn.microsoft.com/en-us/sql/t-sql/functions/approx-count-distinct-transact-sql
  53. https://learn.microsoft.com/en-us/sql/t-sql/functions/approx-percentile-cont-transact-sql