Solucionar problemas e desempenho com SqlPackage

Em alguns cenários, as operações SqlPackage levam mais tempo do que o esperado ou não são concluídas. Este artigo descreve algumas táticas frequentemente sugeridas para solucionar problemas ou melhorar o desempenho dessas operações. Embora a leitura da página de documentação específica para cada ação para entender os parâmetros e propriedades disponíveis seja recomendada, este artigo serve como um ponto de partida na investigação de operações SqlPackage.

Estratégia geral

Como diretriz geral, um melhor desempenho pode ser obtido por meio da versão .NET do SqlPackage em vez da versão do .NET Framework instalada através do DacFramework.msi.

Se não conseguires instalar a ferramenta dotnet SqlPackage, que te permite executar comandos SqlPackage a partir do prompt de comandos em qualquer diretório:

  1. Download o zip do SqlPackage no .NET 8 para o seu sistema operativo (Windows, macOS ou Linux).
  2. Descompacte o arquivo conforme indicado na página de download.
  3. Abra um prompt de comando e altere o diretório (cd) para a pasta SqlPackage.

Use a versão mais recente disponível do SqlPackage, pois melhorias de desempenho e correções de bugs são lançadas regularmente.

Substitua SqlPackage pelo serviço de importação/exportação

Se você tentou usar o Serviço de Importação/Exportação para importar ou exportar seu banco de dados, poderá usar SqlPackage para executar a mesma operação com mais controle sobre parâmetros e propriedades opcionais. O artigo do blogue Otimizar as Importações BACPAC - SqlPackage Feito Corretamente! descreve os passos para utilizar o SqlPackage em vez do Serviço de Importação/Exportação para uma .bacpac importação.

Para Importar, um comando de exemplo é:

./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>

Para Exportar, um comando de exemplo é:

./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>

Utilize autenticação multifator como alternativa ao nome de utilizador e à palavra-passe, para autenticar com a autenticação do Microsoft Entra. Substitua os parâmetros de nome de usuário e senha por /ua:true e /tid:"contoso.onmicrosoft.com".

Diagnostics

O diagnóstico de erros e comportamento inesperado em SqlPackage é suportado por logs de diagnóstico e um pacote de diagnóstico. Os logs de diagnóstico são essenciais para a solução de problemas e são capturados em um arquivo com o parâmetro /DiagnosticsFile:<filename>.

Controla o nível de detalhe na saída de diagnóstico através do /DiagnosticsLevel parâmetro. Use os Information valores e Verbose para obter mais detalhes.

Regista dados de rastreamento relacionados com o desempenho definindo a DACFX_PERF_TRACE=true variável de ambiente antes de executar o SqlPackage. Os dados de rastreamento aumentam a saída logarítmica, por isso só os inclua ao diagnosticar desafios de desempenho. Para definir essa variável de ambiente no PowerShell, use o seguinte comando:

Set-Item -Path Env:DACFX_PERF_TRACE -Value true

No SqlPackage 162.5 e posteriores, pode gerar um pacote de diagnóstico para ajudar na resolução de problemas. O pacote de diagnóstico contém a versão SqlPackage, o comando executado, informações sobre os modelos de banco de dados de origem e destino e a saída do comando. Para gerar um pacote de diagnóstico, use o parâmetro /DiagnosticsPackageFile:<filename>.

Problemas comuns

Erros de tempo limite

Para questões de timeout, use as seguintes propriedades para ajustar a ligação entre o SqlPackage e a instância SQL:

  • /p:CommandTimeout=: Especifica o tempo limite do comando em segundos quando uma consulta é executada. Padrão: 60
  • /p:DatabaseLockTimeout=: Especifica o tempo limite de bloqueio do banco de dados em segundos. Use -1 para esperar indefinidamente. Padrão: 60
  • /p:LongRunningCommandTimeout=: Especifica o tempo limite do comando de longa execução em segundos. O valor padrão, 0, espera indefinidamente.

Consumo de recursos do cliente

