Inquiridor
Mesmo servidor, mesma query, dois planos de execução diferentes

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?
Todas as Respostas
-
-
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)
-
-
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
GoSET NOCOUNT ON
GoDECLARE @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]
-
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
-
-
-
-
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_statsVocê 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]
-
-
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
-
-
-
-
resultado do primeiro script
resultado do segundo script
- Editado Alex Pereira Gonçalves - SQL sexta-feira, 3 de janeiro de 2020 20:26
-
-
-
-
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:
- sys.dm_db_missing_index_group_stats;
- sys.dm_db_missing_index_groups; e
- sys.dm_db_missing_index_details
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
-
-
-
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]
-
-
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.
-
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]
-
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.
-
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]
-
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.
-
-
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]
-
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]
- Sugerido como Resposta Junior Galvão - MVPMVP segunda-feira, 13 de janeiro de 2020 23:40
-
-
Deleted
- Sugerido como Resposta Junior Galvão - MVPMVP segunda-feira, 13 de janeiro de 2020 23:40
-
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]
-