Γεια σε όλους! Πρόσφατα αντιμετώπισα ένα πρόβλημα όπου χρειάστηκε πολύς χρόνος για την επεξεργασία ενός εγγράφου.

Δεδομένα εισαγωγής: διαμόρφωση «Διαχείριση επιχείρησης παραγωγής, έκδοση 1.3 (1.3.52.1)», έγγραφο «Εισερχόμενη εντολή πληρωμής». Παράπονο: η διατήρηση στη βάση δεδομένων εργασίας διαρκεί 20-30 δευτερόλεπτα, κάτι που είναι ενδιαφέρον, σε ένα αντίγραφο της βάσης δεδομένων το ίδιο έγγραφο διατηρείται για 2-4 δευτερόλεπτα. Διαβάστε για τις έρευνες και τον λόγο αυτής της συμπεριφοράς παρακάτω.

Έτσι, με τη βοήθεια μέτρηση επίδοσηςΝομίζω ότι όλοι ξέρουν πώς να το χρησιμοποιούν, βρέθηκε ο ένοχος:

Σε αυτήν την περίπτωση, ένα κενό σύνολο εγγραφών καταγράφηκε στη συσκευή εγγραφής· με άλλα λόγια, οι κινήσεις διαγράφηκαν πριν από την εκτέλεση. Αξίζει να σημειωθεί ότι αυτή η διαδικασία κλήθηκε 26 φορές, δηλ. για κάθε μητρώο στο οποίο θα μπορούσε να γράψει το έγγραφό μας.

Σύμφωνα με μετρήσεις απόδοσης, αυτή η λειτουργία χρειάστηκε 13 δευτερόλεπτα· αν υπολογίσετε τον μέσο όρο, θα έχετε 0,5 δευτερόλεπτα ανά καταχωρητή, μια αιωνιότητα!

Όπως όλοι γνωρίζουμε, δεν μπορούμε να βελτιστοποιήσουμε την εγγραφή, αλλά σαφώς κάτι δεν πάει καλά εδώ.
Για περαιτέρω ανάλυση, ανοίξτε SQL ServerΠροφίλΚαι . Για ανάλυση χρησιμοποίησα τάξεις συμβάντων:

• Προφίλ Στατιστικών Showplan
• Showplan XML Statistics Profile
• RPC Ολοκληρώθηκε
• SQL:BatchCompleted.

Στις ρυθμίσεις ανίχνευσης υπάρχει φιλτράρισμα κατά SPID:

Το SPID είναι το αναγνωριστικό διεργασίας του διακομιστή βάσης δεδομένων. Στην περίπτωση του 1C, είναι ουσιαστικά μια σύνδεση μεταξύ του διακομιστή 1C και του DBMS· μπορείτε να το δείτε στην κονσόλα διαχείρισης των διακομιστών 1C στη στήλη "Σύνδεση με DBMS".

Εμφανίζεται εάν αυτή τη στιγμήη σύνδεση με τη βάση δεδομένων καταγράφεται από τη συνεδρία: είτε πραγματοποιείται μια κλήση DBMS, είτε είναι ανοιχτή μια συναλλαγή, είτε διατηρείται το αντικείμενο "Temporary Table Manager", στο οποίο έχει δημιουργηθεί τουλάχιστον ένας προσωρινός πίνακας.

Ας γράψουμε μια επεξεργασία για τη διατήρηση του SPID, θα περιέχει μία διαδικασία:

Είναι σημαντικό το αντικείμενο σύνδεσης που διατηρείται, στην περίπτωσή μας ο προσωρινός διαχειριστής πίνακα, να ορίζεται ως μεταβλητή επεξεργασίας. Ανοίγουμε την επεξεργασία, τρέχουμε τη διαδικασία και όσο είναι ανοιχτή, το SPID θα διορθωθεί. Ανοίξτε την κονσόλα διαχείρισης διακομιστή 1C:

Λοιπόν, το SPID έχει ληφθεί, εισάγουμε την τιμή του στο φίλτρο και παίρνουμε ένα ίχνος από την τρέχουσα βάση δεδομένων εργασίας για τη συνεδρία μας. Κατά την ανάλυση του ίχνους, βρέθηκε μια επέμβαση που χρειάστηκε 11 δευτερόλεπτα για να ολοκληρωθεί:

Αυτό που τράβηξε επίσης το μάτι μου ήταν ο αριθμός των αναγνώσεων - 1872578 , αλλά δεν έδωσα αμέσως καμία σημασία σε αυτό και άρχισα να καταλαβαίνω τι γινόταν εδώ και με ποιο τραπέζι.

exec sp_executesql <= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))’,N’@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)’,0x8A2F00155DBF491211E87F56DD1A416E,’4018-05-31 23:59:59′,0x00000000000000000000000000000000,’4018-05-31 23:59:59′,0x9A95A0369F30F8DB11E46684B4F0A05F,’4018-05-31 23:59:59"

Όπως μπορείτε να δείτε από το ερώτημα SQL, ο πίνακας υποβάλλεται σε επεξεργασία "AccRg1465"Αυτός είναι ένας πίνακας στο αυτοσυντηρούμενο λογιστικό μητρώο. Κειμενική αναπαράσταση του σχεδίου εκτέλεσης ερωτήματος:

Όπως μπορείτε να δείτε από το σχέδιο εκτέλεσης του ερωτήματος SQL, δεν συμβαίνει τίποτα κακό, ο πίνακας " AcRg1465", η αναζήτηση ομαδοποιημένου ευρετηρίου χρησιμοποιείται παντού.

Επίσης, δεν είδα τίποτα κακό με το γραφικό σχέδιο, αν και μου φαινόταν πολύ φουσκωμένο - υπήρξε μια συγχώνευση και δύο ένθετοι βρόχοι χωρίς προφανή λόγο. Από πού προέρχεται αυτός ο αριθμός αναγνώσεων και ο γιγαντιαίος χρόνος εκτέλεσης;

Όπως αναφέρθηκε παραπάνω, το πρόβλημα δεν αναπαρήχθη σε ένα νέο αντίγραφο της βάσης δεδομένων· το αντίγραφο λήφθηκε από τη βάση δεδομένων εργασίας αφού εμφανίστηκε το πρόβλημα σε αυτήν, οπότε αποφασίστηκε να αναλυθεί η συμπεριφορά του στο SQL Server Profiler στο ίδιο έγγραφο.
Εδώ είναι τα αποτελέσματα:

Κείμενο ερωτήματος σε SQL:

EXEC sp_executesql N"SELECT TOP 1 0x01 FROM dbo._AccRg1465 T1 WHERE (T1._RecorderTRef = 0x0000022D AND T1._RecorderRRef = @P1) AND ((((T1._Period<= @P2) AND (T1._Fld1466RRef = @P3)) OR ((T1._Period <= @P4) AND (T1._Fld1466RRef = @P5))) OR ((T1._Period <= @P6) AND (1=0)))" , N"@P1 varbinary(16),@P2 datetime2(3),@P3 varbinary(16),@P4 datetime2(3),@P5 varbinary(16),@P6 datetime2(3)", 0x8A2F00155DBF491211E87F56DD1A416E, "4018-05-31 23:59:59" ,00, "4018-05-31 23:59:59" , 0x9D915A03 401 8-05-31 23:59:59"

Γραφική αναπαράσταση του σχεδίου ερωτήματος:

Τα κείμενα αιτημάτων είναι τα ίδια, τα σχέδια εκτέλεσης είναι ριζικά διαφορετικά. Τι μπορεί να συμβαίνει; Έκανα λάθος σχετικά με τα στατιστικά στοιχεία στο SQl, αλλά είναι τα ίδια μεταξύ του λειτουργικού και του αντιγράφου της βάσης δεδομένων και τα στατιστικά στοιχεία αποθηκεύονται στη βάση δεδομένων για κάθε πίνακα:

Ας αναλύσουμε περαιτέρω: εάν τα στατιστικά στοιχεία είναι τα ίδια, αλλά τα σχέδια ερωτημάτων είναι διαφορετικά, σημαίνει ότι το πρόγραμμα βελτιστοποίησης δεν έχει πρόσβαση στα στατιστικά στοιχεία για τη δημιουργία ενός σχεδίου ερωτήματος, αλλά έχει ένα σχέδιο προσωρινής αποθήκευσης, το οποίο χρησιμοποιεί. Εκκαθαρίζουμε τη διαδικαστική προσωρινή μνήμη στη βάση δεδομένων μας, για αυτό χρησιμοποιούμε την εντολή

DBCC FLUSHPROCINDB(< database_id >)

Οπου< database_id >είναι το αναγνωριστικό της βάσης δεδομένων. Για να μάθετε το αναγνωριστικό της βάσης δεδομένων, πρέπει να εκτελέσετε το σενάριο

επιλέξτε όνομα, database_id από sys . βάσεις δεδομένων

θα μας επιστρέψει μια λίστα βάσεων δεδομένων και τα αναγνωριστικά τους.

Παίρνουμε πάλι το ίχνος:

Κειμενική αναπαράσταση του σχεδίου ερωτήματος:

Γραφική αναπαράσταση του σχεδίου ερωτήματος:

Όπως μπορείτε να δείτε, το σχέδιο ερωτήματος λήφθηκε ξανά από το βελτιστοποιητή και το παλιό αποθηκευμένο στην προσωρινή μνήμη δεν χρησιμοποιήθηκε, ο χρόνος εκτέλεσης επέστρεψε στο κανονικό, όπως και ο αριθμός των αναγνώσεων. Δεν είναι ξεκάθαρο τι το προκάλεσε, ίσως ένας μεγάλος αριθμός ανταλλαγών ή το κλείσιμο προηγούμενων περιόδων, είναι δύσκολο να πούμε. Έχει ρυθμιστεί η τακτική συντήρηση της βάσης δεδομένων. Αυτή είναι η πρώτη φορά που συναντώ μια απάτη του σχεδίου εκτέλεσης ερωτημάτων προσωρινής αποθήκευσης.

Σας ευχαριστώ για την προσοχή σας!

Σας βοήθησε αυτό το άρθρο;

6 απαντήσεις

Υπάρχουν διάφοροι τρόποι για να αποκτήσετε ένα σχέδιο εκτέλεσης, οι οποίοι θα εξαρτηθούν από τις περιστάσεις σας. Συνήθως, μπορείτε να χρησιμοποιήσετε το SQL Server Management Studio για να αποκτήσετε το πρόγραμμα, ωστόσο, εάν για κάποιο λόγο δεν μπορείτε να εκτελέσετε το ερώτημά σας στο SQL Server Management Studio, μπορεί να σας φανεί χρήσιμο να αποκτήσετε το πρόγραμμα μέσω του SQL Server Profiler ή ελέγχοντας το πρόγραμμα κρύπτη.

Μέθοδος 1 - Χρήση του SQL Server Management Studio

Ο SQL Server έχει ορισμένες προσεγμένες δυνατότητες που κάνουν τη συλλογή ενός σχεδίου εκτέλεσης εύκολη, απλώς βεβαιωθείτε ότι το στοιχείο μενού "Συμπερίληψη πραγματικού σχεδίου εκτέλεσης" (που βρίσκεται στο μενού Ερώτημα) είναι επιλεγμένο και θα εκτελείται το δικό σας κανονικά.

Εάν προσπαθείτε να λάβετε το σχέδιο εκτέλεσης για δηλώσεις σε μια αποθηκευμένη διαδικασία, θα εκτελέσετε την αποθηκευμένη διαδικασία, ως εξής:

Exec p_Παράδειγμα 42

Όταν ολοκληρωθεί το ερώτημά σας, θα δείτε μια πρόσθετη καρτέλα Σχέδιο εκτέλεσης να εμφανίζεται στο παράθυρο αποτελεσμάτων. Εάν έχετε εκτελέσει πολλές εγκρίσεις, ενδέχεται να δείτε πολλά σχέδια να εμφανίζονται σε αυτήν την καρτέλα.

