Analysis Services smette di accettare nuove connessioni
Nota
Questo articolo deriva da un blog MSDN pubblicato originariamente il 3 luglio 2012.
Quando si elabora un Analysis Services, ad esempio FullProcess in un database o un cubo, i file meno vecchi devono essere sostituiti con nuovi file alla fine della fase di elaborazione. Inoltre, è necessario un blocco per il livello più alto nel database. Gli utenti che eseguono query hanno la priorità fino al completamento delle query.
In alcuni casi, gli utenti e l'amministratore del server non possono neanche accedere con SQL Server Management Studio eseguire una nuova query.
Un database Analysis Services è una raccolta di file (alcuni file XML che puntano a file e alcuni file binari compressi) che rappresentano gli oggetti nel cubo su cui si esegue una query MDX. L'elaborazione è l'operazione di aggiornamento di tali oggetti tramite un nuovo set di valori di dati del database relazionale. Esegue query Transact-SQL di database relazionali di grandi dimensioni per eseguire query dalle origini dati, eseguire join, aggregare i dati e salvare i dati compressi e aggregati nel cubo. La copia precedente dell'Analysis Services di database rimane fino alla fine dell'elaborazione. Quando l'elaborazione è quasi terminata, inizia la fase di commit.
La fase di commit richiede un blocco di scrittura esclusivo. Gli utenti non possono eseguire query sugli oggetti al momento in cui scambia la versione precedente dei dati del cubo con la nuova versione.
Un altro problema è che il blocco Master.vmp a livello di istanza è necessario per completare l'elaborazione del commit. Questo file speciale è un puntatore a tutti gli altri oggetti di database e alle relative versioni correnti. Questo file è importante quando si scambiano gli oggetti di database vecchi con i nuovi oggetti di database.
Quando il server entra nella fase 2 del commit, tenta di ottenere un blocco a livello di server per aggiornare Master.vmp. Se a quel punto è in corso un'altra transazione, il server attende un intervallo uguale all'impostazione ForceCommitTimeout. Il valore predefinito è 30 secondi. Esegue quindi il rollback di tutte le transazioni di cui non è stato eseguito il commit e interrompe l'esecuzione di query. Il blocco a livello di server rimane attivo fino al completamento della transazione. Blocca qualsiasi richiesta di blocco di lettura avviata. Quando un nuovo accesso o un utente esistente tenta di connettersi al server, avvia una richiesta di blocco di lettura e attende.
Questo file di piccole dimensioni è il punto centrale dell'elenco di database in Analysis Services. Non manomettere mai il database oppure è probabile che il database sia stato eliminato.

L'interno di master.vmp (illustrato con la formattazione XML per maggiore chiarezza) mostra ogni oggetto (rappresentato da un valore GUID) e il numero di versione (un numero intero 1, 2, 3... 43 e così via). Il numero di versione viene incrementato ogni volta che l'oggetto viene elaborato (o sincronizzato) ed eseguito il commit dal server e l'ora viene aggiornata. Questo è il punto centrale di tutti gli oggetti in un'Analysis Services istanza.

Perché non è possibile accedere quando si verifica il blocco?
Il blocco può essere al centro del problema. Ecco una semplificazione visiva della catena di blocchi che impedisce ai nuovi utenti di entrare nel database ed eseguire qualsiasi query.

