Há poucos dias fiz um post sobre como nem sempre um índice ajuda em updates e deletes.
Para situar quem não o leu, mostrei que um índice que à primeira vista pode ajudar nem sempre o faz. Por vezes até se revela desastroso em termos de performance.
Mas como há sempre quem diga que “não cobriste todos os casos, se tivesses __________ seria diferente”.
E ainda bem que “há sempre quem diga”. São esses que nos fazem testar mais a fundo o que achamos não ser necessário. Por isso, aceito… mas não compro até analisar todos esses casos.
Em relação à outra vez vou apenas inserir mais linhas, criar mais tabelas com diferentes índices e criá-los unique onde for possível.
create table DeleteDemo
(
id int identity(1,1)
, valor bigint
)
go
insert into DeleteDemo
select ABS(CHECKSUM(NEWID())%1000)
from sys.objects a
cross join sys.objects b
cross join sys.objects c
Criamos agora as nossas outras tabelas
select * into DeleteDemoSemIndex from DeleteDemo
select * into DeleteDemoIndexada_Id from DeleteDemo
select * into DeleteDemoIndexada_Id_include_Valor from DeleteDemo
select * into DeleteDemoIndexada_Id_Clustered from DeleteDemo
select * into DeleteDemoIndexada_Valor from DeleteDemo
select * into DeleteDemoIndexada_Valor_include_Id from DeleteDemo
select * into DeleteDemoIndexada_Valor_Clustered from DeleteDemo
select * into DeleteDemoIndexada_Id_Valor from DeleteDemo
select * into DeleteDemoIndexada_Id_Valor_Clustered from DeleteDemo
select * into DeleteDemoIndexada_Valor_Id from DeleteDemo
select * into DeleteDemoIndexada_Valor_Id_Clustered from DeleteDemo
E os nossos índices
create unique nonclustered index idx on DeleteDemoIndexada_Id (id)
create unique nonclustered index idx on DeleteDemoIndexada_Id_include_Valor (id) include (valor)
create unique clustered index idx on DeleteDemoIndexada_Id_Clustered (id)
create nonclustered index idx on DeleteDemoIndexada_Valor (valor)
create nonclustered index idx on DeleteDemoIndexada_Valor_include_Id (valor) include (id)
create clustered index idx on DeleteDemoIndexada_Valor_Clustered (valor)
create unique nonclustered index idx on DeleteDemoIndexada_Id_Valor (id, valor)
create unique clustered index idx on DeleteDemoIndexada_Id_Valor_Clustered (id, valor)
create unique nonclustered index idx on DeleteDemoIndexada_Valor_Id (valor, id)
create unique clustered index idx on DeleteDemoIndexada_Valor_Id_Clustered (valor, id)
Vamos ver agora o que acontece nestes casos?
set statistics io on
set statistics time off
select * from DeleteDemoSemIndex where 1 = (select 1) and valor > 500
select * from DeleteDemoIndexada_Id where 1 = (select 1) and valor > 500
select * from DeleteDemoIndexada_Id_include_Valor where 1 = (select 1) and valor > 500
select * from DeleteDemoIndexada_Id_Clustered where 1 = (select 1) and valor > 500
select * from DeleteDemoIndexada_Valor where 1 = (select 1) and valor > 500
select * from DeleteDemoIndexada_Valor_include_Id where 1 = (select 1) and valor > 500
select * from DeleteDemoIndexada_Valor_Clustered where 1 = (select 1) and valor > 500
select * from DeleteDemoIndexada_Id_Valor where 1 = (select 1) and valor > 500
select * from DeleteDemoIndexada_Id_Valor_Clustered where 1 = (select 1) and valor > 500
select * from DeleteDemoIndexada_Valor_Id where 1 = (select 1) and valor > 500
select * from DeleteDemoIndexada_Valor_Id_Clustered where 1 = (select 1) and valor > 500
DeleteDemoSemIndex, não há índice, scan paralelizado a 6,5k páginas
DeleteDemoIndexada_Id, há índice mas não serve, também scan paralelizado a 6,5k páginas
DeleteDemoIndexada_Id_include_Valor, há índice mas também não serve, de novo scan paralelizado a 6,5k páginas
DeleteDemoIndexada_Id_Clustered, não é por ser clustered que este índice passa a servir para algo, levamos com as mesmas 6,5k páginas num scan em paralelo
DeleteDemoIndexada_Valor, há índice mas não contém o id que precisamos porque “select *”. Adivinhem quantas páginas? 6,5k no mesmo grau de paralelismo de todas as anteriores, 4.
DeleteDemoIndexada_Valor_include_Id, finalmente temos um índice que ajuda e agora temos um seek em série a 4k páginas.
DeleteDemoIndexada_Valor_Clustered, neste temos também um índice que nos ajuda a termos um scan em série a 4,5k páginas.
DeleteDemoIndexada_Id_Valor, temos um índice que tem as colunas que nós queremos, mas onde a primeira coluna não é aquela onde temos o predicado. Voltamos ao scan em paralelo a 6,5k páginas.
DeleteDemoIndexada_Id_Valor_Clustered, nada diferente da anterior, o índice não serve e levamos com um pouco mais que as 6,5k páginas.
DeleteDemoIndexada_Valor_Id, voltámos ao caso em que o índice nos serve e fazemos um scan série a 4k páginas.
DeleteDemoIndexada_Valor_Id_Clustered, para já o melhor caso, 3,2k páginas.
Vamos aos nossos updates?
update DeleteDemoSemIndex set valor = valor*2 where valor > 500
update DeleteDemoIndexada_Id set valor = valor*2 where valor > 500
update DeleteDemoIndexada_Id_include_Valor set valor = valor*2 where valor > 500
update DeleteDemoIndexada_Id_Clustered set valor = valor*2 where valor > 500
update DeleteDemoIndexada_Valor set valor = valor*2 where valor > 500
update DeleteDemoIndexada_Valor_include_Id set valor = valor*2 where valor > 500
update DeleteDemoIndexada_Valor_Clustered set valor = valor*2 where valor > 500
update DeleteDemoIndexada_Id_Valor set valor = valor*2 where valor > 500
update DeleteDemoIndexada_Id_Valor_Clustered set valor = valor*2 where valor > 500
update DeleteDemoIndexada_Valor_Id set valor = valor*2 where valor > 500
update DeleteDemoIndexada_Valor_Id_Clustered set valor = valor*2 where valor > 500
Resultados interessantes.
Vamos por partes.
Páginas carregadas:
DeleteDemoSemIndex, 6535
DeleteDemoIndexada_Id, 6535
DeleteDemoIndexada_Id_include_Valor, 3850768 + 26 + 9620
DeleteDemoIndexada_Id_Clustered, 6550
DeleteDemoIndexada_Valor, 5385732 + 11 + 3679955 + 8397
DeleteDemoIndexada_Valor_include_Id, 5395548 + 12 + 3679955 + 9619
DeleteDemoIndexada_Valor_Clustered, 7598336 + 3660018
DeleteDemoIndexada_Id_Valor, 8008821 + 2 + 9619
DeleteDemoIndexada_Id_Valor_Clustered, 7538182 + 7134
DeleteDemoIndexada_Valor_Id, 5395122 + 1 + 3679955 + 9619
DeleteDemoIndexada_Valor_Id_Clustered, 3815008 + 7133
Sempre que temos a coluna valor no índice os problemas começam.
Planos de execução :
Os únicos mais “limpinhos” são os onde a coluna valor não está indexada. Há spools quando o índice “ajuda” exceptuando no caso em que temos clustered(valor,id)
E deletes?
delete from DeleteDemoSemIndex where valor > 500
delete from DeleteDemoIndexada_Id where valor > 500
delete from DeleteDemoIndexada_Id_include_Valor where valor > 500
delete from DeleteDemoIndexada_Id_Clustered where valor > 500
delete from DeleteDemoIndexada_Valor where valor > 500
delete from DeleteDemoIndexada_Valor_include_Id where valor > 500
delete from DeleteDemoIndexada_Valor_Clustered where valor > 500
delete from DeleteDemoIndexada_Id_Valor where valor > 500
delete from DeleteDemoIndexada_Id_Valor_Clustered where valor > 500
delete from DeleteDemoIndexada_Valor_Id where valor > 500
delete from DeleteDemoIndexada_Valor_Id_Clustered where valor > 500
Aqui vemos coisas diferentes dos updates, porque neste caso ao apagarmos as linhas não temos problemas em as vermos de novo mais à frente. Nada de spools, como esperado. Temos overhead quando há índices nonclustered a serem mantidos, quando temos clustered as mudanças são feitas directamente na tabela (que é o índice).
Se bem se lembram da questão inicial, a que podemos criar um índice para nos “ajudar” em updates e deletes e por vezes esse índice acaba por não ajudar… acham que está respondida?
