Início / dba / Perguntas / 7515
Accepted
RateControl
Asked: 2011-11-03 07:42:23 +0800 CST

Consulta executada de forma diferente no SQL 2005 vs SQL 2008R2

  • 772

Em meu escritório, temos uma consulta que é bem feia, mas roda muito bem em produção e em ambiente de desenvolvimento (20seg e 4seg respectivamente). No entanto, em nosso ambiente de teste, leva mais de 4 horas. SQL2005(+latest patches) está rodando em produção e desenvolvimento. SQL2008R2 está sendo executado em teste.

Dei uma olhada no plano de consulta e mostra que o SQL2008R2 está usando o TempDB, por meio de um Table Spool (lazy spool) para armazenar as linhas retornadas do servidor vinculado. A próxima etapa é mostrar Nested Loops (esquerda anti-semi join) consumindo 96,3% da consulta. A linha entre as duas operadoras está em 5.398MB!

O plano de consulta para o SQL 2005 mostra nenhum uso de tempdb e nenhum uso de Left Anti Semi Join.

Abaixo está o código higienizado e os planos de execução do plano 2005 na parte superior, o 2008R2 na parte inferior.

O que está causando a drástica desaceleração e mudança? Eu esperava ver um plano de execução diferente, então isso não me incomoda. A dramática desaceleração no tempo de consulta é o que me incomoda.

Tenho que olhar para o hardware subjacente, já que a versão 2008R2 está usando tempdb, tenho que dar uma olhada em como otimizar o uso disso?

Existe uma maneira melhor de escrever a consulta?

Obrigado pela ajuda.

    INSERT INTO Table1_GroupLock (iGroupID, dLockedDate)
SELECT 
 Table1.iGroupID,
 GETDATE()
FROM Table1
WHERE 
 NOT EXISTS (
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE 
   (
    Alias2.FirstName + Alias2.LastName = dbo.fnRemoveNonLetter(Table1.FullName)
    AND NOT dbo.fnRemoveNonLetter(Table1.FullName) IS NULL
    AND NOT Alias2.FirstName IS NULL 
    AND NOT Alias2.LastName  IS NULL
   ) OR (
    Alias2.FamilyName = dbo.fnRemoveNonLetter(Table1.FamilyName)
    AND Alias2.Child1Name = dbo.fnRemoveNonLetter(Table1.Child1Name)
    AND NOT dbo.fnRemoveNonLetter(Table1.FamilyName) IS NULL
    AND NOT dbo.fnRemoveNonLetter(Table1.Child1Name) IS NULL
    AND NOT Alias2.Familyname IS NULL
    AND NOT Alias2.Child1Name IS NULL
   ) OR (
    Alias2.StepFamilyName = dbo.fnRemoveNonLetter(Table1.StepFamilyName)
    AND Alias2.StepFamilyNameChild1 = dbo.fnRemoveNonLetter(Table1.StepFamilyNameChild2)
    AND NOT Alias2.StepFamilyName IS NULL
    AND NOT Alias2.StepFamilyNameChild1 IS NULL
    AND NOT dbo.fnRemoveNonLetter(Table1.StepFamilyName) IS NULL
    AND NOT dbo.fnRemoveNonLetter(Table1.StepFamilyNameChild2) IS NULL
   )  
 ) AND NOT EXISTS (
  SELECT 1
  FROM Table3
  INNER JOIN Table4
   ON Table4.FirstNameType = Table3.FirstNameType 
  INNER JOIN table5
   ON table5.LastNameType = Table3.LastNameType 
  WHERE 
   Table3.iGroupID = Table1.iGroupID
   AND Table3.bIsClosed = 0
   AND Table4.sNameTypeConstant = 'new_lastname'
   AND table5.sFirstNameConstant = 'new_firstname'
 )

SQL-2005

SQL2008R2

:: EDIT :: Executou a consulta de uma instância SQL2005 diferente, praticamente o mesmo plano de execução do "bom". Ainda não tenho certeza de como as duas versões de 2005 estão funcionando melhor no servidor vinculado 2008R2 do que as instâncias 2008R2 nas instâncias 2008R2.

Embora eu não negue que o código poderia usar algum trabalho, se fosse o código sendo o problema, eu não veria os mesmos planos executivos em todos os meus testes? Independentemente da versão do SQL?

:: EDIT :: Apliquei SP1 e CU3 a ambas as instâncias 2008R2, ainda sem dados. Eu defini especificamente a colocação no servidor vinculado, sem dados. Eu defini especificamente as permissões da minha conta de usuário para ser administrador do sistema em ambas as instâncias, sem risco. Também me lembrei dos internos e da solução de problemas do SQL Server 2008, veremos se consigo rastrear isso de alguma forma.

Obrigado a todos pela ajuda e pelas dicas.

:: EDIT :: Fiz várias alterações de permissão no servidor vinculado. Usei logins SQL, logins de domínio, personifiquei usuários, usei a opção "ser feito usando este contexto de segurança". Eu criei usuários em ambos os lados do servidor vinculado que possuem direitos de administrador de sistema no servidor. Estou sem ideias.

