Condividi tramite

L'ipotesi di contenimento dei join nella nuova stima della cardinalità degrada le prestazioni delle query

  • Articolo

Questo articolo illustra come risolvere i problemi di prestazioni che possono verificarsi in SQL Server 2014 e versioni successive quando si compilano le query usando il nuovo strumento di stima della cardinalità.

Versione originale del prodotto: SQL Server
Numero KB originale: 3189675

Sintomi

Prendi in considerazione lo scenario seguente:

  • Si sta usando SQL Server 2014 o una versione successiva.
  • Si esegue una query contenente join e predicati di filtro non join.
  • Compilare la query usando la nuova stima della cardinalità (SQL Server) (Nuova stima della cardinalità).

In questo scenario si verifica una riduzione delle prestazioni delle query.

Questo problema non si verifica se si compila la query usando la stima di cardinalità legacy.

Causa

Da SQL Server 2014 è stato introdotto il nuovo strumento di stima della cardinalità (Nuova stima della cardinalità) per il livello di compatibilità del database 120 e versioni successive. La nuova stima di cardinalità modifica diversi presupposti dalla stima della cardinalità legacy nel modello usato da Query Optimizer quando stima la cardinalità per operatori e predicati diversi.

Una di queste modifiche è correlata al presupposto di contenimento dei join.

Il modello ce legacy presuppone che gli utenti eseseguono sempre una query per i dati esistenti. Ciò significa che, per un predicato di join che prevede un'operazione equijoin per due tabelle, le colonne unite sono presenti su entrambi i lati del join. In presenza di predicati di filtro non join aggiuntivi rispetto alla tabella join, la stima di cardinalità legacy presuppone un certo livello di correlazione per i predicati di join e i predicati di filtro non join. Questa correlazione implicita è denominata Contenimento semplice.

In alternativa, la nuova stima di cardinalità usa il contenimento di base come correlazione. Il nuovo modello ce presuppone che gli utenti possano eseguire query per i dati che non esistono. Ciò significa che i predicati di filtro in tabelle separate potrebbero non essere correlati tra loro. Pertanto, usiamo un approccio probabilistico.

Per molti scenari pratici, l'uso del presupposto di contenimento di base crea stime migliori. In questo modo, a sua volta, vengono create scelte di piano di query più efficienti. Tuttavia, in alcune situazioni, l'uso del presupposto di contenimento semplice può fornire risultati migliori. In questo caso, è possibile che si verifichi una scelta del piano di query meno efficiente quando si usa La nuova stima di cardinalità anziché la stima di cardinalità legacy.

Per altre informazioni su come risolvere i problemi relativi alla nuova cardinalità, vedere Riduzione delle prestazioni delle query dopo l'aggiornamento da SQL Server 2012 o versioni precedenti alla versione 2014 o successiva.

Risoluzione

In SQL Server 2014 e versioni successive è possibile usare il flag di traccia 9476 per forzare SQL Server a usare il presupposto di contenimento semplice anziché il presupposto predefinito di contenimento di base. Se è possibile modificare la query dell'applicazione, l'opzione migliore consiste nell'usare l'hint ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS per la query dopo SQL Server 2016 (13.x) SP1. Per altre informazioni, vedere USE HINT. Ad esempio:

SELECT * FROM Table1 t1
JOIN Table2 t2
ON t1.Col1 = t2.Col1
WHERE Col1 = 10
OPTION (USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'));

L'abilitazione di questo flag di traccia o l'uso dell'hint per la query può migliorare la scelta del piano di query senza dover ripristinare completamente il modello ce legacy se sono soddisfatte le condizioni seguenti:

  • Si verifica una scelta del piano di query non ottimale che causa una riduzione complessiva delle prestazioni per le query che contengono join e predicati di filtro non join.
  • È possibile verificare un'imprecisione significativa in una stima di "cardinalità join", ovvero il numero effettivo rispetto al numero stimato di righe che differiscono in modo significativo.
  • Questa imprecisione non esiste quando si compilano query usando la stima di cardinalità legacy.

È possibile abilitare questo flag di traccia a livello globale, a livello di sessione o a livello di query.

Note

L'uso non corretto dei flag di traccia può ridurre le prestazioni del carico di lavoro. Per altre informazioni, vedere Hint (Transact-SQL) - Query.