none
Aiuto per individuazione problema RRS feed

  • Domanda

  • Buongiorno a tutti,

    sono nuovo su questo forum quindi mi scuso in anticipo per eventuali errori.

    Ho questo problema: sono un programmatore che sta cercando di risolvere un problema di velocità su un sito. Mi sono imbattuto in una query. Ho notato che togliendo o "AND numerazione <= 60" o "AND ( ClassificazioneDati.codsottolinea = 'S00068')" questa query in Sql Server 2014 Management Studio ha un tempo di esecuzione pari a 2-4 sec mentre aggiungendo entrambi gli AND cioé "AND numerazione <= 60" o "AND ( ClassificazioneDati.codsottolinea = 'S00068')", il tempo di esecuzione diventa di 45 sec!

    Non è sicuramente un problema di come è scritta la query, ma sembra che dopo un tot di operazioni la query aumenta in maniera stellare il tempo di esecuzione e non ho proprio idea di cosa possa essere.

    Mi dice che non posso aggiungere immagini o collegamenti finché non sono un utente verificato. (?) 

    giovedì 21 novembre 2019 11:33

Risposte

  • Ciao Daniele, Ciao Edoardo,

    confrontando i due piani di esecuzione si nota che in quello da 45 secondi il numero di righe "lavorate" dai diversi operatori è molto maggiore rispetto a quello lavorato nel piano di esecuzione della query da 2 secondi.

    Ad esempio, nella figura seguente, l'index scan sulla tabella Giacenze estrae in un caso 76964 righe e nell'altro più di 4 Milioni nonostante la stima sia sempre di 76964. Questa situazione si ripresenta su tutto il piano di esecuzione provocando il rallentamento. Numero di righe lavorate molto maggiore con errata stima della cardinalità.

    Daniele, ho notato almeno due operazioni di Join (Nested Loop) in cui non è stato specificato il predicato di Join (vedi rettangolo rosso nell'immagine allegata).. per caso sono all'interno delle funzioni tabella? Se il predicato di Join non viene specificato, SQL Server eseguirà il prodotto cartesiano tra i due input.. è voluto?

    La manutenzione degli indici e delle statistiche su questo DB viene eseguita regolarmente?

    Per la colonna calcolata "Numerazione" dove viene applicata la condizione incriminata :) se ti serve solo un progressivo numerico, valuta l'utilizzo di ROW_NUMBER() in sostituzione a RANK().

    Ciao!


    Sergio Govoni

    Microsoft Data Platform MVP | MVP Profile | English Blog | Twitter | LinkedIn


    martedì 26 novembre 2019 23:30
    Moderatore
  • Ciao,

    dalla query si nota:

    1. l'uso di due table value function (TVF) Web_prezziminmaxcompletaxarticolo, Web_dettagliarticoloxelencoarticoli; esse sono usate in inner join quindi potrebbero anche essere quelle che guidano il piano;

    2. la funz. di rango rank() viene usata in condizione where.

    Possiamo dire a priori che la query non è gestibile in modo ottimale dall'ottimizzatore in quanto hanno un ruolo rilevante oggetti volatili (TVF, aggregate functions) di cui sono ignote le statistiche di distribuzione dei valori e sono prive di indici.

    L'introduzione della where condition sul RANK() induce sql ha modificare il metodo di risoluzione della join cha passa da un merge hash del caso 2 a un nested loop del caso 45: in questo modo vengono ripetuti 60 volte gli accessi a varie tabelle tabelle (Articoli, Giacenze, ImmaginiVarie, ...) sia in full che index seek andando a produrre il degrado che si osserva.

    Una possibile soluzione consiste nel forzare con hint il tipo di join, imponendo l'hash: inner hash join. Andrebbe fatto in modo preciso non toccando i loop che invece già funzionano bene ma in prima battuta si può fare alla cieca giusto per verificare un primo sommario risultato.

    Un'altra, almeno a livello di studio della query se non di messa in produzione, consiste nel portare i dati dalle TVF in tabelle temporanee, indicizzate sui campi che vanno in join (in where non ne vedo) ed osservareil piano di esecuzione che in tal modo è ottimo in quanto gestibile da sql con statistiche ed indici.

    In tal caso da verificare bene che sulle temp tab le statistiche siano create e valutate dal piano compilato.

    Giorgio


    martedì 3 dicembre 2019 06:33