Ainda gostaria de saber por que o SQL2005 está executando a consulta de maneira tão diferente do SQL2008R2. Se fosse a consulta ruim, eu veria o tempo de execução de mais de 4 horas no SQL2005 e no SQL2008R2.

sql-server-2008 sql-server-2005

5 respostas

  1. Adicionando às respostas anteriores, o motivo da regressão do plano pode ser devido a um bug de estimativa de cardinalidade conhecido quando o plano inclui um Anti Semi Join. Consulte KB 2222998

    Assumindo que o plano de 2005 produziu um desempenho aceitável, você pode descobrir que atualizar o servidor para uma versão que inclua essa correção (e habilitar o TF4199 para ativá-la) retornará ao plano 'bom'.

    Dito isso, existem muitas outras oportunidades para melhorar essa consulta, então este pode ser um bom momento para se concentrar em fazer isso.

    • 6

  2. Sugiro que os dados remotos sejam colocados em spool localmente porque um dos

    1. As configurações do servidor vinculado (como agrupamento) não são as mesmas
    2. O agrupamento local não é o mesmo que remoto, apesar das configurações do servidor vinculado
    3. A consulta não pode ser executada corretamente remotamente devido a permissões

    Para o ponto 1, consulte sp_serveroption
    E para o ponto 2, mas verifique também as collations server/db.

    Para o ponto 3, veja estes de Linchi Shea:

    Você está pedindo ao SQL Server para processar todos os dados localmente, conforme minha resposta aqui: Implicações de desempenho do uso de OPENQUERY em uma exibição

    Editar

    Na segunda olhada, vejo 2 chamadas remotas no plano "bom" em vez de uma. Isso confirma o que eu digo aqui

    • 5
  3. Best Answer

    Eu gostaria que você retrabalhasse a consulta.

    Você tem problemas de sargability e está usando chamadas de função escalar lá, o que também prejudicará a consulta. Você pode querer criar uma coluna computada FullName na Table2 e colocar um índice nela, certificando-se de que seu índice INCLUA FirstName e LastName. Você também deve adicionar índices que ajudem os outros

    Além disso, crie uma função com valor de tabela embutida para fazer sua funcionalidade "RemoveNonLetter" e refaça sua consulta para usá-la, provavelmente usando APPLY como fiz aqui.

    E, definitivamente, verifique o bug ao qual a resposta de Paul se refere.

    INSERT INTO Table1_GroupLock (iGroupID, dLockedDate)
SELECT 
 Table1.iGroupID,
 GETDATE()
FROM Table1
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.FullName)) AS fn (FullName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.FamilyName)) AS famn (FamilyName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.Child1Name)) AS c1n (Child1Name)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.StepFamilyName)) AS sfn (StepFamilyName)
OUTER APPLY (SELECT NonLettersRemoved FROM dbo.ifnRemoveNonLetter(Table1.StepFamilyNameChild2)) AS sfnc2 (StepFamilyNameChild2)
WHERE 
 NOT EXISTS (
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.FullName = fn.FullName
  UNION ALL
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.FamilyName = famn.FamilyName AND Alias2.Child1Name = c1n.Child1Name
  UNION ALL
  SELECT 1
  FROM LinkedServer.Database.Table2 Alias2
  WHERE Alias2.StepFamilyName = sfn.StepFamilyName AND Alias2.StepFamilyNameChild1 = sfnc2.StepFamilyNameChild2
 ) 
 AND NOT EXISTS (
  SELECT 1
  FROM Table3
  INNER JOIN Table4
   ON Table4.FirstNameType = Table3.FirstNameType 
  INNER JOIN table5
   ON table5.LastNameType = Table3.LastNameType 
  WHERE 
   Table3.iGroupID = Table1.iGroupID
   AND Table3.bIsClosed = 0
   AND Table4.sNameTypeConstant = 'new_lastname'
   AND table5.sFirstNameConstant = 'new_firstname'
 )
;
    • 5

  4. Há tantos problemas com essa comparação... só não sei por onde começar.

    1. Obtenha as especificações exatas para suas máquinas de produção e teste.

    2. Determine os links de rede entre os vários servidores vinculados em ambos os ambientes. São a mesma velocidade? Os servidores estão localizados um ao lado do outro nos dois ambientes?

    3. Existe alguma maneira de reescrever a consulta para NÃO usar servidores vinculados? Juntar tabelas entre servidores deixa você vulnerável a mudanças de topologia e é terrivelmente lento na maioria dos casos.

    4. O uso de NOT e OR normalmente leva a verificações completas da tabela. Tente reescrever a consulta.

    • 3

  5. +1 no comentário Tente reescrever sua consulta de datagod.

    Também estou me perguntando se você está enfrentando um problema de permissão no lado do servidor vinculado, levando a essa lentidão. Eu escrevi sobre essa desaceleração do servidor vinculado há algum tempo. Pode valer a pena verificar as permissões (é um servidor SQL vinculado? Ou é outro DBMS? Se for o último, você não obterá grandes estatísticas de qualquer maneira)

    Você ainda tem o SQL Server 2005 no ambiente de teste para tentar esta consulta e descartar o ambiente?

    Você recriou as estatísticas desde a atualização?

    • 3

relate perguntas