none
Index Scan - Estatísticas Atualizadas RRS feed

  • Discussão Geral

  • Boa tarde,

    Tenho uma procedure que efetua algumas consultas baseadas em parâmetros. Populo uma tabela temporária com os dados da tabela a e depois faço JOIN com a tabela b (o que deveria retornar cerca de 70 mil registros).


    O problema é que está ocorrendo INDEX SCAN na tabela b  que tem 44 milhões de registros.

    Tenho um índice totalmente desfragmentado e estatísticas atualizadas para as duas colunas que utilizo no JOIN e por isso não consigo entender o porque do INDEX SCAN.

    Se forço o uso do indice WITH (INDEX = IX_01), ocorre INDEX SEEK e a consulta é executada rapidamente, mas não gostaria de forçar o uso do índice por alguns motivos (entre eles está o fato de alguém pode excluir esse índice ou de ele estar muito fragmentado um dia e gerar um plano de execução muito ruim).


    Alguém tem alguma ideia pra me ajudar investigar outra coisa? Porque entendo que ele só deveria estar fazendo scan, caso o número de linhas retornadas fossem cerca de 30% do tamanho da tabela (o que não passa nem perto).

    Muito obrigada.
    Mariana



    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    quarta-feira, 10 de setembro de 2014 18:04

