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
| Scope | What you change | Example | Where you can set it | Reach and side effects |
|---|---|---|---|---|
| Statement | A query hint on one statement | OPTION (MAXDOP 2) | SQL Server, Azure SQL Database, Managed Instance | Only that statement; a MAXDOP hint is still capped by the workload group's MAX_DOP |
| Object | One index or one statistics object | ALTER INDEX IX_Orders ON dbo.Orders REORGANIZE | SQL Server, Azure SQL Database, Managed Instance | REORGANIZE does not update statistics; a rebuild needs space for two copies of the index |
| Database | Database-scoped configurations, database SET options, and automatic tuning | ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4 | SQL Server 2016 and later, Azure SQL Database, Managed Instance | One database; the statement clears that database's plan cache |
| Workload group | Resource Governor classifier function, workload groups, and resource pools | CREATE WORKLOAD GROUP ReportGroup WITH (MAX_DOP = 2) USING ReportPool | SQL Server and Managed Instance, not Azure SQL Database | Sessions the classifier assigns to the group, for the lifetime of each session |
| Instance | Server configuration options set with sp_configure | EXEC sp_configure 'max server memory', 4096; RECONFIGURE; | SQL Server; Managed Instance for options such as max degree of parallelism | Every database on the instance; changing options such as max degree of parallelism clears the plan cache |
| Compute and storage | Service objective, vCores, and maximum data size | ALTER DATABASE [db1] MODIFY (SERVICE_OBJECTIVE = 'S3') | Azure SQL Database and Managed Instance | The whole database, elastic pool, or managed instance; open connections end at the switchover |
Decision tree
Cheat sheet
Unlock with Premium — includes all practice exams and the complete study guide.
References
- https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option
- https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query
- https://learn.microsoft.com/en-us/azure/azure-sql/database/scale-resources
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store
- https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes
- https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-data-loading-guidance
- https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level
- https://learn.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options
- https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql
- https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios
- https://learn.microsoft.com/en-us/azure/azure-sql/database/logical-servers
- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-automatic-tuning-options-transact-sql
- https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options
- https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-freeproccache-transact-sql
- https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option
- https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option
- https://learn.microsoft.com/en-us/sql/relational-databases/query-processing-architecture-guide
- https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-classifier-function
- https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-resource-pool
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-workload-group-transact-sql
- https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/tempdb-space-resource-governance
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-details
- https://learn.microsoft.com/en-us/sql/relational-databases/logs/control-transaction-durability
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-resource-stats-azure-sql-database
- https://learn.microsoft.com/en-us/azure/azure-sql/database/resource-limits-logical-server
- https://learn.microsoft.com/en-us/azure/azure-sql/database/purchasing-models
- https://learn.microsoft.com/en-us/azure/azure-sql/database/single-database-scale
- https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-service-objectives-azure-sql-database
- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql?view=azuresqldb-current
- https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/management-operations-overview
- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-operation-status-azure-sql-database
- https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-memory-grant-feedback
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-cardinality-estimation-feedback
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-degree-parallelism-feedback
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/optional-parameter-optimization
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization
- https://learn.microsoft.com/en-us/sql/relational-databases/performance/optimized-plan-forcing-query-store
- https://learn.microsoft.com/en-us/sql/t-sql/functions/approx-count-distinct-transact-sql
- https://learn.microsoft.com/en-us/sql/t-sql/functions/approx-percentile-cont-transact-sql