The most important skill you should acquire in database performance tuning is being able to read and understand query execution plans. The plans explain the behavior of the database engine as it executes queries and retrieves the results.

Query Store helps you quickly identify your most expensive queries, and find any changes in performance. The Query Store provides a powerful data collection including the automation of query plan and execution runtime. SQL Server and Azure SQL provide locking and blocking in order to manage concurrency and ensure data consistency. Finally, you can adjust the isolation levels in SQL Server to help manage concurrency.

Learning objectives:

In this module, you will:

  • Compare the different types of execution plans
  • Understand the purpose and benefits of the Query Store
  • Investigate the available reports and data in the Query Store
  • Understand how blocking and locking work in the SQL Server database engine