Overview

Από τη στιγμή που κάποιος θα ασχοληθεί με το Azure Synapse Analytics SQL Pool, ή είχε ασχοληθεί με τον προκάτοχο του το Azure SQL DW πρέπει να επιλύσει μια αρκετά περίπλοκη άσκηση.

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

Η λύση της συγκεκριμένης άσκησης δεν είναι πάντα εύκολη καθώς υπάρχουν πολλοί παράμετροι που πρέπει να ληφθούν υπόψη. Δεν πρέπει να αγνοήσουμε ότι τα φορτία που δέχεται ένα τέτοιο σύστημα είναι ποικίλα καθώς άλλες ανάγκες υπάρχουν κατά το data loading, άλλες για το data transformation, άλλες για το data query, άλλες για το data management κ.ο.κ.

Το Azure Synapse Analytics SQL Pool παρέχει επιλογές που πρέπει αυτός που θα ασχοληθεί μαζί του να γνωρίζει αρκετά καλά καθώς είναι αυτές που θα το βοηθήσουν στην επίλυση της άσκησης και που θα εξηγήσουμε στο άρθρο αυτό.

Architecture of Azure Synapse Analytics SQL Pool

Αν και είναι γνωστή η αρχιτεκτονική του Azure Synapse Analytics SQL Pool θα την επαναλάβω καθώς είναι θεωρώ το στοιχείο που βοηθάει στην κατανόηση όλων των παρακάτω.

Control Node

Ένα Azure Synapse Analytics SQL Pool έχει ένα Control Node που αποτελεί το συνδετικό κρίκο με τα δυνητικά διαθέσιμα Compute Nodes αλλά και αυτούς που θέλουν να χρησιμοποιήσουν αυτό. Αποτελεί το front-end για τα applications και τα user connections. Πέρα από αυτό είναι το "μυαλό" όλης της αρχιτεκτονικής, καθώς κάθε φορά που ζητάμε να εκτελεστεί ένα query, το Control Node στο οποίο τρέχει το MPP engine μετατρέπει αυτό σε πολλαπλά queries που θα εκτελεστούν στο κάθε διαθέσιμο distribution (Control node) παράλληλα.

Compute Node(s)

Κάθε Compute Node είναι ένα ξεχωριστό instance που έχει τους δικούς πόρους προσφέροντας υπολογιστική ισχύ. Φιλοξενεί όλα ή μέρος των δεδομένων μας ανάλογα με τα DWUs που έχουμε ορίσει κάθε φορά. Ο αριθμός των διαθέσιμων Compute Nodes είναι από 1 έως 60 και εξαρτάται από τι DWUs χρησιμοποιούμε κάθε φορά.

Azure Storage

Τα δεδομένα μας είναι αποθηκευμένα σε Azure Storage, διαμοιρασμένα σε distributions ανάλογα με το πως έχουμε επιλέξει αυτό να γίνεται σε κάθε table (Hash, Round Robin, Replicate).

Data Movement Service (DMS)

Το DMS είναι αυτό που αναλαμβάνει να κάνει την μεταφορά των δεδομένων μεταξύ των Compute Nodes. Είναι μια τεχνολογία που είναι διάφανη σε εμάς αλλά εξυπηρετεί το να διαβάζουμε τα σωστά δεδομένα κάθε φορά.

Distributions

Είναι ο θεμέλιος λίθος για την ύπαρξη της παραλληλίας. Όπως ανέφερα και παραπάνω, κάθε φορά που ζητάμε την εκτέλεση ενός ερωτήματος αυτό χωρίζεται σε 60 μικρότερα queries τα οποία τρέχουν παράλληλα. Το κάθε ένα από αυτά δίνεται σε ένα από τα διαθέσιμα Compute Nodes τα οποία μπορεί να είναι από 1 έως 60 ανάλογα με τα χρησιμοποιούμενα DWUs.

Το πως έχει γίνει το distribution αυτό εξαρτάται από το τρόπο που έχουμε ορίσει να γίνεται αυτό σε κάθε πίνακα. Οι επιλογές που έχουμε για να υλοποιηθεί αυτό είναι οι παρακάτω:

Round robin

Θέτοντας αυτό σαν επιθυμητό τρόπο για το distribution των εγγραφών τα δεδομένα κατανέμονται απλά στο κάθε distribution. Σε όλους μας είναι γνωστό πως λειτουργεί το Round Robin, απλά πρέπει να επισημάνω ότι το distribution από το οποίο θα ξεκινήσει η διαδικασία επιλέγεται κάθε φορά τυχαία. Συνήθως επιλέγουμε να χρησιμοποιήσουμε αυτή την επιλογή σε staging tables για το data loading καθώς είναι η απλούστερη και γρηγορότερη επιλογή. Στις περιπτώσεις όμως που θέλουμε να εκτελέσουμε queries σε ένα table με αυτό σαν distribution method θα πρέπει να γνωρίζουμε ότι δεν θα έχουν την καλύτερη απόδοση. Ιδιαίτερη προσοχή πρέπει να δοθεί στο joins με round robin tables καθώς γίνεται καθολικό διάβασμα των δεδομένων.

Hash

Η επιλογή του hash γίνεται σε ένα πίνακα με την επιλογή ΜΙΑΣ κολώνας (field) με της οποίας τις τιμές γίνεται το distribution. Φυσικά πίσω από την σκηνή η επιλογή αυτής της κολώνας οδηγεί σε μια hash function που αναλαμβάνει να τοποθετήσει την κάθε εγγραφή στο αντίστοιχο distribution. Η επιλογή του hash distribution σε ένα table προσδίδει τη μέγιστη απόδοση σε queries που χρησιμοποιούν joins και aggregations σε μεγάλα tables.

