SQL Server – Planos de Execução – Estimados Vrs. Realizados – set showplan_xml on Vrs. set statistics xml on

Identifico como gargalo atual de um servidor de produção – utilizando script que já escreví a respeito aqui – um comando de update do tipo: update tabela set campo1=@valor1 where idtabela=@id, onde idtabela é primary key clustered.

No mínimo intrigante, analisando o plano de execução do update. Ex.:

set showplan_xml on
go
declare @valor varchar(10)
declare @id int
update tabela set campo1=@valor1 where idtabela=@id

Plano estimado sem anomalias. Mas, percebo triggers associadas à updates na tabela. Detalhando-as, percebo uma série de regras de negócios implementadas. Caso de RN aplicada via TR.

Motivo do post: Comandos T-SQL dentro de trigger não são estimados pelo optimazer durante a construção do plano do comando responsável pelo acionamento da mesma. Este é um dos cenários onde o plano estimado sofre alterações pelo data engine durante sua execução. Logo, preciso do plano executado literalmente pelo data engine, e não “apenas” o estimado pelo optimazer. Ex.:

set statistics xml on

Único porém é que, ao contrário do plano estimado (que apenas estima a execução da query), o plano realizado – como seu nome já implica – irá executar a query! Após a execução, serão coletados os planos para apresentação. Caso realize testes em produção, considere envolver seu DML teste em uma transação, assim, seu impacto poderá ser desfeito. Ex.:

set statistics xml on
go
begin tran
update tabela set campo1=@valor1 where idtabela=@id
rollback tran

Segue em pequeno script t-sql, que exemplifica o conceito acima:

Defino 3 tabelas e 1 trigger que implementa um log de edição de registros para auditoria.
– A primeira tabela mantém a trigger responsável por logar modificações em seus próprios registros;
– A segunda tabela mantém as modificações realizadas na primeira. Uma espécie de “DE”->”PARA”;
– A terceira tabela mantém uma parametrização lida pela trigger que define “o que”/”quando” logar.

Seguindo a execução do mesmo script, apresento o plano estimado para alguns comandos de edição na tabela “triggada”, onde percebe-se a ausência das ações sobre as tabelas “terceiras” e “quartas” cuja responsabilidade é da execução subsequente da trigger.

Para encerrar, o script apresenta o plano realizado para mais comandos de edição na tabela “triggada”, onde pode-se ver tanto desde o plano para comando original, como os demais planos para todos os comandos disparados pala trigger.

Ctrl+C / Ctrl+V e, bom estudo!

-- estimated vrs actual plans
-- planos estimados vrs realizados (optimazer x store engine)
-- [set showplan_xml on] vrs [set statistics xml on]
-- dica: execute os comandos "de go em go" para facilitar a compreensão;

use tempdb
go

-- ambiente - definição e carga inicial
-- > tb: tabela sob auditoria de log de edição
-- > tb_log: tabela que mantem o log de edição para auditoria
-- > tb_log_parametro: tabela que parametriza o comportamento do log de edição
-- > tr_tb_logger: trigger responsável pelo log de edição de acordo com a parametrização do ambiente

create table tb (chave int not null primary key clustered, valor varchar(20))
create table tb_log (idlog int not null identity(1,1) primary key clustered, acao char(3) not null, chave int not null, valor varchar(20))
create table tb_log_parametro (parametro char(7) not null, valor char(3) not null)

insert into tb_log_parametro (parametro, valor)
	values	('LOG_INS', 'SIM')
		,	('LOG_UPD', 'SIM')
		,	('LOG_DEL', 'SIM')
go

create trigger tr_tb_logger on tb 
for insert, update, delete 
as 
begin
	declare @acao char(3)
	
	if (	exists (select null from inserted)
		and exists (select null from deleted) )
		set @acao = 'UPD'
	else if (exists (select null from inserted))
		set @acao = 'INS'
	else
		set @acao = 'DEL'
		
	if ((@acao = 'UPD') and exists (select null from tb_log_parametro where parametro='LOG_UPD' and valor='SIM'))
		insert into tb_log (acao, chave, valor)
						select @acao, chave, valor from deleted 
			union all	select @acao, chave, valor from inserted
	else if ((@acao = 'INS') and exists (select null from tb_log_parametro where parametro='LOG_INS' and valor='SIM'))
		insert into tb_log (acao, chave, valor)
			select @acao, chave, valor from inserted
	else if ((@acao = 'DEL') and exists (select null from tb_log_parametro where parametro='LOG_DEL' and valor='SIM'))
		insert into tb_log (acao, chave, valor)
			select @acao, chave, valor from deleted
end
go	

SELECT 'PARAMETROS...'
select * from tb_log_parametro
go

-- teste do ambiente

select 'INSERT chave 1, 2...'
insert into tb (chave, valor)
	values	(1, 'valor 1')
		,	(2, 'valor 2')
		
select * from tb
select * from tb_log
go

select 'UPDATE chave 2...'
update tb set valor = 'valor 2 edit' where chave=2
select * from tb
select * from tb_log
go

select 'DELETE chave 1...'
delete tb where chave=1
select * from tb
select * from tb_log
go

-- planos de execução - estimado vrs realizado
-- planos de execução estimados (optimazer) são gerados para execução dos comandos, 
-- e repassados para o store engine realizar a operação, entretanto, ao ser executado,
-- pode acabar sofrendo alterações. é o caso dos comandos mantidos na trigger de log
-- que o plano estimado não os considera. não considerando a possível execução da trigger

select 'PLANOS ESTIMADOS...'
go
set showplan_xml on
go

--select 'INSERT chave 3, 4...'
insert into tb (chave, valor)
	values	(3, 'valor 3')
		,	(4, 'valor 4')
go

--select 'UPDATE chave 4...'
update tb set valor = 'valor 4 edit' where chave=4
go

--select 'DELETE chave 3...'
delete tb where chave=3
go

set showplan_xml off
go

-- planos de execução - estimado vrs realizado
-- planos de execução realizados (store engine)
-- após a execução, os planos realizados pelo store engine são coletados para apresentação.
-- logo, ao contrario dos planos estimados (que são apenas compilados), os registros foram afetados, 
-- a trigger foi disparada e, com isto, o plano para cada comando dentro da trigger foi obtido/apresentado

select 'PLANOS REALIZADOS...'
go
set statistics xml on
go

--select 'INSERT chave 5, 6...'
insert into tb (chave, valor)
	values	(5, 'valor 5')
		,	(6, 'valor 6')
go

--select 'UPDATE chave 6...'
update tb set valor = 'valor 6 edit' where chave=6
go

--select 'DELETE chave 5...'
delete tb where chave=5
go

set statistics xml off
go

select 'FIM...'
select * from tb
select * from tb_log
go
		
-- destruicao do ambiente
drop table tb
drop table tb_log
drop table tb_log_parametro
go

T+

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s