Todas as Respostas

  • Mariana,

    Provavelmente este índice não possui todos os campos necessários para a consulta (output) e assim ela procura obter uma referência ao PK de sua tabela para conseguir as demais informações, ocorrendo o INDEX SCAN.

    Realize (preferencialmente em um ambiente de testes isolado) um REBUILD deste índice incluíndo as demais colunas que sua instrução SELECT utiliza para exibição no parâmetro INCLUDE e todas as demais colunas utilizadas em cláusulas WHERE ou JOIN desta instrução efetivamente indexados.

    Veja abaixo um exemplo para adaptar à sua necessidade:

    CREATE NONCLUSTERED INDEX [IDX_VENDA_CLIENTE] ON [TB_CLIENTES] 
    ([CD_FILIAL] ASC, [CD_VENDEDOR] ASC,[DT_ENTREGA] ASC)
    INCLUDE ([NR_NOTAFISCAL],[CD_QTD],[FL_RECEBIDO]) 
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF, 
    IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY] GO


    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    quarta-feira, 10 de setembro de 2014 19:51
    Moderador
  • Durval

    Quando forço o uso do índice com o hint WITH (INDEX = ), ele faz um Index seguido de Key Lookup (no indice clustered)... que acredito ser o "esperado"

    A query melhora e muita assim.. mas realmente tenho receio de deixar esse hint em produção.



    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */


    • Editado Mariana Del Nero quarta-feira, 10 de setembro de 2014 20:11 Complemento Resposta
    quarta-feira, 10 de setembro de 2014 20:07
  • Mariana,

    Concordo com você, eu também não acredito que forçar o uso de um índice (através de um hint ou um template de plano de execução), em detrimento de outro índice selecionado pelo SQL Server (por melhor que seja a performance) é a melhor opção para aplicar em um ambiente de Produção.

    Como disse, realizar um REBUILD incluindo as colunas utilizadas na instrução SELECT que o IX_Record não possui e o IX_ItemPedido_1(desconfio que este índice deve ser melhor analisado) pode ser uma boa opção. 

    Outro ponto a ser analisado é seu Index Seek gerado pelo IX_ItemPedido_1, que favorece mais a performance da consulta. Neste caso, seria interessante estudar se alguma coluna não realiza uma conversão de dados implícita que pode estar descartando este índice do plano de execução padrão do SQL Server.

    Dependendo do ajuste realizado o custo dos índices será modificado, utilizando um pouco mais o índice IX_ItemPedido_1.

    De qualquer forma, este estudo deve ser realizado com foco nas colunas utilizadas com maior frequência na cláusula WHERE para "escapar" de qualquer Index Scan.

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    quarta-feira, 10 de setembro de 2014 21:00
    Moderador
  • Durval, obrigada pela ajuda.

    O índice existente cobre 100% das colunas do meu WHERE. 
    Fiz o que você falou e coloquei as colunas do SELECT no INCLUDE. Continuo tendo INDEX SCAN.

    Essa tabela é particionada e o índice é alinhado com tabela. Aí entendo menos ainda o full scan. Consigo fazer ele usar o índice se o meu resultado for menor que 1000 linhas...

    De tudo o que li sobre tuning, nada do que está acontecendo faz sentido..


    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    quinta-feira, 11 de setembro de 2014 13:18
  • Deleted
    quinta-feira, 11 de setembro de 2014 13:56
  • Deleted
    quinta-feira, 11 de setembro de 2014 14:50
  • Qual versão/build do SQL?

    Se você puder fazer o upload do plano de execução atual (não estimado) para o Onedrive ou outro lugar que eu tenha acesso, posso te ajudar a analisar o caso.

    De preferência os planos com e sem o INDEX hint.


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.


    sexta-feira, 12 de setembro de 2014 00:36
  • Boa tarde José Diz,

    Eu já fiz o teste criando o índice na tabela temporária, porém não ganhei nada de performance e continuo com o index scan..

    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    sexta-feira, 12 de setembro de 2014 17:38
  • Boa tarde Alex,

    A versão Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64) , Build 7601: Service Pack 1.

    Subi os planos com e sem o hint que força o indice no OneDrive

    Existe algum bug em relação do 2012 no cardinality estimator? Não sei porque, mas quando você perguntou da versão me veio na cabeça algo sobre o assunto, que ouvi num evento da Microsoft, mas não consigo me lembrar exatamente.


    Muito obrigada pela ajuda.
    []'s
    Mariana



    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    sexta-feira, 12 de setembro de 2014 17:42
  • Vou fazer o download, analisar e respondo aqui.

    A pergunta da versão é importante, pois tem modificações de comportamento de uma versão para outra, inclusive se tem ou não Service Packs.

    O que você pode ter ouvido é em relação ao SQL 2014 e não o SQL 2012, no 2014 por ter sofrido uma mudança considerável, alguns ambientes podem ter um comportamento ruim.


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.


    sexta-feira, 12 de setembro de 2014 17:47
  • Deleted
    sexta-feira, 12 de setembro de 2014 21:15
  • José Diz, muito obrigada pelas dicas.

    Eu tinha feito algo parecido para remover o GROUP BY com tantos campos, mas não usei CTE e continuava tendo o INDEX SCAN.

    A coluna ObjectID é a chave primária da tabela Pedido? E como se estabelece a ligação entre um pedido e os respectivos itens daquele pedido? Seria a coluna Operacao da tabela ItemPedido? SIM

    Quais colunas compõem o índice IX_ItemPedido_1? Operacao. É um indice particionado pela coluna TableID



    Gostei do seu exemplo e vou testá-lo aqui.  E vou testar também o FORCESEEK

    Em seguida respondo aqui se o plano de execução mudou

    Obrigada.

    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    sexta-feira, 19 de setembro de 2014 21:20
  • José Diz, bom dia.


    Fiz o teste com o FORCESEEK. O tempo de execução melhorou muito, mas olhando para o plano de execução vi algumas coisas que não espera, por exemplo, o fato de não estar usando paralelismo.

    Subi o plano com o ForceSeek no One Drive

    Em relação a fazer o group by com menos campos, o plano continua com INDEX SCAN e praticamente o mesmo número de leituras, CPU e os mesmos operadores.

    Em relação os índices nas tabelas temporárias, ajudou, mas muito pouco. Porém vou deixar criá-los.

    Obrigada.


    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    segunda-feira, 22 de setembro de 2014 16:18
  • Deleted
    terça-feira, 23 de setembro de 2014 00:52
  • Mariana,

    Uma pergunta, você já verificou se as estatísticas das tabelas de sistema do seu banco de dados estão atualizadas?

    O que pode estar acontecendo é uma falha de informação interna do SQL Server em relação a alocação de dados em suas tabelas, bem como, nas próprias tabelas de sistema.

    Utilize o código de exemplo abaixo para verificar se as estatísticas internas estão atualizadas:

    SELECT name AS stats_name, 
        STATS_DATE(object_id, stats_id) AS statistics_update_date
    FROM sys.stats 
    order by statistics_update_date Desc

    Este outro exemplo, você vai conseguir obter informações sobre estatísticas de índices:

    --Create a table for the outdated statistics
    CREATE TABLE Outdated_statistics
    ([Table name] sysname,
    [Index name] sysname,
    [Last updated] datetime NULL,
    [Rows modified] int NULL)
    GO
    
    --Get the list of outdated statistics
    INSERT INTO Outdated_statistics
    SELECT OBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr FROM sys.sysindexes
    WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE()) 
    AND rowmodctr>0 
    AND id IN (SELECT object_id FROM sys.tables)
    GO
    
    Select * from Outdated_statistics
    
    --Set the thresholds when to consider the statistics outdated
    DECLARE @hours int
    DECLARE @modified_rows int
    DECLARE @update_statement nvarchar(300);
    
    SET @hours=24
    SET @modified_rows=10
    
    --Update all the outdated statistics
    DECLARE statistics_cursor CURSOR FOR
    SELECT 'UPDATE STATISTICS '+OBJECT_NAME(id)+' '+name
    FROM sys.sysindexes
    WHERE STATS_DATE(id, indid)<=DATEADD(HOUR,-@hours,GETDATE()) 
    AND rowmodctr>=@modified_rows 
    AND id IN (SELECT object_id FROM sys.tables)
     
    OPEN statistics_cursor;
    FETCH NEXT FROM statistics_cursor INTO @update_statement;
     
     WHILE (@@FETCH_STATUS <> -1)
     BEGIN
      EXECUTE (@update_statement);
      PRINT @update_statement;
     
     FETCH NEXT FROM statistics_cursor INTO @update_statement;
     END;
     
     PRINT 'The outdated statistics have been updated.';
    CLOSE statistics_cursor;
    DEALLOCATE statistics_cursor;
    GO
    

    Neste outro exemplo, você vai poder analisar e obter informações sobre Leitura e Escrita em seus índices:

    --- Index Read/Write stats (all tables in current DB)
    SELECT  OBJECT_NAME(s.[object_id]) AS [ObjectName] ,
                  i.name AS [IndexName] ,
                  i.index_id ,
                  user_seeks + user_scans + user_lookups AS [Reads] ,
                  user_updates AS [Writes] ,
                  i.type_desc AS [IndexType] ,
                  i.fill_factor AS [FillFactor]
    FROM    sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i 
                                                                             ON s.[object_id] = i.[object_id]
    WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
    AND i.index_id = s.index_id
    AND s.database_id = DB_ID()
    ORDER BY OBJECT_NAME(s.[object_id]), Writes DESC, Reads DESC;
    Por fim, utilize a System Stored Procedure: SP_UPDATESTATS para atualizar as estatísticas internas, como também, o comando Update Statistics.


    Um detalhe que talvez seja necessário analisar é a distribuição estatísticas dos seus dados em suas tabelas, como:

    - Pages Scanned................................: 
    - Extents Scanned..............................: 
    - Extent Switches..............................: 
    - Avg. Pages per Extent........................: 
    - Scan Density [Best Count:Actual Count].......: 
    - Logical Scan Fragmentation ..................:
    - Extent Scan Fragmentation ...................: 
    - Avg. Bytes Free per Page.....................: 
    - Avg. Page Density (full).....................: 

    Já pensou em utilizar o comando DBCC ShowContig?

    Veja este link sobre o comando DBCC Show_Statistics, pode ser muito útil: http://msdn.microsoft.com/pt-br/library/ms174384.aspx


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | SoroCódigos] @JuniorGalvaoMVP | pedrogalvaojunior.wordpress.com

    terça-feira, 23 de setembro de 2014 15:15
  • Pelos planos, o indice ITEM_PEDIDO.IX_Record não contém todas as colunas necessárias, como vocês já discutiram.

    Caso você não tenha incluido essas colunas no INCLUDE, seria bom testar, pois a falta das colunas podem estar invalidando o uso do indice devido ao alto custo do KEY LOOKUP.

    Criar um indice temporário, que atenda a pesquisa, pode ser uma saida para testar sem alterar o indice original.

    As estatisticas da tabela ITEM_PEDIDO, parecem estar desatualizadas ou não completas, pois temos:
        EST ROWS = 2,451,741
        ACT ROWS = 76,277

    Uma diferença muito grande entre ESTIMADO e ATUAL.


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.


    sexta-feira, 26 de setembro de 2014 14:34
  • Alex, boa tarde.

    As estatísticas estão atualizadas sim. Isso foi a primeira coisa que chequei quando vi a diferença tão grande entre o estimado e o atual. E assim fiquei ainda mais intrigada.

    Em relação ao índice IX_ItemPedido_Record, ele é um campo BIGINT auto incremental e é o índice clustered da tabela.

    José Diz, vou testar algumas das suas sugestões ainda e volto aqui para postar o resultado.


    Muito obrigada pela ajuda!
    []'s


    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    terça-feira, 30 de setembro de 2014 16:16
  • se está atualizada e como FULL, realmente fica estranho ter a diferença.

    se puder fazer upload do DBCC SHOWSTATISTICS, ajuda na analise.

    Pela descrição do indice que vc comentou, a criação de um outro que atenda melhor essa Query pode ser uma alternativa.


    Alex Rosa - Premier Field Engineer - Data Platform

    Disclaimer: This content is provided "as-is" and without warranties of any kind, either express or implied. You should therefore verify any information contained in the content before acting on it.


    terça-feira, 30 de setembro de 2014 17:45
  • Deleted
    quarta-feira, 1 de outubro de 2014 11:52