Solucionar los errores de tiempo de espera de consulta

Artículo
07/03/2023

Síntomas

Si una aplicación consulta datos de una base de datos de SQL Server. Si la consulta no devuelve datos dentro del valor de tiempo de espera configurado (normalmente 30 segundos), la aplicación cancela la consulta y genera uno de estos mensajes de error:

Se agotó el tiempo de espera. El período de tiempo de espera se agotó antes de la finalización de la operación o el servidor no responde. La instrucción se ha finalizado.
System.Data.SqlClient.SqlException: tiempo de espera expirado. El período de tiempo de espera se agotó antes de la finalización de la operación o el servidor no responde.

Explicación

Estos errores ocurren en la aplicación. La aplicación establece un valor de tiempo de espera y, si se alcanza el tiempo de espera, cancela la consulta. En SQL Server, una cancelación de consulta del cliente provoca un evento de atención, error 3617 (MSSQLSERVER_3617). Si el valor de tiempo de espera en la aplicación se establece en 0 (sin límite de tiempo), el motor de base de datos ejecuta la consulta hasta que se completa.

En System.Data.SqlClient de .NET Framework, el valor del tiempo de espera se establece en la propiedad CommandTimeout .
En la API de ODBC, se establece a través del atributo SQL_ATTR_QUERY_TIMEOUT en la función SQLSetStmtAttr .
En la API de conectividad de base de datos de Java (JDBC), se establece a través del método setQueryTimeout.
En OLEDB, se establece a través de la propiedad DBPROP_COMMANDTIMEOUT de la estructura DBPROP.
En VBA (Excel), se establece a través de la propiedad ADODB.Command.CommandTimeout.

El tiempo de espera de la consulta es diferente al de una propiedad de tiempo de espera de conexión. Este último controla cuánto tiempo esperar para obtener una conexión correcta y no está involucrado en la ejecución de consultas. Para obtener más información, vea El tiempo de espera de la consulta no es el mismo que el de la conexión.

Pasos para la solución de problemas

Con diferencia, la razón más común de los tiempos de espera de las consultas es que las consultas tienen un mal rendimiento. Esto significa que la consulta se ejecuta más tiempo que el valor predefinido de tiempo de espera de consulta. Hacer que la consulta se ejecute más rápido es el primer objetivo recomendado para solucionar los problemas. Aquí se explica cómo comprobar las consultas:

Use Eventos extendidos o Seguimiento SQL para identificar las consultas que provocan los errores de tiempo de espera. Puede realizar un seguimiento del evento de atención junto con los eventos extendidos sql_batch_completed y rpc_completed y ponerlos en correlación en el mismo session_id. Si observa que un evento completado va seguido inmediatamente de un evento de atención y que la duración del evento completado corresponde aproximadamente a la configuración de tiempo de espera, ha identificado la consulta. Aquí le mostramos un ejemplo:

Nota:

En el ejemplo, la consulta SELECT se ejecutó durante casi exactamente 30 segundos y se detuvo. El evento de atención que tiene el mismo Id. de sesión indica que la aplicación canceló la consulta.

Nombre	Session_id	Sql_text	Duración (microsegundos)	Timestamp
sql_batch_started	54	Seleccione ... from Customers WHERE cid = 192937	NULL	30/09/2021 09:50:25.0000
sql_batch_completed	54	Seleccione ... from Customers WHERE cid = 192937	29999981	30/09/2021 09:50:55.0000
Atención	54	Seleccione ... from Customers WHERE cid = 192937	40000	30/09/2021 09:50:55.0400

Ejecute y pruebe las consultas en SQLCMD o en SQL Server Management Studio (SSMS).
Si las consultas también son lentas en SQLCMD y SSMS, solucione los problemas y mejore el rendimiento de las consultas. Para obtener información detallada, consulte Solución de problemas de consultas de ejecución lenta en SQL Server

Nota:

En SQLCMD y SSMS, el valor de tiempo de espera se establece en 0 (sin límite de tiempo) y las consultas se pueden probar e investigar.
Si las consultas son rápidas en SQLCMD y SSMS, pero lentas en la aplicación, cámbielas para usar las mismas opciones SET que en SQLCMD y SSMS. Compare las opciones SET recopilando un seguimiento de eventos extendidos (inicio de sesión y conexión de eventos con collect_options_text) y compruebe la columna options_text. Aquí le mostramos un ejemplo:
```
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 obtener más información, consulte Solucionar problemas de diferencia de rendimiento de consultas entre la aplicación de base de datos y SSMS.

Compruebe si la configuración CommandTimeout es menor que la duración esperada de la consulta. Si la configuración del usuario es correcta y se siguen agotando los tiempos de espera, se debe a un problema de rendimiento de las consultas. Este es un ejemplo de código ADO.NET con un valor de tiempo de espera establecido en 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);
                }
            }
        }
    }
}

El tiempo de espera de la consulta no es el mismo que el de la conexión

El tiempo de espera de una consulta es diferente del de conexión o de inicio de sesión. Estos últimos se producen cuando la conexión inicial al servidor de base de datos alcanza un período de tiempo de espera predefinido. En esta fase, no se ha enviado ninguna consulta al servidor. Estos mensajes son ejemplos de error de tiempo de espera de conexión o inicio de sesión:

Tiempo de espera de conexión expirado. El período de tiempo de espera transcurrido al intentar consumir la confirmación del protocolo de enlace anterior al inicio de sesión. Esto podría deberse a que se produjo un error en el protocolo de enlace anterior al inicio de sesión o que el servidor no pudo responder a tiempo. La duración del intento de conexión a este servidor fue [Pre-Login] initialization=23; handshake=14979;
Se agotó el tiempo de espera. El período de tiempo de espera se agotó antes de la finalización de la operación o el servidor no responde. System.ComponentModel.Win32Exception (0x80004005): Tiempo de espera de la operación de espera agotado.

El valor de tiempo de espera de conexión es una configuración del lado cliente y normalmente se establece en 15 segundos. Para obtener más información sobre cómo solucionar problemas de tiempo de espera de conexión, consulte Solucionar problemas de tiempo de espera de conexión. Para solucionar problemas de tiempo de espera de consulta, vea este vídeo.

Share via