Τι είναι το Query Store και πως το χρησιμοποιούμε στον SQL Server
- Πώς ενεργοποιούμε το Unified Auditing σε Oracle Database - 7 Οκτώβριος 2024
- Τι είναι η PostgreSQL και πως κάνουμε μία πλήρης εγκατάσταση - 2 Σεπτέμβριος 2024
- Πώς ρυθμίζουμε το replication του GoldenGate ώστε να διαβάζει από το Standby του Oracle Data Guard - 2 Αύγουστος 2024
Το Query Store εμφανίστηκε από την έκδοση SQL Server 2016 και έπειτα. Είναι ένα εργαλείο που παρέχεται ώστε να μπορούμε να παρακολουθήσουμε το κάθε query (ερώτημα) στη βάση δεδομένων.
Μέσα από το Query Store μπορούμε να δούμε ένα query το πότε εκτελέστηκε, την διάρκεια του, το execution plan που χρησιμοποίησε και πολλά άλλα.
Ας θυμηθούμε τι είναι το execution plan
Το execution plan είναι ουσιαστικά ένας χάρτης που δημιουργείτε από τον query optimizer του RDBMS λαμβάνοντας υπόψη τα στατιστικά/cardinality ώστε να βρεθεί ποιος είναι ο βέλτιστος τρόπος ώστε να προσπεραστούν τα δεδομένα για την διεργασία που του έχουμε ζητήσει.
Μέσα από το Query Store μπορούμε να δούμε το execution plan (πλάνο) που υπήρχε κάθε φορά που είχε εκτελεστεί το κάθε query σε βάθος χρόνου και να εντοπίσουμε το πότε άλλαξε το πλάνο. Μας δίνει την δυνατότητα να κάνουμε force το πλάνο ώστε να μην επιλέξει ο optimizer αλλά να δηλώσουμε εμείς ότι θέλουμε το συγκεκριμένο.
Πολύ χρήσιμα χαρακτηριστικά επίσης είναι η δυνατότητα να δούμε ποια query είναι τα πιο κοστοβόρα, την ιστορικότητα το κάθε query, πόσες φορές εκτελέστηκε το query σε μια συγκεκριμένη χρονική διάρκεια και πότε έπεσε η απόδοση σε ένα query λόγο αλλαγής πλάνου.
Πώς ενεργοποιείτε και παραμετροποιήτε με T-SQL
Για να ενεργοποιηθει απλά τρέχουμε την εντολή:
ALTER DATABASE [partition] SET QUERY_STORE = ON;
Με το OPERATION_MODE ορίζουμε άν στο Query Store θα μπορούμε μόνο να διαβάζουμε δεδομένα READ_ONLY είτε οτι θα ενημερώνεται και με καινούργια READ_WRITE:
ALTER DATABASE [partition] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
Το MAX_STORAGE_SIZE_MB είναι το όριο του μεγέθους των δεδομένων του query store. Σε παραγωγικό περιβάλλον η τιμή πρέπει να είναι πάνω από 2048 MB, όμως η τιμή πρέπει να είναι ανάλογη και με τον αριθμό των ημερών σε περίπτωση που ορίσαμε των πλήθος των ημερών STALE_QUERY_THRESHOLD_DAYS στις 90 μέρες π.χ. προτείνεται η χρήση 8192 MB και αναλόγως του current_storage_usage στο sys.database_query_store_options:
ALTER DATABASE [partition] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);
Με την παράμετρο SIZE_BASED_CLEANUP_MODE = AUTO όταν τα δεδομένα του Query Store πλησιάσουν το μέγιστο μέγεθος θα διαγράφονται τα πιο παλιά. Στην αντίθετη περίπτωση περίπτωση που έχει οριστεί ως OFF το operation mode αλλάζει σε read-only, με αποτέλεσμα να διακόπτεται η συλλογή νέων δεδομένων:
ALTER DATABASE [partition] SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);
Με την παράμετρο CLEANUP_POLICY ορίζουμε για πόσες μέρες θα κρατιούνται τα δεδομένα. Όσο μεγαλύτερη είναι η τιμή τόσο παραπάνω χώρο θα καταναλώνει στον δίσκο:
ALTER DATABASE [partition] SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));
Το Interval Length Minutes καθορίζει κάθε πότε θα γίνονται τα στατιστικά του query (duration, tempdb_usage, memory_usage, count executions κ.τ.λ.) aggregate(average,sum …) σε διαφορετική εγγραφή στον πίνακα sys.query_store_runtime_stats . Όσο μικρότερή είναι η τιμή τόσο περισσότερη πληροφορία έχουμε μέσα στον χρόνο, όμως ταυτόχρονα αυξάνονται τα δεδομένα στον δίσκο:
*Προσοχή η παράμετρος αυτή μπορεί να πάρει μόνο συγκεκριμένες τιμές (1, 5, 10, 15, 30, 60, 1440)
ALTER DATABASE [partition] SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);
To data flush interval είναι ο χρόνος σε δευτερόλεπτα που γράφονται τα στατιστικά του query store από τη μνήμη στον δίσκο. Σε περίπτωση memory pressure η παράμετρος αυτή αυτόματα αγνοείται και γίνονται flush εκείνη την στιγμή τα δεδομένα στον δίσκο. Όσο τα δεδομένα αυτά δεν έχουν γίνει flush δεν θα φαίνονται στα dynamic views του Query Store. Προτεινόμενη τιμή τα 900 δευτερόλεπτα (15 λεπτά):
ALTER DATABASE [partition] SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);
Με την παράμετρο ALL στο QUERY_CAPTURE_MODE ορίζουμε να καταγράφονται όλα τα queries που εκτελούνται. Με την παράμετρο AUTO θα καταγράφονται μόνο όσα queries έχουν εκτελεστεί πολλές φορές ή έχουν μεγάλη διάρκεια. Με την παράμετρο NONE θα ενημερώνονται τα στατιστικά μόνο για όσα queries έχουν καταγραφεί ήδη:
ALTER DATABASE [partition] SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);
Στον SQL Server 2019 έχουμε και την επιλογή CUSTOM που μπορούμε να ορίσουμε πολλαπλά κριτήρια π.χ.:
ALTER DATABASE [partition] SET QUERY_CAPTURE_POLICY = ( STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS, EXECUTION_COUNT = 30, TOTAL_COMPILE_CPU_TIME_MS = 1000, TOTAL_EXECUTION_CPU_TIME_MS = 100 );
Επίσης στον SQL Server 2017 / 2019 έχουμε την δυνατότητα να ορίσουμε αν θα καταγράφονται και τα wait statistics με την παράμετρο WAIT_STATS_CAPTURE_MODE το οποίο default είναι ON:
ALTER DATABASE [partition] SET QUERY_STORE (WAIT_STATS_CAPTURE_MODE = ON);
Τέλος με την παράμετρο MAX_PLANS_PER_QUERY ορίζουμε το μέγιστό αριθμό πλάνων ανά query. Η τιμή 200 είναι default και προτείνετε να μείνει ως έχει:
ALTER DATABASE [partition] SET QUERY_STORE (MAX_PLANS_PER_QUERY = 200);
Παράδειγμα ενεργοποίησης Query Store για SQL Server 2019
ALTER DATABASE [partition] SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ), DATA_FLUSH_INTERVAL_SECONDS = 900, MAX_STORAGE_SIZE_MB = 2048, INTERVAL_LENGTH_MINUTES = 60, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200, WAIT_STATS_CAPTURE_MODE = ON, QUERY_CAPTURE_MODE = CUSTOM, QUERY_CAPTURE_POLICY = ( STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS, EXECUTION_COUNT = 30, TOTAL_COMPILE_CPU_TIME_MS = 1000, TOTAL_EXECUTION_CPU_TIME_MS = 100 ) );
Πως ελέγχουμε τη καλή λειτουργία του Query Store
Για να ελέγξουμε το status τον χώρο που χρησιμοποιεί και τις παραμέτρους του query store:
select * from sys.database_query_store_options;
Για να καθαρίσουμε τον χώρο από όλα τα δεδομένα που καταλαμβάνουν χώρο:
ALTER DATABASE [partition] SET QUERY_STORE CLEAR;
Αν σταματήσει να λειτουργεί το query store μπορούμε να κάνουμε ένα check με την procedure:
exec sp_query_store_consistency_check;
Για να απενεργοποιήσουμε το Query Store hard way το οποίο όμως έχει σαν αποτέλεσμα να μην γραφτούν τα δεδομένα που είναι στη μνήμη στον δίσκο:
ALTER DATABASE [partition] SET QUERY_STORE = OFF (FORCED);
Σε εκδόσεις πριν τον SQL Server 2019 θα πρέπει να ενεργοποιηθεί το trace flag 7745. Η λειτουργία του είναι σε περίπτωση που γίνει κάποιο shutdown ή failover να μην περιμένει να γράψει όλα τα δεδομένα του Query Store που βρίσκονται στην μνήμη στον δίσκο. Σε διαφορετική περίπτωση το Query Store θα καθηστερούσε την επανεκκίνηση του instance.
Εξάγοντας χρήσιμες πληροφορίες με T-SQL
Το query που έφτιαξα που κάνει τη ζωή μας πιο εύκολη…
Μπορούμε να κάνουμε aggregate τα δεδομένα ώστε να μπορούμε να δούμε για το κάθε πλάνο που έτρεξε ένα query σύγκριση μεταξύ του τελευταίου interval και του συνολικού, average των στατιστικών (διάρκεια εκτέλεσης , χρήση μνήμης , tempdb, χρήση log κ.τ.λ.). Μπορούμε επίσης να δούμε ολόκληρο το γραφικό του πλάνου:
SELECT p.plan_id, p.query_id,qt.query_sql_text ,case when q.object_id = 0 then 'ad_hoc' else OBJECT_NAME(q.object_id) end as object_name ,DATEADD(HOUR,DATEDIFF(hour, SYSUTCDATETIME(),SYSDATETIME() ),q.last_execution_time) as last_execution_time ,ad.last_duration as agg_last_duration ,ad.avg_duration_aggregate ,cast(p.query_plan as xml) query_plan ,ad.count_executions ,ad.avg_tempdb_space_used ,ad.avg_log_bytes_used ,ad.avg_query_max_used_memory ,ad.max_dop FROM sys.query_store_query AS q inner JOIN sys.query_store_plan AS p on p.query_id = q.query_id inner JOIN sys.query_store_query_text AS qt on q.query_text_id = qt.query_text_id inner JOIN (select plan_id,avg(last_duration)as last_duration,avg(avg_duration)as avg_duration_aggregate,sum(count_executions) as count_executions, avg(avg_query_max_used_memory) as avg_query_max_used_memory, avg(avg_tempdb_space_used) as avg_tempdb_space_used,avg(avg_log_bytes_used) as avg_log_bytes_used,max(max_dop) max_dop from sys.query_store_runtime_stats group by plan_id ) as ad on ad.plan_id = p.plan_id WHERE 1=1 --and p.is_forced_plan = 1 --and p.query_id = 201 --and qt.query_sql_text like '%SELECT p.plan_id%' order by q.last_execution_time,q.query_id desc;
*To dateadd χρειάζεται γιατί κλασικά ο SQL Server φέρνει τις ημερομηνίες σε UTC time zone.
**To cast στο query_plan χρειάζεται ώστε όταν κάνουμε κλικ στο πλάνο να μας ανοίγει το γραφικό.
Για να δούμε ποια πλάνα είναι forced και τον λόγο που μπορεί να απέτυχαν να γίνουν
SELECT p.plan_id, p.query_id, qt.query_sql_text,q.object_id as containing_object_id, force_failure_count, last_force_failure_reason_desc FROM sys.query_store_plan AS p inner JOIN sys.query_store_query AS q on p.query_id = q.query_id inner JOIN sys.query_store_query_text AS qt on q.query_text_id = qt.query_text_id WHERE is_forced_plan = 1;
Οι πληροφορίες που βλέπουμε με το γραφικό περιβάλλον του Query Store
Η ευκολία που μας παρέχει το Query Store είναι η εκταμένη χρήση γραφικού περιβάλλοντος. Ας δούμε αναλυτικά τις οθόνες που παρέχει:
Query wait statistics:
Τα query που κατανάλωσαν τους περισσότερους πόρους:
Μπορούμε να κάνουμε παραμετροποίησουμε τα δεδομένα που βλέπουμε και να αλλάξουμε τα φίλτρα:
Μπορούμε να δούμε τα query που είναι forced, το πλάνο και τις αλλαγές που έγιναν στο πλάνο σε βάθος χρόνου.
Όταν επιλέγουμε ένα query από το query report μας εμφανίζει όλα τα πλάνα που έχει αλλάξει μέσα στο χρόνο. Επιλέγοντας το κουμπί “Force Plan” δηλώνουμε ότι από εδώ και πέρα θέλουμε να τρέχει πάντα με το συγκεκριμένο πλάνο.
Κλείνοντας δεν θα πρέπει να ξεχνάμε όπως κάθε έξτρα λειτουργία που προσθέτουμε σε έναν server, έτσι και το Query Store στον SQL Server έρχεται με ένα overhead, που θα μειώσει έστω και σε έναν μικρό βαθμό την απόδοση.
Μπορούμε να αλλάζουμε τις παραμέτρους ανάλογα το πρόβλημα που αντιμετωπίζουμε ανά περίπτωση. Μπορεί ακόμα για το σύστημα μας να μην συμφέρει να το ενεργοποιήσουμε καθόλου και στην θέση του να ενεργοποιήσουμε αντίστοιχο Extended Event (από την έκδοση SQL Server 2017 CU 14 και έπειτα).
Το πως γίνεται αυτό θα το δούμε σε επόμενο άρθρο…!
Καλησπέρα ,
Κατά την εκτελεστή του
“SELECT p.plan_id, p.query_id,qt.query_sql_text
,case when q.object_id = 0 then ‘ad_hoc’ else OBJECT_NAME(q.object_id) end as object_name
,DATEADD(HOUR,DATEDIFF(hour, SYSUTCDATETIME(),SYSDATETIME() ),q.last_execution_time) as last_execution_time
,ad.last_duration as agg_last_duration….”
Λαμβάνω το εξής σφάλμα : XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.
Υπάρχει δυνατότητα να το αυξήσουμε – SQL 2017
Δοκίμασε να μην κάνεις convertion το πεδίο του πλάνου σε xml:
cast(p.query_plan as xml) query_plan
Αλλάξετο σε σκέτο:
p.query_plan
Όμως έτσι όταν κάνεις κλικ στο Πλάνο δεν θα σου το δείχνει με γραφικό. Για να το δεις με γραφικό έχεις δύο τρόπους.
Κάνεις copy το query_plan που θες (ShowPlanXML…) και paste:
– είτε στο site
https://www.brentozar.com/pastetheplan/
– είτε το σώζεις σε ένα άλλο αρχείο με κατάληξη .sqlplan και το ανοίγεις πάλι με το Sql server management studio.