Solucionar problemas e desempenho com SqlPackage

  • Artigo

Em alguns cenários, as operações do 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 ou melhorar o desempenho dessas operações. Embora seja recomendado ler a página de documentação específica de cada ação para entender os parâmetros e as propriedades disponíveis, este artigo serve como um ponto de partida para investigar as operações do SqlPackage.

Estratégia geral

Como diretriz geral, é possível obter melhor desempenho por meio da versão do .NET de SqlPackage em vez da versão do .NET Framework instalada via DacFramework.msi.

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

  1. Baixe o zip do SqlPackage no .NET 8 para seu sistema operacional (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 do SqlPackage.

É importante usar a versão mais recente disponível do SqlPackage, pois melhorias de desempenho e correções de bugs são liberadas regularmente.

Substituir o 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, é possível usar o SqlPackage para executar a mesma operação com mais controle sobre os parâmetros e as propriedades opcionais.

Para importar, um exemplo de comando é:

./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 exemplo de comando é:

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

Alternativa ao nome de usuário e senha, a autenticação multifator pode ser usada para autenticar por meio da autenticação do Microsoft Entra (antigo Azure Active Directory) com autenticação multifator. Substitua os parâmetros de nome de usuário e senha para /ua:true e /tid:"yourdomain.onmicrosoft.com".

Problemas comuns

Erros do tempo limite

Em caso de problemas relacionados a tempos limite, as seguintes propriedades podem ser usadas para ajustar a conexã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. -1 pode ser usado para aguardar indefinidamente, padrão: 60
  • /p:LongRunningCommandTimeout=: especifica o tempo limite do comando de execução prolongada em segundos. O valor padrão, 0, é usado para aguardar indefinidamente.

Consumo de recursos do cliente

Para os comandos de exportação e extração, os dados da tabela são passados para um diretório temporário para buffer antes de serem gravados no arquivo bacpac/dacpac. Esse requisito de armazenamento pode ser grande e é relativo ao tamanho total dos dados a serem exportados. Especifique um diretório temporário alternativo com a propriedade /p:TempDirectoryForTableData=<path>.

O modelo de esquema é compilado na memória. Portanto, para esquemas de banco de dados grandes, o requisito de memória na máquina cliente que executa o SqlPackage pode ser significativo.

Baixo consumo de recursos do servidor

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

Token de acesso

Usar o parâmetro /AccessToken: ou /at: habilita a autenticação baseada em token para SqlPackage, no entanto, passar o token para o comando pode ser complicado. Se você estiver analisando um objeto de token de acesso no PowerShell, passe explicitamente o valor da sequência ou empacote a referência à 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)

Conexão

Se o SqlPackage apresentar falha ao se conectar, o servidor poderá não ter a criptografia habilitada ou o certificado configurado poderá 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 confiar no certificado do servidor. A melhor prática é garantir que uma conexão criptografada confiável com o servidor possa ser estabelecida.

  • Conecte-se sem criptografia: /SourceEncryptConnection:False ou /TargetEncryptConnection:False
  • Confiar em certificado do servidor: /SourceTrustServerCertificate:True ou /TargetTrustServerCertificate:True

Você poderá ver qualquer uma das seguintes mensagens de aviso ao se conectar a uma instância SQL, indicando que os parâmetros da linha de comando poderão exigir alterações para se conectar 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 Aprimoramentos de segurança de conexão no SqlPackage 161.

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

Ao executar uma ação de importação, você pode receber o erro 2714 se já existir um objeto:

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

Estas sã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 o seu banco de dados tiver restrições que estejam usando o atributo DEFAULT (em que o SQL Server atribui um nome aleatório à restrição), bem como uma restrição nomeada explicitamente, uma restrição com o mesmo nome pode ser criada duas vezes. Você deve usar todas as restrições nomeadas explicitamente (sem usar DEFAULT) ou todos os nomes definidos pelo sistema (usando DEFAULT).
  3. Edite manualmente o modelo.xml e renomeie a restrição com o nome que está ocorrendo o erro para um nome exclusivo. Essa opção deve ser realizada somente se orientada pelo suporte da Microsoft e representar um risco de corrupção do .bacpac.

Exceção de Stack Overflow

