CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlstored-proceduressql-execution-plan
uh_big_mike_boi
uh_big_mike_boi

Reputation: 3470

Where is the query plan from an SSRS report rendering

Hi I have a simple question. But it lead to another question. I want to delete the query plan for a stor proc i ran. So I ran the proc using 

exec dbo.uspNameOfProc

and then checked the plan chace using the 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?

Upvotes: 3

Views: 2025

Answers (2)

Zane
Zane

Reputation: 4169

RUN exec dbo.uspNameOfProc Then Run sp_who3 Find out which spid it is running under.

Then run USE master;
GO
SELECT * FROM sys.dm_exec_requests
WHERE session_id = YourSPID;
GO

This will get you the Plan handle. To see the execution plan run.

USE master;
GO
SELECT * FROM sys.dm_exec_query_plan (YourPlanHandleID);
GO

Then if you wish to clear the plan from cache use this.

DBCC FREEPROCCACHE (YourPlanHandleID);
GO

Hope that helps. UPDATE*** Make sure perform these operations on separate query windows so as to not alter the execution plan.

Upvotes: 4

Ben Thul
Ben Thul

Reputation: 32697

A slightly easier way to get the plan handle (since you're 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');

Upvotes: 2

Related Questions