Execution Plan Basics

1.       What is a Execution Plan?

It will tell you how a query was executed.

2.       What happens when a query was submitted?

When a submit a query to sql server database engine, a number of processes work.

Processes that occur in the relational engine

Processes that occur in the storage engine

3.       Execution plan is generated by which component in sql server ?

Query Optimizer

4.       DDL Statements can be optimized?

No

5.       Why DDL Statements cannot be optimized?

Because there is only one way for SQL server system to create a table therefore no other opportunities to improve the performance of that statement.

6.       Types of Execution plans?

There are two types. Actual and Estimated Execution plan

7.       Estimated Execution plan?

This plan represents the output from the query optimizer

8.       Actual Execution plan?

This plan represents the output from the query execution

9.       Plans are stored in a section of memory called?

Plan cache

10.   In previous versions of SQL plan cache also called?

Procedure cache

11.   Execution plan Reuse?

When a query submitted to SQL server, an estimated execution plan is created by the optimizer. Once that plan is created, before it get passed to the storage engine, it compares the estimated plan and actual execution plan that already exist in the plan cache. If actual plan is similar to the estimated plan then sql server uses the existing plan. Since its already been used by the query engine.

12.   Execution plans are kept in memory forever?

No.They are slowly aged out of the system by using an age formula.

13.   Which internal process frees all type of caches?

Lazywriter

14.   Which command is used to completely clear the cache?

DBCC FREEPROCCACHE

15.   Why the Actual and Estimated execution plans might differ?

When statistics are old, Estimated plan is invalid, when parallelism is requested.

16.   Execution plan formats?

SQL Server supports three formats to view the execution plan.

GRAPHICAL PLANS, TEXT PLANS & XML PLANS

17.   Which permission is required to see execution plan?

GRANT SHOW PLAN TO (Username)