Compartir a través de

SQL Server no finaliza la ejecución de un lote grande de instrucciones SQL

Este artículo le ayuda a resolver el problema que se produce al ejecutar un lote grande de instrucciones SQL que devuelve varios conjuntos de resultados.

Versión del producto original: SQL Server
Número de KB original: 827575

Síntomas

Cuando se ejecuta un lote grande de instrucciones SQL que devuelven varios conjuntos de resultados, Microsoft SQL Server puede detener el procesamiento del lote antes de que se ejecuten todas las instrucciones del lote. Los efectos de este comportamiento dependen de las operaciones que realizan las instrucciones por lotes. Por ejemplo, si el lote inicia una transacción al principio y confirma la transacción al final, es posible que no se produzca la confirmación. Este comportamiento hace que los bloqueos se mantengan más tiempo de lo esperado. Esto también puede hacer que la transacción se revierte cuando se cierra la conexión. Si el lote no inicia una transacción, los síntomas del problema pueden ser que algunas instrucciones no se ejecutan.

A continuación se muestran los posibles efectos de este problema. Los efectos son variados y dependen exactamente de lo que contiene el lote.

  • Tenga en cuenta que un lote de instrucciones de consulta de base de datos se ejecuta desde una aplicación. Si el lote de instrucciones de consulta de base de datos se compone de un BEGIN TRANSACTION al principio y COMMIT TRANSACTION al final, es posible que la operación de confirmación no se produzca aunque el control se devuelva a la aplicación. Se trata de un problema porque los bloqueos que posiblemente se mantienen pueden provocar una transacción pendiente y pueden permanecer desapercibidos.

    En este escenario, dado que la transacción nunca se confirma en el lote, permanece pendiente y se revierte en la desconexión de SQL Server.

  • Si usa una interfaz de programa de aplicación (API) para comenzar y confirmar la transacción, es posible que vea el siguiente comportamiento:

    • Si usa la API para enviar una notificación al servidor para iniciar una transacción y, a continuación, ejecuta el lote, SQL puede procesar solo una parte del lote y, a continuación, devolver el control a la aplicación.
    • Después de este paso, si usa la API para confirmar la transacción, solo se confirma la parte del lote que se procesó. No se produce ningún error.

    Por ejemplo, con ODBC se llama SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF) a para iniciar la transacción y, a continuación, se usa SQLEndTran(SQL_COMMIT) para confirmar la transacción.

Causa

Al procesar los resultados de un lote, SQL Server rellena el búfer de salida de conexión con los resultados que proceden del lote. La aplicación cliente debe procesar estos resultados. Si ejecuta un lote grande con varios conjuntos de resultados (varias instrucciones que generan resultados), SQL Server rellena ese búfer de salida hasta que alcanza un límite interno y no puede continuar hasta que la aplicación cliente empiece a consumir esos resultados. Cuando el cliente comienza a consumir los conjuntos de resultados, SQL Server comienza a ejecutar el lote de nuevo porque ahora hay memoria disponible en el búfer de salida. Este comportamiento es por diseño.

En muchos casos, este problema se produce al conectarse a SQL Server mediante el protocolo canalizaciones con nombre o el protocolo de memoria compartida (LPC). Esto se debe al tamaño interno del búfer que SQL Server tiene disponible para los distintos protocolos.

Solución alternativa

Para solucionar el problema, siga estos pasos:

  1. Asegúrese de que la aplicación cliente consume todos los conjuntos de resultados de salida. Tan pronto como el cliente consuma todos los conjuntos de resultados de salida, SQL Server completa la ejecución del lote.

    • Si usa Open Database Connectivity (ODBC) para conectarse a SQL Server, puede llamar al SQLMoreResults método hasta que el método informe de que no hay más conjuntos de resultados.
    • Si usa OLE DB para conectarse a SQL Server, puede llamar repetidamente al método IMultipleResults::GetResult hasta que devuelva DB_S_NORESULT.

  2. Agregue la instrucción SET NOCOUNT ON al principio del lote. Si el lote se ejecuta dentro de un procedimiento almacenado, agregue la instrucción al principio de la definición del procedimiento almacenado. Esto impide que SQL Server devuelva un conjunto de resultados adicional que muestre el número de filas procesadas después del conjunto de resultados principal. Por lo tanto, puede reducir los datos que se van a generar en el búfer de salida del servidor. Sin embargo, esto no garantiza que el problema no se produzca. Solo aumenta la posibilidad de que los datos devueltos desde el servidor sea lo suficientemente pequeño como para caber en un lote de conjuntos de resultados.