Εδώ μπορείτε να ελέγξετε το σχέδιο εκτέλεσης στο SQL Server Management Studio ή να κάνετε δεξί κλικ στο σχέδιο και να επιλέξετε "Αποθήκευση σχεδίου εκτέλεσης ως..." για να αποθηκεύσετε το σχέδιο σε ένα αρχείο XML.

Μέθοδος 2 - Χρήση επιλογών SHOWPLAN

Αυτή η μέθοδος είναι πολύ παρόμοια με τη μέθοδο 1 (αυτό κάνει στην πραγματικότητα εσωτερικά το SQL Server Management Studio), ωστόσο τη συμπεριέλαβα για πληρότητα ή εάν δεν έχετε διαθέσιμο SQL Server Management Studio.

Πριν εκτελέσετε το ερώτημα, εκτελέστε έναςτους ακόλουθους τελεστές. Η δήλωση πρέπει να είναι η μόνη δήλωση στο πακέτο, δηλ. Δεν μπορείτε να εκτελέσετε άλλη εντολή ταυτόχρονα:

SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON SET STATISTICS PROFILE ON SET STATISTICS XML ON -- Αυτή είναι η προτεινόμενη επιλογή για χρήση

Αυτές είναι οι παράμετροι σύνδεσης, επομένως χρειάζεται να το εκτελέσετε μόνο μία φορά για κάθε σύνδεση. Στο εξής, όλες οι δρομολογημένες δηλώσεις θα συνοδεύονται από πρόσθετο σύνολο αποτελεσμάτωνπου περιέχει το σχέδιο εκτέλεσής σας στην απαιτούμενη μορφή - απλώς εκτελέστε το ερώτημά σας ως συνήθως για να δείτε το σχέδιο.

Μόλις τελειώσετε, μπορείτε να απενεργοποιήσετε αυτήν την επιλογή με την ακόλουθη δήλωση:

ΣΕΙΡΑ<> ΑΠΕΝΕΡΓΟΠΟΙΗΣΗ

Σύγκριση μορφών σχεδίου εκτέλεσης

Εάν έχετε μεγάλη προτίμηση, προτείνω να χρησιμοποιήσετε την επιλογή STATISTICS XML. Αυτή η επιλογή είναι ισοδύναμη με την επιλογή "Συμπερίληψη πραγματικού σχεδίου εκτέλεσης" στο SQL Server Management Studio και παρέχει τις περισσότερες πληροφορίες στην πιο χρήσιμη μορφή.

• SHOWPLAN_TEXT - Εμφανίζει το βασικό σχέδιο εκτέλεσης που βασίζεται σε κείμενο χωρίς την εκτέλεση του ερωτήματος
• SHOWPLAN_ALL - Εμφανίζει ένα εκτιμώμενο σχέδιο εκτέλεσης βάσει κειμένου με εκτίμηση κόστους χωρίς την εκτέλεση του ερωτήματος
• SHOWPLAN_XML - Εμφανίζει ένα εκτιμώμενο σχέδιο εκτέλεσης που βασίζεται σε XML με εκτιμήσεις κόστους χωρίς την εκτέλεση του ερωτήματος. Αυτό ισοδυναμεί με την επιλογή "Εμφάνιση σχεδίου εκτέλεσης παραδείγματος..." στο SQL Server Management Studio.
• ΣΤΑΤΙΣΤΙΚΑ ΠΡΟΦΙΛ - Εκτελεί το ερώτημα και εμφανίζει το πραγματικό σχέδιο εκτέλεσης με βάση το κείμενο.
• ΣΤΑΤΙΣΤΙΚΑ XML - Εκτελεί το ερώτημα και εμφανίζει το πραγματικό σχέδιο εκτέλεσης που βασίζεται σε XML. Αυτό ισοδυναμεί με την επιλογή "Συμπερίληψη πραγματικού σχεδίου εκτέλεσης" στο SQL Server Management Studio.

Μέθοδος 3 - Χρήση του SQL Server Profiler

Εάν δεν μπορείτε να εκτελέσετε το ερώτημα απευθείας (ή το ερώτημά σας δεν εκτελείται αργά όταν το εκτελείτε απευθείας - θυμηθείτε ότι θέλουμε το σχέδιο ερωτήματος να εκτελείται ανεπαρκώς), τότε μπορείτε να καταγράψετε το σχέδιο χρησιμοποιώντας το SQL Server Profiler. Η ιδέα είναι να εκτελέσετε το ερώτημά σας ενώ εκτελείται ένα ίχνος που καταγράφει ένα από τα συμβάντα "Showplan".

Σημειώστε ότι ανάλογα με το φορτίο σας μπορείςχρησιμοποιήστε αυτή τη μέθοδο σε περιβάλλον παραγωγής, ωστόσο θα πρέπει προφανώς να είστε προσεκτικοί. Οι μηχανισμοί δημιουργίας προφίλ του SQL Server έχουν σχεδιαστεί για να ελαχιστοποιούν τον αντίκτυπο στη βάση δεδομένων, αλλά αυτό δεν σημαίνει ότι δεν θα υπάρξει αντίκτυπος στην απόδοση. Μπορεί επίσης να έχετε πρόβλημα με το φιλτράρισμα και τον προσδιορισμό του σωστού σχεδίου στο ίχνος σας, εάν η βάση δεδομένων σας είναι υπό υψηλή χρήση. Θα πρέπει προφανώς να ελέγξετε με το DBA σας για να βεβαιωθείτε ότι είναι ευχαριστημένοι που το κάνετε αυτό στην πολύτιμη βάση δεδομένων σας!

• Ανοίξτε το SQL Server Profiler και δημιουργήστε ένα νέο ίχνος που συνδέεται με την επιθυμητή βάση δεδομένων από την οποία θέλετε να καταγράψετε το ίχνος.
• Στην καρτέλα Επιλογή συμβάντος, επιλέξτε το πλαίσιο ελέγχου Εμφάνιση όλων των συμβάντων, επιλέξτε τη γραμμή Απόδοση -> Εμφάνιση σχεδίου XML και εκτελέστε το ίχνος.
• Ενώ εκτελείται το ίχνος, κάντε ό,τι χρειάζεται για να εκτελέσετε το αργό ερώτημα.
• Περιμένετε μέχρι να ολοκληρωθεί το αίτημα και να σταματήσει η ανίχνευση.
• Για να αποθηκεύσετε το ίχνος, κάντε δεξί κλικ στο σχέδιο xml στο προφίλ του SQL Server και επιλέξτε "Εξαγωγή δεδομένων συμβάντος..." για να αποθηκεύσετε το σχέδιο σε ένα αρχείο XML.

Το πρόγραμμα που λαμβάνετε είναι ισοδύναμο με την επιλογή "Συμπερίληψη σχεδίου πραγματικής εκτέλεσης" στο SQL Server Management Studio.

Μέθοδος 4 - Έλεγχος της προσωρινής μνήμης ερωτήματος

Εάν δεν μπορείτε να εκτελέσετε απευθείας το ερώτημά σας και δεν μπορείτε επίσης να καταγράψετε ένα ίχνος προφίλ, θα πρέπει να μπορείτε να λάβετε ένα εκτιμώμενο σχέδιο ελέγχοντας το σχέδιο κρυφής μνήμης του ερωτήματος SQL.

Ελέγχουμε την προσωρινή μνήμη του σχεδίου υποβάλλοντας ερώτημα σε DMV του SQL Server. Παρακάτω είναι ένα βασικό ερώτημα που θα παραθέτει όλα τα σχέδια ερωτημάτων που έχουν αποθηκευτεί στην προσωρινή μνήμη (ως xml) μαζί με το SQL κείμενό τους. Στις περισσότερες βάσεις δεδομένων, θα χρειαστεί επίσης να προσθέσετε επιπλέον συνθήκες φίλτρου για να φιλτράρετε τα αποτελέσματα στα σχέδια που σας ενδιαφέρουν.

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan)(plan_handle)

Εκτελέστε αυτό το ερώτημα και κάντε κλικ στο σχέδιο XML για να ανοίξετε το σχέδιο σε νέο παράθυρο - κάντε δεξί κλικ και επιλέξτε "Αποθήκευση σχεδίου εκτέλεσης ως..." για να αποθηκεύσετε το σχέδιο σε ένα αρχείο σε μορφή XML.

Σημειώσεις:

Επειδή υπάρχουν τόσοι πολλοί παράγοντες (που κυμαίνονται από σχήμα πίνακα και ευρετηρίου έως αποθηκευμένα δεδομένα και στατιστικά στοιχεία πίνακα), πρέπει Πάνταπροσπαθήστε να λάβετε το σχέδιο εκτέλεσης από τη βάση δεδομένων που σας ενδιαφέρει (συνήθως αυτή που αντιμετωπίζει το πρόβλημα απόδοσης).

Δεν μπορείτε να δεσμεύσετε το σχέδιο εκτέλεσης για κρυπτογραφημένες αποθηκευμένες διαδικασίες.

«πραγματικά» και «εκτιμώμενα» σχέδια εκτέλεσης

Το πραγματικό σχέδιο εκτέλεσης είναι αυτό όπου ο SQL Server εκτελεί πραγματικά το ερώτημα, ενώ το σχέδιο εκτιμώμενης εκτέλεσης SQL Server λειτουργεί σε αυτό που θα μπορούσε να κάνει χωρίς να εκτελέσει το ερώτημα. Αν και λογικά ισοδύναμο, το πραγματικό σχέδιο εκτέλεσης είναι πολύ πιο χρήσιμο επειδή περιέχει πρόσθετα δεδομένα και στατιστικά στοιχεία σχετικά με το τι πραγματικά συνέβη όταν εκτελέστηκε το ερώτημα. Αυτό είναι σημαντικό κατά τη διάγνωση προβλημάτων όταν είναι απενεργοποιημένες οι αξιολογήσεις διακομιστή SQL (για παράδειγμα, όταν τα στατιστικά στοιχεία δεν είναι ενημερωμένα).

Πώς να ερμηνεύσετε το σχέδιο εκτέλεσης ερωτήματος;

Αυτό είναι ένα θέμα που αξίζει αρκετά για ένα δωρεάν βιβλίο.

Εκτός από την περιεκτική απάντηση που δημοσιεύεται ήδη μερικές φορές, είναι χρήσιμο να έχετε πρόσβαση στο σχέδιο εκτέλεσης μέσω προγραμματισμού για την εξαγωγή πληροφοριών. Δείγμα κώδικα για αυτό είναι παρακάτω.

ΔΗΛΩΣΤΕ @TraceID INT EXEC StartCapture @@SPID, @TraceID OUTPUT EXEC sp_help "sys.objects" /*<-- Call your stored proc of interest here.*/ EXEC StopCapture @TraceID

Το αγαπημένο μου εργαλείο για τη λήψη και τη εις βάθος ανάλυση των σχεδίων εκτέλεσης ερωτημάτων είναι SQL Sentry Plan Explorer. Είναι πολύ πιο βολικό, φιλικό προς το χρήστη και πλήρες για λεπτομερή ανάλυση και οπτικοποίηση σχεδίων εκτέλεσης από το SSMS.

Ακολουθεί ένα παράδειγμα οθόνης για να κατανοήσετε ποια λειτουργικότητα προσφέρει το εργαλείο:

Αυτή είναι μόνο μία από τις προβολές που είναι διαθέσιμες στο εργαλείο. Παρατηρήστε το σύνολο καρτελών στο κάτω μέρος του παραθύρου της εφαρμογής που σας επιτρέπουν να λαμβάνετε διαφορετικούς τύπους προβολών σχεδίου εκτέλεσης και χρήσιμες πρόσθετες πληροφορίες.

