Compartir a través de

Analysis Services deja de aceptar nuevas conexiones

Nota

Este artículo se deriva de un blog de MSDN publicado originalmente el 3 de julio de 2012.

Al procesar un objeto de Analysis Services, como FullProcess en una base de datos o cubo, los archivos antiguos deben reemplazarse por archivos nuevos cerca del final de la fase de procesamiento. Además, se necesita un bloqueo para el nivel más alto de la base de datos. Los usuarios que ejecutan consultas tienen la prioridad hasta que finalizan las consultas.

A veces, los usuarios y el administrador del servidor ni siquiera pueden iniciar sesión con SQL Server Management Studio para ejecutar una nueva consulta.

Una base de datos de Analysis Services es una colección de archivos (algunos archivos XML que apuntan a archivos y algunos archivos binarios comprimidos) que representan los objetos del cubo que se consultan con consultas MDX. El procesamiento es el acto de actualizar esos objetos mediante un nuevo conjunto de valores de datos de la base de datos relacional. Ejecuta consultas de Transact-SQL de base de datos relacionales de gran tamaño para realizar consultas desde los orígenes de datos, realizar combinaciones, agregar los datos y guardar los datos comprimidos y agregados en el cubo. La copia antigua de los objetos de base de datos de Analysis Services permanece hasta el final del procesamiento. Cuando casi finaliza el procesamiento, comienza la fase de confirmación.

La fase de confirmación necesita un bloqueo de escritura exclusivo. Los usuarios no pueden consultar los objetos en el momento en que intercambia la versión anterior de los datos del cubo para la nueva versión.

Otro problema es que el bloqueo Master.vmp para toda la instancia es necesario para finalizar la confirmación del procesamiento. Este archivo especial es un puntero a todos los demás objetos de base de datos y sus versiones actuales. Este archivo es importante al intercambiar los objetos de base de datos antiguos con los nuevos objetos de base de datos.

A medida que el servidor entra en la fase 2 de la confirmación, intenta obtener un bloqueo de nivel de servidor para actualizar Master.vmp. Si otra transacción está en proceso en ese momento, el servidor espera un intervalo igual al valor ForceCommitTimeout . El valor predeterminado es 30 segundos. A continuación, revierte las transacciones no confirmadas y anula la ejecución de consultas. El bloqueo de todo el servidor permanece en vigor hasta que finaliza la transacción. Bloquea cualquier solicitud de bloqueo de lectura iniciada. Cuando un nuevo usuario de inicio de sesión o existente intenta conectarse al servidor, inicia una solicitud de bloqueo de lectura y espera.

Este pequeño archivo es el punto central de la lista de bases de datos de Analysis Services. No manipule nunca la base de datos o, de lo contrario, es probable que se elimine la base de datos.

Screenshot of master.vmp file

