Prefer another language? Prefere receber em português? ¿Prefieres recibirlo en español? Choose here / Escolha aqui / Elige aquí

Microsoft updated the DP-300 exam on October 27, 2026. DP-300 is the exam for the Azure Database Administrator Associate certification, and many DBAs ask me the same thing about it: "Is this only theory, or will I really use it?"

I know this exam from both sides. I let my DP-300 certification expire instead of renewing it, and when I had to take the full exam again, in June 2026, I failed the first attempt. I was a few points short. I studied the gaps and passed three days later. That was the version before the October 27 update, so the outline in this post has changed a little since then. I told that story in Renew Your Certification, or Take the Full Exam Again?, in my other newsletter. The lesson I took for this post: this exam is much bigger than it looks.

So this post does two things:

And the answer to "will I really use it?" is yes, almost all of it. The study guide is a list of skills, not a map. It tells you what to know, but not how the skills connect in a real project. The case shows that.

Let me be honest from the start. The case is not complete. A real project has more requirements, and each skill could be its own post. Think of it as a first version, one way to see the exam.

The exam has five skill areas. These are the weights from the study guide, "skills measured as of October 27, 2026":

The platforms are Azure SQL Database, Azure SQL Managed Instance, SQL Server on Azure Virtual Machines (Windows and Linux) and SQL Server on-premises. That last one surprises people. This is an Azure exam, but a big part of it is plain SQL Server.

The study guide lists 94 skills in 15 groups. But the exam doesn't ask you about one skill at a time. It gives you a situation, with many requirements at once, and you have to see which skills each one needs. That's where it gets hard.

Here is a case in the style of the exam. The company is fictional. Rota means "route" in Portuguese, a good name for a logistics company.

Rota Logistics. A freight company with orders, shipments and billing on SQL Server.

Existing environment

Problems

Requirements

Now the same case as a picture. In the center, the case. Around it, every DP-300 skill it needs, by skill area, with the requirements each one answers.

Thirty-five of the 94 skills, from all five areas, in one case. And this is a simplified case. A real project has more.

This is how I would think about it, the same way I do with a real customer.

1. Two platforms, not one. R1 is the first clue. Agent jobs, CLR and cross-database queries point to Azure SQL Managed Instance for OrdersDB and ShippingDB. Managed Instance also supports Resource Governor (R11) and ledger (R9).

2. The trap is in R2. Partial-match searches on encrypted data, with no admin able to read it, need Always Encrypted with secure enclaves. Plain Always Encrypted only allows equality comparisons, with deterministic encryption. And here is the trap: Microsoft Learn says secure enclaves are available in SQL Server 2019 and later and in Azure SQL Database, not in Managed Instance. So BillingDB goes to Azure SQL Database, not to the same managed instance. TDE alone doesn't answer R2 either: it protects data at rest, not from the DBA.

3. Minimal downtime means the Managed Instance link. SQL Server 2016 SP3 is supported (with the Azure Connect pack). It replicates in near real time through a distributed availability group, and the only downtime is the cutover. Microsoft calls it the only truly online migration to Managed Instance. Log Replay Service also works, but it restores log backups every few minutes.

4. Not everything goes to the cloud. R3 is Azure Arc: the warehouse servers stay where they are, and Azure gives you inventory, patching and assessments.

5. Performance needs a plan for each symptom. Resource Governor for the month-end reports (R11). Query Store and automatic tuning for the regressions (R12). Statistics maintenance and the execution plans for the morning estimates (R13). That's the deep dive below. Partitioning and compression to turn a 9-hour purge into a partition switch (R14).

6. HA/DR is about the words in the requirement. "Same connection string" in R19 means failover groups, because their read-write listener keeps its name after a geo-failover. "Lose only seconds" fits, because geo-replication is asynchronous. "Seven years" in R20 is long-term retention, which goes up to ten years.

That's how the exam feels. Not one hard question about one feature. Many small requirements, each one pointing to a different skill, and one or two traps where a feature you know well works on one platform and not on the other.

Now let's go deep into one skill from the case: review execution plans, from R12 and R13.

There is one question I hear from customers again and again:

It sounds simple. It isn't. And the honest first answer is: the plan you are looking at is the winner, and the losers are not in it.

