SQL Server Execution Plan

SQL Server Optimizer prepares execution plan in order to communicate with actual database. Execution plan is an algorithm to process user request represented by SQL statements. To execute each T-SQL statement, SQL Server has to create an execution plan.

Execution plan also known as query plan is built based on the different table joins, Index, sub queries, aggregation of group by calculation and estimated cost of T-SQL statement. SQL Server caches execution plan in the memory in order to re-use it. For which it uses Plan cache to reuse the query plan.

Plan cache can be flush totally whenever SQL Server service restarts, database offline/online status change, database auto closes, database restore, Statistics of an object change, database detach operation, insufficient memory on the server and whenever we execute T-SQL command like RECONFIGURE, ALTER DATABASE.. MODIFY FILEGROUP, ALTER DATABASE… COLLATE etc.

To clean the entire plan cache, use DBCC FREEPROCCACHE.

Use below code to clean the database plan cache,

DECLARE @db_Id INT

SELECT @db_Id  = dbid FROM master.dbo.sysdatabases WHERE name ='DB'

DBCC FLUSHPROCINDB (@db_Id)

Use the below script to identify the list of plans, to clean only the compiled execution plan in case of ETL Process.

SELECT

                                    [text],

                                    CachedPlans.size_in_bytes,

                                    CachedPlans.plan_handle,

                                    CachedPlans.objtype,

                                    CachedPlans.usecounts

FROM

                                    sys.dm_exec_cached_plans AS CachedPlans

                                    CROSS APPLY sys.dm_exec_sql_text(plan_handle)

WHERE

                                    CachedPlans.cacheobjtype = N'Compiled Plan'

Then clean those compiled execution plans like below.

DBCC FREEPROCCACHE (plan_handle)

To clean stored procedure or trigger plan cache, use

EXEC sp_recompile N'myObject'';