Replicate

H επιλογή αυτού το distribution σε ένα table είναι καλό να γίνεται πχ σε ένα μικρό dimensional table. Το κέρδος είναι ότι επειδή το table είναι σε όλα τα distribution δεν υπάρχει η ανάγκη για μεταφορά των δεδομένων του σε περιπτώσεις που έχω join ή/και aggregated queries που το συμπεριλαμβάνουν. Το κόστος από την άλλη μεριά είναι ότι χρειαζόμαστε περισσότερο storage και υπάρχει overhead κάθε φορά που έχουμε writes σε αυτόν.

Scheduler

Ο scheduler βρίσκεται στο control node. Eίναι αυτός που βάζει σε σειρά τα request που κάνουμε με τα queries μας. Κάθε request που κάνουμε μπαίνει σε ένα queue περιμένοντας την σειρά του να εκτελεστεί όταν υπάρχουν τα διαθέσιμα resources που αυτό χρειάζεται.

Service Levels - Data Warehouse Units (DWUs)

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

Ένα query που διαβάζει ένα μεγάλο αριθμό γραμμών και που κάνει ένα complex aggregation είναι μια διαδικασία που απαιτεί I/O και CPU.

Ένα query που κάνει load data (PolyBase) στο Synapse SQL pool από Azure Data Lake είναι μια διαδικασία που απαιτεί network και CPU.

Το πόσο γρήγορα θα εκτελεστεί ένα CTAS (CREATE TABLE AS SELECT) command που κάνει copy table είναι μια διαδικασία που πρέπει να διαβάσει δεδομένα από το storage να κάνει distribute αυτά σε όλα τα διαθέσιμα nodes και να τα γράψει στο storage, άρα είναι μια διαδικασία που απαιτεί CPU, IO και network.

Ένα DWU (cDWU για το Gen 2 και σε αυτά θα αναφερθώ καθώς θεωρώ το Gen 1 παρωχημένο) είναι η μονάδα με την οποία ορίζουμε την απόδοση του Azure Synapse Analytics SQL pool. Η μονάδα αυτή περιλαμβάνει τα CPU, memory, IOPS, αλλά όχι μόνο.

Αρχικά θα πρέπει να κατανοήσουμε τη σχέση των Service Levels DWUs με τον αριθμό των Computes nodes που το καθένα προσφέρει, τον αριθμό των distributions, αλλά και το ποσό της μνήμης που έχουμε διαθέσιμη για χρήση.

Ο παραπάνω πίνακας μας λέει ότι αν επιλέξουμε

• DW100c έχουμε ένα Compute Node στο οποίο βρίσκονται και τα 60 distributions του εκάστοτε table που θα έχουμε στο DW που έχουμε φτιάξει και ότι έχουμε 60GB διαθέσιμη μνήμη. Δηλαδή αν έχουμε ένα DW 600 ΤΒ αυτό όλο είναι σε ένα compute node.
• DW1000C έχουμε δύο Compute Nodes όπου στο καθένα έχουμε 30 distributions του εκάστοτε table που θα έχουμε στο DW που έχουμε φτιάξει και ότι έχουμε 600GB διαθέσιμη μνήμη. Δηλαδή αν έχουμε ένα DW 600 ΤΒ αυτό όλο είναι σε δύο compute nodes που το καθένα έχει περίπου 300ΤΒ δεδομένα.
• DW30000C έχουμε εξήντα Compute Nodes όπου στο καθένα έχουμε από ένα distribution του εκάστοτε table που θα έχουμε στο DW που έχουμε φτιάξει και ότι έχουμε 18000GB διαθέσιμη μνήμη. Δηλαδή αν έχουμε ένα DW 600 ΤΒ αυτό όλο είναι σε εξήντα compute nodes που το καθένα έχει περίπου 10ΤΒ δεδομένα.

Set desired Service Level (DWUs) in a database

Μπορούμε να ορίσουμε τα επιθυμητά DWUs τόσο στη δημιουργία της databases είτε από το portal είτε με powershell είτε με Τ-SQL με την χρήση της CREATE DATABASE.

CREATE DATABASE [abcdw2020]  
(
    EDITION = 'DataWarehouse', 
    SERVICE_OBJECTIVE = 'DW200c', 
    MAXSIZE = 128 GB
);

Μπορούμε να δούμε τι DWU έχει κάθε database που έχουμε ρωτώντας το sys.database_service_objectives view.

SELECT
     db.name [Database]
,    ds.edition [Edition]
,    ds.service_objective [Service Objective]
FROM
     sys.database_service_objectives as ds
JOIN
    sys.databases as db ON ds.database_id = db.database_id

Change desired Service Level (DWUs) in a database

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

ALTER DATABASE abcdw2020
MODIFY (SERVICE_OBJECTIVE = 'DW100c');

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

• Γίνονται kill όλα τα active sessions.
• Γίνονται rollback όλα τα ανοικτά transactions.
• Γίνονται detached όλα τα compute nodes και μετά re-attached όσα χρειάζονται (+/- ανάλογα αν πάμε προς τα πάνω ή προς τα κάτω).

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

WHILE
(
    SELECT TOP 1 state_desc
    FROM sys.dm_operation_status
    WHERE
        1=1
        AND resource_type_desc = 'Database'
        AND major_resource_id = 'abcdw2020'
        AND operation = 'ALTER DATABASE'
    ORDER BY
        start_time DESC
) = 'IN_PROGRESS'
BEGIN
    RAISERROR('Scale operation in progress',0,0) WITH NOWAIT;
    WAITFOR DELAY '00:00:05';