El interior de master.vmp (mostrado con formato XML para mayor claridad) muestra cada objeto (representado por un valor GUID) y el número de versión (un entero 1, 2, 3... 43, etc. El número de versión se incrementa cada vez que el servidor procesa (o sincroniza) y confirma el objeto y se actualiza la hora. Este es el punto central de todos los objetos de una instancia de Analysis Services.

Image showing updated version numbers in master.vmp

¿Por qué no puede iniciar sesión cuando se produce el bloqueo?

El bloqueo puede ser el centro del problema. Esta es una simplificación visual de la cadena de bloqueo que impide que los nuevos usuarios entren en la base de datos y ejecuten cualquier consulta.

Image showing the blocking chain

Es posible que encuentre este patrón de bloqueo. Las consultas lentas agravan las esperas de confirmación de procesamiento y el servidor deja de responder. Las consultas principales del conjunto 1 tardan muchas horas. Los bloqueos Set 2 están esperando más de una hora.

  • Establecer 1: Las consultas que se ejecutan contienen bloqueos de lectura de base de datos (que se ejecutan durante varias horas)
  • Establecer 2: El procesamiento de la confirmación necesita bloqueos de escritura de confirmación (esperando aproximadamente 1 hora o más)
  • Establecer 3: Las nuevas conexiones esperan en línea, bloqueadas para leer la base de datos pronto a confirmar

A veces, el administrador ni siquiera puede iniciar sesión con Management Studio porque la conexión se pone en cola en set 3.

Cuando la mayoría de las nuevas conexiones proceden de Management Studio, el servidor realiza su inicialización para ver los nombres de base de datos y los nombres de objeto con comandos discover. Es posible que se bloqueen en línea y esperen a leer la base de datos pronto confirmada y procesada detrás del conjunto de procesamiento 2.

Es probable que las nuevas conexiones realicen un comando de detección de la siguiente manera:

Discover on DBSCHEMA_CATALOGS

Discover on MDSCHEMA_MEMBERS

Durante la fase de confirmación de la transacción de procesamiento, las consultas todavía se pueden enviar al objeto, pero se ponerán en cola hasta que se complete la confirmación. Para obtener más información sobre cómo bloquear y desbloquear durante el procesamiento, consulte Procesamiento de objetos de Analysis Services.

Corrección del problema

Paso 1: Minimizar la duración de la consulta MDX

Ajuste las consultas. Reduzca el tiempo necesario para establecer 1 para finalizar. A continuación, tiene el menor conflicto entre las consultas y el procesamiento. En un ejemplo, la consulta lenta estaba solicitando una forma arbitraria. Ajuste o evite consultas de formas arbitrarias en Establecer 1 en:

  • Ejecute más rápido.
  • Cambie la sintaxis para evitar formas arbitrarias.
  • Configure un tiempo de espera en la aplicación para eliminar las consultas de larga duración.

Agregue agregaciones y particiones para reducir la cantidad de lectura de datos necesarios.

Ajuste los cálculos que puedan provocar que el motor de fórmulas funcione mucho tiempo.

Ejecute un seguimiento del generador de perfiles para investigar la consulta MDX.

A veces, no se pueden controlar las consultas en absoluto. En un entorno ad hoc, cuando Excel usuarios de tabla dinámica y MDX personalizados están habilitados, tiene la consulta MDX descontrolada ocasional que puede tardar mucho tiempo.

Paso 2: Evitar el procesamiento en horas punta para evitar colisiones de consulta y procesamiento

En un ejemplo, el procesamiento completo set 2 se produce a las 11:30 a.m. y al mediodía. Está obligado a haber una colisión de bloqueo durante esos momentos ocupados porque hay consultas significativas que se ejecutan en la empresa. Evite el procesamiento en horas punta.

Paso 3: Indicar al servidor que favorece uno u otro cuando se produce el bloqueo

Pruebe estas dos opciones de configuración para permitir que el servidor intente eliminar las consultas largas del conjunto 1 o el procesamiento en espera del conjunto 2.

  • Elimine las consultas: Establecer 2 puede influir en que el servidor cancele Set 1 después de un tiempo de espera en bloqueos con esta configuración.

    • ForceCommitTimeout: esta propiedad de servidor se usa para controlar lo que sucede cuando una operación de procesamiento está esperando a finalizar su operación para entrar en la fase de confirmación. Cuando este valor es mayor que cero, SQL Server Analysis Services comienza a cancelar transacciones anteriores, pero solo después del valor especificado en milisegundos. Si los bloqueos de lectura están disponibles antes de alcanzar el período ForceCommitTimeout , no se producirá la cancelación.

  • Elimine el procesamiento: El conjunto 1 puede influir en el servidor para cancelar el conjunto 2 después de esperar a que se produzcan bloqueos.

    • CommitTimeout: las operaciones de procesamiento de Analysis Server deben adquirir un bloqueo de escritura para poder confirmar una transacción. Para adquirir un bloqueo de escritura, ningún otro proceso o consulta puede tomar ningún otro bloqueo de lectura. Analysis Services debe esperar hasta que se liberen todos los bloqueos de lectura. La transacción espera un tiempo para adquirir un bloqueo de escritura, como se especifica en la propiedad CommitTimeout , antes de revertirse.

A veces, la cancelación no se produce inmediatamente, por lo que incluso con ForceCommitTimeout y CommitTimeout, puede haber un período en el que se detenga el trabajo.

Otra variación: varias solicitudes de procesamiento se pueden bloquear entre sí

Si ejecuta dos o más lotes de procesamiento al mismo tiempo en transacciones diferentes, puede producirse una cadena de bloqueo y un interbloqueo similares. En el ejemplo siguiente se simplifica. Supongamos que hay dos transacciones de procesamiento que están listas casi al mismo tiempo, pero se bloquean en espera de la consulta MDX larga de un usuario.

Image of multiple processing requests that block each other

La granularidad de bloqueo al final del procesamiento es general en el nivel de base de datos y en el archivo master.vmp, por lo que es difícil que el procesamiento paralelo pase correctamente.

La hora de inicio del procesamiento no es tan importante como la hora de finalización, por lo que debe evitar la superposición al final de los trabajos de procesamiento. Si las dos transacciones de procesamiento están listas para confirmarse aproximadamente al mismo tiempo, pueden contener algunos bloqueos y solicitar otros bloqueos que provocan un interbloqueo.

Agregar una consulta MDX de larga duración a la mezcla hace que sea más probable que provoque una cadena de interbloqueo porque los bloqueos intermedios pueden hacer que se produzca un círculo.

Es posible que reciba este error como evento notification en el seguimiento del generador de perfiles:

Transaction errors: Aborting transaction on session <victimsessionid>

Es probable que el trabajo de procesamiento de víctimas se cancele con este error:

Transaction errors: While attempting to acquire one or more locks, the transaction was canceled.

Soluciones propuestas para bloquear conflictos entre trabajos de procesamiento

  1. Programe el procesamiento de forma escalonada. Recuerde que la hora de finalización es más importante que la hora de inicio porque la fase de confirmación es la hora en la que se necesitan los bloqueos de confirmación de alta granularidad.

  2. Combine el procesamiento en una sola etiqueta XMLA de transacción o lote. Si procesa objetos en el ámbito de una sola transacción, quizás no entren en conflicto y se maten entre sí. Puede procesar objetos paralelos en una sola transacción en lugar de en una secuencia de confirmaciones pequeñas. Podría tener una confirmación más grande y granular para reducir la ventana en la que se producen bloqueos, pero está aumentando el área expuesta del número de bloqueos en granularidad de nivel inferior. Esto puede aumentar el conflicto con las consultas de usuario. Por ejemplo, puede tener varios comandos de procesamiento en un solo lote.

       <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
     <Parallel>
       <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema"    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"    xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"    xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"    xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200"    xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"    xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300"    xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
   <Object>

         <DatabaseID>AdventureWorksDW2012</DatabaseID>
     <DimensionID>Dim Account</DimensionID>
   </Object>
   <Type>ProcessUpdate</Type>
   <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
 </Process>
 <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"    xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"    xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"    xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200"    xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"    xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300"    xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
   <Object>

                 <DatabaseID>AdventureWorksDW2012</DatabaseID>
             <DimensionID>Clustered Customers</DimensionID>
         </Object>
         <Type>ProcessFull</Type>
         <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
     </Process>
  </Parallel>
</Batch>

  3. Procese en un servidor y sincronice con otro servidor para asegurarse de que estos procesos no interfieren entre sí.

¿Cómo ve estos bloqueos y cadenas de bloqueo?

A partir de SQL Server 2008 R2 Service Pack 1, hay algunos eventos de seguimiento del generador de perfiles excelentes que le ayudan a ver estos bloqueos. Las etiquetas XML dentro del texto de los eventos de seguimiento muestran quién está esperando y qué bloqueos se mantienen. Recopile un seguimiento del generador de perfiles con los eventos normales, pero agregue estos eventos para ver quién está bloqueando quién y durante cuánto tiempo, y en qué objetos están en conflicto los bloqueos.

El evento Lock Acquired indica cuándo la transacción ha obtenido un lote de bloqueos para el procesamiento de la transacción. El evento Lock Released indica cuándo la transacción ha liberado un lote de bloqueos que solicitó la transacción. Este evento también indica la duración de los bloqueos. El evento Lock Waiting indica cuándo una transacción intenta y espera en una cola para obtener un bloqueo en un lote. Esta información se encuentra en la columna TextData de esos eventos. Esta información incluye los siguientes datos relacionados adicionales:

  • El identificador de transacción
  • El nodo XML LockList
  • Nodo XML WaitList
  • Nodo XML HoldList

El evento Lock Acquired y el evento Lock Released contienen la información de LockList. El evento Lock Waiting contiene la información LockList, WaitList y HoldList.

LockList

El nodo LockList contiene la siguiente información:

  • Tipo de bloqueo
  • Estado de bloqueo
  • Ruta de acceso del objeto que se solicita
  • Identificador de objeto

Nota

La ruta de acceso del objeto se notifica sin un espacio de nombres. El evento Lock Released contiene además la propiedad Duration . La propiedad Duration indica la duración que el bloqueo se mantiene en milisegundos.

A continuación se muestra un ejemplo del nodo LockList:

<LockList>
<Lock>
<Type>Read</Type>
<LockStatus>Acquired</LockStatus>
<Object><DatabaseID>AdventureWorks</DatabaseID></Object>
<ObjectID>asadfb-vfbvadr-ft3323-54235</ObjectID>
</Lock>
<Lock>
<Type>Read</Type>
<LockStatus>Waiting</LockStatus>
<Object><DatabaseID>FoodMart</DatabaseID><Object>
<ObjectID>asadfb-vfbvadr-ft3323-54235</ObjectID>
</Lock>
<Lock>
<Type>Read</Type>
<LockStatus>Requested</LockStatus>
<Object><DatabaseID>FoodMart</DatabaseID><Object>
<ObjectID>asadfb-vfbvadr-ft3323-54235</ObjectID>
</Lock>
</LockList>

En este ejemplo, la transacción solicita tres bloqueos, obtiene uno y espera el segundo bloqueo.

Espera

El nodo WaitList enumera las transacciones en espera que están por delante de la transacción actual. A continuación se muestra un ejemplo del nodo WaitList:

<WaitList>
     <Object><DatabaseID>FoodMart</DatabaseID><Object>
     <ObjectID>asadfb-vfbvadr-ft3323-54235</ObjectID>
     <Type>Read</Type>
     <Transaction>  
  <TransactionID>2342-3we-dsdf-sdf<TransactionID>
  <SPID>234</SPID>
  <Type>Write</Type>
     </Transaction>  
     <Transaction>  
  <TransactionID>2ger342-3rtee-dsdf-sdf<TransactionID>
  <SPID>222</SPID>
  <Type>Read</Type>
     </Transaction>  
</WaitList>

HoldList

El nodo HoldList enumera las transacciones que contienen un bloqueo que la transacción actual intenta obtener. A continuación se muestra un ejemplo del nodo HoldList:

<HoldList>
     <Object><DatabaseID>FoodMart</DatabaseID><Object>
     <ObjectID>asadfb-vfbvadr-ft3323-54235</ObjectID>
     <Type>Read</Type>
     <Transaction>  
  <TransactionID>2342-3we-dsdf-sdf<TransactionID>
  <SPID>234</SPID>
  <Type>Write</Type>
     </Transaction>  
     <Transaction>  
  <TransactionID>2ger342-3rtee-dsdf-sdf<TransactionID>
  <SPID>222</SPID>
  <Type>Read</Type>
     </Transaction>  
</HoldList>

En SQL Server 2008 Analysis Services o versiones posteriores, puede ejecutar una consulta MDX en las vistas de administración dinámica para ver las distintas conexiones, sus transacciones y quién ha concedido bloqueos y quién está esperando bloqueos (bloqueo).

select * from $system.discover_connections;
go
select * from $system.discover_sessions;
go
select * from $system.discover_transactions; 
go
select * from $system.discover_locks;
go
select * from $system.discover_jobs
go

Más información