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?
source to share
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.
source to share
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');
source to share
You can drop the stored procedure / startup plan [s] and force compilation [re] with
EXEC sp_recompile N'dbo.uspNameOfProc'
[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.
source to share