END
PRINT 'Complete';

Resource Classes - The "old" Workload Management

Τα resource classes είναι προκαθορισμένα. Ορίζουν δύο στοιχεία, compute resources (διαθέσιμη μνήμη ανά query) και concurrency (πόσα queries μπορούν να εκτελεστούν ταυτόχρονα παράλληλα).

Small - Large Resource Classes

To κάθε resource class, ορίζει το πόσα resources το κάθε query θα καταναλώνει και τον αριθμό των queries που θα εκτελούνται παράλληλα.

Είναι ένας συμβιβασμός μεταξύ memory και concurrency που γίνεται με μια λογική που:

• Τα small resource classes δίνουν λιγότερη διαθέσιμη μνήμη σε ένα query αλλά πολλά queries μπορούν να εκτελούνται παράλληλα.
• Τα large resource classes δίνουν περισσότερη διαθέσιμη μνήμη σε ένα query αλλά λιγότερα queries μπορούν να εκτελούνται παράλληλα.

Static - Dynamic Resource Classes

Πέρα όμως από την διάκριση σε small και large, χωρίζονται σε static και dynamic.

Τα static πρέπει να χρησιμοποιούνται σε datasets που δεν αλλάζουν. Πάντα κάνουν allocate το ίδιο μέγεθος μνήμης ανεξάρτητα από τα DWUs που έχουν οριστεί κάθε φορά και έτσι μπορούν να εκτελούνται περισσότερα queries σε αυτά. Tα διαθέσιμα static resource classes είναι τα staticrc10, staticrc20, staticrc30, staticrc40, staticrc50, staticrc60, staticrc70, staticrc80.

Τα dynamic χρησιμοποιούνται σε datasets που μεγαλώνουν διαρκώς σε μέγεθος. Δεν κάνουν σταθερό allocate μνήμης για αυτό και αυτή αυξάνεται ή μειώνεται όταν αυξάνονται ή μειώνονται αντίστοιχα τα DWUs. Τα διαθέσιμα dynamic resource classes είναι τα smallrc, mediumrc, largerc, xlargerc.

Concurrency Slots

Αναφέρθηκε παραπάνω ότι τα resource classes ορίζουν και το concurrency. Για να μπορέσει να γίνει ο έλεγχος και ο περιορισμός των resources με την χρήση των resource classes έπρεπε να υπάρχει ένα στοιχείο αναφοράς. Αυτό το στοιχείο είναι τα concurrency slots τα οποία ορίζουν το concurrency.

Κάθε query πριν ξεκινήσει να εκτελεστεί πρέπει να έχει διασφαλιστεί ότι υπάρχουν διαθέσιμα τα concurrency slots που χρειάζεται, τα οποία αποδεσμεύει όταν ολοκληρωθεί η εκτέλεση του.

Το πόσα συνολικά concurrency slots είναι διαθέσιμα εξαρτάται από τι DWUs έχουν οριστεί κάθε φορά.

Όταν πριν χρόνια ασχολήθηκα με το Azure SQL DW τα concurrency slots μου θύμισαν τα worker threads που έχουμε στον SQL Server. Για να το κάνω ακόμα ποιο κατανοητό φανταστείτε ότι έχω ένα πούλμαν που έχει συγκεκριμένο αριθμό θέσεων. Τα μικρά έχουν λιγότερες θέσεις, τα μεγάλα περισσότερες θέσεις. Όπου πούλμαν βάλτε DWUs και όπου θέσεις concurrency slots.

Database Roles

Όλα τα resource classes έχουν υλοποιηθεί σαν database roles σε κάθε database. Σε αυτά τα database roles ορίζουμε σαν members τους user που θέλουμε να έχουν το αντίστοιχο resource class. Μπορείτε να δείτε αυτούς τους database roles με το εξής query.

SELECT name
FROM   sys.database_principals
WHERE  name LIKE '%rc%' AND type_desc = 'DATABASE_ROLE';

Κάθε user, από προεπιλογή, ανήκει στο smallrc database role, αλλά μπορεί να ανήκει και σε άλλους database roles ταυτόχρονα. Αν συμβαίνει αυτό τότε

• Τα dynamic resource classes υπερισχύουν των static.
  Αν ένας user ανήκει στo database role mediumrc που αντιστοιχεί στη dynamic mediumrc resource class και ταυτόχρονα στο database role staticrc80 που αντιστοιχεί στην αντίστοιχη static resource class τότε τα queries του εκτελούνται με τη mediumrc resource class.
• Τα large resource classes υπερισχύουν των small resource classes.
  Αν ένας user ανήκει στο database role mediumrc που αντιστοιχεί στην mediumrc resource class και ταυτόχρονα στο database role largerc που αντιστοιχεί στην αντίστοιχη resource class τότε τα queries του εκτελούνται με τη largerc resource class.

Ορίζουμε την επιθυμητή resource class σε κάποιο user αρκεί να εκτελέσουμε την sp_addrolemember.

Αφαιρούμε μια resource class σε κάποιο user αρκεί να εκτελέσουμε την sp_droprolemember.

-- Assign Resource Class to user
EXEC sp_addrolemember 'mediumrc', 'user1';  
-- Remove a user from a Resource Class
EXEC sp_droprolemember 'mediumrc', 'user1';  

Affected and Not Affected Operations by Resource Classes

Με τη χρήση των resource classes ελέγχονται συγκεκριμένα operations ως προς το resource management

The New Workload Management

