Εισαγωγή
Κάθε φορά που στέλνουμε ένα Transact SQL statement να εκτελεστεί σε ένα SQL Server instance είναι γνωστό ότι ένα από τα βασικά components του SQL Server Database Engine αναλαμβάνει την διαδικασία αυτή και αυτό δεν είναι άλλο από τον Query Optimizer (QP).
Αυτός αναλαμβάνει να μετατρέψει το δεδομένο T-SQL statement σε μια σειρά από logical operators οι οποίοι θα χρησιμοποιηθούν για την εκπλήρωση της εκτέλεσης του T-SQL statement. Αυτή η σειρά των logical operators είναι γνωστή σαν Query Execution Plan (QEP).
Plan Cache
Η σύνθεση του QEP είναι μια CPU intensive διαδικασία όπου ανάλογα με το πόσο περίπλοκο είναι το T-SQL statement θα χρειαστεί τους ανάλογους πόρους για να το δημιουργήσει. Για αυτό το λόγο μια υπόθεση που έχει γίνει είναι το κάθε T-SQL statement θα ξαναχρησιμοποιηθεί περισσότερο από μια φορά και για αυτό το λόγο το QEP αποθηκεύεται σε μια περιοχή μνήμης την οποία ονομάζουμε Plan Cache.
Έτσι την επόμενη φορά που το ίδιο T-SQL statement ζητηθεί να εκτελεστεί τότε αντί να δημιουργηθεί ξανά το QEP αυτό διαβάζεται από την plan cache και επαναχρησιμοποιείται με αποτέλεσμα να έχουμε κέρδος σε πόρους στην CPU καθώς δεν χρειάζεται να μπει ξανά στην διαδικασία σύνθεσης του QEP.
Plan Cache Stores
H plan cache αποτελείται από τέσσερις βασικές περιοχές που είναι γνωστές σαν plan cache stores και κάθε μια από αυτές περιέχει διαφορετικού είδους cached QEP και είναι οι παρακάτω:
- Object Plans (CACHESTORE_OBJCP) : Περιέχει τα πλάνα των stored procedures, function και triggers.
- SQL Plans (CACHESTORE_SQLCP) : Περιέχει τα πλάνα των ad-hoc, auto-parametrized και prepared statements.
- Bound Trees (CACHESTORE_PHDR) : Περιέχει τις δομές που δημιουργούνται από τον algebrizer για τα views, defaults και constraints.
- Extended Stored Procedures (CACHESTORE_XPROC) : Περιέχει τα πλάνα των extended sp (αυτές πχ που αρχίζουν με xp_)
Μπορεί κάποιος να δεις τα περιεχόμενα των παραπάνω αναφερόμενων περιοχών χρησιμοποιώντας το sys.dm_os_memory_cache_counters DMV με το παρακάτω query:
SELECT *
FROM sys.dm_os_memory_cache_counters
WHERE name in ('Object Plans','SQL Plans','Bound Trees','Extended Stored Procedures');
GOPlan Cache Store Organization
Kάθε plan cache store είναι ένα hash table που αποτελείται από μια σειρά από buckets και κάθε ένα από αυτά περιέχει 0 ή N cached QEP.
Ένας hash αλγόριθμος χρησιμοποιείται για να βάλει το κάθε QEP στο αντίστοιχο bucket. Αυτός ο αλγόριθμος σε υψηλό επίπεδο ανάγνωσης είναι ο παρακάτω:
- Για τα Object Plans, Bound Trees, Extended Stored Procedures cache stores το hash values υπολογίζεται βασισμένο στο database_id και το object_id τους εκάστοτε object.
- Για τα T-SQL Statements τα οποία μπαίνουν στην SQL Plan cache store το hash value βασίζεται στο database_id της βάσης που αυτό εκτελείται.
Με τα παρακάτω queries μπορεί κάποιος να δει πληροφορίες που αφορούν τον αριθμό των hash buckets που υπάρχουν σε κάθε cache store
SELECT cc.name, buckets_count
FROM sys.dm_os_memory_cache_hash_tables AS ht
JOIN sys.dm_os_memory_cache_counters AS cc
ON ht.cache_address = cc.cache_address
WHERE cc.name IN ('Object Plans','SQL Plans','Bound Trees','Extended Stored Procedures');
GO
SELECT type as 'plan cache store', buckets_count
FROM sys.dm_os_memory_cache_hash_tables
WHERE type IN ('CACHESTORE_OBJCP', 'CACHESTORE_SQLCP',
'CACHESTORE_PHDR', 'CACHESTORE_XPROC');
GO
Αναζήτηση ενός Query Execution Plan στην Plan Cache Store
Κάθε μεμονωμένο query plan που βρίσκεται στην plan cache store έχει το plan handle που είναι μια μοναδική τιμή ώστε να εντοπίζεται τόσο από το QP όσο και από εμάς αν θέλουμε να κάνουμε αυτό.
Ο QP το κάνει αυτό ως εξής:
- Ανάλογα με το τύπο του T-SQL statement εντοπίζει τη plan cache store στην οποία μπορεί αυτό να υπάρχει.
- Υπολογίζει το hash value για το plan cache store bucket που το T-SQL statement ανήκει.
- Αναζητεί με το hash value αυτό στην plan cache store bucket ώστε να βρει αν υπάρχει το QEP και αν υπάρχει τότε το επαναχρησιμοποιεί αλλιώς το δημιουργεί.
Με το παρακάτω query μπορούμε να δούμε την λίστα με όλα τα attributes που φτιάχνουν το plan cache key:
SELECT pa.*
FROM (SELECT TOP(1) plan_handle FROM sys.dm_exec_cached_plans) AS cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
WHERE is_cache_key = 1;
Executable Plans
Κάθε QEP που αποθηκεύεται στην plan cache είναι αυτό που ονομάζουμε compiled plans και είναι αυτό που επαναχρησιμοποιούνται.
Κάθε ένα από αυτά μπορεί ταυτόχρονα να χρησιμοποιηθεί από πολλαπλά sessions και όταν ένα από αυτά χρησιμοποιείται από ένα sessions λέμε ότι είναι ένα executable plan ή ένα execution context.
Αυτό σημαίνει ότι αν έχω 5 sessions που ταυτόχρονα ζητάνε το ίδιο τότε έχουμε 5 executable plans που μπορεί να χρησιμοποιούν το ίδιο πλάνο αλλά το κάθε session μπορεί να είναι από διαφορετικό user αλλά και να έχει και διαφορετικές παραμέτρους και κάθε executable plan περιέχει metadata που αφορούν το κάθε compiled plan instance όπως το user id, parameters values και local variable values κάθε ένα από αυτά γίνεται cached και επαναχρησιμοποιείται αλλά και πάλι έχει λιγότερο κόστος να δημιουργηθεί σε σχέση με τα compiled plans (QEP).
Plan Cache Eviction Policy
Το μέγεθος της plan cache δεν μπορεί να μεγαλώνει επ' άπειρο καθώς η μνήμη που έχουμε για το SQL Server instance δεν είναι ατελείωτη. Για αυτό το λόγο o SQL Server έχει μια διαδικασία που την ονομάζει Plan Cache Eviction Policy (PCEP).
Πριν περιγράψω την διαδικασία αυτή θεωρώ σκόπιμο να αναφέρω ότι μπορούμε να την καθαρίσουμε είτε χρησιμοποιώντας την DBCC FREEPROCCACHE είτε την DBCC FREESYSTEMCACHE όπου με την πρώτη έχουμε πολλές επιλογές για το τι θέλουμε να αφαιρέσουμε από αυτή (δείτε το documentation).
Επίσης πρέπει να αναφέρω ότι κάθε φορά που κάνω ALTER PROC σε μια stored procedure αυτό οδηγεί στο να φύγει από την cache το προηγούμενο cached plan αυτής.
Παρόλα αυτά όμως δεν είναι κάτι το οποίο προτείνω να γίνεται σε παραγωγικά περιβάλλοντα καθώς το κόστος του recompilation μεγάλου αριθμού από QEP μπορεί να έχει σημαντικό performance impact.
Σε κάθε άλλη περίπτωση ο τρόπος με τον οποίο καθαρίζεται η plan cache είναι κάτι που το έχει εσωτερικά ο SQL Server και δεν μπορούμε να το αλλάξουμε άμεσα αλλά έμμεσα με το τρόπο που χρησιμοποιούμε το SQL Server και τoυς διαθέσιμους πόρους που έχουμε.
Στο SQL Server όταν αναφερόμαστε στην μνήμη μιλάμε για την visible target memory η οποία είναι το μέγιστο ποσό μνήμης που μπορεί να δει το instance και μπορούμε να δούμε ποιο είναι αυτό με το παρακάτω query:
SELECT visible_target_kb FROM sys.dm_os_sys_info;
Από την έκδοση του SQL Server 2005 SP2 και μετά το pressure limit για την cache υπολογίζεται ως εξής:
75% της visible target memory για τα 0-4GB
+
10% της visible target memory για τα 4-64 GB
+
5% της visible target memory για τα πάνω από 64GB.
Αυτό σημαίνει ότι σε ένα instance με 128 GB target memory έχουμε:
3 GB για τα πρώτα 4GB + 6 GB για τα επόμενα 60GB (64-4) + 32 GB για τα επόμενα 64 (128-64) = 41GB cache plan pressure limit (CPPL)
O SQL Server με βάση αυτό αποφασίζει ότι έχει plan cache pressure όταν έχει:
- Local memory pressure που σημαίνει είτε όταν ένα plan cache store καταλαμβάνει πάνω από το 62,5% του CPPL είτε ο αριθμός των cached plans στην plan cache store είναι πάνω από 4 φορές του αριθμού των hash buckets.
- Internal global memory pressure που σημαίνει είτε όταν έχουμε χαμηλό virtual address space είτε όταν ο συνολικό χώρος όλων των plan cache stores είναι πάνω από το 80% του CPPL.
- External global memory pressure όταν το OS μειώνει την διαθέσιμη μνήμη του SQL Server instance.
Σε κάθε μία από τις παραπάνω περιπτώσεις το μέγεθος της plan cache θα μειωθεί χρησιμοποιώντας τη διαδικασία του plan cache eviction policy η οποία υπολογίζεται με βάση το κόστος που έχει το κάθε plan και αυτά που είναι τα λιγότερο δαπανηρά φεύγουν από την plan cache.
Το κόστος για το κάθε plan υπολογίζεται σε ticks σε μια κλίμακα από το 0-31 λαμβάνοντας υπόψη τα κόστη σε IO + CPU + Memory ως εξής:
- Κάθε ad-hoc plan έχει αρχικό κόστος 0 και αυξάνει κατά 1 κάθε φορά που αυτό επαναχρησιμοποιείται.
- Κάθε άλλο plan (δλδ εκτός από τα ad-hoc) ο υπολογισμός γίνεται ως εξής:
- Για κάθε 2 ΙΟ operations έχω 1 tick με μέγιστο τα 19 ticks
- Για κάθε 2 context switches (CPU) έχω 1 tick με μέγιστο τα 8 ticks
- Για κάθε 128 ΚΒ μνήμης (16 pages) έχω 1 tick με μέγιστο τα 4 ticks
Μπορούμε να δούμε το κόστος του κάθε plan με το παρακάτω query:
SELECT e.[type] AS cache_type
, st.[text]
, p.objtype
, p.usecounts
, p.size_in_bytes
, e.disk_ios_count
, e.context_switches_count
, e.pages_kb AS memory_kB
, e.original_cost
, e.current_cost
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
JOIN sys.dm_os_memory_cache_entries AS e
ON p.memory_object_address = e.memory_object_address
WHERE p.cacheobjtype = 'Compiled Plan'
AND
e.type IN ('CACHESTORE_SQLCP','CACHESTORE_OBJCP')
ORDER BY e.[type], p.objtype, e.current_cost DESC
Queries χωρίς Plan Cache Entries
Πρέπει να επισημανθεί ότι υπάρχουν T-SQL statements που εκτελούνται και δεν μπαίνουν στην plan cache και τέτοιες περιπτώσεις είναι:
- Τα queries που τα objects δεν αναφέρονται με το σωστό τρόπο και αυτός είναι schema.object καθώς δεν μπορεί να γίνει object name resolution.
Τι σημαίνει αυτό;
Ας υποθέσουμε ότι έχουμε τα Α και Β schemas και το table Χ που υπάρχει και στα δύο αυτό schemas και γράφουμε το παρακάτω query SELECT * FROM X.
Σε αυτή την περίπτωση κατά την φάση του binding θα χρησιμοποιηθεί το default schema του χρήστη που θα εκτελέσει το query.
Αυτός είναι και ο βασικός λόγος που χρόνια φωνάζω ότι πρέπει να γράφουμε στα queries μας τα αναφερόμενα objects με το schema τους καθώς έτσι αυξάνουμε την πιθανότητα αυτά να ξαναχρησιμοποιηθούν.
- H άλλη περίπτωση είναι αυτά τα οποία τα έχουμε κάνει mark με το recompilation option ( OPTION (RECOMPILE) ή CREATE PROC WITH RECOMPILE ).
Αποδοτικότερη χρήση της Plan Cache
Από τα παραπάνω καταλαβαίνουμε ότι διαχείριση της plan cache είναι ένα task που δεν πρέπει να ξεχνάμε και ο SQL Server μας δίνει την δυνατότητα να το κάνουμε αυτό και να χρησιμοποιούμε αυτή όσο το δυνατόν καλύτερα με τα παρακάτω:
- Auto-parameterization - Με αυτό ο SQL Server αυτόματα κάνει replace τα literal values που χρησιμοποιούμε στα T-SQL statements με παραμέτρους στο execution plan.
- Optimize for ad-hoc workloads - Με αυτό αντί το statement να μπει στην plan cache από την αρχή μπαίνει εφόσον χρησιμοποιηθεί δεύτερη φορά.
Μπορείτε να δείτε περισσότερα για αυτό σε παλαιότερο άρθρο μου.
- Prepared SQL statements - Μέσα από τις εφαρμογές μας μέσω του db API (OLEDB, ODBC) καλό να είναι να κάνουμε prepared αυτά ώστε το QEP να γίνεται μία φορά compiled.
- The Object Plan plan cache store - Οι stored procedures, triggers , UDFs έχουν QEP στην Object Plan.
Κάθε Τ-SQL statements μέσα σε αυτές έχει το δικό του QEP μέσα στην Object Plan και όλα αυτά μαζί είναι στο ίδιο hash bucket και κάθε ένα από αυτά εκτελείται όταν για πρώτη φορά γίνεται η εκτέλεση της εκάστοτε sp, trigger, udf.
Αυτό σημαίνει ότι θα πάρει τις παραμέτρους που χρησιμοποιήθηκαν την πρώτη φορά και θα δημιουργήσει το QEP και όλες οι επόμενες εκτελέσεις θα χρησιμοποιήσουν αυτό ακόμα και με διαφορετικές παραμέτρους και μπορεί να οδηγήσει σε μια συμπεριφορά που την ονομάζουμε parameter sniffing για την οποία μπορείτε να δείτε περισσότερα για αυτό σε παλαιότερο video μου
Εξερευνώντας την Plan Cache
O SQL Server παρέχει αρκετά DMVs τα οποία μπορούμε να χρησιμοποιήσουμε και κάποια από αυτά τα έχετε ήδη δει στα queries που παρέθεσα παραπάνω και για τα οποία περισσότερες πληροφορίες μπορείτε να βρείτε στο documentation. Αυτά είναι:
//antonch