Επιπλέον, δεν έχω παρατηρήσει κάποιους περιορισμούς στη δωρεάν έκδοσή του που να σας εμποδίζουν να το χρησιμοποιείτε σε καθημερινή βάση ή να σας αναγκάζουν να αγοράσετε τελικά την έκδοση Pro. Έτσι, αν προτιμάτε να μείνετε με τη δωρεάν έκδοση, τίποτα δεν απαγορεύεται.

Εκτός από τις μεθόδους που περιγράφονται σε προηγούμενες απαντήσεις, μπορείτε επίσης να χρησιμοποιήσετε το δωρεάν πρόγραμμα προβολής σχεδίου εκτέλεσης και το εργαλείο βελτιστοποίησης ερωτημάτων ApexSQL Plan (το οποίο συνάντησα πρόσφατα).

Μπορείτε να εγκαταστήσετε και να ενσωματώσετε το σχέδιο ApexSQL στο SQL Server Management Studio, ώστε τα σχέδια εκτέλεσης να μπορούν να προβληθούν απευθείας από το SSMS.

Προβολή προβλεπόμενων σχεδίων εκτέλεσης στο σχέδιο ApexSQL

• Κάντε κλικ στο κουμπί Νέο αίτημασε SSMS και επικολλήστε το κείμενο του ερωτήματος στο πλαίσιο κειμένου ερωτήματος. Κάντε δεξί κλικ και επιλέξτε "Εμφάνιση σχεδίου εκτέλεσης δείγματος" από το μενού περιβάλλοντος.

1. Το διάγραμμα σχεδίου εκτέλεσης θα εμφανίσει την καρτέλα Σχεδιασμός εκτέλεσης στην ενότητα αποτελεσμάτων. Στη συνέχεια, κάντε δεξί κλικ στο σχέδιο εκτέλεσης και επιλέξτε την επιλογή "Άνοιγμα στο σχέδιο ApexSQL" από το μενού περιβάλλοντος.

1. Το εκτιμώμενο σχέδιο εκτέλεσης θα ανοίξει στο σχέδιο ApexSQL και μπορεί να αναλυθεί για τη βελτιστοποίηση των ερωτημάτων.

Προβολή πραγματικών σχεδίων εκτέλεσης σε ένα σχέδιο ApexSQL

Για να προβάλετε το πραγματικό σχέδιο εκτέλεσης ερωτήματος, μεταβείτε στο δεύτερο βήμα που αναφέρθηκε προηγουμένως, αλλά τώρα, μόλις εμφανιστεί το εκτιμώμενο σχέδιο, κάντε κλικ στο κουμπί "Πραγματικό" στην κύρια γραμμή κορδέλας στο Πρόγραμμα ApexSQL.

Αφού κάνετε κλικ στο κουμπί Πραγματική, θα εμφανιστεί το πραγματικό σχέδιο εκτέλεσης με μια λεπτομερή προεπισκόπηση των παραμέτρων κόστους μαζί με άλλα δεδομένα σχεδίου εκτέλεσης.

Περισσότερες πληροφορίες σχετικά με την προβολή σχεδίων εκτέλεσης μπορείτε να βρείτε ακολουθώντας αυτόν τον σύνδεσμο.

Τα σχέδια ερωτημάτων μπορούν να ληφθούν από μια εκτεταμένη περίοδο λειτουργίας συμβάντων μέσω του συμβάντος query_post_execution_showplan. Ακολουθεί ένα παράδειγμα συνεδρίας XEvent:

/* Δημιουργήθηκε μέσω του προτύπου "Παρακολούθηση λεπτομερειών ερωτήματος". */ ΔΗΜΙΟΥΡΓΙΑ ΣΥΝΕΔΡΙΟΥ ΕΚΔΗΛΩΣΗΣ ΣΤΟΝ ΔΙΑΚΟΜΙΣΤΗ ΠΡΟΣΘΗΚΗ ΕΚΔΗΛΩΣΗΣ sqlserver.query_post_execution_showplan(ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_serversqert,sqlserver.query_plan_serversqert,s .tsql_ frame,sqlserver.tsql_stack)), / * Καταργήστε οποιοδήποτε από τα ακόλουθα συμβάντα (ή συμπεριλάβετε πρόσθετα συμβάντα) όπως θέλετε. */ ADD EVENT sqlserver.error_reported(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_server. qlserver.t sql_frame,sqlserver.tsql_stack ) WHERE (.(.,(4)) ΚΑΙ .(.,(0)))), ADD EVENT sqlserver.module_end(SET collect_statement=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_serverids, . plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,)(4.,ENT) sqlserver.rpc_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash.qsession_ersserver,sqlserver. l_ frame,sqlserver.tsql_stack) WHERE (. ( .,(4)) ΚΑΙ .(.,(0))), ΠΡΟΣΘΗΚΗ ΕΚΔΗΛΩΣΗΣ sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_serverer.plan_sqq. query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0)server_er_ENTsq. ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.database_id,sql_server. _st ack) WHERE (.(.,( 4 )) ΚΑΙ .(.,(0)))), ΠΡΟΣΘΗΚΗ ΕΚΔΗΛΩΣΗΣ sqlserver.sql_statement_completed(ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle. er.session_id , sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack) WHERE (.(.,(4)) AND .(.,(0))) ΠΡΟΣΘΗΚΗ ΣΤΟΧΟΥ πακέτου0.ring_buffer ΜΕ (MAX_MEMORY=409000000000000000000% _DISPATCH_L ATENCY =30 ΔΕΥΤΕΡΟΛΕΠΤΑ,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=ΚΑΝΕΝΑ,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO

Μόλις δημιουργηθεί η περίοδος λειτουργίας (σε SSMS), μεταβείτε στο Object Browser και μεταβείτε στη Διαχείριση | Εκτεταμένες εκδηλώσεις | Συνεδρίες. Κάντε δεξί κλικ στην περίοδο λειτουργίας "GetExecutionPlan" και εκτελέστε την. Κάντε δεξί κλικ σε αυτό και επιλέξτε "Παρακολούθηση Ζωντανών Δεδομένων".

Στη συνέχεια, ανοίξτε ένα νέο παράθυρο ερωτήματος και εκτελέστε ένα ή περισσότερα ερωτήματα. Εδώ είναι ένα για το AdventureWorks:

ΧΡΗΣΗ AdventureWorks? GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) FROM Production.Product AS p ΕΣΩΤΕΡΙΚΗ ΣΥΜΜΕΤΟΧΗ Πωλήσεις.SalesΠαραγγελίαΠροϊόνΤΥΟΠροϊόντα. Όνομα προϊόντος DESC; ΠΗΓΑΙΝΩ

Μετά από ένα ή δύο λεπτά, θα δείτε ορισμένα αποτελέσματα στην καρτέλα "GetExecutionPlan: Ζωντανά δεδομένα". Επιλέξτε ένα από τα συμβάντα query_post_execution_showplan στο πλέγμα και, στη συνέχεια, κάντε κλικ στην καρτέλα Σχέδιο ερωτήματος κάτω από το πλέγμα. Θα πρέπει να μοιάζει κάπως έτσι:

ΕΠΕΞΕΡΓΑΣΙΑ: Ο κώδικας και το στιγμιότυπο οθόνης XEvent δημιουργήθηκαν από το SQL/SSMS 2012 w/SP2. Εάν χρησιμοποιείτε SQL 2008/R2, μπορείτε να ρυθμίσετε ένα σενάριο για να το εκτελέσετε. Αλλά αυτή η έκδοση δεν έχει GUI, επομένως θα χρειαστεί να εξαγάγετε το αρχείο showplan XML, να το αποθηκεύσετε ως αρχείο *.sqlplan και να το ανοίξετε σε SSMS. Είναι δυσκίνητο. Τα XEvents δεν υπήρχαν στην SQL 2005 ή παλαιότερα. Επομένως, εάν δεν είστε σε SQL 2012 ή μεταγενέστερη έκδοση, θα πρότεινα ανεπιφύλακτα μία από τις άλλες απαντήσεις που δημοσιεύονται εδώ.

μερίδιο

Βελτιστοποίηση ερωτημάτων στον SQL Server 2005, στατιστικά στοιχεία βάσης δεδομένων SQL Server 2005, ΔΗΜΙΟΥΡΓΙΑ ΣΤΑΤΙΣΤΙΚΩΝ, ΕΝΗΜΕΡΩΣΗ ΣΤΑΤΙΣΤΙΚΩΝ, ΕΝΕΡΓΟΠΟΙΗΣΗ ΣΤΑΤΙΣΤΙΚΩΝ, σχέδια εκτέλεσης ερωτήματος, αριθμός λογικών αναγνώσεων, υποδείξεις βελτιστοποίησης, MAXDOP, OPTIMIZE FOR, tutorials (planguplanate_guidecutions)

Εάν όλες οι άλλες μέθοδοι βελτιστοποίησης της απόδοσης έχουν ήδη εξαντληθεί, τότε οι προγραμματιστές και οι διαχειριστές του SQL Server έχουν ένα τελευταίο απόθεμα στη διάθεσή τους - βελτιστοποίηση της εκτέλεσης μεμονωμένων ερωτημάτων. Για παράδειγμα, εάν η εργασία σας απαιτεί οπωσδήποτε επιτάχυνση της δημιουργίας μιας συγκεκριμένης αναφοράς, μπορείτε να αναλύσετε το ερώτημα που χρησιμοποιείται για τη δημιουργία αυτής της αναφοράς και να προσπαθήσετε να αλλάξετε το σχέδιό της εάν δεν είναι βέλτιστο.

Πολλοί ειδικοί έχουν μια διφορούμενη στάση απέναντι στη βελτιστοποίηση ερωτημάτων. Από τη μία πλευρά, η λειτουργία της ενότητας λογισμικού Query Optimizer, η οποία δημιουργεί σχέδια εκτέλεσης ερωτημάτων, προκαλεί πολλές δίκαιες επικρίσεις τόσο στον SQL Server 2000 όσο και στον SQL Server 2005. Το Query Optimizer συχνά δεν επιλέγει τα βέλτιστα σχέδια εκτέλεσης ερωτημάτων και σε ορισμένες περιπτώσεις χάνει σε παρόμοιες ενότητες από την Oracle και την Informix. Από την άλλη πλευρά, η μη αυτόματη βελτιστοποίηση ερωτημάτων είναι μια εξαιρετικά απαιτητική διαδικασία. Μπορείτε να αφιερώσετε πολύ χρόνο σε μια τέτοια βελτιστοποίηση και, στο τέλος, να ανακαλύψετε ότι τίποτα δεν βελτιστοποιήθηκε: το σχέδιο που προτάθηκε αρχικά από το Query Optimizer αποδείχθηκε το πιο βέλτιστο (αυτό συμβαίνει στις περισσότερες περιπτώσεις). Επιπλέον, μπορεί να συμβεί το σχέδιο εκτέλεσης ερωτήματος που δημιουργήσατε με μη αυτόματο τρόπο μετά από κάποιο χρονικό διάστημα (μετά την προσθήκη νέων πληροφοριών στη βάση δεδομένων) να αποδειχθεί ότι δεν είναι βέλτιστο και να μειώσει την απόδοση κατά την εκτέλεση ερωτημάτων.

Σημειώστε επίσης ότι το Query Optimizer απαιτεί σωστές στατιστικές πληροφορίες για να επιλέξει τα καλύτερα σχέδια ερωτημάτων. Επειδή, σύμφωνα με την εμπειρία του συγγραφέα, δεν γνωρίζουν όλοι οι διαχειριστές τι είναι, θα σας πούμε περισσότερα για τα στατιστικά στοιχεία.

