Ajuste de desempenho com cache de conjunto de resultados

Quando o armazenamento em cache do conjunto de resultados está habilitado, o pool de SQL dedicado armazena em cache automaticamente os resultados da consulta no banco de dados do usuário para uso repetitivo. Isso permite que as execuções de consulta subsequentes obtenham resultados diretamente do cache persistente para que não haja necessidade de recomputação. O cache do conjunto de resultados aprimora o desempenho da consulta e reduz o uso de recursos de computação. Além disso, as consultas que usam resultados em cache definidos não usam nenhum slot de simultaneidade e, portanto, não contam com os limites de simultaneidade existentes. Por segurança, os usuários só poderão acessar os resultados armazenados em cache se tiverem as mesmas permissões de acesso a dados que os usuários que criaram os resultados armazenados em cache. O cache do conjunto de resultados está DESLIGADO por padrão nos níveis de banco de dados e de sessão.

Observação

O cache do conjunto de resultados não deve ser usado em conjunto com DECRYPTBYKEY. Se essa função criptográfica precisar ser usada, verifique se você tem o cache do conjunto de resultados desabilitado (no nível da sessão ou no nível do banco de dados) no momento da execução.

Comandos de chave

Ativar/desativar o armazenamento em cache do conjunto de resultados para um banco de dados de usuário

Ativar/desativar o armazenamento em cache do conjunto de resultados para uma sessão

Verificar o tamanho do conjunto de resultados em cache

Limpar o cache

O que não é armazenado em cache

Quando o armazenamento em cache do conjunto de resultados estiver ATIVADO para um banco de dados, os resultados serão armazenados em cache para todas as consultas até o cache ficar cheio, exceto para estas consultas:

• Consultas com funções internas ou expressões de runtime não determinísticas, mesmo quando não há nenhuma alteração nos dados ou na consulta das tabelas base. Por exemplo, DateTime.Now(), GetDate().
• Consultas que usam funções definidas pelo usuário
• Consultas usando tabelas com segurança em nível de linha
• Consultas que retornam dados com tamanho de linha maior que 64 KB
• Consultas que retornam dados grandes em tamanho (> 10 GB)

Observação

• Algumas funções não determinísticas e expressões de runtime podem ser determinísticas para consultas repetitivas nos mesmos dados. Por exemplo, ROW_NUMBER().
• Use ORDER BY em sua consulta se a ordem/sequência de linhas no conjunto de resultados da consulta for importante para a lógica do aplicativo.
• Se os dados nas colunas ORDER BY não forem exclusivos, não há nenhuma ordem de linha garantida para linhas com os mesmos valores nas colunas ORDER BY, independentemente do cache do conjunto de resultados estar habilitado ou desabilitado.

Importante

As operações para criar o cache do conjunto de resultados e recuperar dados do cache acontecem no nó de controle de uma instância de pool de SQL dedicado. Quando o cache do conjunto de resultados está ATIVADO, a execução de consultas que retornam um conjunto de resultados grande (por exemplo, >1 GB) pode causar alta limitação no nó de controle e atrasar a resposta geral de consultas na instância. Normalmente, essas consultas são usadas normalmente durante a exploração de dados ou operações de ETL. Para evitar sobrecarregar o nó de controle e causar problemas de desempenho, os usuários devem DESATIVAR o cache do conjunto de resultados no banco de dados antes de executar esses tipos de consultas.

Execute esta consulta para o tempo gasto pelas operações de 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'>;

Aqui está um exemplo de saída para uma consulta executada com o armazenamento em cache do conjunto de resultados desabilitado.

Aqui está um exemplo de saída para uma consulta executada com o armazenamento em cache do conjunto de resultados habilitado.

Quando os resultados armazenados em cache são usados

O conjunto de resultados armazenado em cache será reutilizado em uma consulta se todos os requisitos a seguir forem atendidos:

• O usuário que executa a consulta tem acesso a todas as tabelas referenciadas na consulta.
• Há uma correspondência exata entre a nova consulta e a anterior que gerou o armazenamento em cache do conjunto de resultados.
• Não há alterações de dados ou esquemas nas tabelas em que o conjunto de resultados armazenado em cache foi gerado.

Execute esse comando para verificar se uma consulta foi executada com uma perda ou ocorrência no cache de resultado. A coluna result_cache_hit retorna 1 para a ocorrência no cache, 0 para a perda no cache e valores negativos para motivos pelos quais o cache do conjunto de resultados não foi usado. Confira 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'>

Gerenciar resultados em cache

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

A remoção de cache é gerenciada pelo pool de SQL dedicado automaticamente após este agendamento:

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

Os usuários podem esvaziar manualmente todo o cache do conjunto de resultados usando uma destas opções:

• DESATIVAR o recurso de cache do conjunto de resultados do banco de dados
• Executar DBCC DROPRESULTSETCACHE enquanto estiver conectado ao banco de dados

Pausar um banco de dados não esvazia o conjunto de resultados armazenado em cache.

Próximas etapas

Para obter mais dicas de desenvolvimento, confira visão geral de desenvolvimento.