In this post, I have shared some quick tips, including Q/A and useful links from the Day 5 live session of our current batch of Azure Database Administrator Training Program [DP-300]. We have covered some topics like SQL Server Query Plans, Data compression, and Indexes.
On our Day 4 Live Session, we have covered interesting topics like Azure metrics, Azure Monitor and optimize operational resources, and Query Store.
On our Day 5 Live Session of the [DP-300] Microsoft Database Administrator Training Program, we covered the concepts of Understanding SQL Server Query Plans, Explore Performance-based Database Design, Evaluate Performance Improvements.
SQL Server Query Plans
An execution plan in SQL Server is a simple graphical representation of the operations that the query optimizer generates to calculate the most efficient way to return a set of results. The query optimizer calculates a cost for multiple possible plans based on the statistics it has on the columns being utilized and the possible indexes that can be used for each operation in each query plan. Based on this information, it comes up with a total cost for each plan.
Some complex queries can have thousands of possible execution plans. The optimizer does not evaluate every possible plan but uses heuristics to determine plans that are likely to have good performance. The optimizer will then choose the lowest cost plan of all the plans evaluated for a given query.
Q.1 What are Execution Plans?
Every time you execute a query, the query optimizer uses a path of operations to process your query and retrieve data results. the optimizer also collects statistics about the operation and execution of this plan. After being generated by the optimizer, query plans are cached in the area of memory called the plan cache. The Database engine stores these SQL Server Query Plans internally as XML.
Q.2 What is Query Optimizer?
The Query Optimizer takes a query and returns a query plan and will attempt several plans to choose the best guess and generating a plan has high CPU cost, So SQL Server caches plans.
Q.3 How are query plans generated?
The first time you run a given query, the optimizer generates several plans and choose the one with the lower overall cost
The total cost of the execution plan is a blended measure of how much resource consumption the optimizer thinks will be needed to complete the query
The optimizer uses settings and distribution statistics on columns and indexes associated with your query to generate the execution plans
Generating plans is very resource-intensive, so plans are cached after the initial generation.
Q.4 Describe the different types of execution plans?
- Estimated Execution Plans: The path the database engine plans to take to retrieve the data to answer your query
- Actual Execution Plans: The estimated execution plan, but also includes actual statistics from the execution of the query
- Live Query Statistics: An animated view of query execution showing progress and order of operations
Q.5 How to capture plans in a management studio?
SQL Server Management Studio allows you to capture both estimated and actual execution plans, and Live Query Statistics. Plan capture can also be enabled by keyboard commands. You can capture a text query plan by using the SET SHOWPLAN ON option.
Q.6 What are the Common plan operators for finding data?
- Index Seek: Reads the portion of the index which contains the needed data
- Index Scan: Reads the entire index for the needed data
- Table Scan: Reads the entire table for the needed data
- Key Lookup: Looks up values row by row for values that are missing from the index used
- Table-Valued Function: Executes a table-valued function within the database
Q.7 What are the Common plan operators for sorting and filtering?
- Nested Loops: Performs inner, outer, semi and anti semi joins Performs a search on the inner table for each row of the outer table
- Hash Match: Creates a hash for required columns for each row Then creates a hash for the second table and finds matches
- TOP: Returns the specified top number of rows
- Sort: Sorts the incoming rows
- Stream Aggregate: groups rows by one or more columns and calculates one or more aggregate expressions
Q.8 What are Clustered Indexes?
A clustered index sorts and stores the data in a table based on key values. There can only be one clustered index per table since rows can only be stored in one order and Clustered indexes are frequently the primary key for a table. Clustered indexes have no uniqueness requirement
Q.9 What Are Nonclustered indexes?
Nonclustered indexes are secondary indexes used to help the performance of queries not served by the clustered index. You can create multiple nonclustered indexes on a table. You can also create filtered indexes, for tables with large data skew. The cost of nonclustered indexes is space and insert/update performance.
Q.10 What are Columnstore Indexes?
Data is stored in columns, not rows this allows for higher levels of compression, which reduces the storage and memory footprint of data. Columns that are not referenced in a query are not scanned, giving reducing the amount of I/O needed. Best used in large tables (e.g data warehouse fact tables, temporal history tables). It Can be clustered or a nonclustered index. And Clustered column store must include all columns of the table
Q.11 What are Dynamic Management Objects?
Dynamic Manage Views and Functions supply data about database performance and state. These objects may be scoped at the database or server level. Azure SQL Database has some of its own DMVs to provide Azure-specific data at the database level. All objects are in the sys schema and follow the naming convention sys. dm_*.
Q.12 Explain the Steps of the index tuning methodology.
Identify expensive queries, using the Query Store or Extended Events profiling.
Example the query plans for those queriesTest changes to indexes to evaluate improvements in I/O and elapsed time.Implement changes in production.
> Lab: Optimize Query Performance
In this, we will evaluate a database design for problems with normalization, data type selection, and index design. Then run queries with suboptimal performance, examine the query plans, and attempt to make improvements within the AdventureWorks2017 database.
Feedback Received…
Here is some positive feedback from our trainees who attended the session:
Read more about the DP-300 Certification and whether it is the right certification for you, from our blog on Exam DP-300: Microsoft Azure Database Administrator Associate.
Quiz Time (Sample Exam Questions)!
With our Azure Database Administrator Training Program, we cover 200+ sample exam questions to help you prepare for the DP-300 Certification.
Ques: You have an Azure Database for MySQL database. You have to identify which database queries are consuming the most resources. Which of the following tool can be used for this requirement?
A. Query Store
B. Metrics
C. Query Performance Insight
D. Alerts
Comment with your answer & we will tell you if you are correct or not!
Related/References
- Exam DP-300: Microsoft Azure Database Administrator Associate
- Microsoft Certified Azure Database Administrator Associate(Hands-On Labs)
- [DP-300] Day4 Q/A Review – Monitor And Optimize Operational Resources
- [DP-300] Day3 Q/A Review – Implement A Secure Environment
Next Task For You
Begin your journey toward Mastering Azure Cloud and landing high-paying jobs. Just click on the register now button on the below image to register for a Free Class on Mastering Azure Cloud: How to Build In-Demand Skills and Land High-Paying Jobs. This class will help you understand better, so you can choose the right career path and get a higher paying job.
![Microsoft Agentic AI Business Solutions Architect [AB-100] | K21 Academy](https://test.k21academy.com/wp-content/uploads/2025/11/Microsoft-Agentic-AI-Business-Solutions-Architect-AB-100-Exam-Overview1.png)
