O tamanho dos varchar interessa, mesmo que o SQL Server só use o espaço que necessita, revisitado.

O meu post de há algum tempo sobre o tamanho das variáveis e o memory grant deu algo que falar, por isso resolvi, ao estilo de Hollywood destes últimos anos, revisitá-lo.

Se se lembram desse post (também não foi há assim tanto tempo…) a questão era:

O tamanho das variaveis não interessa, se eu colocar um varchar(max) ou varchar(50) é igual porque o SQL Server só usa o espaço que necessita.

O que na altura mostrei é que sim, interessa e muito, porque o memory grant é bem diferente quando usamos essas colunas, por exemplo, num Sort.

Na altura aproveitei para mostrar que se tivessemos um índice clustered na tabela pelo campo da ordenação o Sort desaparecia e o memory grant seria, obviamente, 0.

Mas algumas questões me foram colocadas que vou tentar responder aqui.

E se tivessemos um índice nonclustered?

E se o nonclustered tivesse o campo adicional que metemos no select?

E por que raio é que o SQL Server precisa do espaço da ColunaVarcharMax ou da ColunaVarchar50 se a ordenação está a ser feita no id apenas?

Tudo boas questões. Gosto muito da última, foi bem pertinente.

Criamos uma tabela parecida com a do post original e inserimos algumas linhas.

create table DemoVarchar 
(
	id int identity (1,1),
	ColunaVarcharMax varchar(max),
	ColunaVarchar50 varchar(50),
	UmaData date,
	UmaDataHora datetime
)

insert into DemoVarchar
select		'0123456789'
		,	'0123456789'
		,	GETDATE()
		,	GETDATE()
from sys.objects a
cross join sys.objects b

select top 10 *
from DemoVarchar

Fazemos agora as mesmas duas queries “quase” iguais.

Relembrando… Na primeira queremos as colunas id e ColunaVarcharMax com o resultados ordenados pela id, na segunda apenas trocamos a ColunaVarcharMax pela ColunaVarchar50.

Showplan ligado e…

select id, colunavarcharmax
from DemoVarchar
order by id

select id, colunavarchar50
from DemoVarchar
order by id

Tudo igual até agora, como esperado. Revisitamos o triangulo amarelo na primeira query.

“ExcessiveGrant”. O SQL Server achou que precisaria de 80480 KB mas só usou 1416 KB.

Em comparação, na query em que usamos a ColunaVarchar50 os valores são bem diferentes.

Usou 1024 KB mas reservou só 3056 KB.

Até aqui já tínhamos visto tudo…

Começamos com as coisas interessantes que foram surgindo.

E se tivessemos um índice nonclustered?

Vejo muito deste tipo de coisas por aí… sim, isto é para vocês que gostam muito de heaps e depois colocam um índice nonclustered na(s) coluna(s) onde eu colocaria um índice clustered.

Atenção que eu não tenho nada contra heaps. Uso-as quando preciso. Mas só nesses casos…

create unique nonclustered index uncidx1 on  DemoVarchar (id)

E vamos lá outra vez.

Afinal, o que aconteceu? Table Scan? “Então e o índice?”

Bem, o índice não cobre a query, por isso prefere ir logo à tabela toda do que ir ao índice nonclustered e depois ir buscar cada linha pelo seu row locator. Como não temos nenhum índice clustered, o row locator é um ponteiro para a localização da linha(o RID), que tem o identificador do ficheiro, o número da página e o número da linha na página.

E podemos ver isso se forçarmos o uso do índice nonclustered.

Vamos fazer uma comparação da primeira query com e sem o uso forçado do índice.

select id, ColunaVarcharMax
from DemoVarchar
order by id


select id, ColunaVarcharMax
from DemoVarchar with (index=uncidx1)
order by id

As coisas ficaram feias…

A primeira coisa que vemos é o nosso custo relativo deixar de ser 50% para cada lado e passar para uns interessantes 28% – 72%

Depois notamos que o sort desapareceu. Claro, na de baixo estamos a varrer um índice ordenado como precisamos.