The query optimizer is cost-based. It builds alternatives, estimates how many rows each step will process (that's cardinality estimation, or CE), gives each alternative a cost, and keeps the cheapest plan it found. Found, not "the best that exists". The optimizer has a budget. When it reaches it, it stops and uses the best plan so far. Microsoft documents that this budget is counted in tasks (over half a million of them), not in seconds.

Why stop early? Because searching costs time too. Finding the perfect plan could take longer than running the query with a good one. Think of a query that runs in 50 milliseconds: spending two seconds to compile the perfect plan for it makes no sense. So the optimizer looks for a plan that is good enough, not the perfect one.

Think of the GPS app on your phone. It gives you a good route in a second. It doesn't check every street in the city first, because by then you could already be at your destination. The optimizer works the same way: when it finds a route that fits its budget, it goes.

That's why a plan that looks bad to us, as humans, can be the best option the optimizer saw in the time it allowed itself, or the only one.

An actual execution plan has a lot of evidence, if you know where to look:

Here is the limit. The plan doesn't have:

Let me give you a generic example from a real analysis. The plan had a Nested Loops join. Its inner side was a lazy Table Spool: a small work table that SQL Server fills once and then reads again and again. The spool held about 4,000 rows from a temporary table. It was read again tens of millions of times. The outer estimate was about two times higher than the actual rows.

From the plan XML alone, we could describe all of this very precisely. But we couldn't prove why Nested Loops won over a Hash Match, or over the other join order. Those plans weren't in the XML. So every explanation stayed at the level of "likely".

Step by step, from the safest to the deepest:

Even step 5 has a limit. The final memo keeps the alternatives that survived. Many were pruned during the search and never appear. Sometimes the only complete answer comes from debugging the engine itself. That's why "why not the other plan?" is such a hard question. The full answer often needs more information than anyone has on a normal day.

The case study runs SQL Server 2016 SP3 today and moves to Azure. The tools above don't all exist everywhere:

So on Rota's SQL Server 2016 SP3, you have Query Store, the statistics DMVs and PERSIST_SAMPLE_PERCENT. You don't have automatic tuning or Query Store hints until you move. And PERSIST_SAMPLE_PERCENT behaves the same on 2016 SP3 and in Azure: it keeps your sample rate for later updates, including automatic ones. Since 2016 SP2 CU17, it also survives an index rebuild.

Steps 3 to 5 are for a test copy. But one day the fix has to go to production: a hint, or a statistics update. Do it as a small change with a way back.

1. Capture the "before". You need numbers to compare against.

Also save the actual plan and the output of the statistics query in the next section. Write down the plan_id of the plan in use today.

2. Apply the change in the smallest way.

A hint. On SQL Server 2022 and later, Azure SQL Database and Managed Instance, use a Query Store hint. It changes one query, with no code change:

On SQL Server 2016 to 2019 there are no Query Store hints. The options are a code change with OPTION (...), or a plan guide.

A statistics update. Update the one statistic you need, by name, not the whole table. Do it outside the peak. It doesn't block normal reads and writes, but it reads the data (a lot of I/O on a big table), and it makes the plans that use it recompile.

3. Watch, and know your way back. Compare duration, CPU and logical reads with the "before". Decide the trigger in advance, for example: order entry times out, or CPU stays above 80% for 15 minutes. Then:

That last row is the important one. A statistics update has no undo. Your safety net is the old plan in Query Store, so check that you have it before you start.

Here is a question I get often, in one form or another. It fits the deep dive very well.

The scenario: a query is slow. The table has a statistic on the column group the query uses, built with a full scan (100% sample). The DBA runs UPDATE STATISTICS with the default sample, which on a big table can be a tiny fraction, something like 0.07%. The plan changes to an index seek, and the query is fast again. Then come two questions:

Two good questions. Let's take them one at a time.

Probably the sample rate is not what fixed it. When you run UPDATE STATISTICS, at least three things change at the same time:

There is also a trap with sampling itself. If a statistic was built with FULLSCAN and later an automatic update ran, the automatic update uses the default sample, unless PERSIST_SAMPLE_PERCENT = ON was set. So "the 100% statistic" may not have been 100% anymore.

How do you prove which one it was? Collect the evidence before you update anything:

Save the actual plan too: its OptimizerStatsUsage and ParameterList tell you which statistics were loaded and which values it was compiled for.

How much does each cause matter? That needs numbers, not theory. Part 2 of this post, a detailed lab in a few weeks, will reproduce this case: a full-scan statistic, the data changing, the default-sample update. It will measure the before and after: estimated and actual rows, logical reads and CPU, and it will follow the runbook above.

To go even deeper, my colleague Fabiano Amorim built SQLStatsXRay, a free, open-source (MIT) toolkit that decodes everything SQL Server stores in a statistics object, including parts DBCC SHOW_STATISTICS doesn't show. It checks each histogram step against the real data, and it separates errors that come from sampling from errors that come from changes since the last update. That is exactly the question here. Fabiano also warns about two things, and I agree:

Because a join estimate needs to know how the values on both sides overlap.

Think of two jigsaw puzzles. Before you try to fit pieces together, you look at the picture on each box. A histogram is the picture on the box: it shows which values a column has and how many rows each one has. For an equality join, the optimizer compares the histograms of the join columns on both tables to estimate how many rows will match, before it touches a single row.

Microsoft Learn describes two models:

Either way, histograms are part of the join estimate. And the filters matter too: a WHERE on one table changes which values of the join column survive.

If you suspect the join model is wrong for one query, you can test the other one with OPTION (USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS')), which uses simple containment under the newer CE. Test it; don't use it as a blanket fix. In SQL Server 2025 and Azure SQL, CE feedback for expressions can even learn which containment model fits a repeating join pattern.

Whatever group you're in, try this today. It takes five minutes:

That operator is where the story of that plan starts. Then check the statistics behind it with the query above. Reply to this email and tell me what you found.

In a few weeks, Part 2 brings the lab: the same steps on real data, with the numbers before and after each change.

How I write: every post starts from my own notes and experience. I use AI tools to help draft and edit, and I review every word before it goes out.

The technical details in this post are based on publicly available Microsoft documentation as of November 2026.

Disclaimer: The views expressed in this article are those of the author and do not necessarily reflect the official policy or position of Microsoft. The author is a Microsoft employee.