Guia de validação e otimização pós-migração

Aplica-se a:SQL Server

A etapa pós-migração do SQL Server é crucial para conciliar qualquer precisão e integridade de dados e descobrir problemas de desempenho com a carga de trabalho.

Cenários de desempenho comuns

A seguir estão alguns dos cenários de desempenho comuns encontrados após a migração para a Plataforma SQL Server e como resolvê-los. Isso inclui cenários específicos para migração de SQL Server para SQL Server (de versões mais antigas para versões mais recentes) e de plataformas externas (como Oracle, DB2, MySQL e Sybase) para o SQL Server.

Regressões de consulta devido a mudança na versão do estimador de cardinalidade (CE)

Aplica-se a: migração do SQL Server para o SQL Server.

Ao migrar de uma versão mais antiga do SQL Server para o SQL Server 2014 (12.x) ou versões posteriores e atualizar o nível de compatibilidade do banco de dados para o mais recente disponível, uma carga de trabalho pode estar exposta ao risco de regressão de desempenho.

Isso ocorre porque, a partir do SQL Server 2014 (12.x), todas as alterações do Otimizador de Consulta estão vinculadas ao nível de compatibilidade de banco de dados mais recente, portanto, os planos não são alterados no momento da atualização, mas sim quando um usuário altera a opção de banco de dados COMPATIBILITY_LEVEL para a mais recente. Esse recurso, em combinação com o Repositório de Consultas, oferece um ótimo nível de controle sobre o desempenho da consulta no processo de atualização.

Para obter mais informações sobre as alterações do Otimizador de Consulta introduzidas no SQL Server 2014 (12.x), consulte Otimizando seus planos de consulta com o SQL Server 2014 Cardinality Estimator.

Para obter mais informações sobre a Estimativa de Cardinalidade (CE), consulte Estimativa de Cardinalidade (SQL Server).

Passos para resolver

Altere o nível de compatibilidade do banco de dados para a versão de origem e siga o fluxo de trabalho de atualização recomendado, conforme mostrado na imagem a seguir:

Para obter mais informações sobre este artigo, consulte Manter a estabilidade de desempenho durante a atualização para o SQL Servermais recente .

Sensibilidade à deteção de parâmetros

Aplica-se a: migração de plataformas estrangeiras (como Oracle, DB2, MySQL e Sybase) para SQL Server.

O SQL Server compila planos de consulta em procedimentos armazenados usando a deteção dos parâmetros de entrada na primeira compilação, gerando um plano parametrizado e reutilizável, otimizado para essa distribuição de dados de entrada. Mesmo que não sejam procedimentos armazenados, a maioria das instruções que geram planos triviais são parametrizadas. Depois que um plano é armazenado em cache pela primeira vez, qualquer execução futura é mapeada para um plano previamente armazenado em cache.

Um problema potencial surge quando essa primeira compilação não usa os conjuntos mais comuns de parâmetros para a carga de trabalho usual. Para parâmetros diferentes, o mesmo plano de execução torna-se ineficiente. Para obter mais informações sobre este artigo, consulte Sensibilidade do parâmetro.

Passos para resolver

1. Utiliza a dica RECOMPILE. Um plano é calculado todas as vezes, adaptado a cada valor de parâmetro.
2. Reescreva o procedimento armazenado para usar a opção (OPTIMIZE FOR(<input parameter> = <value>)). Decida qual valor usar que se adapte à maior parte da carga de trabalho relevante, criando e mantendo um plano que se torne eficiente para o valor parametrizado.
3. Reescreva o procedimento armazenado usando a variável local dentro do procedimento. Agora, o otimizador usa o vetor de densidade para estimativas, resultando no mesmo plano, independentemente do valor do parâmetro.
4. Reescreva o procedimento armazenado para usar a opção (OPTIMIZE FOR UNKNOWN). O mesmo efeito que usar a técnica da variável local.
5. Reescreva a consulta para usar a dica DISABLE_PARAMETER_SNIFFING. O mesmo efeito que usar a técnica de variável local desativando totalmente a deteção de parâmetros, a menos que OPTION(RECOMPILE), WITH RECOMPILE ou OPTIMIZE FOR <value> seja usado.

Índices em falta

Aplica-se a: plataforma externa (como Oracle, DB2, MySQL e Sybase) e migração de SQL Server para SQL Server.

Índices incorretos ou ausentes causam E/S extras que levam ao desperdício de memória e CPU extras. Isso pode ocorrer porque o perfil da carga de trabalho foi alterado, como o uso de predicados diferentes, invalidando o design de índice existente. As evidências de uma estratégia de indexação deficiente ou de alterações no perfil da carga de trabalho incluem:

