Vale a pena passar de SQL Server 2014 para SQL Server 2017?

Surgiu num cliente uma questão bem pertinente mas a que já vamos estando habituados.

Será que vale a pena fazer upgrade de SQL Server 2014 Standard para SQL Server 2017 Standard?

tl;dr Sim.

Este cliente tem um misto de SGBD’s num misto de versões.
A questão surgiu para um certo servidor que, estando agora em SQL Server 2014, poderia ser actualizado para SQL Server 2017.

Duas vantagens que saltam logo à vista, sendo o workload maioritariamente de DW, é a possibilidade de compressão e columnstore (em 2014 disponíveis apenas na versão Enterprise).
As melhorias em termos de espaço e rapidez de processamento saltam à vista.
Há muitas mais vantagens, mas resolvi centrar-me nestas duas que me parecem as mais relevantes para o cliente em questão.

As principais questões eram:

Será que as tabelas vão ficar assim tão mais pequenas?

Os inserts vão ficar muito mais lentos?

Se os dados estão comprimidos então as queries vão demorar mais tempo.

Será que vamos aproveitar bem columnstore?

Vamos por partes.

Resolvi agarrar uma tabela relativamente simples e de tamanho médio, com pouco mais de 484 milhões de linhas. (Sim, se não precisa de count_big não é assim tão grande…)

CREATE TABLE [dbo].[tabela1](
	[Col1] [int] NOT NULL,
	[Col2] [date] NOT NULL,
	[Col3] [datetime] NOT NULL,
	[Col4] [char](2) NOT NULL,
	[Col5] [varchar](50) NULL,
	[Col6] [varchar](50) NULL,
	[Col7] [varchar](50) NULL,
	[Col8] [smallint] NOT NULL
)
GO

Temos um índice clustered na Col2 e um índice nonclustered nas (Col1, Col5)

Será que as tabelas vão ficar assim tão mais pequenas?

Criei várias “versões” desta tabela:

  • base, como está, sem compressão, sem índices.
  • com page compression.
  • com clustered columnstore.
  • base com índices que cobrem algumas das queries que desenvolvi para este teste.
  • page compressed com os mesmos índices.

Fiz dois testes, um em que apenas introduzi 2 milhões de linhas e no outro todas as 484374064.

Vamos aos números?

Primeiro para as 2M de linhas

Tabelareserveddataindex_sizeunused
Base141.640141.4648168
Page Compressed45.06444.6488408
Clustered Columstore25.41624.7200696
Base Indexed252.304155.66496.168472
Page Compressed Indexed87.37648.84837.936592

Esta para os quase 500M de linhas.

Tabelareserveddataindex_sizeunused
Base34227208342270487288
Page Compressed9001448900122440184
Clustered Columstore368188836816480240
Base Indexed531193923763763215481296464
Page Compressed Indexed16557824107759125781336576

Os resultados são conclusivos.

Realço que com page compression passamos para 26% do tamanho e no caso de termos índices para 31%.
Com columnstore passamos para 10% do tamanho da tabela base sem índices.

Os inserts vão ficar muito mais lentos.

Usei metodologias diferentes para este caso também.

Inseri 100k e 1M linhas, como e sem tablock, com a tabela com e sem dados, o que perfez 8 runs para cada tabela.

40 runs no total para termos estes belos resultados.

Tabela100k, vazio, sem tablock100k, com dados, sem tablock1M, vazio, sem tablock1M, com dados, sem tablock100k, vazio, com tablock100k, com dados, com tablock1M, vazio, com tablock1M, com dados, com tablockParalel Insert com tablock
Base206186958471873160161400423Sim
Base Indexed5336544676652851268346926593Não
Clustered Columnstore3953752765292978184211141176Sim
Page Compressed5452692023220524250315411347Sim
Page Compressed Indexed103415081093315854108416161138515123Não

Muita coisa interessante para avaliarmos.

Mesmo repetindo várias vezes as operações verifico que há resultados que podem ser considerados outliers.

Exemplos?
Na tabela Base demoro só mais 4ms a inserir 1M de linhas. Demoro menos tempo a inserir 100k de linhas com dados na Page Compressed do que quando está vazia.

O que realço aqui é que com tablock, quando não temos a tabela indexada o insert é feito em paralelo e logo mais rápido. Os tempos onde temos índices são bastante mais lentos.

Com Clustered Colunstore, apesar de a tabela estar indexada, os tempos são bastante baixos, mais meio segundo do que inserirmos na Base que é apenas uma heap.

Se os dados estão comprimidos as queries vão demorar mais tempo.

Várias queries para verificar alguns tipos de workloads nesta tabela.

Caso 1

select 	Col1
	,	Qtd = count(1)
into #res01
from tabela1
where Col5 = 'ABCDEFGHIJK'
group by Col1

Totalmente coberta pelo índice nonclustered. Resultados?

TabelaCPUTIME
Base7656372091
Base Indexed6762330939
Clustered Columnstore25945591
Page Compressed9556329759
Page Compressed Indexed8121822319

