Medir o impacto da durabilidade atrasada no SQL Server 2016 e posterior

Medir o impacto da durabilidade atrasada no SQL Server 2016 e posterior

Medir o impacto da durabilidade atrasada no SQL Server 2016 e posterior 1
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Por: Aaron Bertrand | Atualizado: 2020-03-04 | Comentários Relacionados: Mais> Ajuste de desempenho

Seminário on-line gratuito MSSQLTips: SQL Server na VMWare Performance Optimization

Embora o VMWare hospede aplicativos essenciais do SQL Server em todo o mundo, existem várias opções de configuração e design que podem levar seus bancos de dados a uma interrupção. Suas dicas deste webcast serão sobre como configurar e otimizar servidores SQL em execução no VMWare, compreender melhor como o VMWare pode ser configurado da melhor maneira e como se comunicar melhor com sua equipe do VMWare.

Problema

Quando a durabilidade atrasada estreou no SQL Server 2014, publiquei um blog sobre

seu impacto em algumas cargas de trabalho hipotéticas
. Introdução ao SQL Server 2016
uma nova visão de gerenciamento dinâmico (DMV),

sys.dm_exec_session_wait_stats
, que por coincidência revela maior
detalhes sobre como exatamente o recurso de durabilidade atrasada pode ajudar sessões individuais
faça seu trabalho mais rapidamente.

Com a ressalva de que esse recurso só faz sentido quando uma pequena quantidade de dados
perda é aceitável, continue lendo para ver como eu usei esse novo DMV para demonstrar que o atraso
a durabilidade pode ser benéfica para determinadas cargas de trabalho. Eu vou mostrar como
Eu usei esse novo DMV para demonstrar que a durabilidade atrasada pode ser benéfica para
certas cargas de trabalho, com a ressalva de que esse recurso só faz sentido quando uma pequena
quantidade de perda de dados é aceitável.

Solução

Temos uma carga de trabalho que insere milhares de linhas por vez de uma fonte plana
e exclui milhares de linhas por vez depois de processadas. Se
Se um desses processos falhar ou houver uma falha em algum momento, as inserções podem facilmente
ser gerado novamente a partir da fonte plana, e as exclusões simplesmente não acontecerão
até a próxima vez em que forem tentadas. (Minha amiga e colega, Melissa Connors
(@MelikaNoKaOi),
também blogou sobre

sua utilidade ao limpar dados
.)

Claro, eu não sou capaz de ativar algo assim em uma produção
ambiente e limpe minhas mãos. Então, eu criei um par de bancos de dados em um desenvolvedor
meio Ambiente; um para representar a carga de trabalho existente e o outro para permitir atrasos
durabilidade nessa carga de trabalho:

CREATE DATABASE DDTest_Off 
    ON (    name = N'DDTest_Off_data', size = 32768MB, filegrowth = 1024MB,
        filename = N'K:SQLDataDDDDTest_Off.mdf')
LOG ON (    name = N'DDTest_Off_log',  size = 16384MB, filegrowth = 1024MB,
        filename = N'J:SQLDataDDDDTest_Off.ldf');
GO CREATE DATABASE DDTest_On
    ON (    name = N'DDTest_On_data',  size = 32768MB, filegrowth = 1024MB,
        filename = N'K:SQLDataDDDDTest_On.mdf')
LOG ON (    name = N'DDTest_On_log',   size = 16384MB, filegrowth = 1024MB,
        filename = N'J:SQLDataDDDDTest_On.ldf');
GO ALTER DATABASE DDTest_On SET DELAYED_DURABILITY = ALLOWED;

Em cada banco de dados, criei a seguinte tabela:

CREATE TABLE dbo.TheTable
(
  TheID   int              IDENTITY(1,1) PRIMARY KEY,
  TheDate datetime         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  RowGuid uniqueidentifier NOT NULL DEFAULT NEWID() );

E dois procedimentos para simular uma certa carga de trabalho (embora simplificada):

CREATE PROCEDURE dbo.AddSomeData
  @Iterations int = 1000
AS
BEGIN
  SET NOCOUNT ON;   DECLARE @i int = 1;
  WHILE @i <= @Iterations
  BEGIN
    BEGIN TRANSACTION;     INSERT dbo.TheTable(TheDate)
      SELECT TOP (1000) sysdatetime()
      FROM sys.all_columns AS s;     COMMIT TRANSACTION -- WITH (DELAYED_DURABILITY = ON);
    --                 ^^ not commented out in DDTest_On     SET @i += 1;
  END