• Procure índices duplicados, redundantes, raramente usados e completamente não utilizados.
• Cuidado especial com índices não utilizados com atualizações.

Passos para resolver

1. Use o plano de execução gráfica para quaisquer referências de índice ausentes.
2. Sugestões de indexação geradas pelo Orientador de Otimização do Mecanismo de Banco de Dados .
3. Use o sys.dm_db_missing_index_details.
4. Use scripts pré-existentes que utilizem as DMVs disponíveis para fornecer informações sobre índices ausentes, duplicados, redundantes, raramente usados e completamente não utilizados, além de verificar se alguma referência de índice é sugerida ou incorporada em procedimentos e funções existentes no seu banco de dados.

Incapacidade de usar predicados para filtrar dados

Aplica-se a: plataforma estrangeira (como Oracle, DB2, MySQL e Sybase) e migração do SQL Server para SQL Server.

O SQL Server Query Optimizer só pode contabilizar informações conhecidas em tempo de compilação. Se uma carga de trabalho depende de predicados que só podem ser conhecidos no momento da execução, então o potencial para uma má escolha de plano aumenta. Para um plano de melhor qualidade, os predicados devem ser SARGable, ou Search Argumentcapaz.

Alguns exemplos de predicados não SARGáveis:

• Conversões de dados implícitas, como varchar para nvarcharou int para varchar. Verifique os avisos de tempo de execução CONVERT_IMPLICIT nos Planos de Execução Reais. A conversão de um tipo para outro também pode causar uma perda de precisão.
• Expressões complexas indeterminadas, como WHERE UnitPrice + 1 < 3.975, mas não WHERE UnitPrice < 320 * 200 * 32.
• Expressões que usam funções, como WHERE ABS(ProductID) = 771 ou WHERE UPPER(LastName) = 'Smith'
• Cadeias de caracteres com um caractere curinga no início, como WHERE LastName LIKE '%Smith', mas não WHERE LastName LIKE 'Smith%'.

Passos para resolver

1. Sempre declare variáveis/parâmetros como o destino pretendido Tipos de dados.

   Isso pode envolver a comparação de qualquer construção de código definida pelo usuário armazenada no banco de dados (como procedimentos armazenados, funções definidas pelo usuário ou exibições) com tabelas do sistema que contêm informações sobre tipos de dados usados em tabelas subjacentes (como sys.columns (Transact-SQL)).

2. Se não for possível percorrer todo o código até o ponto anterior, então, para a mesma finalidade, altere o tipo de dados na tabela para corresponder a qualquer declaração de variável/parâmetro.

3. Raciocine a utilidade dos seguintes construtos:

   • Funções utilizadas como predicados;
   • Pesquisas curinga;
   • Expressões complexas baseadas em dados colunares - avalie a necessidade de, em vez disso, criar colunas computadas persistentes, que podem ser indexadas;

Uso de funções com valor de tabela (instrução múltipla vs em linha)

Aplica-se a: plataforma estrangeira (como Oracle, DB2, MySQL e Sybase) e migração do SQL Server para SQL Server.

As Funções com Valor de Tabela retornam um tipo de dados de tabela que pode ser uma alternativa às vistas. Embora os modos de exibição sejam limitados a uma única instrução SELECT, as funções definidas pelo usuário podem conter instruções adicionais que permitem mais lógica do que é possível em modos de exibição.

Passos para resolver

1. Se o MSTVF for apenas uma instrução, converta-o numa função com valor de tabela em linha.

   CREATE FUNCTION dbo.tfnGetRecentAddress(@ID int)
   RETURNS @tblAddress TABLE
   ([Address] VARCHAR(60) NOT NULL)
   AS
   BEGIN
     INSERT INTO @tblAddress ([Address])
     SELECT TOP 1 [AddressLine1]
     FROM [Person].[Address]
     WHERE  AddressID = @ID
     ORDER BY [ModifiedDate] DESC
   RETURN
   END
   

   O exemplo de formato em linha é exibido em seguida.

   CREATE FUNCTION dbo.tfnGetRecentAddress_inline(@ID int)
   RETURNS TABLE
   AS
   RETURN (
     SELECT TOP 1 [AddressLine1] AS [Address]
     FROM [Person].[Address]
     WHERE  AddressID = @ID
     ORDER BY [ModifiedDate] DESC
   )
   

2. Se for mais complexo, considere o uso de resultados intermediários armazenados em tabelas Memory-Optimized ou temporárias.

Conteúdo relacionado

• Práticas Recomendadas com o Query Store
• Memory-Optimized Tabelas
• User-Defined Funções
• Variáveis da Tabela e Estimativas de Linha - Parte 1
• Variáveis da Tabela e Estimativas de Linha - Parte 2
• Cache e reutilização do plano de execução