Para os comandos de exportação e extração, o SqlPackage passa os dados das tabelas para um diretório temporário para buffer antes de os escrever no ficheiro BACPAC ou DACPAC. Este requisito de armazenamento pode ser elevado e é relativo ao tamanho total dos dados a exportar. Especifique um diretório temporário alternativo com a propriedade /p:TempDirectoryForTableData=<path>.

SqlPackage compila o modelo de esquema na memória. Para esquemas de bases de dados grandes, o requisito de memória na máquina cliente a executar o SqlPackage pode ser significativo.

Baixo consumo de recursos do servidor

Por padrão, SqlPackage define o paralelismo máximo do servidor como 8. Se notar um baixo consumo de recursos do servidor, aumentar o valor do MaxParallelism parâmetro pode melhorar o desempenho.

Token de acesso

Usar o parâmetro /AccessToken: ou /at: permite a autenticação baseada em tokens para o SqlPackage, mas passar o token ao comando pode ser complicado. Se estiver a analisar um objeto de token de acesso no PowerShell, passe explicitamente o valor da string ou enrole a referência na propriedade do token em $(). Por exemplo:

$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 o SqlPackage não estiver conseguindo se conectar, o servidor pode não ter a criptografia habilitada ou o certificado configurado pode não ser emitido por uma autoridade de certificação confiável (como um certificado autoassinado). Você pode alterar o comando SqlPackage para se conectar sem criptografia ou para confiar no certificado do servidor. A melhor prática é garantir que se possa estabelecer uma conexão criptografada confiável com o servidor.

  • Ligar sem encriptação: /SourceEncryptConnection:False ou /TargetEncryptConnection:False
  • Certificado do servidor confiável: /SourceTrustServerCertificate:True ou /TargetTrustServerCertificate:True

Pode ver uma ou mais das seguintes mensagens de aviso ao ligar-se a uma instância SQL, indicando que os parâmetros da linha de comandos podem exigir alterações para se ligar ao servidor:

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.

Mais informações sobre as alterações de segurança de conexão no SqlPackage estão disponíveis em Melhorias de segurança de conexão no SqlPackage 161.

Erro de ação de importação 2714 para restrição

Quando realiza uma ação de importação, pode receber o erro 2714 se um objeto já existir:

*** 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];

Aqui estão as causas e soluções para contornar esse erro:

  1. Verifique se o destino para o qual você está importando é um banco de dados vazio.
  2. Se a sua base de dados tiver restrições que usam o DEFAULT atributo (onde o SQL Server atribui um nome aleatório à restrição) e uma restrição explicitamente nomeada, uma restrição com o mesmo nome pode ser criada duas vezes. Use todas as restrições explicitamente nomeadas (não use DEFAULT), ou use todos os nomes definidos pelo sistema (use DEFAULT).
  3. Edite manualmente o model.xml ficheiro e renomeie a restrição com o nome que causa o erro para um nome único. Esta opção deve ser realizada somente se dirigido pelo suporte da Microsoft e representa um risco de corrupção .bacpac.

Exceção de estouro de pilha

Scripts T-SQL de grande dimensão com muitas instruções aninhadas podem causar exceções de transbordo da pilha intermitentes ou persistentes. Quando esta condição ocorre, a mensagem de erro inclui o texto Stack overflow e um stack trace:

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)

Um parâmetro do SqlPackage está disponível em todos os comandos, /ThreadMaxStackSize:, que especifica o tamanho máximo da pilha para o thread que está a executar o processo SqlPackage. O valor padrão é determinado pela versão .NET que executa SqlPackage. Definir um valor elevado pode afetar o desempenho global do SqlPackage. No entanto, aumentar este valor pode resolver a exceção de transbordo da pilha causada por instruções aninhadas. Refatorem o código T-SQL para evitar exceções de stack overflow sempre que possível. Se não conseguir refatorar, use o /ThreadMaxStackSize: parâmetro como solução alternativa.

Quando usar o parâmetro /ThreadMaxStackSize:, ajuste as operações repetidas para o valor mais baixo que resolva a exceção de transbordo da pilha, caso note um impacto no desempenho. O valor do parâmetro está em megabytes (MB). Por exemplo, pode testar valores como 10 e 100.