Se recomienda que la aplicación cliente consuma siempre todos los conjuntos de resultados procedentes de SQL Server, independientemente del tamaño del lote que esté ejecutando. Si no procesa estos datos y hay conjuntos de resultados correctos que se devolverán antes de un error en el lote del conjunto de resultados, es posible que el cliente no detecte los errores del servidor. Las aplicaciones cliente deben procesar los conjuntos de resultados en su totalidad para garantizar la ejecución correcta.

Pasos para reproducir el problema

  1. Conéctese a SQL Server mediante SQL Server Management Studio (SSMS) y cree una base de datos de pubs de ejemplo.

  2. Cree un procedimiento almacenado de SQL en pubs con un lote relativamente grande de instrucciones de consulta de base de datos, como se indica a continuación:

    CREATE PROC bigBatch AS
BEGIN TRANSACTION
UPDATE authors SET au_fname = 'newname1' WHERE au_id='172-32-1176'
UPDATE authors SET au_fname = 'newname2' WHERE au_id='172-32-1176'
UPDATE authors SET au_fname = 'newname3' WHERE au_id='172-32-1176'
-- Add more UPDATE statements here ... 
UPDATE authors SET au_fname = 'newname1000' WHERE au_id='172-32-1176'
COMMIT TRANSACTION

  3. En Explorador de objetos, seleccione Administración>de eventos extendidos.

  4. Haga clic con el botón derecho en Sesiones y, a continuación, seleccione Asistente para nueva sesión.

  5. Cree una nueva sesión de eventos mediante la plantilla de sesión TSQL_SPs .

  6. Inicie la sesión y vea los datos en directo. Para obtener más información, vea Inicio rápido: Eventos extendidos en SQL Server.

  7. Conéctese a SQL Server con ODBC o OLE DB, ejecute bigBatchy, a continuación, analice los datos activos de la sesión de eventos.

Conexión con ODBC

Para conectarse a SQL Server mediante ODBC, siga estos pasos:

  1. Cree y configure un nombre de origen de datos (DSN) con pubs la base de datos que se conecta a SQL Server.
  2. Abra el ejemplo de herramienta de prueba odbc que está disponible con la instalación del SDK de Acceso a datos (MDAC).
  3. En el menú Conn , seleccione Conexión completa.
  4. En el cuadro de diálogo Conexión completa, seleccione el DSN que creó en el paso 1.
  5. Asegúrese de que la conexión a SQL Server se realiza correctamente.
  6. En el menú Stmt , seleccione SQLExecDirect.
  7. En el cuadro StatementText , escriba {call bigBatch}y, a continuación, seleccione Aceptar.

En los datos activos de XEvent, observará que el procesamiento del procedimiento almacenado no se ha completado. Sin embargo, la herramienta de prueba odbc indica que la ejecución se realizó correctamente. Para capturar todos los conjuntos de resultados y hacer que el lote finalice en el servidor, seleccione Obtener todos los datos en el menú Resultados .

Conexión con OLE DB

Para conectarse a SQL Server mediante OLE DB, siga estos pasos:

  1. Abra el ejemplo de herramienta RowsetViewer de OLE DB que está disponible con el SDK de MDAC.
  2. Conéctese a la base de datos de SQL Server pubs mediante la opción Conexión completa .
  3. En el menú Comando , seleccione ICommand y, a continuación, seleccione Ejecutar.
  4. En el cuadro Texto cmd, escriba {call bigBatch}.
  5. Seleccione IID_IMultipleResults en la lista REFIID y, a continuación, seleccione Propiedades.
  6. En el cuadro de diálogo ICommandProperties::SetProperties , seleccione DBPROP_IMultipleResults, cambie el valor a VARIANT_TRUE y, a continuación, seleccione Aceptar.
  7. Seleccione Aceptar.

En los datos activos de XEvent, observará que el procesamiento del procedimiento almacenado no se ha completado. Sin embargo, la herramienta RowsetViewer muestra que la operación se realizó correctamente. Para recuperar todos los conjuntos de resultados, haga clic con el botón derecho en el objeto MultipleResults en el panel izquierdo, seleccione IMultipleResults y, a continuación, seleccione GetResult. Repita la operación hasta que se hayan consumido todos los conjuntos de resultados.

Referencias

  • Last updated on