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

Aplica-se a: 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 ou o Dashboard de Desempenho do SQL Server.
4. Use scripts preexistentes que podem usar DMVs existentes para fornecer insights sobre qualquer índice ausente, duplicado, redundante, raramente usado e nunca utilizado, mas também se alguma referência de índice é sugerida/embutida em código em procedimentos existentes e funções no 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 ou Search Argumentable (argumentos pesquisáveis).

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 os tipos de dados usados nas tabelas subjacentes (como sys.columns (Transact-SQL)).

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.

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

• Melhor prática com o Repositório de Consultas
• Tabelas com otimização de memória
• 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