END
GO CREATE PROCEDURE dbo.DeleteSomeData
  @Iterations int = 1000
AS
BEGIN
  SET NOCOUNT ON;   DECLARE @i int = 1;
  WHILE @i <= @Iterations
  BEGIN
    BEGIN TRANSACTION;     DELETE TOP (1000) dbo.TheTable;     COMMIT TRANSACTION -- WITH (DELAYED_DURABILITY = ON);
    --                 ^^ not commented out in DDTest_On     SET @i += 1;
  END
END
GO

O objetivo era executar os quatro testes a seguir, com 8 e 16 sessões simultâneas:

Leia Também  Aprenda com os outros - SQLBlog.org

  1. INSERIR carga de trabalho com durabilidade atrasada OFF
  2. INSERIR carga de trabalho com durabilidade atrasada ON
  3. DELETE carga de trabalho com durabilidade atrasada OFF
  4. EXCLUIR carga de trabalho com durabilidade atrasada ON

Enquanto esses procedimentos estavam em execução, inserindo ou excluindo linhas de 8MM ou 16MM,
Eu queria capturar algumas métricas sobre o sistema e as sessões específicas.
Eu segui esta sequência:

  1. Capture as estatísticas do arquivo virtual do log antes de iniciar qualquer carga de trabalho
  2. Inicie um loop coletando estatísticas de espera por sessão a cada poucos segundos
  3. Inicie 1 iteração da carga de trabalho usando 8 ou 16 threads no SQLQueryStress
    1. “Registre” o SPID antes de executar a carga de trabalho
    2. Execute a carga de trabalho
    3. “Cancelar o registro” do SPID
  4. Pare o loop coletando estatísticas de espera
  5. Capture as estatísticas do arquivo virtual do log após o término da carga de trabalho

No meu Utility banco de dados, criei o seguinte
objetos. Primeiro, um esquema para separação para outros objetos utilitários:

USE Utility;
GO CREATE SCHEMA DD AUTHORIZATION dbo;
GO

Em seguida, uma tabela vazia para capturar estatísticas de arquivos virtuais:

SELECT TOP () 
  TestID     = CONVERT(tinyint, 1),
  StepID     = CONVERT(tinyint, 1),
  EventTime  = sysdatetime(),
  *
INTO DD.FileStats
FROM sys.dm_io_virtual_file_stats(DB_ID('model'), 2);
GO

E o procedimento para capturar as estatísticas do arquivo virtual no início (etapa 2)
e novamente no final (etapa 5):

CREATE PROCEDURE DD.LogFileStats
  @TestID tinyint,
  @StepID tinyint
AS
BEGIN
  INSERT DD.FileStats
    SELECT @TestID, @StepID, sysdatetime(), *
    FROM sys.dm_io_virtual_file_stats
    (DB_ID(CASE WHEN @TestID IN (1,3) THEN 'DDTest_Off' ELSE 'DDTest_On' END), 2);
END
GO

Em seguida, uma tabela vazia para capturar estatísticas de espera da DMV específica da sessão:

SELECT TOP () 
  TestID     = CONVERT(tinyint, 1),
  EventTime  = sysdatetime(),
  *
INTO DD.WaitStats
FROM sys.dm_exec_session_wait_stats;

E o procedimento que irá registrá-los em um loop:

CREATE PROCEDURE DD.LogWaitStats
  @TestID tinyint
AS
BEGIN
  WHILE 1 = 1
  BEGIN
    INSERT DD.WaitStats
      SELECT @TestID, sysdatetime(), *
        FROM sys.dm_exec_session_wait_stats
       WHERE session_id IN
       (
         SELECT SessionID
           FROM DD.RunningSPIDs
          WHERE EndTime IS NULL
       );     WAITFOR DELAY '00:00:03';
  END
END
GO

Seguido por uma tabela vazia para monitorar processos da carga de trabalho:

CREATE TABLE DD.RunningSPIDs
(
  TestID     tinyint,
  SessionID  int,
  StartTime  datetime2 NOT NULL DEFAULT sysdatetime(),
  EndTime    datetime2
);

E procedimentos para registrar / cancelar o registro desses processos:

Leia Também  Qual foi a pior decisão que você já tomou sobre o banco de dados?
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br
CREATE PROCEDURE DD.RegisterSPID
  @TestID tinyint
AS
BEGIN
    INSERT DD.RunningSPIDs(SessionID, TestID)
      SELECT @@SPID, @TestID
      WHERE NOT EXISTS
      (
        SELECT 1 FROM DD.RunningSPIDs
        WHERE TestID    = @TestID
          AND SessionID = @@SPID
          AND EndTime   IS NULL
      );
END
GO CREATE PROCEDURE DD.DeregisterSPID
  @TestID tinyint
AS
BEGIN
    UPDATE DD.RunningSPIDs
       SET EndTime   = sysdatetime()
     WHERE TestID    = @TestID
       AND SessionID = @@SPID
       AND EndTime   IS NULL;
END
GO

Executando os testes

Por exemplo, para o teste 1, onde estamos adicionando dados usando 8 threads no banco de dados
com durabilidade atrasada desativada, a sequência é a seguinte:

  1. Primeiro, chamei o seguinte em uma janela de consulta:
    EXEC Utility.DD.LogFileStats @TestID = 1, @StepID = 10; 
    EXEC Utility.DD.LogWaitStats @TestID = 1;
    -- EXEC Utility.DD.LogFileStats @TestID = 1, @StepID = 20;	
  2. Em seguida, iniciei o SQLQueryStress com 1 iteração, 8 threads e o seguinte
    lote:

    -- a comment to remind me to perform step 1
    
    -- When a process starts up to run the workload, 
    -- call the following procedure to register a SPID:
    
    EXEC Utility.DD.RegisterSPID @TestID = 1;
    
    -- Then they would run the procedure that adds the data:
    
    EXEC DDTest_On.dbo.AddSomeData @iterations = 1000;
    
    -- Then deregister themselves:
    
    EXEC Utility.DD.DeregisterSPID @TestID = 1;
    
    -- a comment to remind me to perform step 3
    	
  1. Quando todos os threads foram concluídos, parei manualmente a janela de consulta em que
    as esperas estavam sendo coletadas e, em seguida, chamaram a parte comentada para registrar
    as informações “depois” das estatísticas do arquivo virtual:

    EXEC Utility.DD.LogFileStats @TestID = 1, @StepID = 20; 
    	

Repeti por mais 7 testes, depois analisei. Depois de concluir os testes com
16 threads, copiei os dados de cada tabela para uma tabela de sombra (por exemplo,
DD.LogFileStats16Threads) apenas para mantê-los separados.
Então eu repeti o mesmo para os 8 testes de thread.

Analisando os resultados

Demorou um pouco para examinar todas as estatísticas que eu colecionei para determinar
o que seria significativo para compartilhar. As principais diferenças entre qualquer uma das cargas de trabalho
comparando quando a durabilidade atrasada estava ligada e quando estava desligada eram:

  • Total de gravações e paradas de gravação no arquivo de log (quantidade total de dados gravados
    não variou)
  • Esperas médias do WRITELOG (a maioria das outras esperas estava dentro da variação nominal)
  • Duração média que levou cada encadeamento para executar sua carga de trabalho

Eu escrevi essa grande consulta de agregação do Canada Goose para juntar isso
em um pequeno conjunto de resultados para mim:

