Risoluzione dei problemi e delle prestazioni con SqlPackage

In alcuni scenari le operazioni SqlPackage richiedono più tempo del previsto o non vengono completate. Questo articolo descrive alcune tattiche consigliate di frequente per risolvere i problemi o migliorare le prestazioni di queste operazioni. Anche se è consigliabile leggere la pagina della documentazione specifica per ogni azione per comprendere i parametri e le proprietà disponibili, questo articolo funge da punto di partenza per imparare a conoscere in maggiore dettaglio le operazioni di SqlPackage.

Strategia complessiva

Come linea guida generale, è possibile ottenere prestazioni migliori tramite la versione .NET di SqlPackage anziché la versione di .NET Framework installata tramite il DacFramework.msi.

Se non riesci a installare lo strumento dotnet SqlPackage, che ti permette di eseguire comandi SqlPackage dal prompt dei comandi in qualsiasi directory:

  1. Scaricare il file ZIP per SqlPackage in .NET 8 per il sistema operativo in uso (Windows, macOS o Linux).
  2. Sblocca l'archivio come indicato nella pagina di download.
  3. Aprire un prompt dei comandi e passare (cd) alla directory SqlPackage.

Utilizzare l'ultima versione disponibile di SqlPackage, poiché vengono rilasciati regolarmente miglioramenti delle prestazioni e correzioni di bug.

Sostituire SqlPackage con il servizio di importazione/esportazione

Se si è tentato di usare il servizio importazione/esportazione per importare o esportare il database, potrebbe essere utile usare SqlPackage.exe per eseguire la stessa operazione con un maggiore controllo su parametri e proprietà facoltativi. Il post di blog Ottimizzazione delle importazioni BACPAC - SqlPackage Done Right! illustra la procedura per usare SqlPackage anziché il servizio importazione/esportazione per un'importazione .bacpac .

Per l'importazione, un comando di esempio è:

./SqlPackage /Action:Import /sf:<source-bacpac-file-path> /tsn:<full-target-server-name> /tdn:<a new or empty database> /tu:<target-server-username> /tp:<target-server-password> /df:<log-file>

Per l'esportazione, un comando di esempio è:

./SqlPackage /Action:Export /tf:<target-bacpac-file-path> /ssn:<full-source-server-name> /sdn:<source-database-name> /su:<source-server-username> /sp:<source-server-password> /df:<log-file>

Usa l'autenticazione multifattore come alternativa a nome utente e password, per autenticarti con l'autenticazione Microsoft Entra. Sostituire i parametri nome utente e password per /ua:true e /tid:"contoso.onmicrosoft.com".

Diagnostics

La diagnosi di errori e comportamenti imprevisti in SqlPackage è supportata dai log di diagnostica e da un pacchetto di diagnostica. I log di diagnostica sono essenziali per la risoluzione dei problemi e vengono acquisiti in un file con il parametro /DiagnosticsFile:<filename>.

Controlla il livello di dettaglio nell'output diagnostico tramite il parametro /DiagnosticsLevel. Usa i Information valori e Verbose per ottenere più dettagli.

Registra i dati di traccia correlati alle prestazioni impostando la DACFX_PERF_TRACE=true variabile di ambiente prima di eseguire SqlPackage. I dati di traccia aumentano il volume dell’output del log, quindi includeteli solo durante la diagnosi di problemi di prestazioni. Per impostare questa variabile di ambiente in PowerShell, usare il comando seguente:

Set-Item -Path Env:DACFX_PERF_TRACE -Value true

In SqlPackage 162.5 e successivi, puoi generare un pacchetto diagnostico per aiutare nella risoluzione dei problemi. Il pacchetto di diagnostica contiene la versione di SqlPackage, il comando eseguito, le informazioni sui modelli di database di origine e di destinazione e l'output del comando. Per generare un pacchetto di diagnostica, usare il parametro /DiagnosticsPackageFile:<filename>.

Problemi comuni

Errori di timeout

Per problemi di timeout, usa le seguenti proprietà per regolare la connessione tra SqlPackage e l'istanza SQL:

  • /p:CommandTimeout=: Specifica il timeout del comando in secondi durante l'esecuzione di una query. Valore predefinito: 60
  • /p:DatabaseLockTimeout=: specifica il timeout di blocco del database in secondi. Usare -1 per l'attesa indefinita. Valore predefinito: 60
  • /p:LongRunningCommandTimeout=: specifica il timeout per i comandi a esecuzione prolungata in secondi. Il valore predefinito, 0, attende indefinitamente.

Consumo delle risorse del client

Per i comandi esportazione ed estrazione, SqlPackage passa i dati delle tabelle a una directory temporanea per il buffer prima di scriverli nel file BACPAC o DACPAC. Questo requisito di memoria può essere grande e dipende dalla dimensione completa dei dati da esportare. Specificare una directory temporanea alternativa con la proprietà /p:TempDirectoryForTableData=<path>.

SqlPackage compila il modello di schema in memoria. Per grandi schemi di database, il fabbisogno di memoria sulla macchina client che esegue SqlPackage può essere significativo.

