List cache plans and release as needed

-- Clear cache plan area.

-- Do not run this command in production Environoment.
DBCC FREEPROCCACHE
GO

-- Get list of all user cache plan
SELECT [cp].[refcounts] ,[cp].[usecounts] ,[cp].[objtype] , [cp].[plan_handle],
       [st].[dbid] ,[st].[objectid] ,[st].[text] ,[qp].[query_plan]
  FROM sys.dm_exec_cached_plans cp 
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
 WHERE [st].[dbid] <> 32767

-- clear cache from choosen plan using [cp].[plan_handle] value.
DBCC FREEPROCCACHE 0x05002E001412F76EF05190A90800000001000000000000000000000000000000000000000000000000000000

-- To be able to use excecution plan you need permision.
GRANT SHOWPLAN TO [username];

-- Relative to the batch
-- When runing more the one command we get for each command a % relative time.
------------------------------------------------------------------------------
query 1: 33%
------------------------------------------------------------------------------
select * from table1;
------------------------------------------------------------------------------
query 2: 67%
------------------------------------------------------------------------------
select * from table2;

-- fields in SELECT icon :
----------------------------------------------------------------------------------------
Cached plan size :
How much memory the plan generated by this query will take up
in the plan cache. This is a useful number when investigating cache performance issues
because you'll be able to see which plans are taking up more memory.

Degree of Parallelism :
Whether this plan used multiple processors. 

Estimated Operator Cost : percentage cost of operation.

Estimated Subtree Cost :
Tells us the accumulated optimizer cost assigned to this
step and all previous steps, but remember to read from right to left. This number is
meaningless in the real world, but is a mathematical evaluation used by the query
optimizer to determine the cost of the operator in question; it represents the estimated
cost that the optimizer thinks this operator will take.

Estimated Number of Rows :
Calculated based on the statistics available to the
optimizer for the table or index in question.

-- fields in TABLE SCAN icon :
----------------------------------------------------------------------------------------
First are listed the Physical Operation and Logical Operation
Physical Operation :
The logical operators are the results of the optimizer's calculations for what 
should happen when the query executes.

Logical Operation :
The logical and physical operators are usually the same, but not always

estimated I/O costs: 
estimated cost numbers assigned by the query optimizer during its calculations

estimated CPU costs: 
estimated cost numbers assigned by the query optimizer during its calculations

Importent : The bigger the number the more resource are needed.

Ordered:
Is the data ordered ?
when it's set to true more resource are needed.

Node ID:
When the optimizer generates a plan, it numbers the operations in the logical order
of operations.

----------------------------------------------------------------------------------------
SET SHOWPLAN_XML ON
GO
SELECT * FROM TABLE1;
GO
SET SHOWPLAN_XML OFF
GO

SET STATISTICS XML ON
GO
SELECT * FROM TABLE1;
GO
SET STATISTICS XML ON
GO

we can save the plan with extension .sqlplan
----------------------------------------------------------------------------------------