SQL Server query execution plans

A query plan (or query execution plan) is an ordered set of steps used to access data in a SQL relational database management system. This is a specific case of the relational model concept of access plans. Execution plans can tell us to how SQL Server may execute a query, or how it did execute a query.

Estimated and actualexecution plan here:

http://bageshkumarbagi-msbi.blogspot.in/2016/09/estimated-and-actual-execution-plans-in.html

All execution plans are stored in the system table.

With below sql script we get the stored execution plan for our Sp.

SELECT qp.query_plan,        CP.usecounts,        cp.cacheobjtype,                    cp.objtype,        cp.size_in_bytes,        cp.usecounts,        SQLText.text   FROM sys.dm_exec_cached_plans AS CP   CROSS APPLY sys.dm_exec_sql_text( plan_handle)AS SQLText   CROSS APPLY sys.dm_exec_query_plan( plan_handle)AS QP

See the output

Execution plans are stored in xml format. Once we click on the xml we will get the graphical execution plan.

With the help of we can see the all stored all execution plans.