Στατιστική- πρόκειται για ειδικές πληροφορίες υπηρεσίας σχετικά με τη διανομή δεδομένων σε στήλες πίνακα. Ας φανταστούμε, για παράδειγμα, ότι εκτελείται ένα ερώτημα που θα πρέπει να επιστρέψει όλους τους Ιβάνοφ που ζουν στην πόλη της Αγίας Πετρούπολης. Ας υποθέσουμε ότι το 90% των εγγραφών σε αυτόν τον πίνακα έχουν την ίδια τιμή στη στήλη Πόλη - "Αγία Πετρούπολη". Φυσικά, από την άποψη της εκτέλεσης ερωτήματος, είναι πρώτα πιο κερδοφόρο να επιλέξετε όλα τα Ivanov στον πίνακα (προφανώς δεν θα είναι 90%) και στη συνέχεια να ελέγξετε την τιμή της στήλης Πόληγια κάθε επιλεγμένη εγγραφή. Ωστόσο, για να μάθετε πώς κατανέμονται οι τιμές σε μια στήλη, πρέπει πρώτα να εκτελέσετε ένα ερώτημα. Επομένως, ο SQL Server εκκινεί ανεξάρτητα την εκτέλεση τέτοιων ερωτημάτων και, στη συνέχεια, αποθηκεύει πληροφορίες σχετικά με τη διανομή των δεδομένων (η οποία ονομάζεται στατιστική) στους πίνακες υπηρεσιών βάσης δεδομένων.

Για βάσεις δεδομένων SQL Server 2005, οι προεπιλεγμένες ρυθμίσεις είναι AUTO_CREATE_STATISTICSΚαι AUTO_UPDATE_STATISTICS. Σε αυτήν την περίπτωση, τα στατιστικά στοιχεία για τις στήλες της βάσης δεδομένων θα δημιουργηθούν και θα ενημερωθούν αυτόματα. Για τις μεγαλύτερες και πιο σημαντικές βάσεις δεδομένων, ενδέχεται οι λειτουργίες δημιουργίας και ενημέρωσης στατιστικών να επηρεάσουν την τρέχουσα εμπειρία χρήστη. Επομένως, για τέτοιες βάσεις δεδομένων, μερικές φορές αυτές οι παράμετροι απενεργοποιούνται και οι λειτουργίες δημιουργίας και ενημέρωσης στατιστικών εκτελούνται χειροκίνητα τη νύχτα. Οι εντολές που χρησιμοποιούνται για αυτό είναι ΔΗΜΙΟΥΡΓΗΣΤΕ ΣΤΑΤΙΣΤΙΚΑΚαι ΕΝΗΜΕΡΩΣΗ ΣΤΑΤΙΣΤΙΚΩΝ.

Τώρα ας μιλήσουμε για τη βελτιστοποίηση ερωτημάτων.

Το πρώτο πράγμα που πρέπει να κάνετε είναι να βρείτε εκείνα τα ερωτήματα που υπόκεινται κυρίως σε βελτιστοποίηση. Ο ευκολότερος τρόπος για να το κάνετε αυτό είναι με τη βοήθεια ενός προφίλ, ορίζοντας ένα φίλτρο για τη διάρκεια του αιτήματος (φίλτρο Διάρκειαστο παράθυρο ΕπεξεργασίαΦίλτρο(Επεξεργασία φίλτρου), το οποίο μπορεί να ανοίξει χρησιμοποιώντας το κουμπί ΣτήληΦίλτραστην καρτέλα ΕκδηλώσειςΕπιλογήπαράθυρο ιδιοτήτων ιχνηλάτησης συνεδρίας). Για παράδειγμα, οι υποψήφιοι για βελτιστοποίηση μπορεί να περιλαμβάνουν ερωτήματα των οποίων ο χρόνος εκτέλεσης ήταν μεγαλύτερος από 5 δευτερόλεπτα. Μπορείτε επίσης να χρησιμοποιήσετε τις πληροφορίες ερωτήματος που παρέχονται από το Database Tuning Advisor.

Στη συνέχεια, πρέπει να ελέγξετε εάν η παράμετρος έχει οριστεί για τις συνδέσεις, τις αποθηκευμένες διαδικασίες και τις λειτουργίες σας ΟΧΙ. Μπορείτε να το εγκαταστήσετε χρησιμοποιώντας την εντολή ΡΥΘΜΙΣΗ ΕΝΕΡΓΟΠΟΙΗΣΗΣ NOCOUNT. Κατά τη ρύθμιση αυτής της παραμέτρου, πρώτα, η επιστροφή από τον διακομιστή και η εμφάνιση πληροφοριών σχετικά με τον αριθμό των σειρών στα αποτελέσματα του ερωτήματος είναι απενεργοποιημένες (δηλ. η σειρά "Ν σειρές επηρεάζονται"στην καρτέλα Μηνύματα(μηνύματα C) παράθυρα για εργασία με κώδικα κατά την εκτέλεση μιας αίτησης στο Management Studio). Δεύτερον, η μετάδοση ενός ειδικού μηνύματος διακομιστή είναι απενεργοποιημένη DONE_IN_PROC, το οποίο επιστρέφεται από προεπιλογή για κάθε αποθηκευμένο βήμα διαδικασίας. Όταν καλείτε τις περισσότερες αποθηκευμένες διαδικασίες, χρειάζεστε μόνο το αποτέλεσμα της εκτέλεσής τους και κανείς δεν ενδιαφέρεται για τον αριθμό των σειρών που υποβάλλονται σε επεξεργασία για κάθε στάδιο. Επομένως, ορίζοντας την παράμετρο ΟΧΙγια αποθηκευμένες διαδικασίες μπορεί να βελτιώσει σοβαρά την απόδοσή τους. Η ταχύτητα εκτέλεσης των κανονικών ερωτημάτων αυξάνεται επίσης, αλλά σε μικρότερο βαθμό (έως 10%).

Μετά από αυτό, μπορείτε να αρχίσετε να εργάζεστε με σχέδια εκτέλεσης ερωτημάτων.

Ο ευκολότερος τρόπος προβολής του σχεδίου εκτέλεσης ερωτήματος είναι από το SQL Server Management Studio. Για να λάβετε πληροφορίες σχετικά με το αναμενόμενο σχέδιο εκτέλεσης ερωτήματος, μπορείτε να χρησιμοποιήσετε το μενού Ερώτηση(Ερώτημα) επιλέξτε εντολή ΑπεικόνισηΕκτιμώμενοςΕκτέλεσηΣχέδιο(Εμφάνιση σχεδίου αναμενόμενης εκτέλεσης). Εάν θέλετε να μάθετε το πραγματικό σχέδιο για την εκτέλεση ενός ερωτήματος, μπορείτε να ορίσετε την παράμετρο στο ίδιο μενού πριν την εκτελέσετε ΠεριλαμβάνωΠραγματικόςΕκτέλεσηΣχέδιο(Συμπεριλάβετε το πραγματικό σχέδιο εκτέλεσης). Σε αυτήν την περίπτωση, μετά την εκτέλεση του ερωτήματος, θα εμφανιστεί μια άλλη καρτέλα στο παράθυρο αποτελεσμάτων στο SQL Server Management Studio ΕκτέλεσηΣχέδιο(Σχέδιο εκτέλεσης), το οποίο θα εμφανίσει το πραγματικό σχέδιο εκτέλεσης ερωτήματος. Όταν τοποθετείτε το ποντίκι σας πάνω από οποιοδήποτε από τα στάδια, μπορείτε να λάβετε πρόσθετες πληροφορίες σχετικά με αυτό (Εικ. 11.15).

Ρύζι. 11.15.Σχέδιο εκτέλεσης ερωτήματος στο SQL Server Management Studio

Σε ένα σχέδιο εκτέλεσης ερωτήματος, όπως μπορείτε να δείτε στο σχήμα, μπορεί να υπάρχουν πολλά στοιχεία. Η κατανόησή τους, καθώς και η πρόταση ενός διαφορετικού σχεδίου εκτέλεσης, είναι ένα αρκετά δύσκολο έργο. Πρέπει να ειπωθεί ότι κάθε ένα από τα πιθανά στοιχεία είναι βέλτιστο στη δική του κατάσταση. Επομένως, συνήθως τα στάδια της βελτιστοποίησης ερωτημάτων μοιάζουν με αυτό:

q Αρχικά, στο παράθυρο Management Studio, εκτελέστε την εντολή ΡΥΘΜΙΣΗ ΣΤΑΤΙΣΤΙΚΩΝ IO ON. Ως αποτέλεσμα, μετά από κάθε εκτέλεση του αιτήματος, θα εμφανίζονται πρόσθετες πληροφορίες. Σε αυτό μας ενδιαφέρει η τιμή μόνο μιας παραμέτρου - Λογικές αναγνώσεις. Αυτή η παράμετρος σημαίνει τον αριθμό των λογικών αναγνώσεων κατά την εκτέλεση ερωτημάτων, δηλαδή πόσες λειτουργίες ανάγνωσης έπρεπε να εκτελεστούν κατά την εκτέλεση ενός δεδομένου ερωτήματος χωρίς να λαμβάνεται υπόψη η επιρροή της κρυφής μνήμης (ο αριθμός των αναγνώσεων τόσο από την κρυφή μνήμη όσο και από το δίσκο). Αυτή είναι η πιο σημαντική παράμετρος. Ο αριθμός των φυσικών αναγνώσεων (αναγνώσεις μόνο από το δίσκο) δεν είναι πολύ αντιπροσωπευτική πληροφορία, καθώς εξαρτάται από το αν υπήρχαν προηγούμενες προσβάσεις σε αυτούς τους πίνακες ή όχι. Τα στατιστικά χρόνου είναι επίσης μεταβλητά και εξαρτώνται από άλλες λειτουργίες που εκτελεί ο διακομιστής εκείνη τη στιγμή. Αλλά ο αριθμός των λογικών αναγνώσεων είναι ο πιο αντικειμενικός δείκτης, ο οποίος επηρεάζεται λιγότερο από πρόσθετους παράγοντες.

q στη συνέχεια προσπαθήστε να αλλάξετε το σχέδιο εκτέλεσης ερωτήματος και μάθετε τον συνολικό αριθμό λογικών αναγνώσεων για κάθε σχέδιο. Συνήθως, το σχέδιο εκτέλεσης ερωτήματος αλλάζει χρησιμοποιώντας υποδείξεις βελτιστοποίησης. Λένε ρητά στον βελτιστοποιητή ποιο σχέδιο εκτέλεσης να χρησιμοποιήσει.

Υπάρχουν πολλές συμβουλές βελτιστοποίησης στον SQL Server 2005. Μπορείτε να διαβάσετε πληροφορίες σχετικά με αυτά στα Βιβλία Online (στη λίστα στην καρτέλα Δείκτης(Ευρετήριο) πρέπει να επιλεγεί ΕρώτησηΣυμβουλές [SQLΥπηρέτης](Υποδείξεις ερωτήματος), ΣυμμετοχήΣυμβουλές(Συμβουλές συμμετοχής) ή ΤραπέζιΣυμβουλές [SQLΥπηρέτης](Υποδείξεις πίνακα)). Οι πιο συχνά χρησιμοποιούμενες συμβουλές είναι:

q NOLOCK, ΚΟΥΠΑΣΤΗ, ΠΑΓΚΛΟΚ, ΠΙΝΑΚΑΣ, ΚΡΑΤΗΡΙΟ, READCOMMITTEDLOCK, UPDLOCK, XLOCK- αυτές οι συμβουλές χρησιμοποιούνται για τη διαχείριση κλειδαριών (βλ. ενότητα 11.5.7);

q ΓΡΗΓΟΡΑ αριθμός γραμμών - θα επιλεγεί ένα σχέδιο εκτέλεσης ερωτήματος στο οποίο ο καθορισμένος αριθμός σειρών (η πρώτη από την αρχή του συνόλου εγγραφών) θα εμφανίζεται όσο το δυνατόν γρηγορότερα. Εάν ο χρήστης χρειάζεται ακριβώς τις πρώτες εγγραφές (για παράδειγμα, τις πιο πρόσφατες παραγγελίες), τότε αυτή η υπόδειξη μπορεί να χρησιμοποιηθεί για τη φόρτωσή τους στο παράθυρο της εφαρμογής όσο το δυνατόν γρηγορότερα.

