DP-300 Configure and manage automation of tasks Practice Question
Your company uses Azure SQL Managed Instance for a critical OLTP workload. You need to automate index maintenance for all databases in the instance without downtime. The solution must minimize performance impact during business hours. Which approach should you use?
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Deploy Ola Hallengren’s IndexOptimize stored procedure in each database and schedule it via SQL Server Agent.
Azure SQL Managed Instance fully supports SQL Server Agent, which can schedule Ola Hallengren's IndexOptimize stored procedure for index maintenance across all databases. This approach allows centralized management, scheduling during off-peak hours, and granular control over parallelism and throttling to minimize performance impact. Option A is incorrect because Elastic Database Jobs are not available on Azure SQL Managed Instance; they are designed for Azure SQL Database. Option B is incorrect because Azure Logic Apps are not suited for executing T-SQL scripts across multiple databases with performance-sensitive throttling. Option C is incorrect because Azure Automation Runbooks would require complex orchestration and lack native scheduling and throttling capabilities compared to SQL Agent.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use Elastic Database Jobs to run index maintenance scripts on all databases during off-peak hours, with parallel execution throttled.
Why it's wrong here
Elastic Database Jobs are not supported on Azure SQL Managed Instance. They are designed for Azure SQL Database, not Managed Instance.
- ✗
Use Azure Logic Apps with a SQL connector to run index maintenance on each database, with retry policies.
Why it's wrong here
Azure Logic Apps are integration tools, not designed for running T-SQL scripts across multiple databases; they lack built-in throttling for index maintenance.
- ✗
Create an Azure Automation Runbook that connects to each database and runs index maintenance sequentially.
Why it's wrong here
Azure Automation Runbooks can connect to databases, but executing index maintenance sequentially or with custom parallelism is complex and may not provide the desired throttling across the entire instance.
- ✓
Deploy Ola Hallengren’s IndexOptimize stored procedure in each database and schedule it via SQL Server Agent.
Why this is correct
Ola Hallengren's IndexOptimize stored procedure is a widely used, efficient solution for index maintenance. SQL Server Agent is fully supported on Azure SQL Managed Instance and can be scheduled to run during off-peak hours with custom configuration to minimize performance impact.
Go deeper
Related to this question
Learn chapter
Overview of Azure Data Platform Options
Key term
Azure SQL Managed Instance
Azure SQL Managed Instance is a fully managed cloud database service that gives you nearly all the features of Microsoft SQL Server on your own server, without you having to manage the hardware or operating system.
Key term
Azure SQL Performance Tuning
Azure SQL Performance Tuning is the process of optimizing the speed and efficiency of queries and database operations in Microsoft Azure SQL Database or SQL Managed Instance to reduce latency and improve throughput.
About these practice questions
Courseiva writes every DP-300 question from scratch — 906 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-300 exam.