Όπως αναφέρθηκε και παραπάνω στο Synapse SQL pool δημιουργήθηκε ένας νέος τρόπος με το οποίο μπορούμε να κάνουμε καλύτερα resource management έτσι ώστε να έχουμε governance και καλύτερο έλεγχο στα workloads. Δεν χρειάζεται πλέον να χρησιμοποιούμε τα resource class.

Στην εικόνα βλέπουμε ότι τα φορτία που δέχεται το Synapse SQL Pool είναι κυρίως selects, inserts, updates, deletes, που κρύβονται πίσω από ETL/ELT διαδικασίες, Power BI reports, Cube processes, data loading από Data Lakes κλπ.

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

Ο τρόπος με τον οποίο γίνονταν η κατανομή των resources με το να βάζουμε το user σε ένα ρόλο δεν έδινε το καλύτερο αποτέλεσμα. Αν είχαμε δύο users στο ίδιο resource class τότε ήταν ίσοι αλλά δεν σημαίνει ότι αυτά που εκτελούσαν είχαν την ίδια βαρύτητα.

Αυτός ο νέος τρόπος παρέχει, τρεις έννοιες που πρέπει να γνωρίζουμε καθώς είναι αυτές που χρησιμοποιεί και αυτές είναι οι Classification, Importance, Isolation.

Classification

Όπως αναφέρθηκε και παραπάνω δύο users στο ίδιο resource class έχουν ακριβώς τα ίδια resources. Πέρα όμως από αυτό ένας user που ήταν ενταγμένος σε κάποιο resource class όλα του τα queries εκτελούνταν κάτω από αυτή την ομπρέλα.

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

Ουσιαστικά δεν είχαμε άλλη δυνατότητα από το να αλλάζουμε το resource class που άνηκε κάθε φορά ο user, αλλά αυτό ήταν μεγάλος πόνος και τις περισσότερες φορές γίνονταν λάθη που δεν έφερναν το επιθυμητό αποτέλεσμα.

Ακόμα ένας λόγος ήταν ότι τα resource classes, όπως φαίνεται και στους παραπάνω πίνακες, έχουν προκαθορισμένα ποσά/ποσοστά στο τι resources μπορούν να καταναλώσουν.

Με το classification που το νέο workload management παρέχει μπορούμε να έχουμε, θεωρώ, το πλήρη έλεγχο σε πολλαπλά επίπεδα, από επίπεδο user μέχρι query.

Importance

Η διαφορετική βαρύτητα που τα workloads ενός data warehouse έχουν, αλλά και οι locking διενέξεις μεταξύ readers και writes έπρεπε να διευθετηθούν. Για παράδειγμα μπορεί να θέλουμε το data loading να υπερισχύει άλλων διαδικασιών ή το ανάποδο. Αυτό για να γίνει πρέπει να είμαστε σε θέση να θέσουμε προτεραιότητες με ένα όσο το δυνατό αποτελεσματικό τρόπο.

Με το νέο workload management έχουμε την δυνατότητα κατά την στιγμή του classification να ορίσουμε το importance level θέτοντας μια από τις τιμές low, below_normal, normal, above_normal, high.

Isolation

Τα resource classes ορίζουν συγκεκριμένα resources. Η ανάθεση ενός από αυτά σε users δίνει μεν την δυνατότητα να χρησιμοποιηθούν αυτά από τα queries που κάνουν αλλά δεν ήταν πάντα το καλύτερο. Για παράδειγμα ένας user ανήκει στη smallrc resource class αλλά ένα query θέλει παραπάνω resources αλλά όχι πολλά παραπάνω. Αναγκαστικά με την χρήση των resource classes τον βάζαμε στην αμέσως επόμενη με αποτέλεσμα όλα τα αλλά να λαμβάνουν περισσότερα resources. Με αυτό τον τρόπο είχαμε σπατάλη.

Πέρα όμως από αυτό το ουσιαστικότερο είναι ότι θέλουμε να διασφαλίσουμε ότι ο συγκεκριμένος user όταν εκτελεί ένα query που ζητάει συγκεκριμένα resources αυτά είναι διαθέσιμα.

Με το νέο workload management έχουμε την δυνατότητα να δημιουργήσουμε ένα isolation workload group και να διασφαλίσουμε τα resources που θέλουμε να έχει ο user ή το query καθώς αυτά γίνονται reserved.

Implementation of New Workload Management

Η υλοποίηση του νέου workload management γίνεται χρησιμοποιώντας τα workload groups και classifiers.

Create Classifier(s)

Όπως είπαμε όλα ξεκινάνε από το classifier. Μπορούμε να δούμε τους διαθέσιμους classifiers με την χρήση της sys.workload_management_workload_classifiers και την επικουρική σε αυτή sys.workload_management_workload_classifier_details.

select * from sys.workload_management_workload_classifiers;
select * from sys.workload_management_workload_classifier_details;

Κάθε request/query που κάνουμε, με βάση το user και τα χαρακτηριστικά του, γίνεται assign σε κάποιο classifier. Ακόμα και στην περίπτωση που δεν ταιριάζει σε κάποιον γίνεται assign στο default classifer που είναι o smallrc.

Avoid misclassification

Θα παρατηρήσατε βέβαια ότι το group_name, name έχουν ακριβώς τα ίδια ονόματα με τις resource classes. Ο λόγος που έχει γίνει αυτό είναι για σκοπούς migration από resource classes σε classifiers.

Μάλιστα για να μην έχουμε προβλήματα στο classification προτείνεται να κάνουμε remove όλα τα resource class mappings που έχουμε ήδη. Μπορούμε να βρούμε αυτά με το παρακάτω query.

