O plano de execução mostra a operação CONVERT_IMPLICIT cara. Posso corrigir isso com indexação ou preciso alterar a tabela?

Você não deve confiar muito nas porcentagens de custo nos planos de execução. Esses são sempre custos estimados , mesmo em planos de pós-execução com números 'reais' para coisas como contagens de linhas. Os custos estimados são baseados em um modelo que funciona muito bem para o propósito a que se destina: permitir que o otimizador escolha entre diferentes planos de execução candidatos para a mesma consulta. As informações de custo são interessantes e um fator a ser considerado, mas raramente devem ser uma métrica principal para ajuste de consulta. A interpretação das informações do plano de execução requer uma visão mais ampla dos dados apresentados.

Operador de busca de índice agrupado ItemTran

Este operador é realmente duas operações em uma. Primeiro, uma operação de busca de índice localiza todas as linhas que correspondem ao predicado product_code_v42 = 'M10BOLT'e, em seguida, cada linha tem o predicado residual bit_field_v41 & 4 = 0aplicado. Há uma conversão implícita de bit_field_v41seu tipo base ( tinyintou smallint) para integer.

A conversão ocorre porque o operador AND bit a bit (&) exige que ambos os operandos sejam do mesmo tipo. O tipo implícito do valor constante '4' é inteiro e as regras de precedência de tipo de dados significam que o valor do campo de prioridade mais baixa bit_field_v41é convertido.

O problema (tal como é) é facilmente corrigido escrevendo o predicado como bit_field_v41 & CONVERT(tinyint, 4) = 0- o que significa que o valor constante tem a prioridade mais baixa e é convertido (durante a dobragem constante) em vez do valor da coluna. Se bit_field_v41for, tinyintnenhuma conversão ocorrerá. Da mesma forma, CONVERT(smallint, 4)pode ser usado se bit_field_v41for smallint. Dito isso, a conversão não é um problema de desempenho neste caso, mas ainda é uma boa prática combinar tipos e evitar conversões implícitas sempre que possível.

A maior parte do custo estimado dessa busca é devido ao tamanho da mesa base. Embora a chave de índice clusterizado seja razoavelmente estreita, o tamanho de cada linha é grande. Uma definição para a tabela não é fornecida, mas apenas as colunas usadas na exibição somam uma largura de linha significativa. Como o índice clusterizado inclui todas as colunas, a distância entre as chaves de índice clusterizado é a largura da linha , não a largura das chaves de índice . O uso de sufixos de versão em algumas colunas sugere que a tabela real tem ainda mais colunas para versões anteriores.

Observando as colunas de busca, predicado residual e saída, o desempenho desse operador pode ser verificado isoladamente construindo a consulta equivalente ( 1 <> 2é um truque para evitar a parametrização automática, a contradição é removida pelo otimizador e não aparece no plano de consulta):

SELECT
    it.booking_no_v32,
    it.QtyCheckedOut,
    it.QtyReturned,
    it.Trans_qty,
    it.trans_type_v41
FROM dbo.tblItemTran AS it
WHERE
    1 <> 2
    AND it.product_code_v42 = 'M10BOLT'
    AND it.bit_field_v41 & CONVERT(tinyint, 4) = 0;

O desempenho dessa consulta com um cache de dados frio é interessante, pois a leitura antecipada seria afetada pela fragmentação da tabela (índice clusterizado). A chave de agrupamento para esta tabela convida à fragmentação, portanto, pode ser importante manter (reorganizar ou reconstruir) este índice regularmente e usar um apropriado FILLFACTORpara permitir espaço para novas linhas entre as janelas de manutenção do índice.

Realizei um teste do efeito da fragmentação no read-ahead usando dados de exemplo gerados usando o SQL Data Generator . Usando as mesmas contagens de linhas da tabela conforme mostrado no plano de consulta da pergunta, um índice clusterizado altamente fragmentado levou SELECT * FROM view15 segundos após DBCC DROPCLEANBUFFERS. O mesmo teste nas mesmas condições com um índice clusterizado recém-reconstruído na tabela ItemTrans foi concluído em 3 segundos.

Se os dados da tabela estiverem totalmente no cache, o problema de fragmentação é muito menos importante. Mas, mesmo com baixa fragmentação, as linhas largas da tabela podem significar que o número de leituras lógicas e físicas é muito maior do que o esperado. Você também pode experimentar adicionar e remover o explícito CONVERTpara validar minha expectativa de que o problema de conversão implícita não é importante aqui, exceto como uma violação de prática recomendada.

