none
Mesmo servidor, mesma query, dois planos de execução diferentes RRS feed

  • Pergunta

  • Boa tarde pessoal, estou com a seguinte situação, tenho uma query que demora quase 4 minutos pra ser executada, porém toda vez que eu faço rebuild nos indexes, o tempo da query passa a ser menos de 1 segundo, e o problema é que o plano de execução da query é diferente antes do rebuild e depois do rebuild, mas eu não quero ter que ficar dando rebuild a cada meia hora. Alguém pode me ajudar?
    quinta-feira, 2 de janeiro de 2020 18:52

Todas as Respostas

  • Deleted
    quinta-feira, 2 de janeiro de 2020 19:18
  • Olá José Diz,

    esse é o plano anterior ao rebuild: https://www.brentozar.com/pastetheplan/?id=rkzm2ojJU,

    esse é o plano após o rebuild: https://www.brentozar.com/pastetheplan/?id=HJMI6ojJU

    O comando é o abaixo, fazendo em um indice só, mas é um plano de manutenção onde todos os indices do banco de dados são alterados conforme esses parametros, deixando a query muito mais rapida

    ALTER INDEX [PK_t0_Bin] ON [dbo].[t0_Bin] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)

    quinta-feira, 2 de janeiro de 2020 19:32
  • Deleted
    quinta-feira, 2 de janeiro de 2020 20:10
  • Alex,

    Seguindo na mesma linha de raciocínio apresentada pelo outro argumentador.

    Você compartilhar com vocês dois exemplos de código, o primeiro vai identificar as Heap Tables existentes no seu banco de dados e o seguinte as taxas de fragmentação de cada tabela:

    1 - Identificando as Heap Tables, com base, na junão entre as visões de catálogo de sistema sys.indexes, sys.tables e sys.schemas:

    SELECT SCH.name + '.' + TBL.name as TableName 
      from sys.indexes as IDX 
           inner join sys.tables as TBL on TBL.object_id = IDX.object_id
           inner join  sys.schemas as SCH on TBL.schema_id = SCH.schema_id 
      where IDX.type = 0 --> Heap 
      order by TableName;

    2 - Identificando as taxas de fragmentação, elaborado com base na documentação oficial da DMV sys.dm_db_index_physical_stats e sys.indexes:

    Select object_name(ddips.object_id) As 'Tabela', 
           si.name As 'Índice', 
           convert(decimal(5,2),isnull(ddips.avg_fragmentation_in_percent,0)) As '% Média de Fragmentação', 
           ddips.page_count As 'Páginas', 
           ddips.compressed_page_count As 'Páginas compactadas', 
           ddips.record_count As 'Registros', 
           ddips.ghost_record_count As 'Registros Fantasmas' 
    From sys.dm_db_index_physical_stats(db_id(), object_id('DigiteAquioNomedaSuaTabela'),null, null, 'detailed') ddips Inner Join sys.indexes si 
          on si.object_id = ddips.object_id 
    Where ddips.avg_fragmentation_in_percent > 0 
    Go

    Este terceiro exemplo também extraído da documentação Oficial Microsoft relacionada a DMV: sys.dm_db_index_physical_stats vai lhe ajudar a identificar as taxas de fragmentação e o mesmo possibilita executar o processo de Rebuild ou Reorganize de cada índice:

    USE NomeDoSeuBancoDeDados
    Go

    SET NOCOUNT ON
    Go

    DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command nvarchar(4000); -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function -- and convert object and index IDs to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; -- Open the cursor. OPEN partitions; -- Loop through the partitions. WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' + @command; END; -- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions; -- Drop the temporary table. DROP TABLE #work_to_do; GO


    Espero ter ajudado!


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 2 de janeiro de 2020 22:11
  • Olá Jose Diz, esse é o plano de execução da visão: https://www.brentozar.com/pastetheplan/?id=HyyaN32JI

    Respondendo a sua pergunta, não é mostrado fragmentação elevada, mas o fato de dar o rebuild faz o otimizador ler um numero muito inferior de páginas, também percebi que o rebuild não precisa ser feito em todos os indices, somente nos indices da tabela t5_Str

    sexta-feira, 3 de janeiro de 2020 12:36
  • Obrigado por compartilhar Junior Galvão, vou testar esses scripts no banco de dados.
    sexta-feira, 3 de janeiro de 2020 12:37
  • Deleted
    sexta-feira, 3 de janeiro de 2020 13:04
  • José Diz, olha o tanto de reads que o otimizador faz para mostrar o resultado do select, após o rebuild esse número cai para mais ou menos 20.000 reads.

    sexta-feira, 3 de janeiro de 2020 14:26
  • Alex,

    Você realizou o processo de rebuild dos índices envolvidos nas tabelas, neste caso, a parte das estatísticas tanto das tabelas e índices foi atualizada! Mas você esta nos mostrando que existem ainda um número considerável de reads sendo feito.

    Este pode ser um indicador da ocorrência de estatísticas desatualizadas das tabelas internas do seu banco de dados, que se relacionam que com suas tabelas, provocando um comportamento denominada Estatísticas Cumulativas.

    O código de exemplo abaixo, poderá nos ajudar a obter os valores e contadores relacionados a este comportamento:

    SELECT s2.dbid, 
        s1.sql_handle,  
        (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
          ( (CASE WHEN statement_end_offset = -1 
             THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
             ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,
        execution_count, 
        plan_generation_num, 
        last_execution_time,   
        total_worker_time, 
        last_worker_time, 
        min_worker_time, 
        max_worker_time,
        total_physical_reads, 
        last_physical_reads, 
        min_physical_reads,  
        max_physical_reads,  
        total_logical_writes, 
        last_logical_writes, 
        min_logical_writes, 
        max_logical_writes  
    FROM sys.dm_exec_query_stats AS s1 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
    WHERE s2.objectid is null 
    ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

    O mesmo pode ser elaborado com base na documentação Oficial Microsoft da DMV: sys.dm_exec_query_stats

    Você poderia retornar os valores apresentados por este código?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sexta-feira, 3 de janeiro de 2020 15:12
  • Deleted
    sexta-feira, 3 de janeiro de 2020 19:32
  • A versão do SQL Server é a 2016 SP2-CU8 (build 13.0.5426.0)?

    Sim, é exatamente essa versão

    Qual é o nível de compatibilidade do banco de dados?

     Nível 100

    O valor de CardinalityEstimationModelVersion está 70. Você pode informar como está configurado LEGACY_CARDINALITY_ESTIMATION?

    O Valor está configurado como 0

    sexta-feira, 3 de janeiro de 2020 20:02
  • Deleted
    sexta-feira, 3 de janeiro de 2020 20:07
  • Vou verificar com o desenvolvedor da aplicação, não sei dizer se há algum motivo para manter a compatibilidade.
    sexta-feira, 3 de janeiro de 2020 20:11
  • Deleted
    sexta-feira, 3 de janeiro de 2020 20:16
  • resultado do primeiro script

    resultado do segundo script


    sexta-feira, 3 de janeiro de 2020 20:25
  • Deleted
    sexta-feira, 3 de janeiro de 2020 20:47
  • SIm, eu que criei esses indices pra ver se poderia ter alguma melhoria na execução...
    sexta-feira, 3 de janeiro de 2020 20:53
  • Um ponto que eu percebi também é após o rebuild dos indexes a query fica rapida, até que sejam inseridos novos registros, a partir do momento que um registro é inserido, tudo volta a ficar lento até que seja feito novamente o rebuild
    sexta-feira, 3 de janeiro de 2020 21:05
  • Alex,

    O quanto de registros foram inseridos?

    Seria possível pensarmos em alterar o nível de compatibilidade para 130 que é número referente a versão 2016?

    Como você esta trabalhando com o SQL Server 2016 SP2 uma outra funcionalidade interessante que poderíamos pensar em adotar seria o Query Store.

    Tomando com base o questionamento da criação de índices, bem como, a existência de índices possivelmente inúteis, veja o exemplo abaixo, o mesmo vai lhe permitir identificar índices nunca utilizados:

    SELECT  OBJECT_NAME(i.[object_id]) AS [Table Name] ,
            i.name
    FROM    sys.indexes AS i
            INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
    WHERE   i.index_id NOT IN ( SELECT  s.index_id
                                FROM    sys.dm_db_index_usage_stats AS s
                                WHERE   s.[object_id] = i.[object_id]
                                        AND i.index_id = s.index_id
                                        AND database_id = DB_ID() )
            AND o.[type] = 'U'
    ORDER BY OBJECT_NAME(i.[object_id]) ASC ;

    Este exemplo pode ser obtido através da suíte de scripts Startet Pack!

    Pois bem, um outro exemplo de código que eu sempre utilizo, pode ser implementado através do uso das DMV:

    Sendo este uma ferramenta útil que permite identificar uma possível ausência de índices, através do recurso implementado a partir da versão 2008 denominado Missing Index, veja o exemplo:

    SELECT avg_total_user_cost, avg_user_impact,  user_seeks, user_scans,  ID.equality_columns, 
                 ID.inequality_columns, ID.included_columns, ID.statement
    FROM sys.dm_db_missing_index_group_stats GS LEFT OUTER JOIN sys.dm_db_missing_index_groups IG 
                                                                                        On (IG.index_group_handle = GS.group_handle)
    																				LEFT OUTER JOIN sys.dm_db_missing_index_details ID 
    																				On (ID.index_handle = IG.index_handle) 
    Where ID.database_id = DB_ID('InformeAquiONomeDoBancoDeDados')
    ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC

    Recomendo fortemente que você acesse os links abaixo produzidos pelos meus amigos MVPs Luciano Moreira e Fabiano Amorin, os quais tem objetivo de te ajudar a entender mais sobre Estatísticas de Tabelas e Índices, como também, da parte do Transaction-Log existente no SQL Server:

    • Luciano Moreira - Log: https://www.youtube.com/playlist?list=PLGaWZx_3bztdZNnrFSluDpaPjaB6IAx6- 
    • Fabiano Amorin - Estatísticas:
      https://www.youtube.com/playlist?list=PL3hmzvdnIMYoOm_iDpRKehoEQUClOkfFR
      https://www.youtube.com/playlist?list=PL3hmzvdnIMYrx8C_vy5MWuuqF1XrvEs8G

    Em relação ao LEGACY_CARDINALITY_ESTIMATION trata-se de uma característica existente no SQL Server implementada a partir da versão 7.0 isso em 1998 que demorou mais de 20 para ser atualizada.

    Isso ocorreu em 2012 a partir do momento em que a Microsoft adicionou novos modelos para identificar o custo de processamento de uma Query, sendo assim, surgui então o Cardinality_Estimation 120, valor relacionado a nível de compatibilidade 120 o qual identifica a versão do SQL Server 2014.

    A partir do SQL Server 2016 SP1 o Microsoft adicionou uma Query Hint denominada FORCE_LEGACY_CARDINALITY_ESTIMATION, ela poderá ser útil para forçar sua query a utilizar uma Legacy_Cardinality_Estamation 70 mesmo que você venha a ter a necessidade de utilizar um nível de compatibilidade 120 ou superior. Então se não for possível alterar o nível de compatibilidade para 130 mas para 120 temos esta possibilidade.

    Recomendo que você acesse a documentação Oficial Microsoft para conhecer mais sobre Estimativa de cardinalidade.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]





    • Editado Junior Galvão - MVPMVP sábado, 4 de janeiro de 2020 13:51 Adicionando o hyperlink para acessar a sys.dm_db_missing_index_details
    • Sugerido como Resposta Junior Galvão - MVPMVP segunda-feira, 13 de janeiro de 2020 23:40
    sexta-feira, 3 de janeiro de 2020 22:34
  • Deleted
    sábado, 4 de janeiro de 2020 12:41
  • Junior Galvão, obrigado pelas informações e pelas playlists que você passou, no momento estou assistindo a todos os vídeos e são muito interessantes
    sábado, 4 de janeiro de 2020 21:29
  • Junior Galvão, obrigado pelas informações e pelas playlists que você passou, no momento estou assistindo a todos os vídeos e são muito interessantes

    Alex,

    Ok, que bom, fico feliz, espero que venha a ser útil.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    sábado, 4 de janeiro de 2020 22:57
  • Deleted
    domingo, 5 de janeiro de 2020 15:24
  • José Diz, grato por toda a explicação que você me deu, amanhã vou começar fazendo o passo 1 que você recomendou fazer antes de todos os outros passos e qualquer dúvida eu posto aqui.

    Agradeço a você e ao Junior Galvão por toda a ajuda que me deram até aqui, espero que eu consiga resolver esse problema com todas as dicas que me deram.

    domingo, 5 de janeiro de 2020 17:00
  • Alex,

    Estamos a disposição.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    segunda-feira, 6 de janeiro de 2020 15:43
  • Atualizando a situação, devido à pressão do problema estar ocorrendo a varios dias, tive que abrir um chamado na Microsoft.

    O técnico analisou o problema e a conclusão foi que o Sql Server estava escolhendo o plano de execução de forma errônea, e a solução foi forçar na query um LOOP no Inner Join.

    Foi passado para o time de aplicação alterar a query e agora o problema está resolvido.

    Agradeço a todos pelo conhecimento que eu adquiri nesse post.

    segunda-feira, 6 de janeiro de 2020 18:43
  • Alex,

    Sério, esta orientação: O técnico analisou o problema e a conclusão foi que o Sql Server estava escolhendo o plano de execução de forma errônea, e a solução foi forçar na query um LOOP no Inner Join.

    Você saberia nos dizer quem do tipo de suporte Microsoft fez o atendimento e orientação? Foi alguém do time PFE?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 7 de janeiro de 2020 01:21
  • Então, Junior Galvão, eu não vou saber te responder, porque o contrato com a Microsoft é do cliente, eles só redirecionaram o atendimento pra mim, mas é sério rs, essa foi a conclusão.

    Alias perguntei a ele o que eu poderia dizer caso perguntassem porque funcionava antes e agora não funcionava mais e ele não soube me dar uma resposta concreta.

    terça-feira, 7 de janeiro de 2020 14:44
  • Deleted
    terça-feira, 7 de janeiro de 2020 15:16
  • Então, Junior Galvão, eu não vou saber te responder, porque o contrato com a Microsoft é do cliente, eles só redirecionaram o atendimento pra mim, mas é sério rs, essa foi a conclusão.

    Alias perguntei a ele o que eu poderia dizer caso perguntassem porque funcionava antes e agora não funcionava mais e ele não soube me dar uma resposta concreta.

    Alex,

    Ok, eu compreendo.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 7 de janeiro de 2020 18:08
  • Alex, sobre "o Sql Server estava escolhendo o plano de execução de forma errônea", isto às vezes ocorre e a solução de forçar o tipo de junção é uma opção, para certos casos.



    Alex,

    Isso pode acontecer, e foi possível presenciar isso neste post!

    Mas posso dizer que são raras, ou melhor raríssimas as vezes que este tipo de comportamento ocorre por parte do SQL Server, todavia, como o seu cenário esta apresentando uma versão de SQL Server bem superior ao nível de compatibilidade que esta sendo utilizado, se levarmos em consideração o LEGACY_CARDINALITY_ESTIMATION, que esta sendo utilizado este sim pode ser um dos principais vilões.

    Hoje realizei mais uma análise em seus planos de execução, inclusive compartilhei com o time de MVPs do SQL Server nos Estados Unidos, um dos detalhes que eles destacaram é a possibilidade de estarmos se deparando com um ambiente que apresente as estatísticas das tabelas internas inconsistente, e estatísticas criadas automaticamente pelo SQL Server ("normalmente denominadas inicialmente com a sigla _WA") durante o uso dos comandos Select em conjuntos cláusulas Where, que venham a apresentar a estrutura dos Histogramas desatualizadas, principalmente para a distribuição de faixas de valores duplicados.

    E ai que eu gostaria de perguntar:

    Você tem ideia do volume de dados duplicados que possam existir em suas tabelas?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 7 de janeiro de 2020 18:18
  • Junior Galvão, essa informação de duplicidade de dados eu não consigo saber. Porém uma boa noticia é que eu posso tirar o nivel de compatibilidade do banco do SQL Server 2008 para SQL Server 2016, você acha que isso resolveria o problema?
    terça-feira, 7 de janeiro de 2020 21:41
  • Deleted
    terça-feira, 7 de janeiro de 2020 21:44
  • Alex,

    Se for possível sim, inclusive nos posts anteriores já haviamos destacado esta importante alteração, bem como, os links para poderiam lhe ajudar.

    Peço que leia novamente os posts anteriores desta postagem que podem justamente lhe ajudar a entender melhor tudo o que estamos propondo, inclusive a utilização do Query Store como uma alternativa mais avançada para ganho de performance.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 7 de janeiro de 2020 23:02
  • Deleted
    terça-feira, 7 de janeiro de 2020 23:20