Estou planejando e testando uma nova instalação do SQL Server 2022 para migrar de um banco de dados antigo de 2014. Notei durante os testes de desempenho algumas discrepâncias importantes com algumas consultas volumosas.
Aqui está uma consulta que uso para teste:
INSERT INTO @volume
SELECT max(SUBSTRING(dbo.CLEACUM.TRADE_DATE, 1, 4)) AS TRADEYEAR,
sum(CASE [CANCEL] WHEN '1' THEN - cast(abs([TOT_QTY]) as bigint)
ELSE cast(abs([TOT_QTY]) as bigint) END) AS TOT_QTY,0
FROM dbo.CLEACUM LEFT OUTER JOIN
dbo.IBM_SECMASTER ON dbo.CLEACUM.TDE_SYMBOL = dbo.IBM_SECMASTER.DCS_CUSIP LEFT OUTER JOIN
dbo.VOL_IBM_CODES_IDA ON dbo.IBM_SECMASTER.SEC_CLASS >= dbo.VOL_IBM_CODES_IDA.NUMMIN AND
dbo.IBM_SECMASTER.SEC_CLASS <= dbo.VOL_IBM_CODES_IDA.NUMMAX AND dbo.IBM_SECMASTER.SEC_TYPE >= dbo.VOL_IBM_CODES_IDA.TYPEMIN AND
dbo.IBM_SECMASTER.SEC_TYPE <= dbo.VOL_IBM_CODES_IDA.TYPEMAX AND dbo.VOL_IBM_CODES_IDA.DATEMAX >= ABS(CONVERT(float, DATEDIFF(day,
dbo.CLEACUM.MATURITY_DATE, dbo.CLEACUM.TRADE_DATE) / 365.25)) AND dbo.VOL_IBM_CODES_IDA.DATEMIN <= ABS(CONVERT(float, DATEDIFF(day,
dbo.CLEACUM.MATURITY_DATE, dbo.CLEACUM.TRADE_DATE) / 365.25)) LEFT OUTER JOIN
dbo.VOL_CUSIPEquivalence ON SUBSTRING(dbo.CLEACUM.TDE_SYMBOL, 1, 6) = dbo.VOL_CUSIPEquivalence.CUSIP LEFT OUTER JOIN
dbo.VOL_MaturityLabels ON dbo.VOL_MaturityLabels.DATEMAX >= ABS(CONVERT(float, DATEDIFF(day, dbo.CLEACUM.MATURITY_DATE, dbo.CLEACUM.TRADE_DATE)
/ 365.25)) AND dbo.VOL_MaturityLabels.DATEMIN <= ABS(CONVERT(float, DATEDIFF(day, dbo.CLEACUM.MATURITY_DATE, dbo.CLEACUM.TRADE_DATE) / 365.25))
LEFT OUTER JOIN VOL_BusinessDaysPerYear on left(CLEACUM.TRADE_DATE,4)=[VOL_BusinessDaysPerYear].YEAR
WHERE (dbo.CLEACUM.ACCT_NO LIKE '10%') AND (dbo.CLEACUM.SEC_TYPE < 300) AND (dbo.CLEACUM.ACCT_NO NOT IN
(SELECT ACCOUNT_NO
FROM dbo.VOL_ExcludedAccounts)) AND (SUBSTRING(dbo.CLEACUM.TRADE_DATE, 1, 4) > datepart(yyyy,getdate())-5)
and (cast(TRADE_DATE as date)<= VOLUME_SAME_PERIOD_DAY)
group by SUBSTRING(dbo.CLEACUM.TRADE_DATE, 1, 4)
A mesa CLEACUM é bastante grande. Cerca de 17 milhões de linhas em 3,2 GB de espaço em disco. Nada insano, mas ainda assim grande para tal consulta examinar toda a tabela.
Quando executo em 2014, começo do zero ou vivo por dias, a consulta é concluída em no máximo 30 segundos. Quando executo em 2022, leva mais de 50 minutos para ser concluído. A próxima execução será concluída em 30 segundos. O DBCC DROPCLEANBUFFERScomando também fará com que ele fique lento novamente, sem reiniciar ou esperar muito.
Na solução de problemas, posso ver o IO carregando a tabela do disco (lendo ~ 1 MB do arquivo mdf), o que presumo ser o atraso e a execução subsequente usará dados do cache. Eu estou bem com isso, mas...
Como 2014 não está fazendo a mesma coisa? Se eu executar a mesma consulta em 2014 após a reinicialização da máquina, ela ainda será executada em 30 segundos ou menos! Mas em 2022 ele precisará carregá-lo do disco antes de poder usar o cache. Ambos me fornecem as mesmas estatísticas quando medidos:
Table '#B59594BD'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VOL_MaturityLabels'. Scan count 1, logical reads 192958, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 1746, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 14, logical reads 2872, physical reads 338, read-ahead reads 2534, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VOL_CUSIPEquivalence'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VOL_IBM_CODES_IDA'. Scan count 1, logical reads 1736622, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'IBM_SECMASTER'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VOL_ExcludedAccounts'. Scan count 1, logical reads 195636, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CLEACUM'. Scan count 1, logical reads 1040096, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VOL_BusinessDaysPerYear'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 24008 ms, elapsed time = 28572 ms.
Table '#AAEDD858'. Scan count 0, logical reads 5, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'VOL_MaturityLabels'. Scan count 1, logical reads 192958, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 1746, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 14, logical reads 2872, physical reads 338, page server reads 0, read-ahead reads 2534, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'VOL_CUSIPEquivalence'. Scan count 1, logical reads 8, physical reads 5, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'VOL_IBM_CODES_IDA'. Scan count 1, logical reads 1736622, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'IBM_SECMASTER'. Scan count 1, logical reads 65, physical reads 0, page server reads 0, read-ahead reads 88, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'VOL_ExcludedAccounts'. Scan count 1, logical reads 195636, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'CLEACUM'. Scan count 1, logical reads 1040096, physical reads 17904, page server reads 0, read-ahead reads 305712, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'VOL_BusinessDaysPerYear'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 40063 ms, elapsed time = 3041555 ms.
Para fins de contexto, ambos são executados essencialmente no mesmo hipervisor/armazenamento com ambas as VMs configuradas com recursos idênticos. O servidor original não tinha nenhum tipo de índice e o OLTP não existia em 2014 (nem está habilitado em 2022). O recurso HA está configurado em 2022, mas o banco de dados em questão ainda não foi replicado. Tornei ambos tão idênticos quanto possível para limitar as variáveis.
A única diferença que notei é que 2014 alocará todo o máximo de memória (16 GB) para SQL imediatamente no lançamento, enquanto 2022 parece ser mais dinâmico dependendo das consultas.
Sei que a consulta não é a ideal, mas meu trabalho é apenas migrar tudo para um servidor mais recente.
O que estou perdendo, não entendendo? Como posso alcançar o mesmo nível de desempenho em 2022 que o atual 2014?
As estatísticas de espera da sessão são todas PAGEIOLATCH_SH(minha interpretação está aguardando o IO para que os dados continuem, o que corresponde às estatísticas do IO).
Eu configurei a reserva de memória na VM e reiniciei. Ainda nenhuma mudança, nem mesmo um segundo de diferença.
