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

This is Tiago Balabuch's website, and this is a bit of copy about him. He is enthusiast, fascinated, passionate, fanatic by SQL Server and in the off-hours a Data Engineer who loves what he does and he is traveling in the cloud and surfing on the wave of the moment called Azure! Originally from Brazil and with encouragement from family and friends, Tiago moved to Europe in 2017 where lives in Ireland. In addition to being a data engineer, he is also active speaker in the SQL PASS events and keeps up to date on the key technologies and technical certifications. Tiago hold these certification MCT, MCSE - Data Management and Analystics, MCSA - SQL Server 2016/2014/2012. Simply psychedelic and manic he is just one more freak who likes SQL Server and its new features ...

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