Ottimizzazione delle prestazioni con memorizzazione nella cache dei set di risultati

Quando è abilitata la memorizzazione nella cache dei set di risultati, il pool SQL dedicato memorizza automaticamente i risultati delle query nel database dell'utente per l'uso ricorrente. In questo modo, le esecuzioni di query successive ottengono risultati direttamente dalla cache permanente, rendendo superfluo il ricalcolo. La memorizzazione nella cache dei set di risultati migliora le prestazioni delle query e riduce l'utilizzo delle risorse di calcolo. Inoltre, le query che usano set di risultati memorizzati nella cache non usano slot di concorrenza e pertanto non vengono prese in considerazione per i limiti di concorrenza esistenti. Per motivi di sicurezza, gli utenti possono accedere solo ai risultati memorizzati nella cache se hanno le stesse autorizzazioni di accesso ai dati degli utenti che hanno creato i risultati memorizzati nella cache. La memorizzazione nella cache dei set di risultati è OFF per impostazione predefinita a livello di database e sessione.

Nota

La memorizzazione nella cache dei set di risultati non deve essere usata insieme a DECRYPTBYKEY. Se questa funzione di crittografia deve essere usata, assicurarsi di avere disabilitato la memorizzazione nella cache dei set di risultati (a livello di sessione o a livello di database) al momento dell'esecuzione.

Comandi principali

Attivare/disattivare la memorizzazione nella cache dei set di risultati per un database utente

Attivare/disattivare la memorizzazione nella cache dei set di risultati per una sessione

Controllare le dimensioni del set di risultati memorizzato nella cache

Pulire la cache

Elementi non memorizzati nella cache

Dopo l'attivazione della memorizzazione nella cache dei set di risultati per un database, i risultati vengono memorizzati nella cache per tutte le query fino al riempimento della cache, ad eccezione delle query seguenti:

• Query con funzioni predefinite o espressioni di runtime non deterministiche anche quando non vengono apportate modifiche ai dati delle tabelle di base o alla query. Ad esempio, DateTime.Now(), GetDate().
• Query che usano funzioni definite dall'utente
• Query che usano tabelle con sicurezza a livello di riga
• Query che restituiscono dati con dimensioni di riga maggiori di 64 KB
• Query che restituiscono dati di grandi dimensioni (>10 GB)

Nota

• Alcune funzioni non deterministiche ed espressioni di runtime possono essere deterministiche per le query ripetitive sugli stessi dati. Ad esempio, ROW_NUMBER().
• Usare ORDER BY nella query se l'ordine o la sequenza delle righe nel set di risultati della query è importante per la logica dell'applicazione.
• Se i dati nelle colonne ORDER BY non sono univoci, non esiste un ordine di riga garantito per le righe con gli stessi valori nelle colonne ORDER BY, indipendentemente dal fatto che la memorizzazione nella cache del set di risultati sia abilitata o disabilitata.

Importante

Le operazioni per creare la cache dei set di risultati e recuperare i dati dalla cache vengono eseguite nel nodo di controllo di un'istanza del pool SQL dedicato. Quando la memorizzazione nella cache dei set di risultati è attivata, l'esecuzione di query che restituiscono set di risultati di grandi dimensioni, ad esempio >1 GB, può causare una limitazione elevata della larghezza di banda nel nodo di controllo e rallentare nel complesso la risposta alle query nell'istanza. Queste query vengono in genere usate durante l'esplorazione dei dati o le operazioni ETL (estrazione, trasformazione e caricamento). Per evitare il sovraccarico del nodo di controllo e la comparsa di problemi di prestazioni, gli utenti devono DISATTIVARE la memorizzazione nella cache del set di risultati nel database prima di eseguire query di questo tipo.

Eseguire questa query per trovare il tempo impiegato dalle operazioni di memorizzazione nella cache dei set di risultati per una query:

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

Ecco un esempio di output di una query eseguita con la memorizzazione nella cache dei set di risultati disabilitata.

Ecco un esempio di output di una query eseguita con la memorizzazione nella cache dei set di risultati abilitata.

Quando vengono usati i risultati memorizzati nella cache

Il set di risultati memorizzato nella cache viene riutilizzato per una query se vengono soddisfatti tutti i requisiti seguenti:

• L'utente che esegue la query ha accesso a tutte le tabelle a cui viene fatto riferimento nella query.
• È presente una corrispondenza esatta tra la nuova query e la query precedente che ha generato la cache dei set di risultati.
• Non sono presenti dati o modifiche dello schema nelle tabelle da cui è stato generato il set di risultati memorizzato nella cache.

Eseguire questo comando per verificare se una query è stata eseguita con un riscontro nella cache dei risultati. La colonna result_cache_hit restituisce 1 per il riscontro nella cache, 0 per un mancato riscontro nella cache e valori negativi per i motivi per cui non è stata usata la memorizzazione nella cache del set di risultati. Per informazioni dettagliate, vedere sys.dm_pdw_exec_requests.

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

Gestire i risultati memorizzati nella cache

Le dimensioni massime della cache dei set di risultati sono pari a 1 TB per ogni database. I risultati memorizzati nella cache vengono automaticamente invalidati quando i dati della query sottostante cambiano.

L'eliminazione della cache viene gestita automaticamente da l pool SQL dedicato seguendo questa pianificazione:

• Ogni 48 ore, se il set di risultati non è stato usato o è stato invalidato.
• Quando la cache dei set di risultati sta per raggiungere le dimensioni massime.

Per svuotare manualmente l'intera cache dei set di risultati, è possibile usare una di queste opzioni:

• Disattivare la funzionalità di memorizzazione nella cache dei set di risultati per il database
• Eseguire DBCC DROPRESULTSETCACHE mentre si è connessi al database

La sospensione di un database non svuoterà la cache dei set di risultati.

Passaggi successivi

Per altri suggerimenti sullo sviluppo, vedere la panoramica dello sviluppo.