Consumo ridotto delle risorse nel server

Per impostazione predefinita, SqlPackage imposta il parallelismo massimo del server su 8. Se noti un basso consumo di risorse server, aumentare il valore del MaxParallelism parametro può migliorare le prestazioni.

Token di accesso

L'uso del parametro /AccessToken: o /at: consente l'autenticazione basata su token per SqlPackage, ma passare il token al comando può risultare complicato. Se stai eseguendo il parsing di un oggetto token di accesso in PowerShell, passa esplicitamente il valore della stringa oppure racchiudi il riferimento alla proprietà del token in $(). Per esempio:

$Account = Connect-AzAccount -ServicePrincipal -Tenant $Tenant -Credential $Credential
$AccessToken_Object = (Get-AzAccessToken -Account $Account -Resource "https://database.windows.net/")
$AccessToken = $AccessToken_Object.Token

SqlPackage /at:$AccessToken
# OR
SqlPackage /at:$($AccessToken_Object.Token)

Connection

Se SqlPackage non riesce a connettersi, il server potrebbe non avere la crittografia abilitata o il certificato configurato potrebbe non essere emesso da un'autorità di certificazione attendibile, ad esempio un certificato autofirmato. È possibile modificare il comando SqlPackage per connettersi senza crittografia o per considerare attendibile il certificato del server. La procedura consigliata consiste nel garantire che sia possibile stabilire una connessione crittografata attendibile al server.

  • Connettersi senza crittografia: /SourceEncryptConnection:False o /TargetEncryptConnection:False
  • Certificato di attendibilità del server: /SourceTrustServerCertificate:True oppure /TargetTrustServerCertificate:True

Potresti vedere uno o più dei seguenti messaggi di avviso quando ti connetti a un'istanza SQL, che indicano che i parametri della riga di comando potrebbero richiedere modifiche per connettersi al server:

The settings for connection encryption or server certificate trust may lead to connection failure if the server is not properly configured.
The connection string provided contains encryption settings which may lead to connection failure if the server is not properly configured.

Altre informazioni sulle modifiche alla sicurezza delle connessioni in SqlPackage sono disponibili in Miglioramenti della sicurezza della connessione in SqlPackage 161.

Errore dell'azione di importazione 2714 per il vincolo

Quando esegui un'azione di importazione, potresti ricevere l'errore 2714 se un oggetto esiste già:

*** Error importing database:Could not import package.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 2714, Level 16, State 5, Line 1 There is already an object named 'DF_Department_ModifiedDate_0FF0B724' in the database.
Error SQL72045: Script execution error. The executed script:
ALTER TABLE [HumanResources].[Department]
    ADD CONSTRAINT [DF_Department_ModifiedDate_] DEFAULT ('') FOR [ModifiedDate];

Ecco le cause e le soluzioni per risolvere questo errore:

  1. Verificare che la destinazione in cui si sta eseguendo l'importazione sia un database vuoto.
  2. Se il tuo database ha vincoli che utilizzano l'attributo DEFAULT (dove SQL Server assegna un nome casuale al vincolo) e un vincolo esplicitamente nominato, un vincolo con lo stesso nome potrebbe essere creato due volte. Usa tutti i vincoli esplicitamente nominati (non usare DEFAULT), oppure usa tutti i nomi definiti dal sistema (usa DEFAULT).
  3. Modifica manualmente il model.xml file e rinomina il vincolo con il nome che causa l'errore in un nome univoco. Questa opzione deve essere utilizzata solo se richiesto dal supporto tecnico Microsoft e rappresenta un rischio di danneggiamento .bacpac.

Eccezione di overflow dello stack

Script T-SQL di grandi dimensioni con molte istruzioni annidate possono causare eccezioni di stack overflow intermittenti o persistenti. Quando si verifica questa condizione, il messaggio di errore include il testo Stack overflow e una traccia di stack:

Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.Visit(Microsoft.SqlServer.TransactSql.ScriptDom.BinaryQueryExpression)
Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor.ExplicitVisit(Microsoft.SqlServer.TransactSql.ScriptDom.BinaryQueryExpression)
Microsoft.SqlServer.TransactSql.ScriptDom.BinaryQueryExpression.Accept(Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor)
Microsoft.SqlServer.TransactSql.ScriptDom.BinaryQueryExpression.AcceptChildren(Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor)
Microsoft.SqlServer.TransactSql.ScriptDom.BinaryQueryExpression.Accept(Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor)
Microsoft.SqlServer.TransactSql.ScriptDom.BinaryQueryExpression.AcceptChildren(Microsoft.SqlServer.TransactSql.ScriptDom.TSqlFragmentVisitor)

Un parametro per SqlPackage è disponibile in tutti i comandi, /ThreadMaxStackSize:, che specifica le dimensioni massime dello stack per il thread che esegue il processo SqlPackage. Il valore predefinito è determinato dalla versione .NET che esegue SqlPackage. Impostare un valore elevato può influire sulle prestazioni complessive di SqlPackage. Tuttavia, aumentare questo valore potrebbe risolvere l'eccezione di sovraplessità dello stack causata dalle istruzioni annidate. Rifattorizza il codice T-SQL per evitare eccezioni di overflow nello stack ogni volta che è possibile. Se non riesci a rifattorizzare, usa il /ThreadMaxStackSize: parametro come soluzione alternativa.

