.

sql planner

Here is some code to help you view the planner (all cached):

create function SqlPlanner(@myhandle varbinary(max))
returns table
as
return select sql.text, cp.usecounts,cp.cacheobjtype,
cp.objtype, cp.size_in_bytes,
qp.query_plan
 from
 sys.dm_exec_sql_text(@myhandle) as sql cross join
 sys.dm_exec_query_plan(@myhandle)	as qp
 join sys.dm_exec_cached_plans as cp
 on cp.plan_handle = @myhandle;

go 

create view PlannerCached
as
select sp.* from sys.dm_exec_cached_plans   as cp
cross apply SqlPlanner(cp.plan_handle) as sp
go

select * from PlannerCached
go

--dbcc freeproccache

you can also create execution plans as follow:

exec sp_create_plan_guide
@name =N'test123',
@stmt = N'select * from core.patients',
@type = N'SQL',
@module_or_batch = N'select * from core.patients;',
@params=null,
@hints = 'OPTION (test)';

What's your thoughts on this?

*

Protected by WP Anti Spam