Teste de carga do SQL Server com o HammerDB

Configurar a instância do SQL Server para testes de carga

Antes de começar, deve verificar novamente se as regras da firewall do Windows estão configuradas para permitir o tráfego do endereço IP da nova instância do Windows que criou. Em seguida, crie uma nova base de dados para os testes de carga do TPCC e configure uma conta de utilizador através dos seguintes passos:

1. Clique com o botão direito do rato na pasta Bases de dados no SQL Server Management Studio e, de seguida, escolha Nova base de dados.
2. Atribua o nome "TPCC" à nova base de dados.
3. Defina o tamanho inicial do ficheiro de dados para 190 000 MB e o ficheiro de registo para 65 000 MB.

4. Defina os limites de Crescimento automático para valores mais elevados clicando nos botões de reticências, conforme mostrado na captura de ecrã seguinte:

   

5. Defina o ficheiro de dados para aumentar 64 MB até um tamanho ilimitado.

6. Defina o ficheiro de registo para desativar o crescimento automático.

7. Clique em OK.

8. Na caixa de diálogo Nova base de dados, no painel do lado esquerdo, escolha a página Opções.

9. Defina o Nível de compatibilidade como SQL Server 2022 (160).

10. Defina o modelo de recuperação como Simples para que o carregamento não preencha os registos de transações.

    

11. Clique em OK para criar a base de dados TPCC, cuja conclusão pode demorar alguns minutos.

12. A imagem do SQL Server pré-configurada é fornecida apenas com a autenticação do Windows ativada. Por isso, tem de ativar a autenticação no modo misto no SSMS seguindo este guia.

13. Siga estes passos para criar uma nova conta de utilizador do SQL Server no servidor de base de dados que tenha a autorização DBOwner. Atribua o nome "loaduser" à conta e atribua-lhe uma palavra-passe segura.

14. Tome nota do endereço IP interno do SQL Server através do comando Get-NetIPAddress, uma vez que é importante para o desempenho e a segurança usar o IP interno.

Instalar o HammerDB

Pode executar o HammerDB diretamente na sua instância do SQL Server. No entanto, para um teste mais preciso, crie uma nova instância do Windows e teste a instância do SQL Server remotamente.

Criar uma instância

Siga estes passos para criar uma nova instância do Compute Engine:

1. Na Trusted Cloud consola, aceda à página Criar uma instância.

   Aceda a Criar uma instância

2. Em Nome, introduza hammerdb-instance.

3. Na secção Configuração da máquina, selecione o tipo de máquina com, pelo menos, metade do número de CPUs da instância da base de dados.

4. Na secção Disco de arranque, clique em Alterar e, de seguida, faça o seguinte:

   1. No separador Imagens públicas, escolha um sistema operativo Windows Server.
   2. Na lista Versão, clique em Windows Server 2022 Datacenter.
   3. Na lista Tipo de disco de arranque, selecione Disco persistente padrão.
   4. Para confirmar as opções do disco de arranque, clique em Selecionar.

5. Para criar e iniciar a VM, clique em Criar.

Instalar o software

Quando estiver pronta, use um cliente RDP para estabelecer ligação à sua nova instância do Windows Server e instale o seguinte software:

• Driver ODBC 18 para SQL Server
• HammerDB para Windows de 64 bits

Executar o HammerDB

Depois de instalar o HammerDB, execute o ficheiro hammerdb.bat. O HammberDB não aparece na lista de aplicações do menu Iniciar. Use o seguinte comando para executar o HammerDB:

C:\Program Files\HammerDB-VERSION\hammerdb.bat

Substitua VERSION pela versão do HammerDB instalado.

Criar a associação e o esquema

Quando a aplicação está em execução, o primeiro passo é configurar a ligação para criar o esquema.

1. Clique duas vezes em SQL Server no painel Benchmark.
2. Selecione TPROC-C. Do site do HammerDB:

   O TPROC-C é a carga de trabalho OLTP implementada no HammerDB derivada da especificação TPROC-C com modificação para tornar a execução do HammerDB simples e rentável em qualquer um dos ambientes de base de dados suportados. A carga de trabalho TPROC-C do HammerDB é uma carga de trabalho de código aberto derivada da norma de referência TPROC-C e, como tal, não é comparável aos resultados TPROC-C publicados, uma vez que os resultados estão em conformidade com um subconjunto e não com a norma de referência TPROC-C completa. O nome da carga de trabalho do HammerDB TPROC-C significa "Benchmark de processamento de transações derivado da especificação TPC "C"".

