Otimização do desempenho com a colocação em cache dos resultados

  • Artigo

Quando a colocação em cache do conjunto de resultados está ativada, o conjunto de SQL dedicado coloca automaticamente em cache os resultados da consulta na base de dados do utilizador para utilização repetitiva. Isto permite que as execuções de consultas subsequentes obtenham resultados diretamente da cache persistente para que a recompilação não seja necessária. A colocação em cache do conjunto de resultados melhora o desempenho das consultas e reduz a utilização de recursos de computação. Além disso, as consultas que utilizam o conjunto de resultados em cache não utilizam blocos de simultaneidade e, por conseguinte, não contam com os limites de simultaneidade existentes. Para segurança, os utilizadores só podem aceder aos resultados em cache se tiverem as mesmas permissões de acesso a dados que os utilizadores que criam os resultados em cache. A colocação em cache do conjunto de resultados está desativada por predefinição nos níveis de base de dados e sessão.

Nota

A colocação em cache do conjunto de resultados não deve ser utilizada em conjunto com DECRYPTBYKEY. Se esta função criptográfica tiver de ser utilizada, certifique-se de que tem a colocação em cache de conjunto de resultados desativada (ao nível da sessão ou ao nível da base de dados) no momento da execução.

Principais comandos

Ativar/DEsativar a colocação em cache do conjunto de resultados de uma base de dados de utilizador

Ativar/DEsativar a colocação em cache do conjunto de resultados de uma sessão

Verificar o tamanho do conjunto de resultados em cache

Limpar a cache

O que não está em cache

Assim que a colocação em cache do conjunto de resultados estiver ativada para uma base de dados, os resultados são colocados em cache para todas as consultas até a cache estar cheia, exceto para estas consultas:

  • Consultas com funções incorporadas ou expressões de runtime que não são deterministas, mesmo quando não existem alterações nos dados ou consulta das tabelas de base. Por exemplo, DateTime.Now(), GetDate().
  • Consultas com funções definidas pelo utilizador
  • Consultas com tabelas com segurança ao nível da linha
  • Consultas que devolvem dados com tamanho de linha superior a 64 KB
  • Consultas que devolvem dados grandes em tamanho (>10 GB)

Nota

  • Algumas funções não deterministas e expressões de runtime podem ser deterministas para consultas repetitivas em relação aos mesmos dados. Por exemplo, ROW_NUMBER().
  • Utilize ORDER BY na consulta se a ordem/sequência de linhas no conjunto de resultados da consulta for importante para a lógica da aplicação.
  • Se os dados nas colunas ORDER BY não forem exclusivos, não existe uma ordem de linhas garantida para linhas com os mesmos valores nas colunas ORDER BY, independentemente de a colocação em cache do conjunto de resultados estar ativada ou desativada.

Importante

As operações para criar a cache do conjunto de resultados e obter dados da cache ocorrem no nó de controlo de uma instância de conjunto de SQL dedicada. Quando a colocação em cache do conjunto de resultados está ativada, a execução de consultas que devolvem grandes conjuntos de resultados (por exemplo, >1 GB) pode causar uma limitação elevada no nó de controlo e abrandar a resposta geral da consulta na instância. Essas consultas são frequentemente utilizadas durante operações de exploração de dados ou ETL. Para evitar realçar o nó de controlo e causar problemas de desempenho, os utilizadores devem desativar a colocação em cache do conjunto de resultados na base de dados antes de executarem esses tipos de consultas.

Execute esta consulta durante o tempo decorrido pelas operações de colocação em cache do conjunto de resultados para uma consulta:

SELECT step_index, operation_type, location_type, status, total_elapsed_time, command
FROM sys.dm_pdw_request_steps
WHERE request_id  = <'request_id'>;

Eis um resultado de exemplo para uma consulta executada com a colocação em cache do conjunto de resultados desativada.

Captura de ecrã a mostrar os resultados da consulta, incluindo o tipo de localização e o comando.

Eis um resultado de exemplo para uma consulta executada com a colocação em cache do conjunto de resultados ativada.

Captura de ecrã a mostrar os resultados da consulta com o comando selecionado * de [D W ResultCache D b] dot d b o destacado.

Quando são utilizados resultados em cache

O conjunto de resultados em cache é reutilizado para uma consulta se todos os seguintes requisitos forem cumpridos:

  • O utilizador que está a executar a consulta tem acesso a todas as tabelas referenciadas na consulta.
  • Existe uma correspondência exata entre a nova consulta e a consulta anterior que gerou a cache do conjunto de resultados.
  • Não existem alterações de dados ou esquemas nas tabelas a partir das quais o conjunto de resultados em cache foi gerado.

Execute este comando para verificar se uma consulta foi executada com um resultado de cache atingido ou falhado. A coluna result_cache_hit devolve 1 para acerto na cache, 0 para falha na cache e valores negativos por motivos pelos quais a colocação em cache do conjunto de resultados não foi utilizada. Verifique sys.dm_pdw_exec_requests para obter detalhes.

SELECT request_id, command, result_cache_hit FROM sys.dm_pdw_exec_requests
WHERE request_id = <'Your_Query_Request_ID'>

Gerir resultados em cache

O tamanho máximo da cache do conjunto de resultados é de 1 TB por base de dados. Os resultados em cache são automaticamente invalidados quando os dados de consulta subjacentes mudam.

A expulsão da cache é gerida pelo conjunto de SQL dedicado automaticamente ao seguir esta agenda:

  • A cada 48 horas, se o conjunto de resultados não tiver sido utilizado ou tiver sido invalidado.
  • Quando a cache do conjunto de resultados se aproxima do tamanho máximo.

Os utilizadores podem esvaziar manualmente toda a cache do conjunto de resultados com uma destas opções:

  • Desativar a funcionalidade de cache do conjunto de resultados da base de dados
  • Executar DBCC DROPRESULTSETCACHE enquanto estiver ligado à base de dados

Colocar uma base de dados em pausa não esvaziará o conjunto de resultados em cache.

Passos seguintes

Para obter mais sugestões de desenvolvimento, veja Descrição geral do desenvolvimento.