Validação após a migração e guia de otimização

Aplica-se:SQL Server

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

Cenários de desempenho comuns

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

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

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

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

Isso ocorre porque, começando com o SQL Server 2014 (12.x), todas as alterações do Otimizador de Consulta são associadas para o nível de compatibilidade do banco de dados mais recente, portanto, os planos não são alterados diretamente no ponto de 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, fornece um excelente 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 estimador de cardinalidade do SQL Server 2014.

Para obter mais informações sobre CE, confira Estimativa de cardinalidade (SQL Server).

Etapas 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 figura a seguir:

Para obter mais informações sobre este artigo, confira Manter a estabilidade do desempenho durante a atualização para a versão mais recente do SQL Server.

Sensibilidade à detecção de parâmetros

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

O SQL Server compila os planos de consulta em procedimentos armazenados usando a detecção de parâmetros de entrada na primeira compilação, gerando um plano parametrizado, reutilizável e otimizado para distribuição de dados de entrada. Mesmo se não forem procedimentos armazenados, a maioria das instruções que gera planos triviais será parametrizada. Depois que o primeiro plano é armazenado em cache, qualquer execução futura é mapeada para um plano previamente armazenado em cache.

Um possível problema surge quando essa primeira compilação não usa os conjuntos de parâmetros mais comuns para a carga de trabalho normal. Para parâmetros diferentes, o mesmo plano de execução se torna ineficaz. Para obter mais informações sobre esse artigo, confira Confidencialidade do parâmetro.

Etapas para resolver

1. Use a dica RECOMPILE. Um plano é calculado toda vez e adaptado para cada valor de parâmetro.

2. Reescreva o procedimento armazenado para usar a opção (OPTIMIZE FOR(<input parameter> = <value>)). Decida qual valor deve ser usado para adaptar-se à carga de trabalho mais relevante, criar e manter um plano que se torna eficiente para o valor parametrizado.

3. Reescreva o procedimento armazenado usando uma 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). Mesmo efeito que usar a técnica de variável local.

5. Reescreva a consulta para usar a dica DISABLE_PARAMETER_SNIFFING. Mesmo efeito de usar a técnica de variável local desabilitando totalmente a detecção de parâmetros, exceto se OPTION(RECOMPILE), WITH RECOMPILE ou OPTIMIZE FOR <value> for usado.

Índices ausentes

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

Índices ausentes ou incorretos causam E/S extra, o que leva a uso de memória extra e desperdício de CPU. Isso pode ser devido a uma alteração do perfil de carga de trabalho usando predicados diferentes, invalidando o design de índice existente. Evidência de uma estratégia de indexação ineficaz ou de alterações no perfil de carga de trabalho incluem:

• Procure índices duplicados, redundantes, raramente usados ou nunca utilizados.
• Tenha cuidado especial com índices não utilizados com atualizações.

Etapas para resolver

1. Usar o plano de execução gráfico para qualquer referência de Índice Ausente.

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 podem usar DMVs existentes para fornecer insights sobre quaisquer índices ausentes, duplicados, redundantes, raramente usados e completamente não utilizados, mas também se qualquer referência de índice for sugerida/codificada em procedimentos e funções existentes em seu banco de dados.

Incapacidade de usar predicados para filtrar dados

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

O Otimizador de Consulta do SQL Server pode considerar apenas informações que são conhecidas no tempo de compilação. Se uma carga de trabalho se baseia em predicados que podem ser conhecidos apenas no tempo de execução, a possibilidade de escolher um plano ineficaz aumenta. Para um plano de melhor qualidade, os predicados devem ser SARGable.

Alguns exemplos de predicados não SARGable :

• Conversões implícitas de dados, como vaarchar em nvarchar ou int em varchar. Procure os avisos de CONVERT_IMPLICIT de runtime nos Planos de execução reais. Converter de um tipo para outro também pode causar 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 à esquerda como WHERE LastName LIKE '%Smith', mas não WHERE LastName LIKE 'Smith%'.

Etapas para resolver

1. Sempre declare variáveis/parâmetros como os tipos de dados de destino pretendidos.

   Isso pode envolver a comparação de qualquer constructo de código definido pelo usuário armazenado 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).

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

3. Pondere a utilidade das construções a seguir:

   • Funções que estão sendo usadas como predicados;
   • Pesquisas com curinga;
   • Expressões complexas baseadas em dados de coluna – avalie a necessidade de criar colunas computadas persistentes que possam ser indexadas;

Uso de funções com valor de tabela (multi-instruções vs. embutidas)

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

Funções com valor de tabela retornam um tipo de dados de tabela que pode ser uma alternativa a exibições. Enquanto as exibições são limitadas a uma única instrução SELECT, funções definidas pelo usuário podem conter instruções adicionais que permitem mais lógica que é possível nas exibições.

Como a tabela de saída de uma MSTVF (função com valor de tabela de várias instruções) não é criada em tempo de compilação, o Otimizador de Consulta do SQL Server depende da heurística, e não das estatísticas reais, para determinar as estimativas de linha.

Mesmo que os índices sejam adicionados às tabelas base, isso não ajudará.

Para MSTVFs, o SQL Server usa uma estimativa fixa de 1 para o número de linhas esperado a ser retornado por um MSTVF (a partir do SQL Server 2014 (12.x), essa estimativa é fixa em 100 linhas).

Etapas para resolver

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

   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 a seguir.

   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 complexa, considere usar os resultados intermediários armazenados em tabelas com otimização de memória ou tabelas temporárias.

Conteúdo relacionado

• Melhores práticas para monitorar cargas de trabalho com o Repositório de Consultas
• Banco de dados de exemplo para OLTP in-memory
• Funções definidas pelo usuário
• Variáveis de tabela e estimativas de linha — Parte 1
• Variáveis de tabela e estimativas de linha — Parte 2
• Reutilização e armazenamento em cache do plano de execução