È possibile che si verifichi questo modello di blocco. Le query lente peggiorano le attese di commit di elaborazione e il server non risponde. Le query head nel set 1 stanno prendendo molte ore. I blocchi set 2 sono in attesa di più di un'ora.
- Set 1: Le query in esecuzione conservano i blocchi di lettura del database (in esecuzione per diverse ore)
- Set 2: L'elaborazione del commit richiede blocchi di scrittura del commit (in attesa di circa 1 ora o più)
- Set 3: Nuove connessioni in attesa in linea, bloccate per leggere il database di cui verrà eseguito il commit a breve
A volte l'amministratore non può neanche accedere con Management Studio perché la connessione viene accodata nel set 3.
Quando la maggior parte delle nuove connessioni provengono da Management Studio, il server esegue l'inizializzazione per visualizzare i nomi di database e i nomi degli oggetti con i comandi di individuazione. Potrebbero rimanere bloccati in riga e attendere di leggere il database presto disponibile ed elaborato dietro il set di elaborazione 2.
È probabile che le nuove connessioni eseguano un comando di individuazione come segue:
Discover on DBSCHEMA_CATALOGS
Discover on MDSCHEMA_MEMBERS
Durante la fase di commit della transazione di elaborazione, le query possono comunque essere inviate all'oggetto , ma verranno accodati fino al completamento del commit. Per altre informazioni sul blocco e lo sblocco durante l'elaborazione, vedere Elaborazione Analysis Services oggetti.
Risolvere il problema
Passaggio 1: Ridurre al minimo la durata della query MDX
Ottimizzare le query. Ridurre il tempo necessario per il completamento del set 1. Si verifica quindi il conflitto minimo tra query ed elaborazione. In un esempio, la query lenta richiedeva una forma arbitraria. Ottimizzare o evitare query di forma arbitrarie in Impostare 1 su:
- Eseguire più velocemente.
- Modificare la sintassi per evitare forme arbitrarie.
- Configurare un timeout nell'applicazione per l'esecuzione di query con esecuzione lunga.
Aggiungere aggregazioni e partizioni per ridurre la quantità di lettura dei dati necessari.
Ottimizzare i calcoli che potrebbero causare un lungo funzionamento del motore delle formule.
Eseguire una traccia del profiler per analizzare la query MDX.
A volte non è possibile controllare le query. In un ambiente ad hoc, quando Excel gli utenti di tabelle pivot e MDX personalizzati sono abilitati, si ha la query MDX occasionale runaway che potrebbe richiedere molto tempo.
Passaggio 2: Evitare l'elaborazione nelle ore di punta per evitare conflitti di query ed elaborazione
In un esempio, l'elaborazione completa set 2 viene eseguita alle 11:30 e a mezzogiorno. È necessario che si sia verificata una collisione di blocco durante i periodi di attività in cui si verificano query significative in esecuzione nell'azienda. Evitare l'elaborazione nelle ore di punta.
Passaggio 3: Indicare al server di favorire l'uno o l'altro quando si verifica il blocco
Provare queste due impostazioni di configurazione per consentire al server di provare a eliminare le query lunghe del set 1 o l'elaborazione in attesa del set 2.
Uccidi le query: Il set 2 può influire sull'annullamento del set 1 dopo un periodo di attesa dei blocchi con questa impostazione.
- ForceCommitTimeout: questa proprietà del server viene usata per controllare cosa accade quando un'operazione di elaborazione è in attesa di completare l'operazione per entrare nella fase di commit. Quando questo valore è maggiore di zero, SQL Server Analysis Services l'annullamento delle transazioni precedenti, ma solo dopo il valore specificato in millisecondi. Se i blocchi di lettura diventano disponibili prima che venga raggiunto il periodo ForceCommitTimeout, l'annullamento non verrà eseguito.
Uccidi l'elaborazione: L'impostazione 1 può influire sull'annullamento del set 2 da parte del server dopo l'attesa dei blocchi.
- CommitTimeout: Analysis Server di elaborazione devono acquisire un blocco di scrittura prima di poter eseguire il commit di una transazione. Per acquisire un blocco di scrittura, nessun altro blocco di lettura può essere acquisito da un altro processo o query. Analysis Services deve attendere il rilascio di tutti i blocchi di lettura. La transazione attende un po' di tempo per acquisire un blocco di scrittura, come specificato dalla proprietà CommitTimeout, prima di eseguire il rollback.
A volte l'annullamento non si verifica immediatamente, quindi anche con ForceCommitTimeout e CommitTimeout, può esserci un periodo in cui il lavoro è bloccato.
Un'altra variazione: più richieste di elaborazione possono bloccarsi a vicenda
Se si eseguono due o più batch di elaborazione contemporaneamente in transazioni diverse, potrebbero verificarsi una catena di blocco e un deadlock simili. L'esempio seguente è semplificato. Si supponga che due transazioni di elaborazione siano pronte quasi nello stesso momento, ma che siano bloccate in attesa della query MDX lunga di un utente.