Aqui a Clustered Columnstore “massacra” as outras, deixando-as a longa distância, apesar de estarmos a dar as melhores hipóteses às tabelas com índices.

De realçar também que as tabelas comprimidas comportam-se melhor em tempos agregados do que as sem compressão à custa de um pouco mais de tempo de CPU.

Caso 2

Aqui estamos apenas a contar quantos registos temos por cada clustering key.

select   Col2
     ,   Qtd = count(1)
 into #res02 
 from tabela1 
 group by Col2
TabelaCPUTIME
Base7176570626
Base Indexed2837527652
Clustered Columnstore3198
Page Compressed8487429203
Page Compressed Indexed306419295

O “massacre” continua. 98ms com 31ms de CPU! Nem a Page Compressed Indexed lhe chega aos calcanhares.

Caso 3

select * 
into #res03 
from tabela1 
where Col2 >= '20190201'

Um workload menos usual, mas para verificar como columnstore se comportaria quando queremos todas as colunas.

Para termos ideia, a query insere um pouco mais de 125M de linhas na temporária.

TabelaCPUTIME
Base120233122268
Base Indexed9324975024
Clustered Columnstore12437680288
Page Compressed193125150908
Page Compressed Indexed14329697031

Neste caso ganha a Base Indexed com menos tempo e CPU como esperado.

O que não esperava era que Columnstore estivesse tão perto em tempo total à custa de mais tempo de CPU.

Caso 4

select * 
into #res04 
from tabela1
where 	Col2 >= '20190101' 
	and Col2 <= '20190201' 

Similar à anterior, mas com apenas um mês de dados. 56M de linhas para esta query.

TabelaCPUTIME
Base7678391520
Base Indexed4063916413
Clustered Columnstore5551619289
Page Compressed11926559801
Page Compressed Indexed6229721744

Resultados similares também.

Caso 5

select * 
into #res05 
from tabela1
where 	Col2 >= '20190101' 
	and Col2 <= '20190201' 
	and Col5 = 'ABCDEFGHIJK'
TabelaCPUTIME
Base4689283147
Base Indexed138899293
Clustered Columnstore61732745
Page Compressed6237425248
Page Compressed Indexed167025510

Voltamos ao reino da columnstore, com menos tempo total e CPU do que qualquer outra.

Caso 6

select * 
into #res30 
from tabela1
where 	Col2 >= '20190101' 
	and Col2 <= '20190201' 
	and Col5 = 'c.2018.86' 
	and Col8 <= 3
TabelaCPUTIME
Base4043771046
Base Indexed97678986
Clustered Columnstore31392065
Page Compressed5717323301
Page Compressed Indexed125004319

A saga continua. Clustered Columnstore a reduzir em muito qualquer outra estratégia testada.

Caso 7

select max(Col2) 
from tabela1

Valor máximo da clustered key? Alguma dúvida?

TabelaCPUTIME
Base9165569439
Base Indexed02
Clustered Columnstore47195
Page Compressed10562532187
Page Compressed Indexed01

Tabelas indexadas com resposta instantânea e a Columnstore a fazer o seu trabalho.

As não indexadas são obrigadas a um scan à heap toda, que bem podemos notar que demora menos de metade do tempo na tabela com Page Compression.

Caso 8

select max(Col3) 
from tabela1

E um max() a um campo não indexado?

TabelaCPUTIME
Base8715668803
Base Indexed8799974901
Clustered Columnstore37514994
Page Compressed11595234794
Page Compressed Indexed10714128705

Neste caso quem tem vantagem é nitidamente a Columnstore.

Caso 9

select distinct 
		Col5
	, 	Col6
	, 	Col7
into #res09 
from tabela1

Uma agregação para terminar.

TabelaCPUTIME
Base608170173454
Base Indexed604703156662
Clustered Columnstore1922815
Page Compressed724452187215
Page Compressed Indexed683844173062

Nada de novo… Columnstore “a dar-lhe” em grande.

Na esmagadora maioria dos casos temos menos tempo total nas tabelas com compressão. E ainda não estamos a falar de Columnstore….

Será que vamos aproveitar bem columnstore?

Responder com uma pergunta é aceite?

Se sim, a minha resposta é “é preciso mostrar mais alguma coisa”?

Se não, a minha resposta é “vamos rever os dados dos testes acima”.

Os dados são conclusivos, na minha humilde opinião.


Em resumo…

A minha opinião é clara. Neste caso, é “claro”. É claro que o nosso cliente vai ver melhorias.

Não estou sequer a falar de Always Encrypted e Row Level Security que não tem grande interesse para o cliente.

Não estou a colocar na equação Dynamic Data Masking e Temporal Tables, que seriam interessantes.

Querem que comece a falar sobre Query Store? 🙂

Vamos lá rever a questão principal do post

“Será que vale a pena fazer upgrade de SQL Server 2014 Standard para SQL Server 2017 Standard?”

Sim.

Share

You may also like...

Deixe um comentário

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