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 na forma como os aplicativos processam os 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 garantir que elas sejam idênticas em todos os aspectos, 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, encontre o arquivo .xel de destino e clique duas vezes nele. O arquivo será aberto em outra janela do SSMS que você pode usar para exibir e analisar.

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

    • Encontre a localização dos arquivos .xel e leia esse 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, consulte os seguintes exemplos:

  • Se os procedimentos armazenados ou funções 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 a Densidade média do histograma para estimativa de cardinalidade, enquanto a segunda consulta usa a etapa do histograma para estimativa de cardinalidade:

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

    • select * from table where c1 = 123

Pelo mesmo motivo 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. Declarações idênticas devem ser comparadas.

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

Para uma comparação precisa das durações das consultas, você pode excluir o tempo de latência da 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:

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. Ao comparar a execução no servidor, você está comparando quanto tempo as consultas levaram para serem executadas no servidor.

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

Há opções SET que afetam 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 opções de conjunto diferentes 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 os 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 com a mesma configuração no SSMS e no aplicativo para obter uma comparação válida entre os dois.

Aviso

A configuração padrão de ARITHABORT para o 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 executadas insatisfatoriamente. 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 faça a correspondência com a 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.