La granularità di blocco alla fine dell'elaborazione è grossolano a livello di database e a livello di file master.vmp, quindi è difficile ottenere l'elaborazione parallela per eseguire correttamente l'elaborazione.
L'ora di inizio dell'elaborazione non è importante quanto l'ora di fine, pertanto è necessario evitare sovrapposizioni alla fine dei processi di elaborazione. Se le due transazioni di elaborazione sono pronte per il commit nello stesso momento, potrebbero contenere alcuni blocchi e richiedere altri blocchi che causano un deadlock.
L'aggiunta di una query MDX a esecuzione lunga nella combinazione rende più probabile che si verifichi una catena di deadlock perché i blocchi intermedi possono causare un cerchio.
È possibile che venga visualizzato questo errore come evento di notifica nella traccia del profiler:
Transaction errors: Aborting transaction on session <victimsessionid>
Il processo di elaborazione della vittima verrà probabilmente annullato con questo errore:
Transaction errors: While attempting to acquire one or more locks, the transaction was canceled.
Soluzioni proposte per bloccare il conflitto tra processi di elaborazione
Pianificare l'elaborazione in modo sfalsato. Tenere presente che l'ora di fine è più importante dell'ora di inizio perché la fase di commit è l'ora in cui sono necessari i blocchi di commit a granularità elevata.
Combinare l'elaborazione in un singolo tag XMLA transazione/batch. Se si elaborano oggetti nell'ambito di una singola transazione, è possibile che non si scontrano e si uccidono a vicenda. È possibile elaborare oggetti paralleli in una singola transazione anziché in una sequenza di commit di piccole dimensioni. È possibile avere un commit più grande e granulare per ridurre la finestra in cui si verificano i blocchi, ma si sta aumentando la superficie di attacco del numero di blocchi con granularità di livello inferiore. Ciò potrebbe aumentare il conflitto con le query utente. Ad esempio, è possibile avere più comandi di elaborazione in un singolo batch.
<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>Elaborare in un server e sincronizzare con un altro server per assicurarsi che questi processi non interferiscano tra loro.
Come vengono visualizzati questi blocchi e catene di blocco?
A partire SQL Server 2008 R2 Service Pack 1, sono disponibili alcuni eventi di traccia del profiler utili per visualizzare questi blocchi. I tag XML all'interno del testo degli eventi di traccia indicano chi è in attesa e quali blocchi vengono mantenuti. Raccogliere una traccia del profiler con gli eventi ordinari, ma aggiungere questi eventi per vedere chi blocca chi e per quanto tempo e per quali oggetti i blocchi sono in conflitto.
L'evento Lock Acquired indica quando la transazione ha ottenuto un batch di blocchi per l'elaborazione della transazione. L'evento Lock Released indica quando la transazione ha rilasciato un batch di blocchi richiesto dalla transazione. Questo evento indica anche la durata dei blocchi. L'evento Lock Waiting indica quando una transazione tenta e attende in una coda di ottenere un blocco in un batch. Queste informazioni si trova nella colonna TextData di tali eventi. Queste informazioni includono i dati aggiuntivi seguenti:
- ID transazione
- Nodo XML LockList
- Nodo XML WaitList
- Nodo XML HoldList
L'evento Lock Acquired e l'evento Lock Released contengono le informazioni LockList. L'evento Lock Waiting contiene le informazioni LockList, WaitList e HoldList.
LockList
Il nodo LockList contiene le informazioni seguenti:
- Tipo di blocco
- Stato di blocco
- Percorso dell'oggetto richiesto
- ID dell'oggetto.
Nota
Il percorso dell'oggetto viene segnalato senza uno spazio dei nomi. L'evento Lock Released contiene anche la proprietà Duration. La proprietà Duration indica la durata in millisecondi del blocco.
Di seguito è riportato un esempio 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>
In questo esempio la transazione richiede tre blocchi, ne ottiene uno e attende il secondo blocco.
Elenco di attesa
Il nodo WaitList elenca le transazioni in attesa che precedeno la transazione corrente. Di seguito è riportato un esempio 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
Il nodo HoldList elenca le transazioni che contengono un blocco che la transazione corrente tenta di ottenere. Di seguito è riportato un esempio 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>
In SQL Server 2008 Analysis Services o versioni successive, è possibile eseguire una query MDX nelle viste a gestione dinamica per visualizzare le varie connessioni, le relative transazioni e chi ha concesso blocchi e chi è in attesa di blocchi (blocco).
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