Início / dba / Perguntas / 203399
Accepted
Shnugo
Asked: 2018-04-10 06:15:16 +0800 CST

O desempenho da consulta depende drasticamente do banco de dados em uso

  • 772

Estou no SQL-Server 12.0.5203.0

Há uma consulta cobrindo vários bancos de dados. Eu acho que não é útil postar a consulta real (como é bastante complexa e confidencial, basta pensar em uma consulta como

SELECT t1.Column1
      ,t2.Column2
      ,t3.Column3
FROM db1.schema.TableName1 t1 with (nolock)
JOIN db1.schema.TableName2 t2 with (nolock) ON SomeCriteria
JOIN db2.schema.TableName3 t3 with (nolock) ON SomeCriteria  
JOIN db3.schema.TableName4 t4 with (nolock) ON SomeCriteria
WHERE SomeCriteria  
  AND t4.ColumnA = (SELECT DISTINCT ColumnX
                      FROM db2.schema.TableName5 with (nolock) 
                      WHERE ColumnY = 2902)

Por favor: Esta não é minha pergunta... Não discuta o uso de WITH(NOLOCK)ou GROUP BYcontra DISTINCT, thx :-D

Os usuários relataram tempos limite devido a durações de execução de mais de um minuto. Não consegui reproduzir isso, pois exatamente a mesma consulta no mesmo ambiente trouxe exatamente o mesmo resultado em menos de um segundo.

Então - definindo todas as configurações possíveis para os mesmos valores - encontrei algo estranho: a diferença depende do banco de dados em uso.

Isso é reproduzível: Se houver USE master;, é 1 segundo, com USE db1;(ou qualquer outro) é terrivelmente lento.

Algumas observações gerais

  • Todas as tabelas usadas são totalmente qualificadas com database.schema.tablealias
  • Todas as tabelas são chamadas comWITH(NOLOCK)
  • O criador de perfil para a execução rápida mostra CPU(30), Reads(20000), Duration(30)
  • lento (mesmo SSMS, apenas duas janelas): CPU(11000), Reads(13M!!!), Duration(12000)
  • Os planos de execução são extremamente diferentes
    • slow: começa com uma busca de índice retornando 2,6 milhões de linhas
      Combina isso com outra busca de índice empurrando para 27 milhões de linhas (estimada em 45!)
      Filtra até 1626 linhas, que é a contagem do resultado final
    • rápido: começando com pequenos conjuntos de cerca de 100 linhas, nunca mais de 8000 linhas

Minhas perguntas

  • O que está acontecendo aqui? Por que o banco de dados, de onde venho , é tão importante?

Lições aprendidas e uma solução

O que eu não sabia: Os planos de consulta são armazenados com cada banco de dados separadamente, portanto, o banco de dados de contexto pode ser muito importante. Eu realmente não consigo entender a vantagem desse conceito... Por que não armazenar melhor os planos em um local central, um por ação? Mas essa é uma pergunta diferente...

Usando uma consulta contra sys.dm_exec_cached_planse dm_exec-query_plan()com query_plan.exist('//*:StmtSimple[contains(@*:StatementText[1],"Some specific part of the query")]')=1, encontrei alguns planos armazenados. Após a remoção tudo foi rápido e bem.

Mas no dia seguinte o mau comportamento estava de volta.

A solução: depois de uma análise detalhada do melhor plano, reorganizei tudo JOINspara essa ordem de execução e uso OPTION(FORCE ORDER). Isso parece resolver o problema.

performance t-sql

1 respostas

  1. Best Answer

    Em um nível alto, o contexto do banco de dados é uma das chaves que tornam um plano em cache único (há outras coisas, como certas configurações de sessão, o texto da consulta, obviamente, etc).

    Portanto, é bem possível que, na primeira vez que essa consulta foi executada em um banco de dados, ela compilou (e agora está presa a) um plano muito diferente de quando foi compilada para algum outro banco de dados.

    Existem muitas razões pelas quais um plano pode ser diferente, dependendo do que aconteceu entre a primeira compilação e quaisquer compilações subsequentes, mas agora que você tem dois planos, é irrelevante que você esteja emitindo os USEcomandos na mesma janela. Você ainda usará o plano que foi compilado para esse banco de dados. Reprodução rápida:

    USE AdventureWorks;
GO
SELECT TOP (1) Monkey = BusinessEntityID 
  FROM AdventureWorks2012.Person.Person;
GO
USE OtherDB;
GO
SELECT TOP (1) Monkey = BusinessEntityID 
  FROM AdventureWorks2012.Person.Person;
GO
SELECT * FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.[text] LIKE '%Monkey%';

    Você obtém três linhas - ignore a primeira, porque essa é apenas a consulta que executei para verificar (que é armazenada em cache antes da execução, portanto, se adiciona ao conjunto de resultados) e lembre-se de executar um banco de dados de zoológico ou trabalhar na SurveyMonkey, você pode ter que filtrar mais:

    insira a descrição da imagem aqui

    Claramente você pode ver que temos um plano para AdventureWorks e um plano para OtherDB, mesmo que esta seja a mesma instância do SQL Server. Novamente, não posso voltar na história e dizer por que ou quando você teve dois planos diferentes, mas é óbvio para mim que essa é uma explicação possível. Fácil para você verificar, é claro.

    Então, mais uma vez, tente emitir a consulta com OPTION (RECOMPILE)quando você USE <slowdatabase>;ou, de outra forma, despeje esses planos específicos do cache e tente novamente:

    DBCC FREEPROCCACHE(0x...); -- where 0x... is the plan_handle
    • 1

relate perguntas