Início / dba / Perguntas / 279210
Accepted
Newbie-DBA
Asked: 2020-11-05 14:41:11 +0800 CST

Como posso ajustar abaixo do proc alterando a consulta ou adicionando o índice

  • 772

Estou tendo problemas com um dos proc rodando muito lento por aproximadamente uma hora:

Por favor, perdoe, pois tenho que escrever o código inteiro fora do sistema para colar aqui. Estarei editando a pergunta com mais detalhes sobre o esquema e os índices da tabela à medida que copio essas informações:

SP é algo como abaixo

CREATE PROCEDURE [dbo].[testsp1]
@parameter1 VARCHAR(MAX),
@parameter2 DATETIME

AS

DECLARE @Today DATETIME =GETDATE()
IF @parameter2 IS NULL
 SET @parameter2 = Getdate()

SET @parameter2 = CONVERT(VARCHAR, @parameter2 , 101)

DECLARE @table1 TABLE ( tabletypeID BIGINT)

Insert into @table1
SELECT Value from stringsplit(@parameter1, '|')

UPDATE bigtable
    SET value1 = 33, statusdate = @today
from 
    bigtable t
    INNER JOIN anotherbigtable t1 on t.id=t1.id
    INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
    t1.paydate <= @parameter2
    AND t.value1=32
    AND t1.payid =2

bigtable tem aproximadamente 800 M de linhas e foi agrupado como PK no id e NC no userid (1ª chave), tabletypeid (2ª chave)

anotherbigtable é aprox. 300 M linhas

@parameter2é na maioria das vezes recebido como NULL enquanto @parameter1 varia

plano em cache é algo como abaixo

insira a descrição da imagem aqui

sql-server query-performance

2 respostas

  1. Best Answer

    A primeira coisa que eu faria é evitar o uso de variáveis ​​de tabela em operações SQL pesadas. Você deve usar uma tabela temporária em vez disso. Inicialmente ou selecione sua variável de tabela @table1 em uma tabela temporária e use essa tabela temporária em sua instrução de atualização.

    As variáveis ​​de tabela são historicamente conhecidas por serem gargalos porque as estatísticas não são mantidas nelas e, mesmo com as melhorias da Microsoft no SQL 2014, elas ainda podem ter um desempenho ruim. Aqui está um bom artigo de Brent Ozar sobre eles: https://www.brentozar.com/archive/2014/04/table-variables-good-temp-tables-sql-2014/

    Você também pode se beneficiar do uso da dica de busca forçada em "outra tabela de bg". Mas comece com a mudança acima primeiro.

    Para referência: http://nisalbi.blogspot.com/2015/11/what-is-forceseek-and-forcescan-table.html?m=1

    E MS Docs em dicas de tabela: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15

    Normalmente, as dicas de consulta devem ser usadas apenas em casos muito situacionais por pessoas com experiência, mas descobri que, com tabelas maiores, às vezes o SQL Engine tenta fazer uma verificação de índice quando uma busca de índice realmente é uma escolha melhor. (A atualização das estatísticas da tabela em "anotherbg table" também pode corrigir seu problema com mais naturalidade.)

    Portanto, é situacional e você precisará testar, mas é possível que uma dica de índice de busca forçada ajude.

    Esta é a aparência da sua consulta de atualização com a dica de índice:

    UPDATE bigtable
    SET value1 = 33, statusdate = @today
from 
    bigtable t
    INNER JOIN anotherbigtable t1 WITH (FORCESEEK) on t.id=t1.id
    INNER JOIN @table1 t3 on t.tabletypeid = t3.tabletypeid
WHERE
    t1.paydate <= @parameter2
    AND t.value1=32
    AND t1.payid =2
    • 1

  2. Eu concordo com @JD disse sobre variáveis ​​de tabela temporária. Porque até a versão 2019 do SQL Server, o Query Engine pensa que a estimativa das variáveis ​​da tabela temporária retorna apenas uma linha.

    Caso contrário, acho que você deve deixar também Clustered Index Scanem anotherbigtable. Porque custou 98%

    Não conhecia seus índices, mas recomendo o índice abaixo.

    CREATE INDEX paydate_payid_inc ON anotherbigtable (paydate,payid) INCLUDE (id);

    Além disso, não é grande necessário, mas eu não gosto.

    SET @parameter2 = Getdate()
    
SET @parameter2 = CONVERT(VARCHAR, @parameter2 , 101)

    Por favor, tente isso.

    SET @parameter2 = CONVERT(VARCHAR, GETDATE(), 101)

    BTW, você pode compartilhar seu plano de execução com pasttheplan , para que possamos ver muitas informações de sua consulta (estimativas, custo etc.) e podemos escrever uma resposta mais poderosa.

    • 0

relate perguntas