In this article, we will discuss a few very common questions that you may be asked during a SQL Server administrator or developer technical job interview.
Q1: What is a SQL Server Execution Plan?
SQL Server Execution Plan is a binary representation of the steps that are followed by the SQL Server Engine to execute the query. It also is known as the most efficient roadmap for the query
For more information, check SQL Server Execution Plans overview
Q2: Which component of the SQL Server Engine is responsible for generating an Execution Plan for the submitted query?
A: The SQL Server Query Optimizer is responsible for creating the most efficient plan to execute the provided query
For more information, check SQL Server Execution Plans overview
Q3: Where will the generated Execution Plan be stored?
A: The SQL Server Execution Plan will be stored in the Plan Cache memory storage
For more information, check SQL Server Execution Plans overview
Q4: What is the goal behind storing the Execution Plan for the query in the Plan Cache?
A: The process of generating the most optimal execution plan is an expensive process. Instead of creating a new Execution Plan each time a new query is submitted, the SQL Server Query Optimizer will search in the plan cache storage for an existing Execution Plan for the submitted query and use it. If there is no plan that can be used for that query, the Query Optimizer will create a new plan, taking more time to execute that query. The Execution plans reuse mechanism is very helpful when there are stored procedures executed frequently
For more information, check SQL Server Execution Plans overview
Q5: What are the main types of Execution Plans that you can generate for a T-SQL query and what is the difference between these two types?
A: The Estimated Execution Plan. It is the plan that is generated by parsing the submitted query as an estimate of how the query will be executed, without being executed
The Actual Execution Plan, that is generated by executing the submitted query, displaying the actual steps that followed while executing the query
For more information, check SQL Server Execution Plans types
Q6: What are the three Execution Plan formats?
A: Graphical Format, Text Format and XML Format
For more information, check SQL Server Execution Plans types
Q7: How could the Execution Plan help in identifying the missing index for a specific query?
A: Based on the available SQL Server statistics and the workload performed on the SQL Server, the SQL Server Query Optimizer will provide us with a suggested index, that may improve the performance of the submitted query in a calculated percentage. So, it will display that index as a recommendation with the query plan in green
For more information, check How to Analyze SQL Execution Plan Graphical Components
Q8: What is the correct way of reading the Execution Plan?
A: The correct way to read the SQL Execution Plan is to start from the right side of the plan to the left side and from the top to the bottom, and the most left SELECT operator contains the final result of the submitted query
For more information, check How to Analyze SQL Execution Plan Graphical Components
Q9: How could we use the arrow between the Execution Plan operators to read the plan?
A: You can make use of the arrows that are connecting the operators in identifying the direction and the amount of the data passed between the Execution Plan operators. In addition, the arrow is an indication of how much data passed between the operators
For more information, check How to Analyze SQL Execution Plan Graphical Components
Q10: Can the Estimated SQL Execution Plan result be trusted?
A: This depends on the statistics. If it is updated, the results should be the same. You need the Estimated SQL Execution Plan in case the query will take a long time to execute and you need to troubleshoot it
For more information, check How to Analyze SQL Execution Plan Graphical Components
Q11: What is the difference between the RID and the Key Lookup operators?
A: RID is a row locator that includes information about the location of that record such as the database file, the page, the slot numbers that helps to identify the location of the row quickly
The Key Lookup operator is the Clustered equivalent of the RID Lookup operator
For more information, check SQL Server Execution Plan Operators – Part 2
Q12: What is the Aggregate operator in the Execution Plan?
A: The Aggregate Operator is mainly used to calculate the aggregate expressions in the submitted query, by grouping the values of an aggregated column. The aggregate expressions include the MIN, MAX, COUNT, AVG, SUM operations
For more information, check SQL Server Execution Plan Operators – Part 3
Q13: What is the Compute Scalar operator in the Execution Plan?
A: The Compute Scalar operator is used to perform scalar computation operations in order to calculate a new value from the existing row value
For more information, check SQL Server Execution Plan Operators – Part 3
Q14: What is the Concatenation operator in the Execution Plan?
A: The Concatenation operator takes one or more data sets in sequence as inputs and returns all records from all the input data set. A good example of the concatenation operator is the UNION ALL T-SQL statement
For more information, check SQL Server Execution Plan Operators – Part 3
Q15: What is the ASSERT operator in the Execution Plan?
A: The Assert operator will verify whether the inserted values meet the defined CHECK or FOREIGN KEY constraints on the table called by the query
For more information, check SQL Server Execution Plan Operators – Part 3
Q16: What is the Hash Match operator in the Execution Plan?
A: Hashing table is used when the SQL Server engine divides the joined tables in the query into equally sized buckets, using a Hashing Function, so that it can access these data in a quick manner. In this case, the SQL Server Optimizer will use the Hash Match operator to perform that action
For more information, check SQL Server Execution Plan Operators – Part 3
Q17: What is the Lazy Spool operator in the Execution Plan?
A: The SQL Server Lazy Spool is used to build a temporary table on the TempDB and fill it in a lazy manner. In other words, it fills the table by reading and storing the data only when individual rows are required by the parent operator
For more information, check SQL Server Execution Plan Operators – Part 4
Q18: What is the Parallelism operator in the Execution Plan?
A: The parallel plan is used by the SQL Server Engine to execute the expensive queries faster. The SQL Server Engine decides to use a parallel plan to execute the query when the SQL Server is installed on a multi-processor server, the number of threads that are requested are available to be assigned, the value of the Maximum Degree of Parallelism option is not equal to 1 and the cost of the submitted query is larger than the Cost Threshold for Parallelism value. The Parallelism operator is used by the SQL Server Engine to execute the query using a parallel plan
For more information, check SQL Server Execution Plan Operators – Part 4
Q19: How could we take benefits from the SQL Server Execution Plan in tuning the T-SQL queries performance?
A: The SQL Server Execution Plan can be used in identifying the bad performance parts of the query. The first thing to look at is the most expensive operator with the highest cost, compared with the overall query cost. In addition, having a fat arrow, which is followed by a thin one, is an indication of the missing index that forced scanning a large amount of data to retrieve a small number of records
Next, you need to search for the extra operators, as its overhead will degrade the query performance. Also, the Scan operators that read the overall table or index is an indication of a missing index, the existing index is badly used, or the submitted query has no filtering condition. The Execution Plan Warnings messages are a sign of different query performance problems that should be checked
For more information, check Using the SQL Execution Plan for Query Performance Tuning
Q20: What is the SQL Server level option that can be used to enhance the Plan Cache usage performance and minimize the memory pressure when the majority of your system workload are ad-hoc queries?
A: You can enable the Optimize for Ad hoc Workloads option, to store the SQL Execution Plan of the query in the Plan Cache at the second execution of the query
For more information, check Saving your SQL Execution Plan
Q21: How long will the plan be stored in the Plan cache?
A: It is useless to keep the SQL Server Execution Plan in the Plan cache forever. The SQL Server Engine will automatically drop any plan from the Plan Cache whenever more memory is required by the system or when the plan becomes old and not called for a long time. The SQL Server Engine users the Lazy Writer system process to clean these aged plans
For more information, check Saving your SQL Execution Plan
Q22: How could we explicitly clear the Plan cache?
A: Using the DBCC FREEPROCCACHE T-SQL command
For more information, check Saving your SQL Execution Plan
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021