q ΑΝΑΓΚΑΣΤΙΚΗ ΔΙΑΤΑΞΗ- η ένωση πινάκων κατά την εκτέλεση ενός ερωτήματος θα εκτελείται ακριβώς με τη σειρά με την οποία αυτοί οι πίνακες παρατίθενται στο ερώτημα.

q MAXDOP(από Maximum Degree of Parallelism - ο μέγιστος βαθμός παραλληλοποίησης μιας αίτησης) - χρησιμοποιώντας αυτήν την υπόδειξη, υποδεικνύεται ο μέγιστος αριθμός επεξεργαστών που μπορούν να χρησιμοποιηθούν για την εκτέλεση της αίτησης. Συνήθως αυτή η υπόδειξη χρησιμοποιείται σε δύο περιπτώσεις:

· όταν λόγω εναλλαγής μεταξύ επεξεργαστών ( συμφραζόμεναεναλλαγή) η ταχύτητα εκτέλεσης ερωτήματος μειώνεται σημαντικά. Αυτή η συμπεριφορά ήταν τυπική για τον SQL Server 2000 σε συστήματα πολλαπλών επεξεργαστών.

· όταν θέλετε κάποιο βαρύ αίτημα να έχει ελάχιστο αντίκτυπο στην τρέχουσα εμπειρία χρήστη.

q ΒΕΛΤΙΣΤΟΠΟΙΗΣΤΕ ΓΙΑ- αυτή η υπόδειξη σάς επιτρέπει να καθορίσετε ότι το αίτημα έχει βελτιστοποιηθεί για μια συγκεκριμένη τιμή της παραμέτρου που της έχει διαβιβαστεί (για παράδειγμα, για την τιμή φίλτρου για ΟΠΟΥ);

q ΣΧΕΔΙΟ ΧΡΗΣΗΣ- αυτή είναι η πιο δυνατή ευκαιρία. Χρησιμοποιώντας μια τέτοια υπόδειξη, μπορείτε να ορίσετε ρητά το σχέδιο εκτέλεσης ερωτήματος περνώντας το σχέδιο ως τιμή συμβολοσειράς σε μορφή XML. Ιχνος ΣΧΕΔΙΟ ΧΡΗΣΗΣεμφανίστηκε μόνο στον SQL Server 2005 (σε προηγούμενες εκδόσεις ήταν δυνατό να καθοριστούν ρητά σχέδια εκτέλεσης ερωτημάτων, αλλά αυτό γινόταν με άλλα μέσα). Ένα σχέδιο σε μορφή XML μπορεί να γραφτεί χειροκίνητα ή να δημιουργηθεί αυτόματα (για παράδειγμα, κάνοντας δεξί κλικ στην οθόνη γραφικών με το σχέδιο εκτέλεσης που φαίνεται στην Εικ. 11.15 και επιλέγοντας την εντολή στο μενού περιβάλλοντος ΑποθηκεύσετεΕκτέλεσηΣχέδιοΟπως και(Αποθήκευση σχεδίου εκτέλεσης ως)).

Ο SQL Server 2005 εισάγει μια σημαντική νέα δυνατότητα που σας επιτρέπει να αλλάζετε με μη αυτόματο τρόπο το σχέδιο εκτέλεσης ερωτήματος χωρίς να χρειάζεται να παραβιάζετε το κείμενο του ερωτήματος. Συχνά συμβαίνει ότι ο κωδικός αιτήματος δεν μπορεί να αλλάξει: είναι ενσωματωμένος στον κώδικα της μεταγλωττισμένης εφαρμογής. Για την καταπολέμηση αυτού του προβλήματος, ο SQL Server 2005 εισήγαγε την αποθηκευμένη διαδικασία sp_create_plan_guide. Σας επιτρέπει να δημιουργήσετε τα λεγόμενα Οδηγοί σχεδίου εκτέλεσης (σχέδιοοδηγούς), το οποίο θα εφαρμοστεί αυτόματα σε αιτήματα που ταιριάζουν.

Εάν αναλύετε ερωτήματα που αποστέλλονται σε μια βάση δεδομένων από μια εφαρμογή, είναι λογικό να δώσετε πρώτα προσοχή στα ακόλουθα σημεία:

q πόσο συχνά εμφανίζεται η λειτουργία στα σχέδια εκτέλεσης ερωτημάτων ΤραπέζιΣάρωση(Πλήρης σάρωση πίνακα). Μπορεί κάλλιστα να αποδειχθεί ότι η πρόσβαση σε έναν πίνακα χρησιμοποιώντας ευρετήρια θα είναι πιο αποτελεσματική.

q εάν χρησιμοποιούνται δρομείς στον κώδικα. Οι δρομείς είναι πολύ απλοί όσον αφορά τη σύνταξη του προγράμματος, αλλά εξαιρετικά αναποτελεσματικός από την άποψη της απόδοσης. Πολύ συχνά μπορείτε να αποφύγετε τη χρήση δρομέων χρησιμοποιώντας άλλες συντακτικές κατασκευές και να έχετε μεγάλο κέρδος στην ταχύτητα.

q εάν ο κώδικας χρησιμοποιεί προσωρινούς πίνακες ή έναν τύπο δεδομένων Τραπέζι. Η δημιουργία προσωρινών πινάκων και η εργασία μαζί τους απαιτεί πολλούς πόρους, επομένως θα πρέπει να τους αποφύγετε εάν είναι δυνατόν.

q Εκτός από τη δημιουργία προσωρινών πινάκων, η αλλαγή της δομής τους απαιτεί επίσης σημαντική κατανάλωση πόρων του συστήματος. Επομένως, οι εντολές για την αλλαγή της δομής των προσωρινών πινάκων θα πρέπει να προσελκύσουν αμέσως την προσοχή σας. Συνήθως είναι δυνατή η άμεση δημιουργία ενός προσωρινού πίνακα με όλες τις απαραίτητες στήλες.

q μερικές φορές τα ερωτήματα επιστρέφουν περισσότερα δεδομένα από αυτά που πραγματικά χρειάζεται η εφαρμογή (επιπλέον στήλες ή σειρές). Φυσικά, αυτό δεν βελτιώνει την παραγωγικότητα.

q εάν η εφαρμογή στέλνει εντολές στον διακομιστή ΕΚΤΕΛΕΣΗ, τότε είναι λογικό να σκεφτείτε να τα αντικαταστήσετε με μια κλήση αποθηκευμένης διαδικασίας sp_executesql. Έχει πλεονεκτήματα απόδοσης σε σχέση με μια κανονική εντολή ΕΚΤΕΛΕΣΗ;

q Βελτιώσεις απόδοσης μερικές φορές μπορούν να επιτευχθούν εξαλείφοντας την ανάγκη για εκ νέου μεταγλώττιση αποθηκευμένων διαδικασιών και δημιουργία νέων σχεδίων εκτέλεσης ερωτημάτων. Πρέπει να δώσετε προσοχή στη χρήση των παραμέτρων, προσπαθήστε να μην αναμιγνύετε εντολές DML και DDL στον κώδικα της αποθηκευμένης διαδικασίας και βεβαιωθείτε ότι οι παράμετροι σύνδεσης ΡΥΘΜΙΣΗ ANSI_ΠΡΟΕΠΙΛΟΓΙΚΩΝ, ΣΕΤ ANSI_NULLS, ΡΥΘΜΙΣΗ ANSI_PADDING, ΣΕΤ ANSI_WARNINGSΚαι ΣΕΤ CONCAT_NULL_YIELDS_NULLδεν έχουν αλλάξει μεταξύ των αιτημάτων (οποιαδήποτε αλλαγή σε τέτοιες παραμέτρους ακυρώνει τα παλιά σχέδια εκτέλεσης). Συνήθως, το πρόβλημα μπορεί να προκύψει όταν αυτές οι παράμετροι ορίζονται σε επίπεδο μεμονωμένου αιτήματος ή σε αποθηκευμένο κωδικό διαδικασίας.

Σημειώστε ότι σε κάθε περίπτωση, η μη αυτόματη δημιουργία σχεδίων εκτέλεσης ερωτημάτων και η χρήση υποδείξεων είναι η έσχατη λύση και θα πρέπει να αποφεύγεται εάν είναι δυνατόν.

σχέδιο εκτέλεσης ερωτημάτων SQL,ή σχέδιο ερωτήματος, είναι μια ακολουθία βημάτων ή εντολών DBMS που απαιτούνται για την εκτέλεση ενός ερωτήματος SQL. Σε κάθε βήμα, η λειτουργία που ξεκίνησε αυτό το βήμα εκτέλεσης ερωτήματος SQL ανακτά σειρές δεδομένων που μπορούν να σχηματίσουν το τελικό αποτέλεσμα ή να χρησιμοποιηθούν για περαιτέρω επεξεργασία. Οι οδηγίες του σχεδίου εκτέλεσης ερωτημάτων SQL αντιπροσωπεύονται ως μια ακολουθία πράξεων που εκτελούνται από το DBMS FOR SQL δηλώσεις SELECT, INSERT, διαγραφή και εκσυγχρονίζω. Τα περιεχόμενα ενός σχεδίου ερωτήματος αντιπροσωπεύονται συνήθως σε μια δομή δέντρου και περιλαμβάνουν τις ακόλουθες πληροφορίες:

• τη σειρά σύνδεσης πηγών δεδομένων (πίνακες, προβολές κ.λπ.)·
• μέθοδος πρόσβασης για κάθε πηγή δεδομένων·
• μέθοδοι σύνδεσης πηγών δεδομένων·
• λειτουργίες περιορισμού της επιλογής, ταξινόμησης και συγκέντρωσης δεδομένων·
• κόστος και σοβαρότητα κάθε επέμβασης·
• πιθανή χρήση κατάτμησης και παραλληλισμού. Οι πληροφορίες που παρέχονται από το σχέδιο εκτέλεσης ερωτημάτων SQL επιτρέπουν στον προγραμματιστή να δει ποιες προσεγγίσεις και μεθόδους επιλέγει ο βελτιστοποιητής για την εκτέλεση λειτουργιών SQL.

Ερμηνεία του σχεδίου εκτέλεσης ερωτημάτων SQL

Η οπτικοποίηση του σχεδίου εκτέλεσης ενός ερωτήματος SQL εξαρτάται από εργαλεία και εργαλεία ανάπτυξης, τα οποία μπορεί είτε να αποτελούν μέρος του DBMS του οποίου το ερώτημα ενδιαφέρει για ανάλυση, είτε να είναι ξεχωριστά εμπορικά ή ελεύθερα διανεμημένα προϊόντα λογισμικού που δεν σχετίζονται άμεσα με ένα συγκεκριμένο DBMS κατασκευαστής. Η χρήση ενός ή άλλου εργαλείου οπτικοποίησης σχεδίου ερωτημάτων συνήθως δεν επηρεάζει σημαντικά την αντίληψη αυτού που περιγράφει το παρουσιαζόμενο σχέδιο ερωτημάτων. Ο καθοριστικός παράγοντας στη διαδικασία ανάλυσης της διαδρομής που θα ακολουθήσει ο βελτιστοποιητής κατά την εκτέλεση ενός συγκεκριμένου ερωτήματος είναι η ικανότητα σωστής ερμηνείας των πληροφοριών που παρουσιάζονται στο σχέδιο ερωτήματος.

Όπως ήδη αναφέρθηκε, ένα σχέδιο ερωτημάτων SQL έχει μια δενδρική δομή που περιγράφει όχι μόνο την ακολουθία εκτέλεσης των λειτουργιών SQL, αλλά και τις σχέσεις μεταξύ αυτών των λειτουργιών. Κάθε κόμβος στο δέντρο σχεδίου ερωτήματος είναι μια λειτουργία, όπως μια ταξινόμηση ή μια μέθοδος πρόσβασης σε πίνακα. Υπάρχει μια σχέση γονέα-παιδιού μεταξύ των κόμβων. Οι σχέσεις γονέα-παιδιού διέπονται από τους ακόλουθους κανόνες:

• ένας γονέας μπορεί να έχει ένα ή περισσότερα παιδιά.
• ένα παιδί έχει μόνο έναν γονέα.
• μια λειτουργία που δεν έχει γονική λειτουργία είναι η κορυφή του δέντρου.
• Ανάλογα με τη μέθοδο οπτικοποίησης του σχεδίου ερωτήματος SQL, το παιδί τοποθετείται με κάποια εσοχή σε σχέση με τον γονέα. Οι απόγονοι του ενός γονέα βρίσκονται στην ίδια απόσταση από τον γονέα τους.

Ας ρίξουμε μια πιο προσεκτική ματιά στις πληροφορίες που παρέχονται από το σχέδιο εκτέλεσης ερωτημάτων SQL. Τα παραδείγματα που δίνονται πραγματοποιήθηκαν στο περιβάλλον Oracle DBMS. Το Oracle SQL Developer χρησιμοποιήθηκε ως εργαλείο για την εκτέλεση ερωτημάτων και την οπτικοποίηση του σχεδίου ερωτημάτων SQL. Ένα τμήμα ενός σχεδίου ερωτήματος SQL φαίνεται στο Σχήμα. 10.11.

Λειτουργία Id I

• 0RDER_ITEMS

PR0DUCT_INF0RMATI0N_PK ΠΛΗΡΟΦΟΡΙΕΣ ΠΡΟΪΟΝΤΟΣ

ΕΠΙΛΟΓΗ ΔΗΛΩΣΗΣ ΤΑΞΙΝΟΜΗΣΗ ΣΕΙΡΑ ΚΑΤΑ ΕΝΤΟΛΕΜΕΝΟΥΣ ΒΡΟΧΟΥΣ ΕΦΩΛΕΜΕΝΟΥΣ ΒΡΟΧΟΥΣ ΠΡΟΣΒΑΣΗ ΠΙΝΑΚΑΣ ΠΛΗΡΗΣ ΕΥΡΕΤΗΡΙΟ ΜΟΝΑΔΙΚΗ ΠΡΟΣΒΑΣΗ ΠΙΝΑΚΑ ΣΑΡΩΣΗΣ ΚΑΤΑ ΣΕΙΡΑ ΕΥΡΕΤΗΡΙΟΥ

Ρύζι. 10.11. Τμήμα ενός σχεδίου εκτέλεσης ερωτημάτων SQL στο περιβάλλον Oracle DBMS

Χρησιμοποιώντας τους κανόνες σχέσεων των λειτουργιών του σχεδίου ερωτημάτων, μπορούμε να ορίσουμε την ακόλουθη επίσημη περιγραφή τους.

Η λειτουργία 0 είναι η ρίζα του δέντρου σχεδίου ερωτήματος. Η ρίζα έχει ένα παιδί: λειτουργία 1.

Λειτουργία 1 - η λειτουργία έχει ένα παιδί: λειτουργία 2.

Λειτουργία 2 - η επέμβαση έχει δύο παιδιά: λειτουργία 3 και λειτουργία 6.

Λειτουργία 3 - η επέμβαση έχει δύο παιδιά: λειτουργία 4 και λειτουργία 5.

Λειτουργία 4 - η επέμβαση δεν έχει παιδιά.

Λειτουργία 5 - η επέμβαση δεν έχει παιδιά.

Λειτουργία 6 - η επέμβαση δεν έχει παιδιά.

Η αλληλεπίδραση γονέα-παιδιού μεταξύ λειτουργιών σχεδίου ερωτήματος φαίνεται στο Σχ. 10.12.

Οι λειτουργίες που εκτελούνται σε ένα σχέδιο ερωτήματος μπορούν να χωριστούν σε τρεις τύπους: αυτόνομες, μη δεσμευμένες λειτουργίες σύνδεσης και λειτουργίες συνδεδεμένης σύνδεσης (Εικόνα 10.13).

Αυτονόμος		Λειτουργίες άσχετες		Σχετικές Λειτουργίες
επιχειρήσεις		ενώσεις		ενώσεις

Ρύζι. 10.12.

Ρύζι. 10.13.

Αυτόνομες λειτουργίες -Πρόκειται για επεμβάσεις που έχουν το πολύ μία επέμβαση για παιδιά.

Οι ακόλουθοι κανόνες με τους οποίους εκτελούνται αυτόνομες λειτουργίες μπορούν να διατυπωθούν ως εξής.

• 2. Κάθε θυγατρική λειτουργία εκτελείται μόνο μία φορά.
• 3. Κάθε θυγατρική λειτουργία επιστρέφει το αποτέλεσμά της στη γονική λειτουργία.

Στο Σχ. Το Σχήμα 10.14 δείχνει το σχέδιο για το ακόλουθο ερώτημα:

ΕΠΙΛΟΓΗ o.order_id ,o.order_status ΑΠΟ παραγγελίες o ORDER BY o.order_status

Αυτό το ερώτημα περιέχει μόνο αυτόνομες λειτουργίες.

Λαμβάνοντας υπόψη τους κανόνες για την παρακολούθηση αυτόνομων λειτουργιών, η σειρά εκτέλεσής τους θα είναι η εξής.

• 1. Σύμφωνα με τον κανόνα της παρακολούθησης αυτόνομων πράξεων Νο. 1, θα εκτελεστεί πρώτα η πράξη με ID = 2. Όλες οι σειρές του πίνακα παραγγελιών διαβάζονται διαδοχικά.
• 2. Στη συνέχεια, εκτελείται η πράξη με ID = 1. Οι σειρές που επιστρέφονται από την πράξη με ID = 2 ταξινομούνται σύμφωνα με τις συνθήκες της ρήτρας ταξινόμησης ORDER BY.
• 3. Εκτελείται η λειτουργία με ID = 0. Το σύνολο δεδομένων που προκύπτει επιστρέφεται.

Χωρίς δεσμεύσεις της Ένωσης

Χωρίς δεσμεύσεις της Ένωσηςείναι λειτουργίες που έχουν περισσότερες από μία θυγατρικές πράξεις που εκτελούν ανεξάρτητα. Παράδειγμα: HASH JOIN, MERGE JOIN, ΤΟΜΟΣΥΝΗ, ΜΕΙΟΝ, ΕΝΩΣΗ ΟΛΩΝ.

Οι ακόλουθοι κανόνες με τους οποίους λειτουργούν οι λειτουργίες αδέσμευτης ένωσης μπορούν να διατυπωθούν ως εξής.

• 1. Η θυγατρική λειτουργία εκτελείται πριν από τη γονική λειτουργία.
• 2. Οι θυγατρικές λειτουργίες εκτελούνται διαδοχικά, ξεκινώντας με τη μικρότερη τιμή ID λειτουργίας σε αύξουσα σειρά αυτών των τιμών.
• 3. Πριν ξεκινήσει κάθε επόμενη παιδική λειτουργία, η τρέχουσα λειτουργία πρέπει να ολοκληρωθεί πλήρως.
• 4. Κάθε θυγατρική λειτουργία εκτελείται μόνο μία φορά, ανεξάρτητα από άλλες θυγατρικές λειτουργίες.
• 5. Κάθε θυγατρική λειτουργία επιστρέφει το αποτέλεσμά της στη γονική λειτουργία.

Στο Σχ. Το Σχήμα 10.15 δείχνει το σχέδιο για το ακόλουθο ερώτημα:

ΕΠΙΛΟΓΗ o.order_id από παραγγελίες o UNION ALL

ΕΠΙΛΕΞΤΕ oi.order_id από order_items oi

Αυτό το ερώτημα περιέχει μια μη δεσμευμένη λειτουργία σύνδεσης ΕΝΩΣΗ όλα. Οι υπόλοιπες δύο λειτουργίες είναι αυτόνομες.

Ρύζι. 10.15. Μη δεσμευμένες λειτουργίες σύνδεσης, σχέδιο ερωτήματος

	1 ΕΠΙΛΟΓΗ ΔΗΛΩΣΗΣ I

Λαμβάνοντας υπόψη τους κανόνες για την παρακολούθηση άσχετων λειτουργιών σύνδεσης, η σειρά εκτέλεσής τους θα είναι η εξής.

• 1. Σύμφωνα με τους κανόνες 1 και 2 για τις ακόλουθες άσχετες λειτουργίες σύνδεσης, θα εκτελεστεί πρώτα η λειτουργία με ID = 2. Όλες οι σειρές του πίνακα παραγγελιών διαβάζονται διαδοχικά.
• 2. Σύμφωνα με τον κανόνα 5, η λειτουργία με ID = 2 επιστρέφει τις σειρές της γονικής λειτουργίας με ID = 1 που διαβάζεται στο βήμα 1.
• 3. Η λειτουργία με ID = 3 θα αρχίσει να εκτελείται μόνο όταν τελειώσει η πράξη με ID = 2.
• 4. Μετά την ολοκλήρωση της λειτουργίας με ID = 2, ξεκινά η λειτουργία με ID = 3. Όλες οι σειρές του πίνακα order_item διαβάζονται διαδοχικά.
• 5. Σύμφωνα με τον κανόνα 5, η λειτουργία με ID = 3 επιστρέφει τις σειρές της γονικής λειτουργίας με ID = 1 που διαβάζεται στο βήμα 4.
• 6. Η λειτουργία με ID = 1 δημιουργεί ένα σύνολο δεδομένων αποτελεσμάτων με βάση τα δεδομένα που λαμβάνονται από όλες τις θυγατρικές λειτουργίες της (με ID = 2 και ID = 3).
• 7. Εκτελείται η λειτουργία με ID = 0. Επιστρέφεται το σύνολο δεδομένων που προκύπτει.

Έτσι, μπορεί να σημειωθεί ότι η λειτουργία ανεξάρτητης σύνδεσης εκτελεί τις θυγατρικές λειτουργίες της διαδοχικά.

Συνδεδεμένες λειτουργίες σύνδεσης

Συνδεδεμένες λειτουργίες σύνδεσης -Πρόκειται για λειτουργίες που έχουν περισσότερες από μία θυγατρικές λειτουργίες, με μία από τις λειτουργίες να ελέγχει την εκτέλεση των άλλων. Παράδειγμα: ένθετοι βρόχοι, ενημέρωση.

Οι ακόλουθοι κανόνες με τους οποίους λειτουργούν οι λειτουργίες αλυσιδωτής σύνδεσης μπορούν να διατυπωθούν ως εξής.

• 1. Η θυγατρική λειτουργία εκτελείται πριν από τη γονική λειτουργία.
• 2. Η θυγατρική λειτουργία με τον χαμηλότερο αριθμό λειτουργίας (ID) ελέγχει την εκτέλεση των υπόλοιπων θυγατρικών λειτουργιών.
• 3. Οι θυγατρικές λειτουργίες που έχουν μια κοινή γονική λειτουργία εκτελούνται ξεκινώντας με τη χαμηλότερη τιμή του αναγνωριστικού της λειτουργίας σε αύξουσα σειρά αυτών των τιμών. Οι υπόλοιπες θυγατρικές λειτουργίες ΔΕΝ εκτελούνται διαδοχικά.
• 4. Μόνο η πρώτη θυγατρική λειτουργία εκτελείται μία φορά. Όλες οι άλλες θυγατρικές λειτουργίες εκτελούνται πολλές φορές ή καθόλου.

Στο Σχ. Το Σχήμα 10.16 δείχνει το σχέδιο για το ακόλουθο ερώτημα:

ΑΠΟ παραγγελία_στοιχεία oi, παραγγελίες ο

ΠΟΥ o.order_id= oi.order_id

ΚΑΙ oi.product_id>100

ΚΑΙ o.customer_id μεταξύ 100 και 1000