3. Clique em OK

   

4. Clique em Esquema e, de seguida, clique duas vezes em Opções.

5. Preencha o formulário com o seu endereço IP, nome de utilizador e palavra-passe, conforme mostrado na imagem seguinte:

   

6. Defina o controlador ODBC do SQL Server para o controlador ODBC 18 para SQL Server

7. Neste caso, o Número de armazéns (a escala) está definido como 460, mas pode escolher um valor diferente. Algumas diretrizes sugerem 10 a 100 armazéns por CPU. Para este tutorial, defina este valor como 10 vezes o número de núcleos: 160 para uma instância de 16 núcleos.

8. Para Utilizadores virtuais para criar esquema, escolha um número entre 1 e 2 vezes o número de vCPUs do cliente. Pode clicar na barra cinzenta junto ao controlador de deslize para aumentar o número.

9. Limpe a opção Usar BPC

10. Clique em OK

11. Clique duas vezes na opção Criar abaixo da secção Criação de esquema para criar o esquema e carregar as tabelas. Quando terminar, clique no ícone de luz vermelha no centro superior do ecrã para destruir o utilizador virtual e avançar para o passo seguinte.

Se criou a sua base de dados com o modelo de recuperação Simple, pode querer alterá-lo novamente para Full neste momento para obter um teste mais preciso de um cenário de produção. Esta alteração não entra em vigor até fazer uma cópia de segurança completa ou diferencial para acionar o início da nova cadeia de registos.

Criar o script do controlador

O HammerDB usa o script do controlador para orquestrar o fluxo de declarações SQL para a base de dados de modo a gerar a carga necessária.

1. No painel Teste de referência, expanda a secção Script do controlador e clique duas vezes em Opções.
2. Verifique se as definições correspondem às que usou na caixa de diálogo Criação de esquema.
3. Escolha Script de acionador cronometrado.
4. A opção Checkpoint when complete força a base de dados a escrever tudo no disco no final do teste. Por isso, selecione esta opção apenas se planear executar vários testes seguidos.
5. Para garantir um teste exaustivo, defina Minutos de tempo de implementação gradual para 5 e Minutos para a duração do teste para 20.
6. Clique em OK para sair da caixa de diálogo.
7. Clique duas vezes em Carregar na secção Guião do controlador do painel Benchmark para ativar o guião do controlador.

Criar utilizadores virtuais

Normalmente, a criação de um carregamento realista requer a execução de scripts como vários utilizadores diferentes. Crie alguns utilizadores virtuais para o teste.

1. Expanda a secção Utilizadores virtuais e clique duas vezes em Opções.
2. Se definir a quantidade de armazéns (escala) como 160, defina os utilizadores virtuais como 16, uma vez que as diretrizes do TPROC-C recomendam uma proporção de 10 vezes para evitar o bloqueio de linhas. Selecione a caixa de verificação Mostrar saída para ativar as mensagens de erro na consola.
3. Clique em OK

Recolha de estatísticas de tempo de execução

O HammerDB e o SQL Server não recolhem facilmente estatísticas detalhadas de tempo de execução para si. Embora as estatísticas estejam disponíveis no SQL Server, têm de ser capturadas e calculadas regularmente. Se ainda não tiver um procedimento ou uma ferramenta para ajudar a captar estes dados, pode usar o procedimento nesta secção para captar algumas métricas úteis durante os testes. Os resultados são escritos num ficheiro CSV no diretório temp do Windows. Pode copiar os dados para uma Folha de cálculo do Google Sheets através da opção Colar especial > Colar CSV.

