The question is, can we spy on procedure cache to see all the plans stored in there?
Yes we can! (by the way, congrats to the new president)
Select e.cacheobjtype, q.query_plan , DB_NAME(q.dbid) as database_name, e.refcounts, e.usecounts , (e.size_in_bytes / 1024 ) as size_in_kb from sys.dm_Exec_cached_plans as e Cross Apply sys.dm_exec_query_plan(e.plan_handle) as q
The query returns the following result:`(i know is a bit messy, but who cares?)
cacheobjtype query_plan database_name refcounts usecounts size_in_kb
-------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- -----------
Compiled Plan <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="
CREATE PROCEDURE [dbo].[CleanExpiredJobs]
AS
SET NOCOUN ReportServer 2 2 48
Compiled Plan <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="
CREATE PROCEDURE [dbo].[CleanOrphanedSnapshots]
@Machine nvarcha ReportServer 2 2 1096
Compiled Plan <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="
CREATE PROCEDURE [dbo].[CleanExpiredCache]
AS
SET NOCOU ReportServer 2 2 96
Compiled Plan <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="
CREATE PROCEDURE [dbo].[DeleteExpiredPersistedStreams]
AS
&# ReportServer 2 2 56
0 comentários:
Postar um comentário