Importar dicas de ação

Para importações que contenham tabelas grandes ou tabelas com muitos índices, usar /p:RebuildIndexesOfflineForDataPhase=True ou /p:DisableIndexesForDataPhase=False pode melhorar o desempenho. Essas propriedades modificam a operação de reconstrução de índice para ocorrer offline ou não ocorrer, respectivamente. Pode usar estas propriedades e outras para ajustar a operação de Importação do SqlPaket .

Os índices são desativados após uma importação

Para carregar os dados de forma eficiente, uma importação desativa índices não agrupados antes da fase de dados e reconstrói-os depois (o comportamento padrão /p:DisableIndexesForDataPhase=True ). Se a importação for interrompida ou falhar após o carregamento dos dados mas antes do término da reconstrução, um ou mais índices não agrupados podem permanecer desativados. Um índice desativado permanece nos metadados, mas o otimizador de consultas ignora-o, o que pode causar consultas lentas após uma importação que, de outra forma, parece ter sucesso.

Para localizar índices desativados, consulte a coluna is_disabled na vista de catálogo 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;

Para reativar um índice desativado, reconstrua-o com ALTER INDEX. Use ALTER INDEX ALL ... REBUILD para ativar todos os índices desativados numa tabela:

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

Para mais informações, consulte Ativar índices e restrições.

Dicas para ações de exportação

Para que uma exportação seja transacionalmente consistente, assegura-te de que não há atividade de escrita durante a exportação, ou que estás a exportar a partir de uma cópia transacionalmente consistente da tua base de dados. Se receber erros sobre restrições de chave estrangeira durante uma importação, a exportação pode não ser transacionalmente consistente devido a registos inseridos ou atualizados durante o processo de exportação.

Desempenho durante a exportação

Uma causa comum de degradação de desempenho durante a exportação são as referências de objetos não resolvidas. Este problema faz com que o SqlPackage tente resolver o objeto várias vezes. Por exemplo, é definida uma vista que faz referência a uma tabela, mas a tabela já não existe na base de dados. Se referências não resolvidas aparecerem no log de exportação, considere corrigir o esquema do banco de dados para melhorar o desempenho da exportação.

Durante um processo de exportação, os dados da tabela são compactados no arquivo bacpac. Definir /p:CompressionOption para Fast, SuperFast, ou NotCompressed pode melhorar a velocidade do processo de exportação enquanto comprime menos o ficheiro bacpac de saída.

Para obter a estrutura e os dados do banco de dados, enquanto se ignora a validação da estrutura, execute um Export com a propriedade /p:VerifyExtraction=False. Pode ser produzida uma exportação inválida que não pode ser importada.

Espaço em disco durante a exportação

Em cenários em que o espaço em disco do sistema operativo é limitado e se esgota durante a exportação, use /p:TempDirectoryForTableData para armazenar os dados para exportação num disco alternativo. O espaço necessário para essa ação pode ser grande e é relativo ao tamanho total do banco de dados. Podes ajustar a operação de Exportação do SqlPackage definindo esta e outras propriedades.

Base de Dados SQL do Azure

As dicas a seguir são específicas para executar a importação ou exportação no Banco de Dados SQL do Azure a partir de uma máquina virtual (VM) do Azure:

  • Use o banco de dados de nível Business Critical ou Premium para obter o melhor desempenho.
  • Use o armazenamento SSD na VM.
  • Certifique-se de que há espaço suficiente para abrir o bacpac.
  • Execute SqlPackage a partir de uma VM na mesma região do banco de dados.
  • Habilite a rede acelerada na VM.

Para mais informações sobre o uso de um script PowerShell para recolher detalhes sobre uma operação de importação, consulte Lição Aprendida #211: Monitorização do Processo de Importação do SQLPackage.

Mais recursos

O Blog de Suporte do Banco de Dados do Azure contém muitos artigos sobre solução de problemas e ajuste de desempenho para o Banco de Dados SQL do Azure, incluindo vários artigos sobre SqlPackage.

Alguns dos artigos mais relevantes incluem:

Conteúdo relacionado

  • Last updated on