Para usar este procedimento, primeiro tem de ativar temporariamente os procedimentos de automatização OLE para escrever o ficheiro no disco. Não se esqueça de a desativar após o teste:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Segue-se o código para criar o procedimento sp_write_performance_counters no SQL Server Management Studio. Antes de iniciar o teste de carga, vai executar este procedimento no Management Studio:

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/***
LogFile path has to be in a directory that SQL Server can Write To.
*/
CREATE PROCEDURE [dbo].[sp_write_performance_counters] @LogFile varchar (2000) = 'C:\\WINDOWS\\TEMP\\sqlPerf.log', @SecondsToRun int =1600, @RunIntervalSeconds int = 2

AS

BEGIN
--File writing variables
DECLARE @OACreate INT, @OAFile INT, @FileName VARCHAR(2000), @RowText VARCHAR(500), @Loops int, @LoopCounter int, @WaitForSeconds varchar (10)
--Variables to save last counter values
DECLARE @LastTPS BIGINT, @LastLRS BIGINT, @LastLTS BIGINT, @LastLWS BIGINT, @LastNDS BIGINT, @LastAWT BIGINT, @LastAWT_Base BIGINT, @LastALWT BIGINT, @LastALWT_Base BIGINT
--Variables to save current counter values
DECLARE @TPS BIGINT, @Active BIGINT, @SCM BIGINT, @LRS BIGINT, @LTS BIGINT, @LWS BIGINT, @NDS BIGINT, @AWT BIGINT, @AWT_Base BIGINT, @ALWT BIGINT, @ALWT_Base BIGINT, @ALWT_DIV BIGINT, @AWT_DIV BIGINT

SELECT @Loops = case when (@SecondsToRun % @RunIntervalSeconds) > 5 then @SecondsToRun / @RunIntervalSeconds + 1 else @SecondsToRun / @RunIntervalSeconds end
SET @LoopCounter = 0
SELECT @WaitForSeconds = CONVERT(varchar, DATEADD(s, @RunIntervalSeconds , 0), 114)
SELECT @FileName = @LogFile + FORMAT ( GETDATE(), '-MM-dd-yyyy_m', 'en-US' ) + '.txt'

--Create the File Handler and Open the File
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OACreate OUT
EXECUTE sp_OAMethod @OACreate, 'OpenTextFile', @OAFile OUT, @FileName, 2, True, -2

--Write the Header
EXECUTE sp_OAMethod @OAFile, 'WriteLine', NULL,'Transactions/sec, Active Transactions, SQL Cache Memory (KB), Lock Requests/sec, Lock Timeouts/sec, Lock Waits/sec, Number of Deadlocks/sec, Average Wait Time (ms), Average Latch Wait Time (ms)'
--Collect Initial Sample Values
SET ANSI_WARNINGS OFF
SELECT
  @LastTPS= max(case when counter_name = 'Transactions/sec' then cntr_value end),
  @LastLRS = max(case when counter_name = 'Lock Requests/sec' then cntr_value end),
  @LastLTS = max(case when counter_name = 'Lock Timeouts/sec' then cntr_value end),
  @LastLWS = max(case when counter_name = 'Lock Waits/sec' then cntr_value end),
  @LastNDS = max(case when counter_name = 'Number of Deadlocks/sec' then cntr_value end),
  @LastAWT = max(case when counter_name = 'Average Wait Time (ms)' then cntr_value end),
  @LastAWT_Base = max(case when counter_name = 'Average Wait Time base' then cntr_value end),
  @LastALWT = max(case when counter_name = 'Average Latch Wait Time (ms)' then cntr_value end),
  @LastALWT_Base = max(case when counter_name = 'Average Latch Wait Time base' then cntr_value end)
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
'Transactions/sec',
'Lock Requests/sec',
'Lock Timeouts/sec',
'Lock Waits/sec',
'Number of Deadlocks/sec',
'Average Wait Time (ms)',
'Average Wait Time base',
'Average Latch Wait Time (ms)',
'Average Latch Wait Time base') AND instance_name IN( '_Total' ,'')
SET ANSI_WARNINGS ON
WHILE @LoopCounter <= @Loops
BEGIN
WAITFOR DELAY @WaitForSeconds
SET ANSI_WARNINGS OFF
SELECT
  @TPS= max(case when counter_name = 'Transactions/sec' then cntr_value end)   ,
  @Active = max(case when counter_name = 'Active Transactions' then cntr_value end)   ,
  @SCM = max(case when counter_name = 'SQL Cache Memory (KB)' then cntr_value end)   ,
  @LRS = max(case when counter_name = 'Lock Requests/sec' then cntr_value end)   ,
  @LTS = max(case when counter_name = 'Lock Timeouts/sec' then cntr_value end)   ,
  @LWS = max(case when counter_name = 'Lock Waits/sec' then cntr_value end)   ,
  @NDS = max(case when counter_name = 'Number of Deadlocks/sec' then cntr_value end)   ,
  @AWT = max(case when counter_name = 'Average Wait Time (ms)' then cntr_value end)   ,
  @AWT_Base = max(case when counter_name = 'Average Wait Time base' then cntr_value end)   ,
  @ALWT = max(case when counter_name = 'Average Latch Wait Time (ms)' then cntr_value end)   ,
  @ALWT_Base = max(case when counter_name = 'Average Latch Wait Time base' then cntr_value end)
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
'Transactions/sec',
'Active Transactions',
'SQL Cache Memory (KB)',
'Lock Requests/sec',
'Lock Timeouts/sec',
'Lock Waits/sec',
'Number of Deadlocks/sec',
'Average Wait Time (ms)',
'Average Wait Time base',
'Average Latch Wait Time (ms)',
'Average Latch Wait Time base') AND instance_name IN( '_Total' ,'')
SET ANSI_WARNINGS ON

