Τι είναι το Query Store και πως το χρησιμοποιούμε στον SQL Server

• Σχετικά με
• τελευταίες δημοσιεύσεις

Stratos Matzouranis

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

• Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
• Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
• Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 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 και έπειτα).

Το πως γίνεται αυτό θα το δούμε σε επόμενο άρθρο…!

Πηγές:

• Microsoft Query Store