Scripts T-SQL grandes com muitas instruções aninhadas costumam ser a causa de exceções intermitentes ou persistentes de Stack Overflow. Quando esse for o caso, a mensagem de erro incluirá o texto Stack overflow e o rastreamento de pilha:

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 para SqlPackage está disponível em todos os comandos, /ThreadMaxStackSize:, especificando o tamanho máximo da pilha para o thread que executa o processo SqlPackage. O valor padrão é determinado pela versão do .NET que executa o SqlPackage. A definição de um valor grande pode afetar o desempenho geral do SqlPackage. Entretanto, aumentar esse valor pode resolver a exceção de stack overflow causada por instruções aninhadas. A refatoração do código T-SQL é recomendada para evitar exceções de stack overflow sempre que possível, mas o /ThreadMaxStackSize: parâmetro pode ser usado como uma solução alternativa.

Ao usar o parâmetro /ThreadMaxStackSize:, é recomendável ajustar operações repetidas para o valor mais baixo que resolve a exceção de estouro de pilha se o impacto no desempenho for observado. O valor do parâmetro está em megabytes (MB), os valores de exemplo para teste como solução alternativa incluem 10 e 100.

Diagnósticos

Os logs são essenciais para a solução de problemas. Capture os logs de diagnóstico em um arquivo com o parâmetro /DiagnosticsFile:<filename>.

Mais dados de rastreamento relacionados ao desempenho podem ser registrados em log com a configuração da variável de ambiente DACFX_PERF_TRACE=true antes de executar o SqlPackage. Para definir essa variável de ambiente no PowerShell, use o seguinte comando:

Set-Item -Path Env:DACFX_PERF_TRACE -Value true

Dicas da ação de importação

Para importações que contêm tabelas grandes ou tabelas com muitos índices, o uso de /p:RebuildIndexesOfflineForDataPhase=True ou /p:DisableIndexesForDataPhase=False pode melhorar o desempenho. Essas propriedades modificam a operação de recompilação de índice para que ela ocorra offline ou não ocorra, respectivamente. Essas e outras propriedades estão disponíveis para ajustar a operação Import do SqlPackage.

Dicas da ação de exportação

Uma causa comum de degradação de desempenho durante a exportação são referências de objeto não resolvidas, o que faz com que o SqlPackage tente resolver o objeto várias vezes. Por exemplo, um modo de exibição é definido de modo que faça referência a uma tabela e a tabela não exista mais no banco de dados. Se aparecerem referências não resolvidas no log de exportação, considere corrigir o esquema do banco de dados para aprimorar o desempenho de exportação.

Em cenários em que o espaço em disco do sistema operacional é limitado e acaba durante a exportação, o uso de /p:TempDirectoryForTableData permite que os dados para exportação sejam armazenados em buffer em um disco alternativo. O espaço necessário para essa ação pode ser grande e é relativo ao tamanho total do banco de dados. Essa e outras propriedades estão disponíveis para ajustar a operação Export do SqlPackage.

Durante um processo de exportação, os dados da tabela são compactados no arquivo bacpac. O uso de /p:CompressionOption definido para Fast, SuperFast ou NotCompressed pode aprimorar a velocidade do processo de exportação ao compactar menos o arquivo bacpac de saída.

Para obter o esquema de banco de dados e os dados ao ignorar a validação de esquema, execute uma Exportação com a propriedade /p:VerifyExtraction=False. Pode ser produzida uma exportação inválida que não pode ser importada.

Banco de Dados SQL do Azure

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

  • Use o banco de dados de nível Comercialmente Crítico ou Premium para obter o melhor desempenho.
  • Use o armazenamento SSD na VM.
  • Verifique se há espaço suficiente para descompactar o bacpac.
  • Execute o SqlPackage em uma VM na mesma região que o banco de dados.
  • Habilite a rede acelerada na VM.

Para obter mais informações sobre a utilização de um script do PowerShell para coletar mais informações sobre uma operação de importação, consulte Lição aprendida nº 211: Monitoramento do processo de importação do pacote SQL.

Mais recursos

O Blog de Suporte do Banco de Dados do Azure contém vários artigos de 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 são:

Conteúdo relacionado