SELECT  @AWT_DIV = case when (@AWT_Base - @LastAWT_Base) > 0 then (@AWT_Base - @LastAWT_Base) else 1 end ,
    @ALWT_DIV = case when (@ALWT_Base - @LastALWT_Base) > 0 then (@ALWT_Base - @LastALWT_Base) else 1 end

SELECT @RowText = '' + convert(varchar, (@TPS - @LastTPS)/@RunIntervalSeconds) + ', ' +
          convert(varchar, @Active) + ', ' +
          convert(varchar, @SCM) + ', ' +
          convert(varchar, (@LRS - @LastLRS)/@RunIntervalSeconds) + ', ' +
          convert(varchar, (@LTS - @LastLTS)/@RunIntervalSeconds) + ', ' +
          convert(varchar, (@LWS - @LastLWS)/@RunIntervalSeconds) + ', ' +
          convert(varchar, (@NDS - @LastNDS)/@RunIntervalSeconds) + ', ' +
          convert(varchar, (@AWT - @LastAWT)/@AWT_DIV) + ', ' +
          convert(varchar, (@ALWT - @LastALWT)/@ALWT_DIV)

SELECT  @LastTPS = @TPS,
    @LastLRS = @LRS,
    @LastLTS = @LTS,
    @LastLWS = @LWS,
    @LastNDS = @NDS,
    @LastAWT = @AWT,
    @LastAWT_Base = @AWT_Base,
    @LastALWT = @ALWT,
    @LastALWT_Base = @ALWT_Base

EXECUTE sp_OAMethod @OAFile, 'WriteLine', Null, @RowText

SET @LoopCounter = @LoopCounter + 1

END

--CLEAN UP
EXECUTE sp_OADestroy @OAFile
EXECUTE sp_OADestroy @OACreate
print 'Completed Logging Performance Metrics to file: ' + @FileName

END

GO

Executar o teste de carregamento TPROC-C

No SQL Server Management Studio, execute o procedimento de recolha através do seguinte script:

Use master
Go
exec dbo.sp_write_performance_counters

Na instância do Compute Engine onde instalou o HammerDB, inicie o teste na aplicação HammerDB:

1. No painel Benchmark, em Utilizadores virtuais, clique duas vezes em Criar para criar os utilizadores virtuais, o que ativa o separador Resultado do utilizador virtual.
2. Clique duas vezes em Executar imediatamente abaixo da opção Criar para iniciar o teste.
3. Quando o teste estiver concluído, verá o cálculo das transações por minuto (TPM) no separador Saída do utilizador virtual.
4. Pode encontrar os resultados do procedimento de recolha no diretório c:\Windows\temp.
5. Guarde todos estes valores numa folha do Google Sheets e use-os para comparar várias execuções de testes.