Solucionar problemas de erros de tempo limite de consulta

Sintomas

Suponha que um aplicativo consulte dados de um banco de dados do SQL Server. Se a consulta não retornar dados dentro do valor de tempo limite configurado (normalmente 30 segundos), o aplicativo cancelará a consulta e gerará uma destas mensagens de erro:

• O tempo limite expirou. O período de tempo limite decorreu antes da conclusão da operação ou o servidor não está respondendo. A instrução foi encerrada.

• System.Data.SqlClient.SqlException: tempo limite expirado O período de tempo limite decorreu antes da conclusão da operação ou o servidor não está respondendo.

Explicação

Esses erros ocorrem no lado do aplicativo. O aplicativo define um valor de tempo limite e, se o tempo limite for atingido, ele cancelará a consulta. No lado do SQL Server, um cancelamento de consulta do lado do cliente causa um evento de Atenção, erro 3617 (MSSQLSERVER_3617). Se o valor de tempo limite no lado do aplicativo for definido como 0 (sem limite de tempo), o Mecanismo de Banco de Dados executará a consulta até que ela seja concluída.

• No .NET Framework System.Data.SqlClient, o valor de tempo limite é definido na propriedade CommandTimeout.
• Na API ODBC, ela é definida por meio do atributo SQL_ATTR_QUERY_TIMEOUT na função SQLSetStmtAttr .
• Na API JDBC (Java Database Connectivity), ela é definida por meio do método setQueryTimeout.
• No OLEDB, ele é definido por meio da propriedade DBPROP_COMMANDTIMEOUT na estrutura DBPROP.
• No VBA (Excel), ele é definido por meio da propriedade ADODB.Command.CommandTimeout.

O tempo limite da consulta é diferente de uma propriedade de tempo limite de conexão. O último controla quanto tempo esperar por uma conexão bem-sucedida e não está envolvido na execução da consulta. Para obter mais informações, consulte O tempo limite da consulta não é o mesmo que o tempo limite da conexão.

Etapas para a solução de problemas

De longe, o motivo mais comum para tempos limite de consulta é o baixo desempenho de consultas. Isso significa que a consulta é executada por mais tempo do que o valor de tempo limite de consulta predefinido. Fazer com que a consulta seja executada mais rapidamente é o primeiro destino recomendado da solução de problemas. Veja como verificar consultas:

1. Use Eventos Estendidos ou Rastreamento do SQL para identificar as consultas que causam os erros de tempo limite. Você pode rastrear o evento de atenção junto com os eventos estendidos sql_batch_completed e rpc_completed e correlacioná-los no mesmo session_id. Se você observar que um evento concluído é imediatamente seguido por um evento de atenção e a duração do evento concluído corresponde aproximadamente à configuração de tempo limite, você identificou a consulta. Veja um exemplo:

   Observação

   No exemplo, a consulta SELECT foi executada por quase exatamente 30 segundos e parou. O evento de atenção com a mesma ID de sessão indica que a consulta foi cancelada pelo aplicativo.

   Nome	Session_id	Sql_text	Duração (microssegundos)	Carimbo de data/hora
   sql_batch_started	54	Selecionar ... de Clientes EM QUE cid = 192937	NULL	30/09/2021 09:50:25.0000
   Sql_batch_completed	54	Selecionar ... de Clientes EM QUE cid = 192937	29999981	30/09/2021 09:50:55.0000
   Atenção	54	Selecionar ... de Clientes EM QUE cid = 192937	40000	30/09/2021 09:50:55.0400

2. Execute e teste as consultas no SQLCMD ou SQL Server Management Studio (SSMS).

3. Se as consultas também forem lentas no SQLCMD e no SSMS, solucione problemas e melhore o desempenho das consultas. Para obter informações detalhadas, consulte Solucionar problemas de consultas em execução lenta no SQL Server

   Observação

   No SQLCMD e no SSMS, o valor de tempo limite é definido como 0 (sem limite de tempo) e as consultas podem ser testadas e investigadas.

4. Se as consultas forem rápidas no SQLCMD e no SSMS, mas lentas no lado do aplicativo, altere as consultas para usar as mesmas opções SET usadas no SQLCMD e no SSMS. Compare as opções SET coletando um rastreamento de Eventos Estendidos (entrando e conectando eventos comcollect_options_text) e verifique a coluna options_text. Veja um exemplo:

   ALTER EVENT SESSION [setOptions] ON SERVER 
   ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1) 
       ACTION(package0.event_sequence,package0.last_error,sqlos.system_thread_id,sqlserver.context_info,sqlserver.session_id,sqlserver.sql_text)), 
   ADD EVENT sqlserver.login(SET collect_options_text=(1)
       ACTION(sqlos.system_thread_id,sqlserver.context_info,sqlserver.sql_text))
   

   Para obter mais informações, consulte Solucionar problemas de diferença de desempenho de consulta entre o aplicativo de banco de dados e o SSMS.

5. Verifique se a configuração CommandTimeout é menor que a duração esperada da consulta. Se a configuração do usuário estiver correta e os tempos limite ainda ocorrerem, será devido a um problema de desempenho de consulta. Aqui está um exemplo de código ADO.NET com um valor de tempo limite definido como 10 segundos:

   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Text;
   using System.Threading.Tasks;
   using System.Data.SqlClient;
   using System.Data;
   
   namespace ConsoleApplication6
   {
       class Program
       {
           static void Main()
           {
               string ConnectionString = "Data Source=.\sql2019;Integrated Security=SSPI;Initial Catalog=tempdb;";
               string queryString = "exec test";
   
               using (SqlConnection connection = new SqlConnection(ConnectionString))
               {
                   connection.Open();
                   SqlCommand command = new SqlCommand(queryString, connection);
   
                   // Setting command timeout to 10 seconds
                   command.CommandTimeout = 10;
                   //command.ExecuteNonQuery();
                   try {
                       command.ExecuteNonQuery();
                   }
                   catch (SqlException e) {
                       Console.WriteLine("Got expected SqlException due to command timeout ");
                       Console.WriteLine(e);
                   }
               }
           }
       }
   }
   

O tempo limite da consulta não é o mesmo que o tempo limite de conexão

Um tempo limite de consulta é diferente de um tempo limite de conexão ou tempo limite de logon. O tempo limite de conexão ou logon ocorre quando a conexão inicial com o servidor de banco de dados atinge um período de tempo limite predefinido. Neste estágio, nenhuma consulta foi enviada ao servidor. Essas mensagens são exemplos de erro de tempo limite de conexão ou logon:

• Tempo Limite de Conexão Expirado. O período de tempo limite decorrido ao tentar consumir a confirmação de handshake de pré-logon. Isso pode ser porque o handshake de pré-logon falhou ou o servidor não pôde responder no tempo. A duração da tentativa de conexão com esse servidor foi [Pre-Login] initialization=23; handshake=14979;

• O tempo limite expirou. O período de tempo limite decorreu antes da conclusão da operação ou o servidor não está respondendo. System.ComponentModel.Win32Exception (0x80004005): o tempo limite de espera foi atingido.

O valor de tempo limite da conexão é uma configuração do lado do cliente e normalmente é definido como 15 segundos. Para obter mais informações sobre como solucionar problemas de tempo limite de conexão, consulte solucionar problemas de tempo limite de conexão. Para solucionar problemas de tempo limite de consulta, assista a este vídeo.