Partilhar via

Solucionar problemas de diferença de desempenho de consulta entre o aplicativo de banco de dados e o SSMS

  • Artigo

Quando você executa uma consulta em um aplicativo de banco de dados, ela é executada mais lentamente do que a mesma consulta em um aplicativo como SQL Server Management Studio (SSMS), Azure Data Studio ou SQLCMD.

Esse problema pode ocorrer pelas seguintes razões:

  • As consultas usam parâmetros ou variáveis diferentes.

  • As consultas são enviadas ao servidor em redes diferentes ou há uma diferença de como os aplicativos processam dados.

  • As opções SET no aplicativo de banco de dados e no SSMS são diferentes.

Para solucionar o problema, siga estas etapas:

Etapa 1: verificar se as consultas são enviadas com os mesmos parâmetros ou variáveis

Para comparar essas consultas e verificar se elas são idênticas em todos os sentidos, siga estas etapas:

  1. Abra o SSMS e conecte-o ao Mecanismo de Banco de Dados que você está usando.

  2. Execute os seguintes comandos para criar uma sessão de Eventos Estendidos:

    CREATE EVENT SESSION <EventSessionName> ON SERVER
ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)),
ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(sqlserver.client_app_name)),
ADD EVENT sqlserver.rpc_completed,
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.client_app_name)),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name)),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name))
ADD TARGET package0.event_file(SET filename=N'<FilePath>')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 
SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

    Observação

    Substitua os espaços reservados <EventSessionName> e <FilePath> pelos que você deseja criar.

  3. Execute os seguintes comandos para iniciar a sessão EventSessionName:

    ALTER EVENT SESSION <EventSessionName> ON SERVER
STATE = START

  4. Execute suas consultas para reproduzir o problema.

  5. Use um dos seguintes métodos para analisar os dados coletados:

    • Abra o Windows Explorer, localize o arquivo .xel de destino e clique duas vezes nele. O arquivo será aberto em outra janela SSMS que você pode usar para exibir e analisar.

    • Em Pesquisador de Objetos,expanda Eventos Estendidos> de Gerenciamento>EventSessionName>, clique com o botão direito do mouse em package0.event_file e selecione Exibir Dados de Destino....

    • Localize o local dos arquivos .xel e leia este arquivo usando a função sys.fn_xe_file_target_read_file.

  6. Compare a instrução Field verificando os seguintes eventos:

    • sp_statement_completed
    • sql_batch_completed
    • sql_statement_completed
    • rpc_completed

Para obter mais informações sobre as consultas idênticas, confira os seguintes exemplos:

  • Se os procedimentos ou funções armazenados tiverem valores de parâmetro diferentes, os tempos de consulta poderão ser diferentes:

    • SpUserProc @p1 = 100

    • SpUserProc @p1 = 270

  • As consultas a seguir são diferentes. A primeira consulta usa Densidade Média do histograma para estimação de cardinalidade, enquanto a segunda consulta usa a etapa de histograma para estimação de cardinalidade:

    • declare @variable1 = 123
select * from table where c1 = @variable1

    • select * from table where c1 = 123

Pelo mesmo motivo que acima, comparar a execução de um procedimento armazenado com a execução da consulta ad hoc equivalente (usando variáveis locais) pode ser diferente. Instruções idênticas precisam ser comparadas.

Etapa 2: medir o tempo de execução no servidor

Para uma comparação precisa das durações da consulta, você pode excluir o tempo de latência de rede ou o tempo de processamento de dados específico do aplicativo. Use um dos seguintes métodos para medir apenas o tempo de execução no SQL Server:

  • Execute sua consulta usando SET STATISTICS TIME:

    SET STATISTICS TIME ON
<YourQuery>
SET STATISTICS TIME OFF

  • Use o XEvent da etapa 1 para examinar a duração ou o tempo decorrido de uma consulta (classe SQL:StmtCompletedde evento , SQL:BatchCompletedou RPC:Completed).

Em alguns casos, a diferença de tempo entre as consultas pode ser causada por um aplicativo em execução em uma rede diferente ou pelo próprio aplicativo. Quando você compara a execução no servidor, você está comparando quanto tempo as consultas levaram para serem executadas no servidor.

Etapa 3: verificar as opções SET para cada conexão

Há opções SET que estão afetando o plano de consulta, o que significa que elas podem alterar a escolha do plano de consulta. Portanto, se um aplicativo de banco de dados usar diferentes opções de conjunto do SSMS, cada opção de conjunto poderá obter um plano de consulta diferente. Por exemplo, ARITHABORT, NUMERIC_ROUNDABORT, ROWCOUNT, FORCEPLAN e ANSI_NULLS. A diferença mais comum observada entre aplicativos SSMS e .NET é a opção SET ARITHABORT . Por padrão, a opção é definida como ON no SSMS, mas definida como OFF na maioria dos aplicativos de banco de dados. Com base nas necessidades do aplicativo, defina ARITHABORT como a mesma configuração no SSMS e no aplicativo para uma comparação válida entre os dois.

Aviso

A configuração padrão do ARITHABORT para SQL Server Management Studio é ON. Os aplicativos cliente que definem ARITHABORT como OFF podem receber planos de consulta diferentes, dificultando a solução de problemas de consultas com desempenho ruim. Ou seja, a mesma consulta pode ser executada rapidamente no Management Studio, mas lenta no aplicativo. Ao solucionar problemas de consultas com o Management Studio, sempre corresponda à configuração ARITHABORT do cliente.

Para obter uma lista de todas as opções que afetam o plano, consulte Definir opções.

Para garantir que as opções SET no SSMS e no aplicativo sejam as mesmas para poder executar uma comparação válida, siga estas etapas:

  1. Use os dados coletados na etapa 1.

  2. Compare as opções definidas verificando os eventos login e existing_connection, especificamente as options_text colunas e opções.