DP-200 Exam Prep - Additional Topics
SQL Database

This course explains some additional topics you should make sure you understand before taking the DP-200 Exam. These include:

  • SQL Database
  • Data Factory

If you’re preparing to write Microsoft’s DP-200 exam, then there are some additional topics you should make sure you understand first. These topics deal with two different services: SQL Database and Data Factory.

Let’s start with SQL Database. It has a really cool feature called Automatic Tuning. There are three options you can enable. The CREATE INDEX option will determine whether queries would perform better if particular indexes were created. Then it actually creates those indexes automatically! It even monitors the performance of queries afterward to make sure that the new indexes really have improved performance.

The DROP INDEX option finds indexes that can be safely removed without impacting performance.

The FORCE PLAN option is a bit more complicated. When you run a query, the database engine can execute that query in a variety of different ways, so it has to decide which specific sequence of steps to use. This sequence of steps is called an execution plan. But it can be difficult for it to determine up-front what will be the most efficient execution plan. If you enable FORCE PLAN, then it will monitor workloads to see which plan has the best performance for a particular query, and it will force that plan to be used.

You’ll notice that you can set each of these options to On, Off, or Inherit. By default, they are all set to Inherit, which means it gets the settings from the server. It’s a best practice to set your preferred configuration on the server and then set all of its databases to inherit from the server. This provides consistency and is easier to manage.

And that’s it for this lesson.

About the Author
Learning Paths

Guy launched his first training website in 1995 and he's been helping people learn IT technologies ever since. He has been a sysadmin, instructor, sales engineer, IT manager, and entrepreneur. In his most recent venture, he founded and led a cloud-based training infrastructure company that provided virtual labs for some of the largest software vendors in the world. Guy’s passion is making complex technology easy to understand. His activities outside of work have included riding an elephant and skydiving (although not at the same time).