Quando usi il /ThreadMaxStackSize: parametro, regola le operazioni ripetute al valore più basso che risolva l'eccezione di overflow dello stack se noti un impatto sulle prestazioni. Il valore del parametro è in megabyte (MB). Ad esempio, puoi testare valori come 10 e 100.

Suggerimenti per l'azione di importazione

Per importazioni che contengono tabelle grandi o tabelle con molti indici, utilizzare /p:RebuildIndexesOfflineForDataPhase=True o /p:DisableIndexesForDataPhase=False può migliorare le prestazioni. Queste proprietà modificano rispettivamente l'operazione di ricompilazione dell'indice in modo che venga eseguita offline o non venga eseguita affatto. Puoi usare queste proprietà e altre per regolare l'operazione di importazione di SqlPackage .

Gli indici vengono disabilitati dopo un'importazione

Per caricare i dati in modo efficiente, un'importazione disabilita gli indici non clusterizzati prima della fase dati e li ricostruisce successivamente (comportamento predefinito /p:DisableIndexesForDataPhase=True ). Se l'importazione viene interrotta o fallisce dopo il caricamento dei dati ma prima che la ricostruzione termini, uno o più indici non clusterizzati possono rimanere disabilitati. Un indice disabilitato rimane nei metadati, ma l'ottimizzatore delle query lo ignora, il che può causare query lente dopo un'importazione che altrimenti sembra avere successo.

Per trovare indici disabilitati, controlla la is_disabled colonna nella vista catalogo sys.indexes :

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS table_name,
       name AS index_name
FROM sys.indexes
WHERE is_disabled = 1;

Per riattivare un indice disabilitato, ricostruiscilo con ALTER INDEX. Da usare ALTER INDEX ALL ... REBUILD per abilitare tutti gli indici disabilitati su una tabella:

ALTER INDEX ALL ON <schema>.<table> REBUILD;

Per ulteriori informazioni, vedi Abilita indici e vincoli.

Suggerimenti per l'azione di esportazione

Perché un'esportazione sia transazionalmente coerente, assicurati o che non ci sia attività di scrittura durante l'esportazione, oppure che tu stia esportando da una copia transazionalmente coerente del tuo database. Se ricevi errori sui vincoli di chiave estera durante un'importazione, l'esportazione potrebbe non essere transazionalmente coerente a causa di record inseriti o aggiornati durante il processo di esportazione.

Prestazioni durante l'esportazione

Una causa comune di degrado delle prestazioni durante l'esportazione sono i riferimenti agli oggetti non risolti. Questo problema porta SqlPackage a tentare di risolvere l'oggetto più volte. Ad esempio, viene definita una vista che fa riferimento a una tabella ma la tabella non esiste più nel database. Se nel log di esportazione compaiono riferimenti non risolti, valutare la possibilità di correggere lo schema del database per migliorare le prestazioni di esportazione.

Durante un processo di esportazione i dati della tabella vengono compressi nel file bacpac. Impostare /p:CompressionOption a Fast, SuperFast, o NotCompressed potrebbe migliorare la velocità del processo di esportazione comprimendo meno il file bacpac di output.

Per ottenere lo schema e i dati del database ignorando la convalida dello schema, eseguire l'azione Export con la proprietà /p:VerifyExtraction=False. Potrebbe essere generata un'esportazione non valida che non può essere importata.

Spazio su disco durante l'esportazione

Negli scenari in cui lo spazio su disco del sistema operativo è limitato e si esaurisce durante l'esportazione, usa /p:TempDirectoryForTableData per buffer i dati per l'esportazione su un disco alternativo. Lo spazio necessario per questa azione potrebbe essere grande ed è relativo alle dimensioni complete del database. Puoi regolare l'operazione di esportazione di SqlPackage impostando questa e altre proprietà.

Database SQL di Azure

I suggerimenti seguenti sono specifici dell'esecuzione dell'importazione o dell'esportazione nel database SQL di Azure da una macchina virtuale di Azure:

  • Usare un database di livello Business Critical o Premium per ottenere prestazioni ottimali.
  • Usare l'archiviazione SSD nella macchina virtuale.
  • Fare in modo che ci sia spazio sufficiente per decomprimere il bacpac.
  • Eseguire SqlPackage da una macchina virtuale nella stessa area del database.
  • Abilitare la rete accelerata nella macchina virtuale.

Per ulteriori informazioni sull'uso di uno script PowerShell per raccogliere dettagli su un'operazione di importazione, vedi Lezione appresa #211: Monitoraggio del processo di importazione SQLPackage.

Altre risorse

Il blog del supporto del database di Azure contiene molti articoli sulla risoluzione dei problemi e sull'ottimizzazione delle prestazioni per database SQL di Azure, inclusi diversi articoli su SqlPackage.

Alcuni degli articoli più rilevanti includono:

Contenuti correlati

  • Last updated on