;WITH LogFileStats AS 
(
  SELECT Threads, TestID, TotalWrites, TotalWriteStall_ms FROM
  (
    SELECT Threads = 16, TestID, StepID,
        TotalWrites = num_of_writes - LAG(num_of_writes, 1)
          OVER (PARTITION BY TestID ORDER BY StepID),
        TotalWriteStall_ms = io_stall_write_ms - LAG(io_stall_write_ms, 1)
          OVER (PARTITION BY TestID ORDER BY StepID)
      FROM DD.FileStats16Threads
    UNION ALL
    SELECT Threads = 8,  TestID, StepID,
        num_of_writes - LAG(num_of_writes, 1)
          OVER (PARTITION BY TestID ORDER BY StepID),
        io_stall_write_ms - LAG(io_stall_write_ms, 1)
          OVER (PARTITION BY TestID ORDER BY StepID)
      FROM DD.FileStats8Threads
  ) AS x WHERE StepID = 20
),
WritelogWaitStats (Threads, TestID, AvgWritelogWaits_ms) AS
(
  SELECT Threads, TestID, CONVERT(decimal(10,2),AVG(1.0 * HighestWait)) FROM
  (
    SELECT Threads = 16, session_id, TestID, HighestWait = MAX(wait_time_ms)
      FROM DD.WaitStats16Threads WHERE wait_type = 'WRITELOG'
      GROUP BY session_id, TestID
    UNION ALL
    SELECT Threads = 8,  session_id, TestID, HighestWait = MAX(wait_time_ms)
      FROM DD.WaitStats8Threads WHERE wait_type = 'WRITELOG'
      GROUP BY session_id, TestID
  ) AS y GROUP BY Threads, TestID
),
WorkloadDurations (Threads, TestID, AvgDuration_s) AS
(
  SELECT Threads = 16, TestID, AVG(1.0*DATEDIFF(SECOND, StartTime, EndTime))
    FROM DD.RunningSPIDs16Threads GROUP BY TestID
  UNION ALL
  SELECT Threads = 8, TestID, AVG(1.0*DATEDIFF(SECOND, StartTime, EndTime))
    FROM DD.RunningSPIDs8Threads  GROUP BY TestID
)
SELECT Test = CASE WHEN l.TestID < 3 THEN 'Insert' ELSE 'Delete' END
    + ', ' + RTRIM(l.Threads) + ' threads, DD = '
    + CASE l.TestID % 2 WHEN 1 THEN 'OFF' ELSE 'ON' END,
  l.TotalWrites, l.TotalWriteStall_ms, w.AvgWritelogWaits_ms,
  AvgDuration_s = CONVERT(decimal(10,2), d.AvgDuration_s)
FROM LogFileStats AS l
INNER JOIN WritelogWaitStats AS w
  ON  l.Threads = w.Threads AND l.TestID  = w.TestID
INNER JOIN WorkloadDurations AS d
  ON  l.Threads = d.Threads AND l.TestID  = d.TestID
ORDER BY l.Threads, l.TestID;

Os resultados iniciais são assim:

Você pode ver as tendências que destacam os benefícios do uso de durabilidade atrasada
se você digitalizar visualmente: menos gravações gerais de log, muito menos tempo gasto em paradas de gravação,
quase nenhum WRITELOG espera e uma duração média mais baixa. Podemos olhar para esses últimos
duas métricas nos seguintes gráficos:

Algumas advertências:

  • Este estava em um servidor com E / S muito rápidas (Dell
    XtremIO
    ) Você pode imaginar que qualquer efeito em baias, esperas e duração que eu tenha
    observado aqui pode ser muito mais pronunciado em um subsistema de disco menos capaz.
  • Mesmo com durabilidade atrasada, um sistema ainda terá esperas WRITELOG,
    já que o log deve acontecer eventualmente. Então, não espere que eles desapareçam
    no nível da instância; a chave é simplesmente que sua carga de trabalho altamente transacional
    não está mais esperando.
Leia Também  Painéis de servidor e banco de dados no Azure Data Studio

Conclusão

É claro que a durabilidade atrasada pode trazer benefícios a uma carga de trabalho com muita gravação,
especialmente aquele em que o WRITELOG espera já é predominante. Se você tem um sistema
onde sua carga de trabalho está sendo amplamente eliminada ou onde os dados podem ser facilmente reconstruídos
de outras fontes no caso de um desastre (por exemplo, essa não é sua única cópia),
pode valer a pena considerar testar esse recurso. Eu posso não ter capturado exatamente
métricas que você está interessado em ver para sua carga de trabalho, como pode ser após outras
criadores de problemas, como espera de disco em geral ou taxas de liberação de log. Mas espero ter
descreveu como você pode configurar seus próprios testes, dividir os dados de uma maneira que
para você, para que você possa sintonizar melhor seus gargalos mais problemáticos. O nível da sessão
wait stats O DMV realmente oferece uma camada extra de análise para ajudar você a ver a precisão
impactos desse recurso (ou de qualquer recurso, na verdade).

Próximos passos

Continue lendo para obter dicas relacionadas e outros recursos:

Última atualização: 2020-03-04

Sobre o autor

Aaron Bertrand (@AaronBertrand) é um tecnólogo apaixonado com experiência no setor que remonta ao ASP clássico e SQL Server 6.5. Ele é editor-chefe do blog relacionado ao desempenho, SQLPerformance.com, e também blogs no sqlblog.org.

Ver todas as minhas dicas



cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br