Αυτό το ερώτημα περιέχει μια σχετική λειτουργία σύνδεσης, NESTED BOOPS.

Λειτουργία Id I

		ΕΠΙΛΟΓΗ ΔΗΛΩΣΗΣ |

Ρύζι. 10.16. Συνδεδεμένες λειτουργίες σύνδεσης, σχέδιο ερωτήματος

Λαμβάνοντας υπόψη τους κανόνες για την παρακολούθηση των λειτουργιών αλυσιδωτής σύνδεσης, η ακολουθία της εκτέλεσής τους θα είναι η εξής.

• 1. Σύμφωνα με τους κανόνες 1 και 2 των επόμενων λειτουργιών αλυσιδωτής σύνδεσης, πρέπει πρώτα να εκτελεστεί η λειτουργία με ID = 2. Ωστόσο, οι λειτουργίες με 1D = 2 και 1D = 3 είναι αυτόνομες και σύμφωνα με τον κανόνα 1 των επόμενων αυτόνομων λειτουργιών, Πρώτα θα εκτελεστεί η λειτουργία με ID = 2. ID = 3. Η περιοχή ευρετηρίου ORDCUSTOMERIX προβάλλεται με βάση την συνθήκη: o. αναγνωριστικό πελάτη μεταξύ 100 και 1000.
• 2. Η λειτουργία με ID=3 επιστρέφει στη γονική λειτουργία (με Ш=2) μια λίστα με αναγνωριστικά σειρών Rowld που ελήφθησαν στο βήμα 1.
• 3. Η λειτουργία με ID = 2 διαβάζει σειρές στον πίνακα παραγγελιών στις οποίες η τιμή Rowld ταιριάζει με τη λίστα των τιμών Rowld που ελήφθησαν στο βήμα 2.
• 4. Η λειτουργία με ID = 2 επιστρέφει τις σειρές ανάγνωσης της γονικής λειτουργίας (με ID = 1).
• 5. Για κάθε σειρά που επιστρέφεται από την πράξη με ID = 2, εκτελείται η δεύτερη θυγατρική πράξη (με ID = 4) της πράξης ένθετες θηλιές. Δηλαδή, για κάθε σειρά που επιστρέφεται από μια λειτουργία με ID = 2, εκτελείται μια πλήρης διαδοχική σάρωση του πίνακα order_items για να βρεθεί μια αντιστοιχία στο χαρακτηριστικό join.
• 6. Το βήμα 5 επαναλαμβάνεται όσες φορές είναι ο αριθμός των σειρών που επιστρέφονται από την πράξη με ID = 2.
• 7. Μια πράξη με ID = 1 επιστρέφει τα αποτελέσματα της γονικής λειτουργίας (με ID = 0).
• 8. Εκτελείται η λειτουργία με ID = 0. Επιστρέφεται το σύνολο δεδομένων που προκύπτει.

Ανάλογα με την πολυπλοκότητα του αναλυόμενου ερωτήματος, το σχέδιο εκτέλεσής του μπορεί να έχει μια αρκετά περίπλοκη δομή, η οποία με την πρώτη ματιά φαίνεται δύσκολο να ερμηνευτεί. Η μεθοδική εφαρμογή των κανόνων που περιγράφονται παραπάνω και η αποσύνθεση των λειτουργιών θα σας επιτρέψουν να αναλύσετε αποτελεσματικά το σχέδιο εκτέλεσης ενός ερωτήματος SQL οποιασδήποτε πολυπλοκότητας. Ας δούμε ένα παράδειγμα ερωτήματος που δημιουργεί μια λίστα πελατών, τον αριθμό των αγαθών που αγόρασαν και το συνολικό τους κόστος:

ΕΠΙΛΟΓΗ s. cust_first_name πελάτη_όνομα,

COUNT(DISTINCT oi.product_id) ως product_qty,

SUM(oi.quantity* oi.unit_price) ως συνολικό_κόστος ΑΠΟ οε.παραγγελίες o πελάτες INNER JOIN c ON

o.customer_id=c.customer_id

ΕΣΩΤΕΡΙΚΗ ΣΥΝΔΕΣΗ oe.order_items oi ON o.order_id= oi.order_id ΟΜΑΔΑ ΚΑΤΑ c. cust_first_name

Η ακολουθία λειτουργιών αυτού του σχεδίου ερωτήματος φαίνεται στο Σχ. 10.17.

ΕΠΙΛΟΓΗ ΔΗΛΩΣΗΣ Ι

ΤΑΞΙΝΟΜΗΣΗ ΟΜΑΔΑΣ ΚΑΤΑ ΥΓ

	ΠΛΗΡΗΣ ΠΡΟΣΒΑΣΗ ΤΡΑΠΕΖΙΟΥ
	ΣΑΡΩΣΗ ΕΥΡΕΤΗΡΙΟΥ ΕΥΡΕΤΗΡΙΟΥ
ΠΡΟΣΒΑΣΗ ΤΟΥ ΠΙΝΑΚΑ ΚΑΤΑ ΕΥΡΕΤΗ ROWIDd
ΠΛΗΡΗΣ ΠΡΟΣΒΑΣΗ ΤΡΑΠΕΖΙΟΥ

Ρύζι. 10.17. Σχέδιο ερωτήματος, ακολουθία πράξεων

Ας περιγράψουμε μια πιθανή προσέγγιση για την ερμηνεία του σχεδίου εκτέλεσης του ερωτήματος 80b που παρουσιάζεται στο Σχήμα. 10.17. Αυτή η προσέγγιση περιλαμβάνει δύο κύρια στάδια: την αποσύνθεση των λειτουργιών σε μπλοκ και τον καθορισμό της σειράς των πράξεων.

Στο πρώτο στάδιο, είναι απαραίτητο να αποσυντεθούν οι λειτουργίες που εκτελούνται σε μπλοκ. Για να γίνει αυτό, βρίσκουμε όλες τις συνδικαλιστικές λειτουργίες, δηλ. λειτουργίες που έχουν περισσότερες από μία θυγατρικές λειτουργίες (στο Σχ. 10.17 αυτές είναι οι πράξεις 2, 3 και 4) και διαχωρίζουν αυτές τις θυγατρικές πράξεις σε μπλοκ. Ως αποτέλεσμα, χρησιμοποιώντας το παράδειγμα στο Σχ. 10.17, έχουμε τρεις συνδικαλιστικές λειτουργίες και επτά ομάδες λειτουργιών.

Στο δεύτερο στάδιο, καθορίζεται η σειρά εκτέλεσης των μπλοκ λειτουργιών. Για να το κάνετε αυτό, πρέπει να εφαρμόσετε τους κανόνες για τις ακόλουθες λειτουργίες που περιγράφονται παραπάνω. Ας κάνουμε μια σειρά από σκέψεις σχετικά με την εκτέλεση κάθε λειτουργίας σε σχέση με τον αριθμό αναγνώρισης (III).

Η πράξη Ш = 0 είναι αυτόνομη και είναι ο γονέας της πράξης сШ = 1.

Η λειτουργία Yu = 1 είναι επίσης αυτόνομη. είναι ο γονέας της πράξης W = 2 και εκτελείται πριν από την πράξη Y = 0.

Η λειτουργία GO = 2 είναι μια άσχετη πράξη ένωσης και είναι η μητρική λειτουργία για τις λειτουργίες Yu = 3, Yu = 8. Η λειτουργία GO = 2 εκτελείται πριν από τη λειτουργία GO = 1.

Η λειτουργία GO = 3 είναι μια λειτουργία συνδεδεμένης ένωσης, είναι η μητρική λειτουργία για τις πράξεις GO = 4, GO = 7. Η λειτουργία GO = 3 εκτελείται πριν από τη λειτουργία GO = 2.

Η λειτουργία GO = 4 είναι μια λειτουργία συνδεδεμένης ένωσης, είναι η μητρική λειτουργία για τις πράξεις GO = 5, GO = 6. Η λειτουργία GO = 4 εκτελείται πριν από τη λειτουργία GO = 3.

Η λειτουργία GO = 5 είναι μια αυτόνομη λειτουργία, που εκτελείται πριν από τη λειτουργία GO = 4.

Η λειτουργία GO = 6 είναι μια αυτόνομη λειτουργία, που εκτελείται πριν από τη λειτουργία GO = 5.

Η λειτουργία GO = 7 είναι μια αυτόνομη λειτουργία, που εκτελείται μετά την εκτέλεση του μπλοκ λειτουργιών "C".

Η λειτουργία GO = 8 είναι μια αυτόνομη λειτουργία, που εκτελείται μετά το μπλοκ λειτουργιών "E".

Με βάση το παραπάνω σκεπτικό και τους ακόλουθους κανόνες, διατυπώνουμε την ακολουθία των πράξεων που εκτελούνται:

• 1. Εκτελείται πρώτα η αυτόνομη λειτουργία GO = 5, δείτε τους κανόνες για την ακολουθία των σχετικών λειτουργιών σύνδεσης. Ολόκληρος ο πίνακας διαβάζεται διαδοχικά.
• 2. Το αποτέλεσμα της πράξης GO = 5 - οι σειρές του πίνακα ανάγνωσης - μεταφέρεται στην πράξη GO = 4.
• 3. Εκτελείται η λειτουργία GO = 4: για κάθε σειρά που επιστρέφεται από την πράξη GO = 5, εκτελείται η λειτουργία GO = 6. Δηλαδή, η περιοχή ευρετηρίου σαρώνεται με βάση το χαρακτηριστικό join. Λήψη λίστας αναγνωριστικών σειρών Yaou1s1.
• 4. Το αποτέλεσμα της πράξης GO = 4 μεταφέρεται στην πράξη GO = 3. Δηλαδή, μεταφέρεται η λίστα με τα αναγνωριστικά σειρών Kosh1s1.
• 5. Εκτελείται η πράξη GO = 3: για κάθε τιμή 11оу1с1 που επιστρέφεται ως αποτέλεσμα της λειτουργίας του μπλοκ πράξεων «C», εκτελείται η πράξη GO = 7, δηλ. Οι σειρές του πίνακα διαβάζονται από μια δεδομένη λίστα με αναγνωριστικά σειρών ITMI, που λαμβάνονται μετά την εκτέλεση της λειτουργίας Ш = 4.
• 6. Εκτελείται η αυτόνομη πράξη GO = 8 - διαδοχική ανάγνωση όλου του πίνακα.
• 7. Εκτελείται μια άσχετη λειτουργία σύνδεσης GO = 2: μια ένωση εκτελείται με κατακερματισμό των αποτελεσμάτων των λειτουργικών μπλοκ "E" και "E".
• 8. Το αποτέλεσμα της λειτουργίας GO = 2 μεταφέρεται στη λειτουργία GO = 1.
• 9. Εκτελείται η άσχετη πράξη συγχώνευσης GO = 1: τα δεδομένα που λαμβάνονται ως αποτέλεσμα της πράξης GO = 2 συγκεντρώνονται και ταξινομούνται.
• 10. Εκτελείται η λειτουργία GO = 0. Το σύνολο δεδομένων που προκύπτει επιστρέφεται.

Οι ακόλουθοι κανόνες που διατυπώνονται για τους κύριους τύπους λειτουργιών ισχύουν για τα περισσότερα σχέδια για την εκτέλεση ερωτήματος BSGO. Ωστόσο, υπάρχουν κατασκευές που χρησιμοποιούνται σε ερωτήματα BSGO που υποδηλώνουν παραβίαση της σειράς λειτουργιών που περιγράφονται στους ακόλουθους κανόνες. Τέτοιες καταστάσεις μπορεί να προκύψουν ως αποτέλεσμα της χρήσης, για παράδειγμα, υποερωτημάτων ή κατηγορημάτων κατά της σύνδεσης. Σε κάθε περίπτωση, η διαδικασία ερμηνείας του σχεδίου εκτέλεσης ενός ερωτήματος BSGO δεν συνεπάγεται μόνο τη χρήση ορισμένων κανόνων που θα εξασφαλίσουν την πιο ακριβή ανάλυση του τι πρόκειται να κάνει ο βελτιστοποιητής κατά την εκτέλεση ενός ερωτήματος BSGO. Ένα άλλο αίτημα BSGO είναι πάντα μια μεμονωμένη περίπτωση. και το πώς θα εκτελεστεί στο DBMS εξαρτάται από πολλούς παράγοντες, συμπεριλαμβανομένης της έκδοσης του DBMS, της έκδοσης και του τύπου του λειτουργικού συστήματος στο οποίο αναπτύσσεται η παρουσία του DBMS, του υλικού που χρησιμοποιείται, των προσόντων του συντάκτη του ερωτήματος 80b, και τα λοιπά.

