In this article, we will discuss a few very common interview questions that you may be asked during a SQL Server Execution Plan or developer technical job interview. Let’s see 22 very common SQL Server execution plan interview questions as bellow:
Q1: What is a SQL Server Execution Plan?
A: 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.
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
Q3: Where will the generated Execution Plan be stored?
A: The SQL Server Execution Plan will be stored in the Plan Cache memory storage
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 expensive. 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 no plan 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.
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
Q6: What are the three Execution Plan formats?
A: Graphical Format, Text Format, and XML Format
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.
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.
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. Besides, the arrow is an indication of how much data passed between the operators.
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
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.
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.
Q13: What is the Compute Scalar operator in the Execution Plan?
A: The Compute Scalar operator is used to perform scalar computation operations to calculate a new value from the existing row value.
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.
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.
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 quickly access these data. In this case, the SQL Server Optimizer will use the Hash Match operator to perform that action.
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 slowly. In other words, it fills the table by reading and storing the data only when individual rows are required by the parent operator
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 is 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
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. Also, 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
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 is 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
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
Q22: How could we explicitly clear the Plan cache?
A: Using the DBCC FREEPROCCACHE T-SQL command