Mais direto ao ponto é o número estimado de linhas que saem do operador de busca. A estimativa de tempo de otimização é de 165 linhas, mas 4.226 foram produzidas no tempo de execução. Voltarei a esse ponto mais tarde, mas o principal motivo da discrepância é que a seletividade do predicado residual (envolvendo o AND bit a bit) é muito difícil para o otimizador prever - na verdade, ele recorre à adivinhação.

Operador de filtro

Estou mostrando o predicado do filtro aqui principalmente para ilustrar como as duas NOT INlistas são combinadas, simplificadas e expandidas, e também para fornecer uma referência para a discussão de correspondência de hash a seguir. A consulta de teste da busca pode ser expandida para incorporar seus efeitos e determinar o efeito do operador Filter no desempenho:

SELECT
    it.booking_no_v32,
    it.trans_type_v41,
    it.Trans_qty,
    it.QtyReturned,
    it.QtyCheckedOut
FROM dbo.tblItemTran AS it
WHERE
    it.product_code_v42 = 'M10BOLT'
    AND it.bit_field_v41 & CONVERT(tinyint, 4) = 0
    AND
    (
        (
            it.trans_type_v41 NOT IN (2, 3, 6, 7, 18, 19, 20, 21, 12, 13, 22)
            AND it.trans_type_v41 NOT IN (6, 7)
        )
        OR
        (
            it.trans_type_v41 NOT IN (6, 7)
        )
        OR 
        (
            it.trans_type_v41 IN (6, 7)
            AND it.QtyCheckedOut = 0
        )
        OR 
        (
            it.trans_type_v41 IN (6, 7)
            AND it.QtyCheckedOut > 0
            AND it.trans_qty - (it.QtyCheckedOut - it.QtyReturned) > 0
        )
    );

O operador Compute Scalar no plano define a seguinte expressão (o próprio cálculo é adiado até que o resultado seja solicitado por um operador posterior):

[Expr1016] = (trans_qty - (QtyCheckedOut - QtyReturned))

O operador de correspondência de hash

Executar uma junção em tipos de dados de caracteres não é o motivo do alto custo estimado desse operador. A dica de ferramenta SSMS mostra apenas uma entrada Hash Keys Probe, mas os detalhes importantes estão na janela SSMS Properties.

O operador Hash Match cria uma tabela hash usando os valores da booking_no_v32coluna (Hash Keys Build) da tabela ItemTran e, em seguida, investiga as correspondências usando a booking_nocoluna (Hash Keys Probe) da tabela Bookings. A dica de ferramenta SSMS normalmente também mostraria um Probe Residual, mas o texto é muito longo para uma dica de ferramenta e é simplesmente omitido.

Um Residual de Sonda é semelhante ao Residual visto após a busca de índice anterior; o predicado residual é avaliado em todas as linhas que correspondem ao hash para determinar se a linha deve ser passada para o operador pai. Encontrar correspondências de hash em uma tabela de hash bem balanceada é extremamente rápido, mas aplicar um predicado residual complexo a cada linha correspondente é bastante lento em comparação. A dica de ferramenta Hash Match no Plan Explorer mostra os detalhes, incluindo a expressão Probe Residual:

O predicado residual é complexo e inclui a verificação do status do progresso da reserva agora que a coluna está disponível na tabela de reservas. A dica de ferramenta também mostra a mesma discrepância entre as contagens de linhas estimadas e reais vistas anteriormente na busca de índice. Pode parecer estranho que grande parte da filtragem seja executada duas vezes, mas isso é apenas o otimizador sendo otimista. Ele não espera que as partes do filtro que podem ser empurradas para baixo no plano do teste residual eliminem quaisquer linhas (as estimativas de contagem de linhas são as mesmas antes e depois do filtro), mas o otimizador sabe que pode estar errado sobre isso. A chance de filtrar as linhas antecipadamente (reduzindo o custo da junção de hash) compensa o pequeno custo do filtro extra. O filtro inteiro não pode ser empurrado para baixo porque inclui um teste em uma coluna da tabela de reservas, mas a maior parte pode ser.

A subestimação da contagem de linhas é um problema para o operador Hash Match porque a quantidade de memória reservada para a tabela de hash é baseada no número estimado de linhas. Onde a memória é muito pequena para o tamanho da tabela de hash necessária no tempo de execução (devido ao maior número de linhas), a tabela de hash é despejada recursivamente no armazenamento físico do tempdb , geralmente resultando em um desempenho muito ruim. No pior caso, o mecanismo de execução para de derramar baldes de hash recursivamente e recorre a um processo muito lentoalgoritmo de salvamento. O derramamento de hash (recursivo ou resgate) é a causa mais provável dos problemas de desempenho descritos na pergunta (não colunas de junção de tipo de caractere ou conversões implícitas). A causa raiz seria o servidor reservando pouca memória para a consulta com base na estimativa de contagem de linha (cardinalidade) incorreta.

