Where is the query plan from SSRS report
Hi I have a simple question. But this leads to another question. I want to drop the query plan for storing proc i. So I ran proc using
exec dbo.uspNameOfProc
and then checked the plan using query
SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp CROSS APPLY
sys.dm_exec_sql_text(plan_handle)
where [text] like '%uspNameOfProc%'
what else am I missing?
RUN exec dbo.uspNameOfProc
Then run sp_who3 Find out which line it runs on.
Then run USE master;
GO
SELECT * FROM sys.dm_exec_requests
WHERE session_id = YourSPID;
GO
This will give you a Plan pen. To see the execution of the execution plan.
USE master;
GO
SELECT * FROM sys.dm_exec_query_plan (YourPlanHandleID);
GO
Then if you want to clear the plan from the cache use that.
DBCC FREEPROCCACHE (YourPlanHandleID);
GO
Hope it helps. UPDATE *** Be sure to run these operations in separate query windows so that the execution plan does not change.
Slightly easier to get the plan handle (since you are using a stored procedure) would be from sys.dm_exec_procedure_stats. Something like (from your database):
select plan_handle
from sys.dm_exec_procedure_stats
where database_id = db_id()
and object_id = object_id('dbo.uspNameOfProc');
You can drop the stored procedure / startup plan [s] and force compilation [re] with
EXEC sp_recompile N'dbo.uspNameOfProc'
Link
[sp_recompile] causes stored procedures and triggers to be recompiled the next time they are run. It does this by discarding the existing plan from the procedure cache, creating a new plan to be created the next time the procedure or trigger is run. In the SQL Server Profiler gallery, the SP: CacheInsert event is logged instead of the SP: Recompile event.