Há uns dias atrás andava pela minha vidinha a avaliar a performance de uma SP que estava a desenvolver e deparei-me com uma situação pouco usual.
Não tenho uma maneira fácil de explicar o que estava a acontecer sem mostrar onde me estava a meter.
Vamos lá explicar isto pondo já as mãos na massa.
Vou criar uma tabela base e copiá-la para 4 tabelas que vão fazer parte de uma vista.
create table NullsOnClustered
(
id bigint
, col1 char(50) default cast(newid() as char(50))
, col2 char(10) default '0123456789'
)
go
insert into NullsOnClustered
(id)
select ABS(CHECKSUM(NEWID()))%10000000
from sys.objects a
cross join sys.objects b
cross join sys.objects c
go
select *
into NullsOnClustered1
from NullsOnClustered
select *
into NullsOnClustered2
from NullsOnClustered
select *
into NullsOnClustered3
from NullsOnClustered
select *
into NullsOnClustered4
from NullsOnClustered
go
create view NullsOnClustered_All
as
select * from NullsOnClustered1
union all
select * from NullsOnClustered2
union all
select * from NullsOnClustered3
union all
select * from NullsOnClustered4
No meu caso foram criadas 2,5M linhas em casa tabela.
Imaginem agora que quero saber o max(id) que tenho na tabela original, numa das cópias e na vista.
set statistics time, io on
go
select MAX(id) from NullsOnClustered
select MAX(id) from NullsOnClustered1
select MAX(id) from NullsOnClustered_All
go
Tudo certo. Mesmos resultados, table scans porque não tenho nenhum índice, mesmos reads em cada tabela.
Nos planos de execução aparece o que espereraríamos, Scans, Aggregate paralelizado e um Aggregate final.
A matemática até funciona, duas queries com mais ou menos um terço do trabalho e a outra com os outros dois terços.
Agora vamos criar uns índices porque… sim. Primeiro porque foi assim que dei de caras com isto e depois porque… sim.
create clustered index cidx on NullsOnClustered1 (id)
create nonclustered index ncidx1 on NullsOnClustered1 (col1)
create nonclustered index ncidx2 on NullsOnClustered1 (col2)
create clustered index cidx on NullsOnClustered2 (id)
create nonclustered index ncidx1 on NullsOnClustered2 (col1)
create nonclustered index ncidx2 on NullsOnClustered2 (col2)
create clustered index cidx on NullsOnClustered3 (id)
create nonclustered index ncidx1 on NullsOnClustered3 (col1)
create nonclustered index ncidx2 on NullsOnClustered3 (col2)
create clustered index cidx on NullsOnClustered4 (id)
create nonclustered index ncidx1 on NullsOnClustered4 (col1)
create nonclustered index ncidx2 on NullsOnClustered4 (col2)
Temos agora em cada tabela um clustered no id e dois nonclustered em cada uma das outras colunas. Em demos podemos fazer todas as coisas que não devemos ver em produção mas que às vezes vemos, não é?
Agora estas queries vão ficar mais rápidas, certo?
select MAX(id) from NullsOnClustered
select MAX(id) from NullsOnClustered1
select MAX(id) from NullsOnClustered_All
go
Bem… à primeira vista pareceram-me mais lentas… vamos lá a olhar bem para os resultados.
Resultados ok, não podia ser outra coisa.
Já há aqui algo estranho.
Quando faço a primeira query obtenho o que esperaria, 23k reads. Com a segunda também, 3 reads apenas porque agora tenho um índice feito especialmente para esta query.
Mas quando olhamos para a terceira… espera lá, se um scan à tabela são 23k reads como é que tenho 31k reads nas primeiras 3 e só 7k na última?
Até tenho de separar os planos de execução. Na primeira imagem nada de novo, mas no plano da query à view…
Vamos analisar melhor isto. Focamo-nos para já no mais óbvio, além do scan ao índice clustered temos um scan a um nonclustered seguido de um sort.
Hã? Mas nem temos o id nesse índice! Relembrando, o ncidx2 apenas tem a col2.
Será?
Os mais atentos estão agora a responder “claro que o id está lá”. Sim, claro que o id está lá, estamos numa tabela com um índice clustered e temos as colunas do índice clustered em cada índice nonclustered. Relembrando, se fosse uma heap teríamos o RID. Não estou preocupado agora com índices não únicos e o correspondente uniquifier.
Mas mesmo assim, que raio… então eu preciso do max(id), tenho um índice no id de todas as tabelas de uma vista e agora, de repente, o SQL Server anda a varrer índices nonclustered para quê?
Vamos analizar o que ele faz no scan a cada índice
Predicate id IS NULL e Predicate id IS NOT NULL? Bem, então é por isso que ele anda a usar o noclustered, para procurar os id a null.
Vamos lá ver o que ele faz se eu explicitamente disser que quero o max(id) nos não nulos?
select MAX(id) from NullsOnClustered_All where id is not null
Tudo o que esperaríamos: 3 reads a cada índice e nada de scans a nonclustereds.
Então pelo menos já sabemos o que se passa.
E se não usassemos a vista?
select MAX(id)
from (
select id = MAX(id) from NullsOnClustered1
union all
select id = MAX(id) from NullsOnClustered2
union all
select id = MAX(id) from NullsOnClustered3
union all
select id = MAX(id) from NullsOnClustered4
) a
Esta agora… se eu for directamente às tabelas e não usar a vista isto fica “normal”…
Que bela mentira que vos estou a pregar… esta query não tem nada a ver com a vista que tinha. Esta é que sim!
select MAX(id)
from (
select * from NullsOnClustered1
union all
select * from NullsOnClustered2
union all
select * from NullsOnClustered3
union all
select * from NullsOnClustered4
) a
Regresso ao passado. Tudo na mesma. Coerência, pelo menos.
Schemabinding? Será que ajuda?
create view NullsOnClustered_All_SB
with schemabinding
as
select id, col1, col2 from dbo.NullsOnClustered1
union all
select id, col1, col2 from dbo.NullsOnClustered2
union all
select id, col1, col2 from dbo.NullsOnClustered3
union all
select id, col1, col2 from dbo.NullsOnClustered4
select MAX(id) from NullsOnClustered_All_SB
Queriam, não era?
Vista só com o id?
create view NullsOnClustered_All_id
as
select id from NullsOnClustered1
union all
select id from NullsOnClustered2
union all
select id from NullsOnClustered3
union all
select id from NullsOnClustered4
select MAX(id) from NullsOnClustered_All_id
Queriam, não era (parte 2)?
Realmente há uma solução para isto.
Vamos alterar a coluna id definindo-a para not null.
O que poderia ter sido poupado aquando do desenho da tabela… fazemos agora.
Drop da view que tem schemabinding, drop dos índices clustered e consequentes operações em todos os índices nonclustered, alterar a coluna e criar os índices depois. Com estas singelas 4 tabelas com 10M de linhas é rápido (pouco mais de um minuto), mas imaginem em tabelas um “pouquinho” maiores.
drop view NullsOnClustered_All_SB
go
drop index cidx on NullsOnClustered1
drop index cidx on NullsOnClustered2
drop index cidx on NullsOnClustered3
drop index cidx on NullsOnClustered4
go
alter table NullsOnClustered1
alter column id bigint not null
alter table NullsOnClustered2
alter column id bigint not null
alter table NullsOnClustered3
alter column id bigint not null
alter table NullsOnClustered4
alter column id bigint not null
go
create clustered index cidx on NullsOnClustered1 (id)
create clustered index cidx on NullsOnClustered2 (id)
create clustered index cidx on NullsOnClustered3 (id)
create clustered index cidx on NullsOnClustered4 (id)
go
E agora?
Agora já está melhor, não?
Acho que isto não acaba aqui, porque ainda falta explicar muita coisa… uma delas é que eu quero o max(id) e o SQL Server se preocupa com as possíveis rows em que o id possa ser null…