Infelizmente, antes do SQL Server 2012, não há nenhuma indicação no plano de execução de que uma operação de hash excedeu sua alocação de memória (que não pode crescer dinamicamente após ser reservada antes do início da execução, mesmo que o servidor tenha muita memória livre) e teve que vazar para tempdb. É possível monitorar a classe de evento de aviso de hash usando o Profiler, mas pode ser difícil correlacionar os avisos com uma consulta específica.

Corrigindo os problemas

Os três problemas são a fragmentação, o complexo teste residual no operador de correspondência de hash e a estimativa de cardinalidade incorreta resultante da suposição na busca do índice.

Solução recomendada

Verifique a fragmentação e corrija-a se necessário, agendando a manutenção para garantir que o índice permaneça organizado de forma aceitável. A maneira usual de corrigir a estimativa de cardinalidade é fornecer estatísticas. Nesse caso, o otimizador precisa de estatísticas para a combinação ( product_code_v42, bitfield_v41 & 4 = 0). Não podemos criar estatísticas em uma expressão diretamente, portanto, devemos primeiro criar uma coluna computada para a expressão do campo de bits e, em seguida, criar as estatísticas manuais de várias colunas:

ALTER TABLE dbo.tblItemTran
ADD Bit3 AS bit_field_v41 & CONVERT(tinyint, 4);

CREATE STATISTICS [stats dbo.ItemTran (product_code_v42, Bit3)]
ON dbo.tblItemTran (product_code_v42, Bit3);

A definição de texto da coluna computada deve corresponder ao texto na definição de visualização exatamente para que as estatísticas sejam usadas, portanto, corrigir a visualização para eliminar a conversão implícita deve ser feito ao mesmo tempo e tomar cuidado para garantir uma correspondência textual.

As estatísticas de várias colunas devem resultar em estimativas muito melhores, reduzindo bastante a chance de que o operador de correspondência de hash use derramamento recursivo ou o algoritmo de salvamento. Adicionar a coluna computada (que é uma operação somente de metadados e não ocupa espaço na tabela, pois não está marcada PERSISTED) e as estatísticas de várias colunas é meu melhor palpite para uma primeira solução.

Ao resolver problemas de desempenho de consulta, é importante medir coisas como tempo decorrido, uso de CPU, leituras lógicas, leituras físicas, tipos e durações de espera... e assim por diante. Também pode ser útil executar partes da consulta separadamente para validar as causas suspeitas, conforme mostrado acima.

Em alguns ambientes, onde uma visão atualizada dos dados não é importante, pode ser útil executar um processo em segundo plano que materialize toda a visão em uma tabela de instantâneo de vez em quando. Esta tabela é apenas uma tabela base normal e pode ser indexada para consultas de leitura sem se preocupar em afetar o desempenho da atualização.

Ver indexação

Do not be tempted to index the original view directly. Read performance will be amazingly fast (a single seek on a view index) but (in this case) all the performance problems in the existing query plans will be transferred to queries that modify any of the table columns referenced in the view. Queries that change base table rows will be impacted very badly indeed.

Advanced solution with a partial indexed view

There is a partial indexed-view solution for this particular query that corrects cardinality estimates and removes the filter and probe residual, but it is based on some assumptions about the data (mostly my guess at the schema) and requires expert implementation, particularly regarding suitable indexes to support the indexed view maintenance plans. I share the code below for interest, I do not propose you implement it without very careful analysis and testing.

-- Indexed view to optimize the main view
CREATE VIEW dbo.V1
WITH SCHEMABINDING
AS
SELECT
    it.ID,
    it.product_code_v42,
    it.trans_type_v41,
    it.booking_no_v32,
    it.Trans_qty,
    it.QtyReturned,
    it.QtyCheckedOut,
    it.QtyReserved,
    it.bit_field_v41,
    it.prep_on,
    it.From_locn,
    it.Trans_to_locn,
    it.PDate,
    it.FirstDate,
    it.PTimeH,
    it.PTimeM,
    it.RetnDate,
    it.BookDate,
    it.TimeBookedH,
    it.TimeBookedM,
    it.TimeBookedS,
    it.del_time_hour,
    it.del_time_min,
    it.return_to_locn,
    it.return_time_hour,
    it.return_time_min,
    it.AssignTo,
    it.AssignType,
    it.InRack
FROM dbo.tblItemTran AS it
JOIN dbo.tblBookings AS tb ON
    tb.booking_no = it.booking_no_v32
