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.

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