SELECT  r.name AS [Resource Class]
,       m.name AS membername
FROM    sys.database_role_members rm
JOIN    sys.database_principals AS r ON rm.role_principal_id = r.principal_id
JOIN    sys.database_principals AS m ON rm.member_principal_id = m.principal_id
WHERE   r.name IN ('mediumrc','largerc','xlargerc','staticrc10','staticrc20','staticrc30','staticrc40','staticrc50','staticrc60','staticrc70','staticrc80');

Για το κάθε ένα member που επιστρέφει το παραπάνω query θα πρέπει να βγει από το resource class που είναι με την sp_droprolemember.

sp_droprolemember '[Resource Class]', membername

CREATE WORKLOAD CLASSIFIER

Για να δημιουργήσουμε ένα δικό μας classifier χρησιμοποιούμε την CREATE WORKLOAD CLASSIFIER

H σύνταξη και οι παράμετροι που αυτή δέχεται είναι παρακάτω

CREATE WORKLOAD CLASSIFIER classifier_name  
WITH  
    (   WORKLOAD_GROUP = 'name'  
    ,   MEMBERNAME = 'security_account' 
[ [ , ] WLM_LABEL = 'label' ]  
[ [ , ] WLM_CONTEXT = 'context' ]  
[ [ , ] START_TIME = 'HH:MM' ]  
[ [ , ] END_TIME = 'HH:MM' ]  
  
[ [ , ] IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH }]) 
[;]

WORKLOAD_GROUP

Είναι το group που περιέχει το πόσα resources μπορεί να καταναλώσει το request/query που θα γίνει assign από το συγκεκριμένο classifier.

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

SELECT * FROM sys.workload_management_workload_groups;
GO

MEMBERNAME

Κάθε request/query εκτελείται μέσω κάποιου database user ή database role ή Azure Active Directory login ή Azure Active Directory group.

Σε αυτή την παράμετρο ορίζουμε κάποιο από τα παραπάνω το οποίο θέλουμε ο classifier να χρησιμοποιήσει σαν κριτήριο για να γίνει assign στο request/query.

Η παράμετρος αυτή είναι υποχρεωτική και θα πρέπει να γνωρίζουμε τα εξής:

• Εάν η τιμή της παραμέτρου είναι database user αντί για database role, μεγαλύτερη βαρύτητα έχει ο database user για την επιλογή του classification.
• Εάν ένας user είναι μέλος σε πολλαπλούς database roles με διαφορετικούς classifiers τότε γίνεται χρήση αυτού που έχει τα περισσότερα resources.

WLM_LABEL

Ένας user μπορεί να εκτελεί πολλά requests/queries. Αν θέλουμε κάποια να έχουν διαφορετική μεταχείριση τότε σε αυτά χρησιμοποιούμε την OPTION(LABEL) όπως για παράδειγμα:

CREATE TABLE stg.Customers
WITH ( DISTRIBUTION = ROUND_ROBIN,HEAP)
AS SELECT  * FROM  ext.Customers
OPTION (LABEL = 'CTAS : Load Customers to Stage');
GO
SELECT COUNT(*) FROM stg.Customers
OPTION (LABEL = 'Count Customers from Stage');
GO

Με αυτό τον τρόπο ορίζουμε στον εκάστοτε classifier την αντίστοιχη τιμή στην παράμετρο, η οποία θα χρησιμοποιηθεί στα κριτήρια του classification. Είναι από τις αγαπημένες μου παραμέτρους.

CREATE WORKLOAD CLASSIFIER wcl1 WITH  
( 
    WORKLOAD_GROUP = '...'
   ,MEMBERNAME = '...'
   ,WLM_LABEL = 'CTAS : Load Customers to Stage' 
);
GO
CREATE WORKLOAD CLASSIFIER wcl2 WITH  
( 
    WORKLOAD_GROUP = '...'
   ,MEMBERNAME = '...'
   ,WLM_LABEL = 'Count Customers from Stage' 
);
GO

WLM_CONTEXT

Μπορούμε να κάνουμε ότι ακριβώς κάνουμε με την χρήση της WLM_LABEL χωρίς να χρειάζεται να προσθέσουμε το OPTION(LABEL) στα queries αλλά αυτό να γίνει με ένα πιο γενικό τρόπο. Ο τρόπος αυτός είναι με το session context και την χρήση της sp_set_session_context όπως στο παράδειγμα που ακολουθεί.

EXEC sys.sp_set_session_context @key = 'wlm_context', @value = 'Count Customers from Stage';
SELECT COUNT(*) FROM stg.Customers
GO

Στον classifier έχουμε ορίσει την τιμή αυτή στο WLM_CONTEXT

CREATE WORKLOAD CLASSIFIER wcl4 WITH  
( 
    WORKLOAD_GROUP = '...'
   ,MEMBERNAME     = '...'
   ,WLM_CONTEXT    = 'Count Customers from Stage' 
)
GO

START_TIME and END_TIME

Με αυτές τις δύο παραμέτρους ορίζουμε το χρονικό παράθυρο που θα χρησιμοποιηθεί σαν κριτήριο στο classification. Αυτό μου δίνει την ευχέρεια να ορίζω πχ ότι το βράδυ το data loading θα έχει περισσότερα resources από ότι το πρωί που θέλω τα reporting queries να έχουν περισσότερα. Από τις πολύ αγαπημένες μου παραμέτρους.

CREATE WORKLOAD CLASSIFIER wcl3 WITH  
(
    WORKLOAD_GROUP = '...'
   ,MEMBERNAME = '...'  
   ,START_TIME = '22:00'
   ,END_TIME = '02:00' 
);
GO

IMPORTANCE

Ορίζουμε το importance του request/query. Οι τιμές που μπορεί να πάρει είναι μια από τις:

