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
Tabela | reserved | data | index_size | unused |
Base | 141.640 | 141.464 | 8 | 168 |
Page Compressed | 45.064 | 44.648 | 8 | 408 |
Clustered Columstore | 25.416 | 24.720 | 0 | 696 |
Base Indexed | 252.304 | 155.664 | 96.168 | 472 |
Page Compressed Indexed | 87.376 | 48.848 | 37.936 | 592 |
Esta para os quase 500M de linhas.
Tabela | reserved | data | index_size | unused |
Base | 34227208 | 34227048 | 72 | 88 |
Page Compressed | 9001448 | 9001224 | 40 | 184 |
Clustered Columstore | 3681888 | 3681648 | 0 | 240 |
Base Indexed | 53119392 | 37637632 | 15481296 | 464 |
Page Compressed Indexed | 16557824 | 10775912 | 5781336 | 576 |
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.
Tabela | 100k, vazio, sem tablock | 100k, com dados, sem tablock | 1M, vazio, sem tablock | 1M, com dados, sem tablock | 100k, vazio, com tablock | 100k, com dados, com tablock | 1M, vazio, com tablock | 1M, com dados, com tablock | Paralel Insert com tablock |
Base | 206 | 1869 | 5847 | 1873 | 160 | 161 | 400 | 423 | Sim |
Base Indexed | 533 | 654 | 4676 | 6528 | 512 | 683 | 4692 | 6593 | Não |
Clustered Columnstore | 395 | 375 | 2765 | 2929 | 781 | 842 | 1114 | 1176 | Sim |
Page Compressed | 545 | 269 | 2023 | 2205 | 242 | 503 | 1541 | 1347 | Sim |
Page Compressed Indexed | 1034 | 1508 | 10933 | 15854 | 1084 | 1616 | 11385 | 15123 | Nã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?
Tabela | CPU | TIME |
Base | 76563 | 72091 |
Base Indexed | 67623 | 30939 |
Clustered Columnstore | 2594 | 5591 |
Page Compressed | 95563 | 29759 |
Page Compressed Indexed | 81218 | 22319 |
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
Tabela | CPU | TIME |
Base | 71765 | 70626 |
Base Indexed | 28375 | 27652 |
Clustered Columnstore | 31 | 98 |
Page Compressed | 84874 | 29203 |
Page Compressed Indexed | 30641 | 9295 |
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.
Tabela | CPU | TIME |
Base | 120233 | 122268 |
Base Indexed | 93249 | 75024 |
Clustered Columnstore | 124376 | 80288 |
Page Compressed | 193125 | 150908 |
Page Compressed Indexed | 143296 | 97031 |
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.
Tabela | CPU | TIME |
Base | 76783 | 91520 |
Base Indexed | 40639 | 16413 |
Clustered Columnstore | 55516 | 19289 |
Page Compressed | 119265 | 59801 |
Page Compressed Indexed | 62297 | 21744 |
Resultados similares também.
Caso 5
select * into #res05 from tabela1 where Col2 >= '20190101' and Col2 <= '20190201' and Col5 = 'ABCDEFGHIJK'
Tabela | CPU | TIME |
Base | 46892 | 83147 |
Base Indexed | 13889 | 9293 |
Clustered Columnstore | 6173 | 2745 |
Page Compressed | 62374 | 25248 |
Page Compressed Indexed | 16702 | 5510 |
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
Tabela | CPU | TIME |
Base | 40437 | 71046 |
Base Indexed | 9767 | 8986 |
Clustered Columnstore | 3139 | 2065 |
Page Compressed | 57173 | 23301 |
Page Compressed Indexed | 12500 | 4319 |
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?
Tabela | CPU | TIME |
Base | 91655 | 69439 |
Base Indexed | 0 | 2 |
Clustered Columnstore | 47 | 195 |
Page Compressed | 105625 | 32187 |
Page Compressed Indexed | 0 | 1 |
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?
Tabela | CPU | TIME |
Base | 87156 | 68803 |
Base Indexed | 87999 | 74901 |
Clustered Columnstore | 3751 | 4994 |
Page Compressed | 115952 | 34794 |
Page Compressed Indexed | 107141 | 28705 |
Neste caso quem tem vantagem é nitidamente a Columnstore.
Caso 9
select distinct Col5 , Col6 , Col7 into #res09 from tabela1
Uma agregação para terminar.
Tabela | CPU | TIME |
Base | 608170 | 173454 |
Base Indexed | 604703 | 156662 |
Clustered Columnstore | 1922 | 815 |
Page Compressed | 724452 | 187215 |
Page Compressed Indexed | 683844 | 173062 |
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.