WHERE
    (
        it.trans_type_v41 NOT IN (2, 3, 7, 18, 19, 20, 21, 12, 13, 22)
        AND it.trans_type_v41 NOT IN (6, 7)
        AND it.bit_field_v41 & CONVERT(tinyint, 4) = 0
    )
    OR
    (
        it.trans_type_v41 NOT IN (6, 7)
        AND it.bit_field_v41 & CONVERT(tinyint, 4) = 0
        AND tb.BookingProgressStatus = 1
    )
    OR 
    (
        it.trans_type_v41 IN (6, 7)
        AND it.bit_field_v41 & CONVERT(tinyint, 4) = 0
        AND it.QtyCheckedOut = 0
    )
    OR 
    (
        it.trans_type_v41 IN (6, 7)
        AND it.bit_field_v41 & CONVERT(tinyint, 4) = 0
        AND it.QtyCheckedOut > 0
        AND it.trans_qty - (it.QtyCheckedOut - it.QtyReturned) > 0
    );
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.V1 (product_code_v42, ID);
GO

The existing view tweaked to use the indexed view above:

CREATE VIEW [dbo].[vwReallySlowView2]
AS
SELECT
    I.booking_no_v32 AS bkno,
    I.trans_type_v41 AS trantype,
    B.Assigned_to_v61 AS Assignbk,
    B.order_date AS dateo,
    B.HourBooked AS HBooked,
    B.MinBooked AS MBooked,
    B.SecBooked AS SBooked,
    I.prep_on AS Pon,
    I.From_locn AS Flocn,
    I.Trans_to_locn AS TTlocn,
    CASE I.prep_on 
        WHEN 'Y' THEN I.PDate
        ELSE I.FirstDate
    END AS PrDate,
    I.PTimeH AS PrTimeH,
    I.PTimeM AS PrTimeM,
    CASE
        WHEN I.RetnDate < I.FirstDate 
        THEN I.FirstDate 
        ELSE I.RetnDate
    END AS RDatev,
    I.bit_field_v41 AS bitField,
    I.FirstDate AS FDatev,
    I.BookDate AS DBooked,
    I.TimeBookedH AS TBookH,
    I.TimeBookedM AS TBookM,
    I.TimeBookedS AS TBookS,
    I.del_time_hour AS dth,
    I.del_time_min AS dtm,
    I.return_to_locn AS rtlocn,
    I.return_time_hour AS rth,
    I.return_time_min AS rtm,
    CASE
        WHEN
            I.Trans_type_v41 IN (6, 7) 
            AND I.Trans_qty < I.QtyCheckedOut
            THEN 0 
        WHEN
            I.Trans_type_v41 IN (6, 7)
            AND I.Trans_qty >= I.QtyCheckedOut
            THEN I.Trans_Qty - I.QtyCheckedOut
        ELSE
            I.trans_qty
    END AS trqty,
    CASE
        WHEN I.Trans_type_v41 IN (6, 7)
        THEN 0
        ELSE I.QtyCheckedOut
    END AS MyQtycheckedout,
    CASE
        WHEN I.Trans_type_v41 IN (6, 7)
        THEN 0
        ELSE I.QtyReturned
    END AS retqty,
    I.ID,
    B.BookingProgressStatus AS bkProg,
    I.product_code_v42,
    I.return_to_locn,
    I.AssignTo,
    I.AssignType,
    I.QtyReserved,
    B.DeprepOn,
    CASE B.DeprepOn
        WHEN 1 THEN B.DeprepDateTime
        ELSE I.RetnDate
    END AS DeprepDateTime,
    I.InRack
FROM dbo.V1 AS I WITH (NOEXPAND)
JOIN dbo.tblbookings AS B ON
    B.booking_no = I.booking_no_v32
JOIN dbo.tblInvmas AS M ON
    I.product_code_v42 = M.product_code;

Example query and execution plan:

SELECT
    vrsv.*
FROM dbo.vwReallySlowView2 AS vrsv
WHERE vrsv.product_code_v42 = 'M10BOLT';

In the new plan, the hash match has no residual predicate, there is no complex filter, no residual predicate on the indexed view seek, and the cardinality estimates are exactly correct.

As an example of how insert/update/delete plans would be affected, this is the plan for an insert to the ItemTrans table:

The highlighted section is new and required for indexed view maintenance. The table spool replays inserted base table rows for indexed view maintenance. Each row is joined to the bookings table using a clustered index seek, then a filter applies the complex WHERE clause predicates to see if the row needs to be added to the view. If so, an insert is performed to the view's clustered index.

O mesmo SELECT * FROM viewteste executado anteriormente foi concluído em 150ms com a visualização indexada no lugar.

Última coisa: notei que seu servidor 2008 R2 ainda está em RTM. Isso não resolverá seus problemas de desempenho, mas o Service Pack 2 para 2008 R2 está disponível desde julho de 2012 e há muitos bons motivos para manter os service packs o mais atualizados possível.