Vistas sobre tabelas com índices clustered sobre colunas nullable trazem surpresas

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…

Share

You may also like...

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios marcados com *