Database Administration, Databases, DP-800, Microsoft Certification

Recommend database configurations (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Optimize database performance
      --> Recommend database configurations

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Proper database configuration is one of the most effective ways to achieve high performance, scalability, availability, and cost efficiency. Even well-designed databases and optimized queries can perform poorly if the underlying database configuration is not appropriate for the workload.

The DP-800: Developing AI-Enabled Database Solutions exam expects candidates to understand how to recommend database configurations for SQL Server, Azure SQL Database, Azure SQL Managed Instance, Microsoft Fabric SQL Database, and other SQL-based data platforms. Rather than simply changing code, developers should be able to identify when performance issues can be addressed through configuration changes involving compute resources, storage, memory, indexing strategies, concurrency, automatic tuning, and database compatibility settings.

A well-configured database should balance:

  • Performance
  • Scalability
  • Security
  • High availability
  • Cost
  • Maintainability

Why Database Configuration Matters

Database configuration directly affects:

  • Query execution speed
  • Transaction throughput
  • Concurrent user capacity
  • AI workload responsiveness
  • Resource utilization
  • Operational costs
  • System reliability

Poor configurations can result in:

  • Long-running queries
  • Excessive locking
  • Deadlocks
  • High CPU utilization
  • Memory pressure
  • Storage bottlenecks
  • Increased cloud costs

Understand the Workload

Before recommending a configuration, identify the workload characteristics.

Questions include:

  • Is the workload transactional (OLTP)?
  • Is it analytical (OLAP)?
  • Is it mixed?
  • Is it AI-enabled?
  • How many concurrent users exist?
  • What is the expected database size?
  • Is low latency required?
  • Are workloads predictable or bursty?

Understanding the workload guides all subsequent configuration decisions.

Choose the Appropriate SQL Platform

Microsoft offers several SQL deployment options.

SQL Server

Best for:

  • On-premises deployments
  • Complete administrative control
  • Highly customized environments

Developer considerations:

  • Hardware sizing
  • Memory configuration
  • Storage layout
  • Backup strategy

Azure SQL Database

Best for:

  • Cloud-native applications
  • Fully managed environments
  • Elastic scaling
  • Minimal administration

Features include:

  • Automatic tuning
  • Automatic backups
  • Built-in high availability
  • Automatic patching

Azure SQL Managed Instance

Best for:

  • Existing SQL Server applications
  • High compatibility
  • Managed platform
  • Near full SQL Server feature support

Microsoft Fabric SQL Database

Best for:

  • Analytics
  • AI-enabled workloads
  • Integrated Microsoft Fabric solutions
  • Modern cloud-native architectures

Compute Configuration

Choosing the proper compute tier significantly affects performance.

Azure SQL offers multiple purchasing models.

DTU Model

Combines:

  • CPU
  • Memory
  • Storage I/O

into a single performance unit.

Advantages:

  • Simple sizing
  • Easier cost estimation

Disadvantages:

  • Less granular control

vCore Model

Separates:

  • CPU
  • Memory
  • Storage

Advantages:

  • More flexibility
  • Better workload tuning
  • Easier migration from SQL Server

The DP-800 exam generally emphasizes the vCore model because it provides greater control over resource allocation.

Service Tiers

Azure SQL Database supports multiple service tiers.

General Purpose

Suitable for:

  • Typical business applications
  • Moderate workloads
  • Cost-sensitive deployments

Business Critical

Provides:

  • Low latency
  • Faster storage
  • Multiple replicas
  • High availability

Ideal for:

  • Mission-critical applications
  • High transaction workloads

Hyperscale

Designed for:

  • Very large databases
  • Rapid storage growth
  • Read scale-out
  • High-performance cloud workloads

Serverless vs. Provisioned Compute

Serverless

Advantages:

  • Auto-scaling
  • Auto-pausing
  • Cost savings
  • Ideal for intermittent workloads

Suitable for:

  • Development environments
  • Departmental applications
  • Variable workloads

Provisioned

Advantages:

  • Predictable performance
  • Always available
  • Consistent response times

Suitable for:

  • Production systems
  • High-volume applications
  • Mission-critical workloads

Storage Configuration

Storage performance greatly affects database responsiveness.

Recommendations include:

  • Premium SSD storage
  • Sufficient IOPS
  • Low latency
  • Adequate capacity planning

Avoid running databases near storage limits.

TempDB Configuration (SQL Server)

TempDB supports:

  • Temporary tables
  • Sort operations
  • Hash joins
  • Version store
  • Snapshot isolation

Best practices include:

  • Multiple TempDB data files
  • Equal file sizes
  • Fast storage
  • Proper autogrowth settings

Although Azure SQL manages TempDB automatically, understanding these concepts remains valuable.

Database Compatibility Level

SQL Server compatibility levels determine optimizer behavior and available features.

Newer compatibility levels provide:

  • Improved query optimization
  • New T-SQL features
  • Better cardinality estimation
  • Performance enhancements

However, compatibility changes should be tested because query plans may change.

Automatic Tuning

Azure SQL Database supports automatic tuning features.

These include:

  • CREATE INDEX
  • DROP INDEX
  • FORCE LAST GOOD PLAN

Benefits include:

  • Improved query performance
  • Reduced manual administration
  • Automatic regression correction

Developers should understand when automatic tuning is appropriate and how to monitor its recommendations.

Intelligent Query Processing

Recent SQL Server versions include Intelligent Query Processing (IQP).

Features include:

  • Memory Grant Feedback
  • Batch Mode on Rowstore
  • Scalar UDF Inlining
  • Table Variable Deferred Compilation
  • Parameter Sensitive Plan Optimization

These features improve query performance without requiring application changes.

Configure Appropriate Indexes

Configuration recommendations often involve indexing.

Common index types include:

  • Clustered indexes
  • Nonclustered indexes
  • Filtered indexes
  • Columnstore indexes
  • XML indexes
  • Spatial indexes
  • Full-text indexes

Recommendations depend on workload characteristics.

For example:

OLTP systems benefit primarily from clustered and nonclustered indexes, while analytical workloads often benefit from columnstore indexes.

Partition Large Tables

Partitioning improves manageability and can improve query performance when queries access only specific partitions.

Benefits include:

  • Faster maintenance
  • Improved archiving
  • Reduced I/O
  • Partition elimination

Partitioning is especially useful for:

  • Sales history
  • Audit logs
  • Time-series data
  • IoT data

Optimize Concurrency

Database configuration affects concurrent users.

Recommendations include:

  • Appropriate transaction isolation levels
  • Snapshot Isolation
  • Read Committed Snapshot Isolation (RCSI)
  • Short transactions
  • Efficient indexing

Reducing blocking improves application scalability.

Configure Memory Usage

Memory influences:

  • Buffer cache
  • Query execution
  • Sort operations
  • Hash joins
  • Plan cache

For SQL Server:

Configure:

  • Maximum Server Memory
  • Minimum Server Memory

Avoid allowing SQL Server to consume all available system memory.

Azure SQL manages memory automatically.

Configure Database Files

Best practices include:

  • Multiple data files for very large databases
  • Appropriate autogrowth settings
  • Fixed-size growth increments
  • Avoid very small autogrowth values
  • Separate data and log files (SQL Server)

Poor autogrowth settings can increase fragmentation.

Statistics Configuration

Query optimization depends heavily on statistics.

Recommendations include:

  • Enable AUTO_CREATE_STATISTICS
  • Enable AUTO_UPDATE_STATISTICS
  • Update statistics after major data changes

Outdated statistics frequently result in poor execution plans.

High Availability Configuration

Configuration should match business requirements.

Options include:

  • Always On Availability Groups
  • Azure SQL built-in HA
  • Geo-replication
  • Auto-failover groups
  • Read replicas

Choose configurations based on:

  • Recovery Time Objective (RTO)
  • Recovery Point Objective (RPO)

AI Workload Considerations

AI-enabled applications often perform:

  • Vector searches
  • Embedding generation
  • Semantic search
  • Retrieval-Augmented Generation (RAG)
  • JSON processing

Recommendations include:

  • Sufficient memory
  • Fast storage
  • Columnstore indexes for analytics
  • Azure AI Search integration
  • Read replicas for heavy query workloads

Monitor Before Recommending Changes

Performance recommendations should be evidence-based.

Useful monitoring tools include:

  • Query Store
  • Execution Plans
  • Azure Monitor
  • SQL Insights
  • Dynamic Management Views (DMVs)
  • Performance Dashboard
  • Extended Events
  • Intelligent Insights (Azure SQL)

Common Configuration Mistakes

Avoid:

  • Choosing Business Critical for low-volume applications
  • Underprovisioning CPU
  • Ignoring storage latency
  • Disabling automatic statistics
  • Excessive indexing
  • Using outdated compatibility levels without testing
  • Poor TempDB configuration
  • Unlimited autogrowth
  • Ignoring Query Store recommendations
  • Not monitoring workload trends

Best Practices

  • Size resources based on workload characteristics.
  • Prefer the vCore purchasing model when granular control is needed.
  • Enable automatic tuning where appropriate.
  • Monitor Query Store regularly.
  • Keep statistics current.
  • Configure indexes based on workload patterns.
  • Test compatibility level changes before production deployment.
  • Use Business Critical only when required.
  • Consider serverless compute for intermittent workloads.
  • Use Hyperscale for very large databases.
  • Continuously monitor performance and adjust configurations.

DP-800 Exam Tips

Remember these key points for the exam:

  • Understand when to recommend General Purpose, Business Critical, or Hyperscale service tiers.
  • Know the differences between DTU and vCore purchasing models.
  • Understand when serverless compute is appropriate.
  • Automatic tuning can create indexes, remove unused indexes, and correct query regressions.
  • Query Store is one of the primary tools for identifying performance problems.
  • Statistics and indexes are fundamental to query optimization.
  • Compatibility level influences the query optimizer and available SQL features.
  • Database recommendations should always be based on observed workload characteristics and performance metrics.

Practice Exam Questions

Question 1

A database experiences unpredictable traffic during business hours but is often idle overnight. Which Azure SQL compute option is likely to provide the best balance between performance and cost?

A. Business Critical with maximum vCores

B. Hyperscale

C. Serverless compute

D. Dedicated SQL Server on a virtual machine

Answer: C

Explanation: Serverless compute automatically scales resources and can pause during periods of inactivity, reducing costs while still supporting variable workloads.

Question 2

A company requires extremely low latency and high availability for a mission-critical online transaction processing (OLTP) application. Which Azure SQL service tier should be recommended?

A. General Purpose

B. Business Critical

C. Basic

D. Serverless

Answer: B

Explanation: Business Critical uses local SSD storage, multiple replicas, and built-in high availability, making it ideal for latency-sensitive, mission-critical workloads.

Question 3

Which Azure SQL purchasing model provides independent control over CPU, memory, and storage resources?

A. DTU

B. Elastic Pool

C. vCore

D. Consumption

Answer: C

Explanation: The vCore model allows independent configuration of compute and storage resources, making it suitable for workload-specific optimization.

Question 4

Which SQL Server feature automatically recommends creating or dropping indexes and can force the last known good execution plan?

A. SQL Server Agent

B. Query Notifications

C. Extended Events

D. Automatic Tuning

Answer: D

Explanation: Automatic Tuning can recommend and apply index changes and automatically correct certain query regressions by forcing a previously successful execution plan.

Question 5

A developer notices that query execution plans are using outdated data distribution estimates after a large data import. Which recommendation is most appropriate?

A. Disable Query Store

B. Shrink the database

C. Update database statistics

D. Reduce TempDB size

Answer: C

Explanation: Accurate statistics help the query optimizer estimate row counts correctly and generate efficient execution plans.

Question 6

Which feature should be reviewed first when investigating consistently slow queries in Azure SQL Database?

A. SQL Server Configuration Manager

B. Query Store

C. Windows Event Viewer

D. Azure Key Vault

Answer: B

Explanation: Query Store captures execution plans, runtime statistics, and query history, making it one of the best tools for diagnosing performance problems.

Question 7

A database stores several years of sales history, but most queries retrieve only recent records. Which configuration recommendation can improve performance and simplify maintenance?

A. Disable indexing

B. Reduce available memory

C. Partition the table by date

D. Increase transaction isolation to SERIALIZABLE

Answer: C

Explanation: Partitioning large tables by date enables partition elimination, reducing I/O and improving maintenance operations such as archiving.

Question 8

Which database configuration recommendation helps reduce blocking while supporting high levels of concurrent read activity?

A. Enable Read Committed Snapshot Isolation (RCSI)

B. Disable indexes

C. Increase autogrowth frequency

D. Force table scans

Answer: A

Explanation: RCSI uses row versioning, allowing readers to access consistent data without blocking writers, thereby improving concurrency.

Question 9

A development team is selecting a compatibility level for a SQL Server database. What is the primary benefit of using a newer compatibility level after proper testing?

A. It automatically encrypts all database data.

B. It enables newer query optimizer improvements and T-SQL features.

C. It eliminates the need for indexes.

D. It disables Query Store.

Answer: B

Explanation: Newer compatibility levels introduce optimizer enhancements, improved cardinality estimation, and access to newer T-SQL functionality. Testing is important because execution plans may change.

Question 10

A database administrator configures SQL Server with unrestricted memory usage on a shared server hosting several applications. What is the most likely recommendation?

A. Continue using the default settings.

B. Increase TempDB file count only.

C. Disable automatic statistics.

D. Configure Maximum Server Memory to reserve memory for the operating system and other applications.

Answer: D

Explanation: Configuring Maximum Server Memory prevents SQL Server from consuming all available system memory, helping maintain overall server stability and ensuring sufficient resources remain available for the operating system and other applications.

Go to the DP-800 Exam Prep Hub main page

Leave a comment