1: 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
2: 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
3: Where will the generated Execution Plan be stored?
A: The SQL Server Execution Plan will be stored in the Plan
Cache memory storage
4: 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
5: 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
6: What are the three Execution Plan formats?
A: Graphical Format, Text Format and XML
Format
7: 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
8: 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
9: 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
10: 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
11: 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
12: 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 in order to calculate a new value from
the existing row value
14: 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
15: 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
16: 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
17: 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
18: 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
19: 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
20: 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
21: 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
22: How could we explicitly clear the Plan cache?
A: Using the DBCC FREEPROCCACHE T-SQL
command
No comments:
Post a Comment