• LOW
• BELOW_NORMAL
• NORMAL (default)
• ABOVE_NORMAL
• HIGH

How Classification works

Με όσα έχουν προηγούμενα αναφερθεί έχει γίνει κατανοητό το τι είναι οι classifiers.

Αυτό που μένει να γίνει κατανοητό είναι το πως επιλέγεται κάθε φορά o workload classifier ιδιαίτερα στις περιπτώσεις που ένας user έχει περισσότερους από έναν classifiers.

Από τις παραμέτρους που δέχεται η CREATE WORKLOAD CLASSIFIER οι μόνες που δεν συμμετέχουν σαν κριτήρια επιλογής είναι η WORKLOAD_GROUP και η IMPORTANCE, όλες οι άλλες συμμετέχουν και έχουν διαφορετική βαρύτητα στην επιλογή ή όχι ενός classifier.

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

Στο παραπάνω πίνακα έχουμε δύο διαφορετικούς classifiers με τις παραμέτρους τους και το βάρος που έχει η κάθε παράμετρος για τον userA.

Αν ο userA εκτελέσει ένα query που έχει LABEL='salesreport' και είναι μεταξύ 20:00-07:00 θα εκτελεστεί με το Α classifier και όχι με το Β καθώς ο Α έχει μεγαλύτερο σκορ βάρους από τον Β.

Αν θέλαμε να εκτελεστεί με το workload group και το importance που έχει το B θα έπρεπε να έχουμε ακόμα ένα classifier όπως φαίνεται στο πίνακα παρακάτω

Σε κάθε περίπτωση μπορούμε να κάνουμε monitor με ποιο classifier εκτελέστηκε στο συγκεκριμένο request/query με το παρακάτω query

SELECT s.session_id,s.login_name,r.classifier_name,r.command,r.submit_time, r.start_time
FROM sys.dm_pdw_exec_sessions as s
JOIN sys.dm_pdw_exec_requests as r on s.session_id=r.session_id
WHERE s.login_name='userA' AND r.command like '<query>%';

Importance Levels

Όπως είδαμε στους classifiers υπάρχει το importance level, το οποίο δέχεται τις τιμές LOW, BELOW_NORMAL, NORMAL, ABOVE_NORMAL, HIGH, με default value το NORMAL.

Ο ρόλος του είναι σημαντικός καθώς εκτός από το προφανές, που είναι η ιεράρχηση των queries, μας δίνει δυνατότητες διαχείρισης που μέχρι πριν λίγο καιρό δεν είχαμε και αφορούν locking και τα non-uniform requests.

Importance Fundamentals

Παραπάνω αναφέρθηκαν τα concurrency slots που έχουμε με την χρήση των resource classes. Αυτά όμως με την χρήση του νέου workload management και των workload groups δεν ισχύουν. Πλέον ανά service level έχουμε το μέγιστο αριθμό σε concurrent queries. Ο παρακάτω πίνακας δείχνει τα στοιχεία αυτά.

Αν έχουμε DW200c σαν service level έχουμε 8 concurrent queries να εκτελούνται και όλα τα άλλα περιμένουν στο queue να πάρουν την σειρά τους με FIFO λογική όπως συμβαίνει σε αυτά. Αν αποτυπώσουμε αυτό σε εικόνα θα είναι η παρακάτω.

Αυτό σημαίνει ότι για να εκτελεστεί το Q9 θα πρέπει κάποιο από τα Q1-Q8 να έχει ολοκληρωθεί, για να εκτελεστεί το Q10 θα πρέπει κάποιο από αυτά που είναι running να έχει ολοκληρωθεί κ.ο.κ.

Αυτή ήταν η διαδικασία που o scheduler έκανε και συνεχίζει να κάνει μέχρι τώρα που δεν υπήρχε η χρήση του importance.

Πλέον με την χρήση του importance που ορίζεται στον classifier αυτό άλλαξε.

Αν για παράδειγμα ο classifier που έχει το Q12 έχει high importance και τα Q9-Q11 χαμηλότερο, τότε με το που θα ολοκληρωθεί κάποιο από τα Q1-Q8, ο scheduler αυτό θα βάλει στα running queries καθώς έχει υψηλότερη προτεραιότητα σε σχέση με τα άλλα.

Locking and Importance

Locking without importance

Πριν την χρήση του importance όπου όλα τα queries είχαν την ίδια βαρύτητα, αν το Q4 είχε κάνει lock πχ ένα table διότι έκανε ένα μεγάλο read operation και το Q9 έπρεπε να κάνει data loading ή κάποιο άλλο write operation όπως partition switching στο ίδιο πίνακα τότε αυτό περίμενε μέχρι αυτό να ολοκληρωθεί το Q4. Λογικό και αναμενόμενο και δεν έχει αλλάξει αυτό σαν λογική καθώς είναι 101% σωστή.

Ποιο όμως είναι το πρόβλημα σε αυτό το σενάριο;

Το πρόβλημα είναι όταν έχω περισσότερα από ένα queries που ήδη τρέχουν και έχουν κάνει lock σε διαφορετικά resources το ίδιο table.

Αν το Q4 και το Q6 έχουν locks στο ίδιο table, τότε το Q9 θα πρέπει να περιμένει στο queue μέχρι αυτά να ολοκληρωθούν, το οποίο είναι λογικό.

Αν το Q4 ολοκληρωθεί πριν την ολοκλήρωση του Q6 θα αφήσει μια θέση κενή στα running queries η οποία θα καλυφθεί από το Q10 το οποίο είναι και αυτό λογικό.

Αν όμως το Q10 έκανε και αυτό lock στο table, ακόμα και αν ολοκληρωθεί το Q6, το Q9 δεν μπορεί να ξεκινήσει.

