[DP-300] Day5 Q/A Review – Understanding 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.

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.

Do check our blog at k21academy.com/dp300day5 for more information

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.

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

If you are planning to become a Microsoft Azure Database Administrator Associate, then join the FREE CLASS now at https://k21academy.com/ dp30002

Motivate your friends as well as it is the next big hit in the technology world. Also, do not forget to join us on our FREE Telegram group https://t.me/k21microsoftazure , and be the first to receive Microsoft Azure related news and updates.