Tutte le risposte

  • Buongiorno Daniele,

    (Benvenuto sul Forum)

    sarebbe interessante confrontare i piani di esecuzione generati per le due "versioni" della query. Per la versione con le condizioni in AND..

    AND numerazione <= 60" o "AND ( ClassificazioneDati.codsottolinea = 'S00068')

    e per la versione senza condizioni.

    Riesci a riprodurre il problema eseguendo la query da SQL Server Management Studio? Se riesci a riprodurre il caso su SSMS potrai consultare il piano di esecuzione e avremo qualche elemento in più per individuare il problema.

    Hai verificato il numero di record estratti in entrambi i casi? Quanti sono?

    Ciao


    Sergio Govoni

    Microsoft Data Platform MVP | MVP Profile | English Blog | Twitter | LinkedIn

    giovedì 21 novembre 2019 23:29
    Moderatore
  • Buongiorno Sergio,

    grazie della risposta e del suo tempo. Di seguito trova la cartella di One Drive con i file richiesti. 

    https://1drv.ms/u/s!AmkOKuulRBxshcM3xbENb6DkhsTXTQ?e=oduA5D

    I file che troverà sono:

    • Query45sec : E' la query completa
    • Tolto-Numerazione: Tolto "AND numerazione <= 60"
    • Tolto-Sottolinea: Tolto "AND ClassificazioneDati.sottolinea = 'S00068'"
    • MSDN: contiene degli screenshot con i relativi tempi e record risultanti









    venerdì 22 novembre 2019 11:49
  • prima di eseguire le query (immagini MSDN) non hai attivato "includi piano di esecuzione" e quindi la relativa tab non compare

    Edoardo Benussi
    Microsoft MVP - Cloud and Datacenter Management
    e[dot]benussi[at]outlook[dot]it

    lunedì 25 novembre 2019 09:56
    Moderatore
  • Ho cambiato i file presenti nella cartella:

    https://1drv.ms/u/s!AmkOKuulRBxshcM3xbENb6DkhsTXTQ?e=oduA5D

    QueryDa45Pianodiesecuzione.sqlplan --> è la query completa da 45 sec

    QueryDa2.sqlplan --> è la query da due secondi togliendo "AND numerazione <= 60"

    SecondaQueryDa2.sqlplan --> è la query da due secondi togliendo "AND (Classificazione.codsottolinea='S00068' "

    In msdn sono presenti gli screnshoot.

    lunedì 25 novembre 2019 14:02
  • puoi riportare qui anche la query completa ?

    Edoardo Benussi
    Microsoft MVP - Cloud and Datacenter Management
    e[dot]benussi[at]outlook[dot]it

    martedì 26 novembre 2019 07:54
    Moderatore
  • Ok,

    ho riportato la query completa nella solita cartella One drive nella cartella "QueryCompleta"

    https://1drv.ms/u/s!AmkOKuulRBxshcM3xbENb6DkhsTXTQ?e=oduA5D
    martedì 26 novembre 2019 08:31
  • Ciao Daniele, Ciao Edoardo,

    confrontando i due piani di esecuzione si nota che in quello da 45 secondi il numero di righe "lavorate" dai diversi operatori è molto maggiore rispetto a quello lavorato nel piano di esecuzione della query da 2 secondi.

    Ad esempio, nella figura seguente, l'index scan sulla tabella Giacenze estrae in un caso 76964 righe e nell'altro più di 4 Milioni nonostante la stima sia sempre di 76964. Questa situazione si ripresenta su tutto il piano di esecuzione provocando il rallentamento. Numero di righe lavorate molto maggiore con errata stima della cardinalità.

    Daniele, ho notato almeno due operazioni di Join (Nested Loop) in cui non è stato specificato il predicato di Join (vedi rettangolo rosso nell'immagine allegata).. per caso sono all'interno delle funzioni tabella? Se il predicato di Join non viene specificato, SQL Server eseguirà il prodotto cartesiano tra i due input.. è voluto?

    La manutenzione degli indici e delle statistiche su questo DB viene eseguita regolarmente?

    Per la colonna calcolata "Numerazione" dove viene applicata la condizione incriminata :) se ti serve solo un progressivo numerico, valuta l'utilizzo di ROW_NUMBER() in sostituzione a RANK().

    Ciao!


    Sergio Govoni

    Microsoft Data Platform MVP | MVP Profile | English Blog | Twitter | LinkedIn


    martedì 26 novembre 2019 23:30
    Moderatore
  • Ciao,

    dalla query si nota:

    1. l'uso di due table value function (TVF) Web_prezziminmaxcompletaxarticolo, Web_dettagliarticoloxelencoarticoli; esse sono usate in inner join quindi potrebbero anche essere quelle che guidano il piano;

    2. la funz. di rango rank() viene usata in condizione where.

    Possiamo dire a priori che la query non è gestibile in modo ottimale dall'ottimizzatore in quanto hanno un ruolo rilevante oggetti volatili (TVF, aggregate functions) di cui sono ignote le statistiche di distribuzione dei valori e sono prive di indici.

    L'introduzione della where condition sul RANK() induce sql ha modificare il metodo di risoluzione della join cha passa da un merge hash del caso 2 a un nested loop del caso 45: in questo modo vengono ripetuti 60 volte gli accessi a varie tabelle tabelle (Articoli, Giacenze, ImmaginiVarie, ...) sia in full che index seek andando a produrre il degrado che si osserva.

    Una possibile soluzione consiste nel forzare con hint il tipo di join, imponendo l'hash: inner hash join. Andrebbe fatto in modo preciso non toccando i loop che invece già funzionano bene ma in prima battuta si può fare alla cieca giusto per verificare un primo sommario risultato.

    Un'altra, almeno a livello di studio della query se non di messa in produzione, consiste nel portare i dati dalle TVF in tabelle temporanee, indicizzate sui campi che vanno in join (in where non ne vedo) ed osservareil piano di esecuzione che in tal modo è ottimo in quanto gestibile da sql con statistiche ed indici.

    In tal caso da verificare bene che sulle temp tab le statistiche siano create e valutate dal piano compilato.

    Giorgio


    martedì 3 dicembre 2019 06:33
  • Buongiorno,

    Giorgio, grazie mille per la spiegazione così esaustiva.

    mercoledì 4 dicembre 2019 09:58
  • Buongiorno Sergio,

    le risposte alle domande che mi hai fatto sono:

    1) La manutenzione degli indici e delle statistiche su questo DB viene eseguita regolarmente? --> Viene fatta ogni sera

    2) Per la colonna calcolata "Numerazione" dove viene applicata la condizione incriminata :) se ti serve solo un progressivo numerico, valuta l'utilizzo di ROW_NUMBER() in sostituzione a RANK(). --> Prima c'era ROW_NUMBER() ma hanno visto che RANK è più veloce.

    3) Daniele, ho notato almeno due operazioni di Join (Nested Loop) in cui non è stato specificato il predicato di Join (vedi rettangolo rosso nell'immagine allegata).. per caso sono all'interno delle funzioni tabella? Se il predicato di Join non viene specificato, SQL Server eseguirà il prodotto cartesiano tra i due input.. è voluto? --> No, non è voluto.


    mercoledì 4 dicembre 2019 10:01
  • Grazie a tutti per il supporto!

    Abbiamo risolto il problema introducendo la INNER HASH JOIN.

    giovedì 5 dicembre 2019 10:02
  • Ciao Daniele,

    grazie a te per il feedback!

    A presto!


    Sergio Govoni

    Microsoft Data Platform MVP | MVP Profile | English Blog | Twitter | LinkedIn

    sabato 7 dicembre 2019 23:08
    Moderatore