Φανταστείτε τώρα ότι την θέση του Q6 την πάρει το Q11 το οποίο και αυτό κάνει lock το table. Η πιθανότητα να εκτελεστεί το Q9 το δυνατόν συντομότερα δεν είναι εφικτή. Αν δε η εργασία του Q9 είναι σημαντική τότε έχουμε πραγματικό πρόβλημα.

Locking with importance

Το παραπάνω πρόβλημα με την χρήση του importance λύνεται ως εξής:

Αν το Q9 εκτελεστεί με ένα classifier που έχει υψηλότερο importance, έστω high, από τα Q10-Q12, έστω normal, τότε αν το Q4 ολοκληρωθεί δεν θα μπει στην θέση του το Q10 ή κάποιο από τα υπόλοιπα που έχουν χαμηλότερη προτεραιότητα. καθώς το Q9 έχει προτεραιότητα. Αφού το Q6 ολοκληρωθεί τότε θα εκτελεστεί το Q9 και μετά όλα τα υπόλοιπα.

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

Non-uniform requests

Όπως μέχρι τώρα έχουμε δει ο scheduler, που βρίσκεται στο control node, είναι αυτός που βάζει σε σειρά τα request που κάνουμε με τα queries μας. Κάθε request που κάνουμε μπαίνει σε ένα queue περιμένοντας την σειρά του να εκτελεστεί με βάση την σειρά του και τα διαθέσιμα resources που αυτό χρειάζεται για να εκτελεστεί.

Για να γίνει κατανοητό στο παράδειγμα της παραπάνω εικόνας έστω ότι έχουμε το εξής σενάριο.

• Τα Q1-Q8 που είναι ήδη σε φάση εκτέλεσης και εκτελούνται με classifiers που έχουν smallrc workload group.
• Στο queue τα
  - Q9 & Q11 είναι με classifiers που έχουν largerc workload group
  - τα Q10 & Q12 είναι με classifiers που έχουν smallrc workload group
  - σε όλα το importance level είναι το ίδιο, έστω normal.

Αν έστω ολοκληρωνόταν το Q2 την θέση του δεν θα μπορούσε να πάρει το Q9, που είναι το επόμενο στο queue, καθώς δεν υπάρχουν τα διαθέσιμα resources για αυτό. Αντίθετα θα έμπαινε στην θέση του το Q10 και το Q9 θα περίμενε. Στην ουρά πλέον θα ήταν τα Q9, Q11, Q12.

Αν στην συνέχεια ολοκληρώνονταν πχ το Q3 τότε στην θέση του θα έπαιρνε το Q12 και στην ουρά θα είχαμε τα Q9, Q11.

Για να ολοκληρωθεί το Q9 θα έπρεπε να ολοκληρώσουν περισσότερα από ένα από τα running queries.

With the usage of importance level

Tώρα με το importance level αν έχω πει στους classifiers των Q9, Q11 ότι αυτό είναι πχ high τότε:

Mε την ολοκλήρωση πχ του Q2 δεν παίρνει τη θέση του το Q10 καθώς έχει χαμηλότερο importance level από το Q9, αλλά ούτε και αυτό εκτελείται καθώς θέλει περισσότερα resources. Αν στην συνέχεια ολοκληρωθεί το Q3 τότε επειδή υπάρχουν τα διαθέσιμα resources εκτελείται το Q9. Άρα στην ουρά είναι πλέον τα Q10, Q11, Q12.

Συνεχίζοντας το παράδειγμα αν πχ το Q4 ολοκληρωθεί πάλι δεν θα εκτελεστούν ούτε το Q10, ούτε το Q12 γιατί έχουν χαμηλότερο importance level από το Q11. Αν ολοκληρωθεί και το Q5 τότε θα εκτελεστεί το Q11 καθώς έχει μεγαλύτερη προτεραιότητα και πλέον υπάρχουν τα resources και στην ουρά έχουν μείνει πλέον τα Q10, Q12.

Αρκετά χρήσιμο σε πολλά σενάρια, όπως για παράδειγμα data loading σε fact columnstore tables που σαν διαδικασίες θέλουν περισσότερα resources. Φυσικά είναι επιλογή μας το πως θα δράσουμε καθώς δεν είναι τίποτα default.

Monitor and Verification the Importance level usage

Χρησιμοποιώντας το παρακάτω query μπορούμε να κάνουμε monitor αλλά και να επιβεβαιώσουμε τα όλα όσα γράφτηκαν παραπάνω.

Μπορεί εύκολα να το κάνουμε αυτό παρατηρώντας τις κολώνες summit_time και start_time.

Η πρώτη δείχνει τη χρονική στιγμή που στάλθηκε το query και η δεύτερη το πότε ξεκίνησε η εκτέλεση της. Βλέποντας αυτές σε συνάρτηση με τις classifier_name και importance επιβεβαιώνουμε αυτά.

SELECT s.session_id, s.login_name, r.classifier_name, r.importance, r.command, r.submit_time, r.start_time
FROM sys.dm_pdw_exec_sessions as s
JOIN sys.dm_pdw_exec_requests as r on s.session_id=r.session_id ;

Workload Groups

Τα workload groups είναι containers και η βάση πάνω στην οποία έχει χτιστεί όλη η υλοποίηση του νέου workload management. Μέσα από αυτά είμαστε σε θέση να ορίσουμε το πως θέλουμε να κατανείμουμε τα resources στα queries μας.

Mπορούμε να δούμε τα διαθέσιμα worlkload groups που έχουμε στην database με το παρακάτω query στη sys.workload_management_workload_groups.

