Uma forma simples de visualizar Cached Plan

Recentemente estava assistindo alguns vídeos no Pluralsights e um código usado em um vídeo do Dan Sullivan para demonstrar Cached Plan me chamou a atenção.

Gostei e resolvi compartilhar, e fiz uma alteração apenas para incluir o nome da database.

create function SqlAndPlan (@handle varbinary(max)) 
returns table 
as 
return 
select 
  sql.text 
 ,db_name(qp.dbid) dbname -- eu adicionei essa informação 
 ,cp.usecounts 
 ,cp.cacheobjtype 
 ,cp.objtype 
 ,cp.size_in_bytes 
 ,qp.query_plan 
from 
 sys.dm_exec_sql_text (@handle) as sql
cross apply sys.dm_exec_query_plan(@handle) as qp
join sys.dm_exec_cached_plans as cp 
     on cp.plan_handle = @handle
go

Primeiramente é criado uma função auxilizar para retornar informações plano de execução, onde é passado como parâmetro o plan_handle que uma referencia ao plano de execução em si e também uma referencia ao texto que causou a construção do plano de execução.

clip_image002

Com essa função podemos obter informações uteis, porem temos que ter o plan_handle especifico. Isso é interessante se queremos saber o cached plan de um unico plano de execução, porem, se quiseremos saber todos os cached plan e contidos na instancia, como fazemos? Teremos que passar um a um?

Não! Vamos criar uma view que irá percorrer todos os plan_handle e usar a função SqlAndPlan criada acima para obter as informações.

create view PlanCache
as
select
   sp.dbname -- eu adicionei essa informação 
  ,sp.text
  ,sp.usecounts
  ,sp.cacheobjtype
  ,sp.objtype
  ,sp.size_in_bytes
  ,sp.query_plan
from
  sys.dm_exec_cached_plans as cp
CROSS APPLY SqlAndPlan(cp.plan_handle) as sp

go

Caso tenha alguma sugestao de melhoria no codigo, 
por favor, compartilhe.

[]’s

 

Referências:

Pluralsight – http://pluralsight.com

sys.dm_exec_sql_text – http://msdn.microsoft.com/pt-br/library/ms181929.aspx

sys.dm_exec_query_plan – http://msdn.microsoft.com/pt-br/library/ms189747(v=sql.110).aspx

sys.dm_exec_cached_plans – http://msdn.microsoft.com/pt-br/library/ms187404.aspx

Maníaco, entusiasta, fascinado, fanático por SQL Server e nas horas vagas um DBA que adora o que faz! Também possui certificações como: MCT, MCSE - Data Management and Analystics, MCSE - Data Plataform e MCSA - SQL Server 2016/2014/2012

One Response to “Uma forma simples de visualizar Cached Plan”

  1. Salvando um Plano de Execução (via TSQL) « Alex Souza, Responder

    […] Uma forma simples de visualizar Cached Plan […]

Responda