Mas agora, para cada linha que sai desse índice temos de ir à procura dos valores em falta, neste caso o ColunaVarcharMax.

Vamos lá ver o que aconteceu em termos de reads?

As nossas leituras passaram de 101 para 16167. Significativo?
O tal fenómeno chamado Bookmark Lookups. Tanto a dizer sobre isto…

Vamos ver no caso da ColunaVarchar50?

select id, colunavarchar50
from DemoVarchar 
order by id


select id, colunavarchar50
from DemoVarchar with (index=uncidx1)
order by id

Onde é que eu já vi isto?

Nada de novo… reads a disparar.

Segunda questão.

E se o nonclustered tivesse o campo adicional que metemos no select?

Podemos então mudar o nosso índice, adcionando-lhe a coluna que precisamos para que ele cubra as nossas queries.

Pelo menos para esta…

drop index uncidx1 on DemoVarchar

create unique nonclustered index uncidx1 on  DemoVarchar (id) include (ColunaVarcharMax)

Mais uma voltinha…

select id, ColunaVarcharMax
from DemoVarchar
order by id

select id, ColunaVarchar50
from DemoVarchar
order by id

Onde temos índice tudo bem. Relembro que temos um índice na coluna id e que contém apenas o ColunaVarcharMax.

E se o índice fosse bom para as duas colunas ColunaVarcharMax e ColunaVarchar50? Vamos a isso?

drop index uncidx1 on DemoVarchar

create unique nonclustered index uncidx1 on  DemoVarchar (id) include (ColunaVarcharMax, ColunaVarchar50)

Ah e tal, agora está tudo bem.

Depende da nossa definição de “bem”.

Recapitulemos… temos uma tabela que tem 5 colunas e que, por necessidade, para satisfazer duas queries, criámos um índice nonclustered que contém 3 colunas mais o RID. Ou seja, temos guardado em páginas a “tabela” (5 colunas mais o RID) e o índice (3 colunas mais o RID).

Vamos ver o tamanho da tabela?

exec sp_spaceused 'DemoVarchar'

Ora 808 KB de dados e 792 KB de index. Lindo serviço.

Lembrem-se que não tenho nada contra heaps. Quando se justificam…
Definitivamente este não é um caso desses.

Vamos lá fazer as coisas como deveriam ter sido feitas desde o início.

drop index uncidx1 on DemoVarchar
create unique clustered index ucidx1 on DemoVarchar (id)

select id, ColunaVarcharMax
from DemoVarchar
order by id

select id, ColunaVarchar50
from DemoVarchar
order by id

Nada de sorts.

exec sp_spaceused 'DemoVarchar'

Quase só espaço usado nos dados.

Está mais bonito, ou não?

Vamos à questão que deixei para último.

E por que raio é que o SQL Server precisa do espaço da ColunaVarcharMax ou da ColunaVarchar50 se a ordenação está a ser feita no id apenas?

A resposta a esta está, em parte, demostrada mais acima, quando forçamos o índice nonclustered que criámos na coluna id.

O que aconteceu? Ele pegou na coluna id (o índice que criámos) já ordenada e depois foi à procura da outra coluna que lhe faltava.

Se isso aconteceu com o id já ordenado, imaginem o que teria de ser feito sem o id ordenado.

Ele iria à tabela, faria um scan retirando todos os valores da coluna id e do RID, depois ordenava-os numa worktable qualquer, depois iria varrer essa worktable indo buscar pelo RID a linha correspondente na coluna que precisava.

Se fosse convosco, fariam isso ou pura e simplesmente colocariam logo os valores das duas colunas que precisam de devolver numa worktable e depois ordenavam essas linhas pela coluna necessária?

Eu e o SQL Server achamos que era preferível a segunda opção.
Por isso precisamos de guardar essas colunas numa estrutura e depois precisamos de ordenar essas linhas. Se as colunas forem maiores (ou o SQL Server achar que são) vai ter de guardar mais espaço e consequentemente mais memória para o Sort.

Óbvio, não?