SELECT * FROM sys.workload_management_workload_groups;
GO

CREATE WORKLOAD GROUP

Μπορούμε να δημιουργήσουμε workload groups με την χρήση της CREATE WORKLOAD GROUP της οποίας το syntax είναι

CREATE WORKLOAD GROUP group_name
 WITH
 (  [ MIN_PERCENTAGE_RESOURCE = value ]
  [ [ , ] CAP_PERCENTAGE_RESOURCE = value ]
  [ [ , ] REQUEST_MIN_RESOURCE_GRANT_PERCENT = value ]
  [ [ , ] REQUEST_MAX_RESOURCE_GRANT_PERCENT = value ]
  [ [ , ] IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH } ]
  [ [ , ] QUERY_EXECUTION_TIMEOUT_SEC = value ] )
  [ ; ]

IMPORTANCE

Με αυτή την παράμετρο ορίζεται το default importance level που έχει αναφερθεί και παραπάνω. Αν στο classifier που δημιουργούμε και στον οποίο χρησιμοποιούμε το συγκεκριμένο workload group οριστεί διαφορετικό, ισχύει αυτό του classifier.

QUERY_EXECUTION_TIMEOUT_SEC

Ορίζεται το time out σε seconds των queries που είναι σε running state, και όχι όσο είναι αυτά στο queue. Η τιμή μηδέν σημαίνει ότι δεν υπάρχει time out.

MIN_PERCENTAGE_RESOURCE

Ορίζει το ελάχιστο ποσοστό των resources που το συγκεκριμένο workload group δεσμεύει προκαταβολικά από το σύνολο των διαθέσιμων resources που έχουμε και δεν πρόκειται πότε να το χάσει ή να το μοιραστεί με άλλα workload groups.

Αυτό σημαίνει ότι το ποσοστό που ορίζουμε σε αυτό, δεν μπορεί να είναι τέτοιο που να οδηγεί σε άθροισμα της παραμέτρου αυτής σε όλα τα workload groups που έχουμε διαθέσιμα πάνω από 100%. π.χ. Αν έχω τα workload groups WG1, WG2 στα οποία έχω βάλει στην συγκεκριμένη παράμετρο τις τιμές WG1 = 40%, WG2= 40% και φτιάξω το WG3 η μέγιστη τιμή που μπορώ να βάλω για αυτή είναι 20%.

CAP_PERCENTAGE_RESOURCE

Ορίζει το μέγιστο ποσοστό των resources που δυνητικά μπορούν να χρησιμοποιηθούν για όλα τα request που θα γίνουν classify στο συγκεκριμένο workload group. Πρέπει να είναι μεγαλύτερο από το MIN_PERCENTAGE_RESOURCE.

REQUEST_MIN_RESOURCE_GRANT_PERCENT

Ορίζει το ελάχιστο ποσοστό των resources που θα χρησιμοποιηθούν για κάθε request/query που θα γίνει classify στο συγκεκριμένο workload group.

REQUEST_MAX_RESOURCE_GRANT_PERCENT

Ορίζει το μέγιστο ποσοστό των resources που μπορούν να χρησιμοποιηθούν σε κάθε request/query.

Monitor Workload Groups runtime values

Μπορούμε να δούμε, να ελέγξουμε αλλά και να κατανοήσουμε όλα τα παραπάνω εκτελώντας query στην sys.dm_workload_management_workload_groups_stats.

SELECT * FROM sys.dm_workload_management_workload_groups_stats;
GO

Workload Isolation

Ένα από τα χαρακτηριστικά που έχει το νέο workload management είναι αυτό του isolation.

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

Αυτό μπορούμε να το κάνουμε ορίζοντας τιμή μεγαλύτερη από μηδέν στην παράμετρο MIN_PERCENTAGE_RESOURCE της CREATE WORKLOAD GROUP που είδαμε παραπάνω.

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

Πέρα από τη διασφάλιση των πόρων με την χρήση του isolation διασφαλίζεται και το concurrency level με την φόρμουλα

Concurrency = MIN_PERCENTAGE_RESOURCE / REQUEST_MIN_RESOURCE_GRANT_PERCENT

Αν για παράδειγμα θέλουμε να έχουμε διασφαλίσει ότι θα υπάρχουν τουλάχιστον 8 concurrency slots θα πρέπει η MIN_PERCENTAGE_RESOURCE να είναι ίση με 26 και η REQUEST_MIN_RESOURCE_GRANT_PERCENT ίση με 3,25. Για αυτό η τιμή της REQUEST_MIN_RESOURCE_GRANT_PERCENT πρέπει να είναι συντελεστής της MIN_PERCENTAGE_RESOURCE.

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

IF   ( REQUEST_MAX_RESOURCE_GRANT_PERCENT > REQUEST_MIN_RESOURCE_GRANT_PERCENT )
     AND
    ( CAP_PERCENTAGE_RESOURCE > MIN_PERCENTAGE_RESOURCE )
THEN MORE RESEOURCE ADDED PER REQUEST.

IF   ( REQUEST_MAX_RESOURCE_GRANT_PERCENT = REQUEST_MIN_RESOURCE_GRANT_PERCENT )
     AND
    ( CAP_PERCENTAGE_RESOURCE > MIN_PERCENTAGE_RESOURCE )
THEN POSSIBLE MORE RESEOURCE CAN ADDED PER REQUEST.

Conclusion

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

Ελπίζω να κατάφερα να εξηγήσω το πως μπορεί να λυθεί η άσκηση που έλεγα στην αρχή.

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

Φυσικά και πρέπει να χρησιμοποιηθεί το νέο workload management

//Antonios Chatzipavlis