Έπεσα πάνω σε μια συμπεριφορά του sql server 2005 που δεν μπορώ να εξηγήσω.

Έχω ένα σχετικά μεγάλο script που κάνει migration μια παλιά βάση σε μια νέα. Και οι δυο βάσεις είναι στον ίδιο sql server. Η όλη εγκατάσταση βρίσκεται σε ένα virtual machine και γίνεται trigger από έναν build server (ουσιαστικά οι συνθήκες που τρέχει κάθε φορά είναι πάρα πολύ συγκεκριμένες). Το virtual machine έχει στη διάθεσή του 1GB RAM και 1 core shared με τον host. Το όλο script χρειαζόταν περίπου 20 λεπτά για να ολοκληρωθεί.

Κάποια στιγμή μετέφερα όλο το virtual machine σε ένα νέο host (δυο quad cores, 12GB RAM) και πλέον τρέχει κάτω από το HyperV το οποίο του δίνει 2 cores και 3GB RAM. Δεν έκανα καμιά αλλαγή εσωτερικά στο virtual machine. Απλά το μετέφερα από το παλιό μηχάνημα όπου έτρεχε κάτω από VPC 2007 σε ένα νέο στο οποίο τρέχει κάτω από το HyperV. Από εκείνη τη στιγμή το script απλά δεν τρέχει. Το σταματάω με το χέρι μετά από 1 ώρα.

Μετά από διερεύνηση του θέματος, ένας συνάδερφος ανακάλυψε ότι το SET IMPLICIT_TRANSACTIONS ON βελτιώνει την κατάσταση. Το ίδιο και αν ανοίξει explicitely transactions μέσα στο script.

Έχετε ιδέα γιατί άλλαξε η συμπεριφορά, χωρίς καμιά αλλαγή στον server, παρά μόνο μεταφέροντάς τον σε γρηγορότερο περιβάλλον;

Dimitris Papadimitriou Software Development Professional

Χωρίς λεπτομέρειες μόνο εικασίες μπορεί να κάνει κάποιος. Τί έκδοση του SQL είναι (Enterprise/Development ή Standard?), τί Service Packs έχει, τί κάνει το script? Κοίταξες το Current Activity του SQL Server? Έβαλες τον SQL Profiler να δεις τί τρέχει?

Όσον αφορά τις αλλαγές, αν πέρασες από 1 σε 2 core έκανες μία πολύ σημαντική αλλαγή - τρέχεις κάποια statements παράλληλα. Αν το script εκτελείται μονοκόματα, ίσως τα statements να μην δουλεύουν καλά σε παράλληλη εκτέλεση. Αυτό διορθώνεται με hints. Η έκδοση του SQL Server έχει επίσης σημασία, καθώς η Enterprise χρησιμοποιεί αυτόματα όλους τους επεξεργαστές ενώ η Standard μόνο με hints. Τέλος, o SQL Server 2008 φέρεται πολύ καλύτερα σε multi-core περιβάλλον απ' ότι ο SQL Server 2005.

Αν διάφορα statements εκτελούνται παράλληλα, μπορεί άνετα το ένα να μπλοκάρει το άλλο. Το SET IMPLICIT_TRANSACTIONS ON ξεκινάει transactions όταν συναντήσει οποιαδήποτε σημαντική εντολή SQL (SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER) με αποτέλεσμα τα transactions του script να καλύπτουν πολύ μεγαλύτερο τμήμα του. Ίσως έτσι κάποια statements να κλειδώνουν και να κρατάνε resources μέχρι το επόμενο commit, ενώ πριν τα έπιανε κάποιο άλλο statement και έπρεπε να περιμένουν.

Παναγιώτης Καναβός:

Χωρίς λεπτομέρειες μόνο εικασίες μπορεί να κάνει κάποιος. Τί έκδοση του SQL είναι (Enterprise/Development ή Standard?), τί Service Packs έχει, τί κάνει το script? Κοίταξες το Current Activity του SQL Server? Έβαλες τον SQL Profiler να δεις τί τρέχει?

Καλά μην εξάπτεσαι! Μου λες τι πληροφορία θέλεις και σου απαντώ. Δεν θα σου αραδιάσω όλη την ιστορία της ζωής μου, μήπως και είναι χρήσιμη στην απάντηση. Χρησιμοποιώ SQL Server Developer Edition, SP3. Δεν έκανα εγώ την ανάλυση και την λύση του προβλήματος οπότε δεν ξέρω αποτελέσματα από profiler. Όπως και δεν γνωρίζω ιδιαίτερα τι κάνει το script. Η ερώτησή μου είναι περισσότερο θεωρητική και δεν περιμένω συγκεκριμένη απάντηση για να λύση το πρόβλημά μου.

Παναγιώτης Καναβός:

Όσον αφορά τις αλλαγές, αν πέρασες από 1 σε 2 core έκανες μία πολύ σημαντική αλλαγή - τρέχεις κάποια statements παράλληλα. Αν το script εκτελείται μονοκόματα, ίσως τα statements να μην δουλεύουν καλά σε παράλληλη εκτέλεση. Αυτό διορθώνεται με hints. Η έκδοση του SQL Server έχει επίσης σημασία, καθώς η Enterprise χρησιμοποιεί αυτόματα όλους τους επεξεργαστές ενώ η Standard μόνο με hints. Τέλος, o SQL Server 2008 φέρεται πολύ καλύτερα σε multi-core περιβάλλον απ' ότι ο SQL Server 2005.