1 msdevcon.ru #msdevcon

3 Olontsev Sergey SQL Server MCM, MVP Kaspersky Lab

4 Γλώσσα δομημένης ερώτησης

5 Παράδειγμα ερωτήματος επιλέξτε pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber από HumanResources.Employee as emp inner join Person.Person as pers on pers.businessentityid = emp.businessentityid όπου pers.firstname" = N"Joh emp.hiredate >= " "

6 Λογικό δέντρο ερωτήματος Project pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber D A T A Φίλτρο Join pers.firstname = N"John" and emp.hiredate >= " " pers.businessentityid = emp.businessentityid ως Person.Person pers Λήψη δεδομένων Λήψη δεδομένων HumanResources.Employee as emmp

7 Σχέδιο ερωτήματος Δείχνει πώς εκτελείται ένα ερώτημα T-SQL σε φυσικό επίπεδο.

8 Με διάφορους τρόπους

9 DEMO Απλό σχέδιο Επιλογή όλων των δεδομένων από έναν πίνακα, πώς να λάβετε ένα σχέδιο ερωτήματος

11 Μέθοδοι χειριστή Init() Η μέθοδος Init() αναγκάζει τον φυσικό χειριστή να αρχικοποιηθεί και να προετοιμάσει τις απαραίτητες δομές δεδομένων. Ένας φυσικός χειριστής μπορεί να λάβει πολλές κλήσεις προς την Init(), αν και συνήθως λαμβάνει μόνο μία. GetNext() Η μέθοδος GetNext() αναγκάζει τον φυσικό τελεστή να λάβει την πρώτη ή την επόμενη σειρά δεδομένων. Ένας φυσικός χειριστής μπορεί να λάβει πολλές κλήσεις GetNext() ή καμία. Η μέθοδος GetNext() επιστρέφει μία σειρά δεδομένων και ο αριθμός των φορών που καλείται υποδεικνύεται από την τιμή ActualRows στην έξοδο της πρότασης Showplan. Close() Όταν καλείται η μέθοδος Close(), ο φυσικός χειριστής εκτελεί κάποια εκκαθάριση και κλείνει. Ο φυσικός χειριστής λαμβάνει μόνο μία κλήση στο Close().

12 Αλληλεπίδραση μεταξύ χειριστών Χειριστής 1 Χειριστής 2 Χειριστής 3

13 Αλληλεπίδραση μεταξύ τελεστών 1. Αίτημα χειριστή γραμμής 1 χειριστή 2 χειριστή 3

14 Αλληλεπίδραση μεταξύ τελεστών 1. Αίτημα γραμμής 2. Αίτημα χειριστή γραμμής 1 χειριστή 2 χειριστή 3

15 Αλληλεπίδραση μεταξύ τελεστών 1. Αίτηση γραμμής 2. Αίτηση γραμμής χειριστή 1 χειριστή 2 χειριστή 3 3. Αποστολή σειράς

16 Αλληλεπίδραση μεταξύ τελεστών 1. Αίτηση γραμμής 2. Αίτημα χειριστή γραμμής 1 χειριστή 2 χειριστή 3 4. Αποστολή σειράς 3. Αποστολή σειράς

17 Αλληλεπίδραση μεταξύ τελεστών 1. Αίτηση γραμμής 2. Αίτημα χειριστή γραμμής 1 χειριστή 2 χειριστή 3 4. Αποστολή σειράς 3. Αποστολή σειράς

18 DEMO Operator TOP Ή γιατί είναι καλύτερο να καλέσετε έναν χειριστή επαναληπτικό

19 Πίνακες δεν υπάρχουν!

20 HoBT Σελίδα 1 Σελίδα 2 Σελίδα 3 Σελίδα 4 Σειρά 1 Σειρά 3 Σειρά 5 Σειρά 7 Σειρά 2 Σειρά 4 Σειρά 6 Σειρά 8

21 Σελίδα σελίδας HoBT Σελίδα Σελίδα Σελίδα Σελίδα Σελίδα

22 DEMO Χειριστές πρόσβασης δεδομένων Σάρωση, Αναζήτηση, Αναζήτηση

23 Ποιος έχει μόνο έναν πίνακα στη βάση δεδομένων;

24 Nested Loops, Hash Join και Merge Join

25 Join Operators Nested Loops εσωτερική ένωση, αριστερή εξωτερική ένωση, αριστερή ημι-σύνδεση, αριστερό αντι ημι-σύνδεση Συγχώνευση Σύνδεση εσωτερικής σύνδεσης, αριστερή εξωτερική ένωση, αριστερό ημι-σύνδεση, αριστερή αντι-ημι-σύνδεση, δεξιά εξωτερική ένωση, δεξιά ημι-σύνδεση, δεξιά κατά ημι-σύνδεση , union Hash Συμμετοχή όλων των τύπων λογικών πράξεων

26 DEMO Σύνδεση, ταξινόμηση και πρώτος τελεστής Nested Loops, Merge Join, Hash Join, Sort, First Operator

27 Προειδοποιήσεις

28 DEMO Σφάλματα και προειδοποιήσεις σε σχέδια ερωτημάτων

29 Ξέρω ότι δεν ξέρω τίποτα. Σωκράτης

30 DEMO Ένα μικρό παράδειγμα για κάτι ασαφές

31 Διάγνωση σχεδίων ερωτημάτων -- Τα TOP 10 ερωτήματα που καταναλώνουν την περισσότερη CPU και τα σχέδιά τους επιλέγουν την top(10) substring(t.text, qs.statement_start_offset / 2, περίπτωση όταν qs.statement_end_offset = -1 και στη συνέχεια len(t.text) other (qs.statement_end_offset - qs.statement_start_offset) / 2 end), qs.execution_count, cast(qs.total_worker_time / as decimal(18, 2)) as total_worker_time_ms, cast(qs.total_worker_count * 1. / qs.exe (18, 2)) Ως avg_worker_time_ms, cast (p.query_plan ως xml) ως query_plan από sys.dm_exec_query_stats ως qs cross apply sys.dm_exec_sql_text (qs.sql_handle) ως cross apply sys.dm_text_query_stats (qs cross applem , qs.statement_end_offset) ως p κατά σειρά qs.total_worker_time desc; πηγαίνω

32 Τεχνικές για την ανάγνωση μεγάλων σχεδίων ερωτημάτων Δοκιμάστε να τα χωρίσετε σε λογικά τμήματα και να τα αναλύσετε σταδιακά. Στο SSMS, όταν το σχέδιο εμφανίζεται γραφικά, εμφανίζεται ένα κουμπί στην κάτω δεξιά γωνία για ευκολότερη πλοήγηση στο σχέδιο ερωτήματος. Μπορείτε να χρησιμοποιήσετε το XQuery\XPath.

33 DEMO Μεγάλο σχέδιο ερωτήματος

35 DEMO SQL Sentry Plan Explorer

36 Ας συνοψίσουμε Πρώτος τελεστής Επίπεδο βελτιστοποίησης Χρόνος μεταγλώττισης Μέγεθος σε προσωρινή μνήμη Παράμετροι, Τιμές μεταγλώττισης Λόγος πρόωρου τερματισμού Κόστος επαναλήψεων Κοιτάξτε πρώτα τους τελεστές με το υψηλότερο κόστος. Λάβετε υπόψη ότι αυτές είναι απλώς εκτιμώμενες τιμές (ακόμη και σε πραγματικά σχέδια εκτέλεσης).

37 Ας συνοψίσουμε το Bookmark\Key Lookup Εάν υπάρχουν λίγα από αυτά, τότε πιθανότατα δεν υπάρχει πρόβλημα. Εάν υπάρχουν πολλά από αυτά, η δημιουργία ενός ευρετηρίου κάλυψης θα σας βοηθήσει να απαλλαγείτε από αυτά. Προειδοποιήσεις Πρέπει να ελέγξετε γιατί συμβαίνει και να λάβετε μέτρα εάν είναι απαραίτητο.

38 Ας συνοψίσουμε Συνδέσεις μεταξύ τελεστών (ροές δεδομένων) Όσο πιο παχιά είναι η σύνδεση, τόσο περισσότερα δεδομένα περνούν μεταξύ αυτών των τελεστών. Αξίζει ιδιαίτερα να προσέχετε εάν σε κάποιο στάδιο η ροή δεδομένων αυξάνεται απότομα. Σειρά ένωσης πινάκων Όσο μικρότερες είναι οι ροές δεδομένων, τόσο πιο εύκολο είναι να τις ενώσετε. Επομένως, πρώτα απ 'όλα, πρέπει να ενώσετε τους πίνακες των οποίων η προκύπτουσα ροή δεδομένων θα είναι μικρότερη.

39 Σύνοψη Σαρώσεις Οι σαρώσεις δεν σημαίνουν ότι υπάρχει πρόβλημα. Είναι πιθανό να μην υπάρχει αρκετό ευρετήριο στον πίνακα για να κάνετε μια πιο αποτελεσματική αναζήτηση. Από την άλλη πλευρά, εάν πρέπει να επιλέξετε όλο ή ένα μεγάλο μέρος του πίνακα, η σάρωση θα είναι πιο αποτελεσματική. Το να ψάχνεις δεν σημαίνει ότι όλα είναι καλά. Ο μεγάλος αριθμός αναζητήσεων σε μη συγκεντρωμένα ευρετήρια μπορεί να είναι πρόβλημα. Οτιδήποτε δεν γνωρίζετε για το σχέδιο θα μπορούσε ενδεχομένως να είναι πρόβλημα.

40 Ερωτήσεις

41 Επαφές Olontsev Sergey Kaspersky Lab

42 2013 Microsoft Corporation. Ολα τα δικαιώματα διατηρούνται. Τα Microsoft, Windows, Windows Vista και άλλες ονομασίες προϊόντων είναι ή ενδέχεται να είναι σήματα κατατεθέντα ή/και εμπορικά σήματα στις Η.Π.Α. και/ή άλλες χώρες. Οι πληροφορίες στο παρόν προορίζονται μόνο για ενημερωτικούς σκοπούς και αντιπροσωπεύουν την τρέχουσα άποψη της Microsoft Corporation από την ημερομηνία αυτής της παρουσίασης. Επειδή η Microsoft πρέπει να ανταποκρίνεται στις μεταβαλλόμενες συνθήκες της αγοράς, δεν θα πρέπει να ερμηνεύεται ως δέσμευση εκ μέρους της Microsoft και η Microsoft δεν μπορεί να εγγυηθεί την ακρίβεια οποιασδήποτε πληροφορίας που παρέχεται μετά την ημερομηνία αυτής της παρουσίασης. Η MICROSOFT ΔΕΝ ΠΑΡΕΧΕΙ ΕΓΓΥΗΣΕΙΣ, ΡΗΤΕΣ, ΣΙΩΠΗΡΕΣ Ή ΝΟΜΙΚΕΣ, ΟΣΟΝ ΑΦΟΡΑ ΤΙΣ ΠΛΗΡΟΦΟΡΙΕΣ ΣΕ ΑΥΤΗ ΤΗΝ ΠΑΡΟΥΣΙΑΣΗ.