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 comuns de desempenho

Abaixo 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), bem como migração de plataforma externa (como Oracle, DB2, MySQL e Sybase) para SQL Server.

Regressões de consulta devido à alteração na versão CE

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 mais recente 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.

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 tópico, consulte 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.

Observação

Para migrações do SQL Server para SQL Server, se esse problema existia no SQL Server de origem, migrar para uma versão mais recente do SQL Server no estado em que se encontra não abordará este cenário.

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 pode não ter usado 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 este tópico, consulte Detecção de 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.

Dica

Aproveite o recurso de Análise de Plano do Management Studio para identificar rapidamente se isso é um problema. Mais informações estão disponíveis aqui.

Í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. Aproveite o plano de execução gráfica para todas as referências de índice ausentes.
2. Sugestões de indexação geradas pelo Orientador de Otimização do Mecanismo de Banco de Dados.
3. Aproveite o DMV de índices ausentes ou por meio do Painel de Desempenho do SQL Server.
4. Utilize scripts pré-existentes 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 no código em procedimentos existentes e funções no banco de dados.

Dica

Exemplos de tais scripts pré-existentes incluem Criação de Índice e Informações de Índice.

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.

Observação

Para migrações do SQL Server para SQL Server, se esse problema existia no SQL Server de origem, migrar para uma versão mais recente do SQL Server no estado em que se encontra não abordará este cenário.

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 VARCHAR para NVARCHAR ou INT para 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 o tipo de dados de destino pretendido.

• Isso pode envolver a comparação de qualquer constructo de código definido pelo usuário que é 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 os tipos de dados usados nas 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;

Observação

Todas as opções acima podem ser feitas programaticamente.

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

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.

Observação

Para migrações do SQL Server para SQL Server, se esse problema existia no SQL Server de origem, migrar para uma versão mais recente do SQL Server no estado em que se encontra não abordará este cenário.

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.

Importante

Como a tabela de saída de um MSTVF (função com valor de tabela de várias instruções) não é criada no tempo de compilação, o Otimizador de Consulta do SQL Server conta com heurística e não estatísticas reais, para determinar as estimativas de linha. Mesmo se os índices forem adicionados às tabelas, isso não vai 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 a TVF de várias instruções tiver somente uma instrução, converta-a em TVF 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.

Leituras Adicionais

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