Είναι δυνατόν κάτι τέτοιο; Μπορεί εσωτερικά ο server να εκμεταλλεύται τα cores αλλά είναι δυνατόν να παίρνει πρωτοβουλίες και να εκτελεί τα δικά μου statements παράλληλα; Είναι σαν να μου λες ότι ξαφνικά το .net runtime εκεί που τρέχει τον κώδικά μου αποφασίζει ότι θα βάλει σε ένα άλλο thread μια γραμμή κώδικα και θα προχωρήσει στην επόμενη πριν τελειώσει η προηγούμενη!

Παναγιώτης Καναβός:

Αν διάφορα statements εκτελούνται παράλληλα, μπορεί άνετα το ένα να μπλοκάρει το άλλο. Το SET IMPLICIT_TRANSACTIONS ON ξεκινάει transactions όταν συναντήσει οποιαδήποτε σημαντική εντολή SQL (SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER) με αποτέλεσμα τα transactions του script να καλύπτουν πολύ μεγαλύτερο τμήμα του. Ίσως έτσι κάποια statements να κλειδώνουν και να κρατάνε resources μέχρι το επόμενο commit, ενώ πριν τα έπιανε κάποιο άλλο statement και έπρεπε να περιμένουν.

Στην περίπτωσή μου το script δεν μπλοκάρεται (dead lock). Απλά τρέχει πολύ πολύ πιο αργά.

Dimitris Papadimitriou Software Development Professional

Δημήτρη, σε ρωτάω τί έκδοση είναι γιατί όπως ανέφερα παίζει ρόλο στο πως δουλεύει ο optimizer του SQL Server. Ανάλογα με την έκδοση, τα hints και το workload ο optimizer χρησιμοποιεί τους αντίστοιχους αλγόριθμους. Δεν σημαίνει ότι αν βρει 2 CPUs θα τις χρησιμοποιήσει και τις 2 για να εκτελέσει το ίδιο statement. Για παράδειγμα, ένα join μπορεί να εκτελεστεί παράλληλα σπάζοντας τα δεδομένα σε διάφορα streams, στέλνοντας κάθε stream σε άλλο επεξεργαστή και ενώνοντας τα αποτελέσματα στο τέλος. Αν ο optimizer κάνει λάθος επιλογή ή αν αναγκαστεί μέσω hint να χρησιμοποιήσει παράλληλους αλγόριθμους το αποτέλεσμα θα είναι πιο αργό απ' ότι αν είχες ένα μόνο CPU.
Δεν σημαίνει ότι ο SQL Server αποφάσισε ξαφνικά να σου αλλάξει το πρόγραμμα, ούτε ότι αποφάσισε δύο διαδοχικά statements να τα εκτελέσει ταυτόχρονα αλλά ότι αποφάσισε να αλλάξει τρόπο επεξεργασίας για ένα συγκεκριμένο statement.

Για να ελέγξεις αν η καθυστέρηση οφείλεται στους παράλληλους αλγόριθμους μπορείς να αλλάξεις τον αριθμό των CPUs που χρησιμοποιεί ο server σε 1 από το 0 που είναι το default. Αυτό γίνεται είτε από το Management Studio είτε αλλάζοντας το max degree of parallelism option

Όσο για το μπλοκάρισμα, εννοώ blocking και όχι deadlock. Blocking σημαίνει ότι ένα transaction περιμένει να ελευθερωθούν resources από κάποιο άλλο για να συνεχίσει. Δεν σημαίνει ότι το block θα οδηγήσει σε deadlock.  Αυτή είναι η πιο πιθανή αιτία καθυστερήσεων, ειδικά όταν αυξάνεται ο αριθμός των statements που εκτελούνται παράλληλα. Από τη στιγμή που λες ότι το IMPLICIT TRANSACTIONS επηρεάζει τη συμπεριφορά του script το blocking σίγουρα παίζει κάποιο ρόλο.

Τα στοιχεία που ρώτησα είναι απαραίτητα για να βγάλεις οποιοδήποτε συμπέρασμα για τον SQL Server. Χωρίς αυτά δεν είναι δυνατόν να απαντήσει κανείς παρά μόνο να μαντέψει.

Έτσι από περιέργεια, αυτό το script είχε παραχθεί από κάποιο εργαλείο ή το έχει γράψει κάποιος; Και αν έχει παραχθεί αυτόματα, ο server πάνω στον οποίο έτρεξε το εργαλείο, ήταν ο παλιός;

Αν πεις "ναι" στα παραπάνω, θα έχει ενδιαφέρον να κάνεις το εξής test: Backup τη βάση από τον παλιό server, restore στον νέο, re-run το εργαλείο και σύγκριση αν έχει βγάλει το ίδιο script. Μπορεί το εργαλείο να βγάζει διαφορετικό κώδικα στο νέο περιβάλλον...

KelMan:

Έτσι από περιέργεια, αυτό το script είχε παραχθεί από κάποιο εργαλείο ή το έχει γράψει κάποιος; Και αν έχει παραχθεί αυτόματα, ο server πάνω στον οποίο έτρεξε το εργαλείο, ήταν ο παλιός;

Όχι, το script έχει γραφτεί από άνθρωπο.

Dimitris Papadimitriou Software Development Professional