go backarticles

Articles of SQLschool.gr Team

Overview

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

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

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

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

NOTE
Εύλογα θα αναρωτηθεί κάποια ή κάποιος, "γιατί αυτές δεν υπάρχουν στα docs;". Η απάντηση είναι ναι υπάρχουν αλλά είναι διάσπαρτες σε διάφορα σημεία και κάποιες φορές δημιουργούν περισσότερες απορίες. Με το άρθρο αυτό θέλω να τα βάλω όσα σε μια σειρά.

Architecture of Azure Synapse Analytics SQL Pool

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


Architecture of Azure Synapse Analytics SQL Pool
image

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.

ATTENTION
Υπάρχει όμως κάτι που πρέπει να προσεχθεί και αυτό αφορά την κολώνα που θα επιλεγεί για να γίνει το distribution. Αυτή θα πρέπει να έχει πάνω από 60 μοναδικές διακριτές τιμές αλλιώς θα οδηγηθούμε σε data skew φαινόμενα.

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, αλλά και το ποσό της μνήμης που έχουμε διαθέσιμη για χρήση.


Capacity by Service Objective Levels table
Service
level
Compute
nodes
Distributions per
Compute node
Memory per
data warehouse (GB)
DW100c 1 60 60
DW200c 1 60 120
DW300c 1 60 180
DW400c 1 60 240
DW500c 1 60 300
DW1000c 2 30 600
DW1500c 3 20 900
DW2000c 4 15 1200
DW2500c 5 12 1500
DW3000c 6 10 1800
DW5000c 10 6 3000
DW6000c 12 5 3600
DW7500c 15 4 4500
DW10000c 20 3 6000
DW15000c 30 2 9000
DW30000c 60 1 18000

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

  • 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.

SQL Script

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

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

SQL Script

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

Query Results
Database Edition Service Objective
abcdw2020 DataWarehouse DW200c
master System System0

Change desired Service Level (DWUs) in a database

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

SQL Script

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

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

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

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

SQL Script

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 μπορούν να εκτελεστούν ταυτόχρονα παράλληλα).

NOTE
Τα resource classes μέχρι πριν λίγο καιρό ήταν ο τρόπος με τον οποίο μπορούσαμε να διαχειριστούμε την απόδοση των queries. Αυτό ο τρόπος έχει πλέον αλλάξει καθώς χρειαζόμασταν δύο σημαντικά στοιχεία για την διαχείριση των workloads. Αυτά είναι το governance και καλύτερος έλεγχος των resources. Είναι απαραίτητο να γίνει αναφορά σε αυτά για γίνει κατανοητός αργότερα ο νέος τρόπος.

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.


Memory allocation for each resource class table
Service Level smallrc mediumrc largerc xlargerc
DW100c 25% 25% 25% 70%
DW200c 12.5% 12.5% 22% 70%
DW300c 8% 10% 22% 70%
DW400c 6.25% 10% 22% 70%
DW500c 5% 10% 22% 70%
DW1000c to DW30000c 3% 10% 22% 70%

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.


Maximum concurrent queries and Concurrency slots for each Static Resource class
Service
Level
Maximum
concurrent
queries
Concurrency
slots
available
Slots
used by
staticrc10
Slots
used by
staticrc20
Slots
used by
staticrc30
Slots
used by
staticrc40
Slots
used by
staticrc50
Slots
used by
staticrc60
Slots
used by
staticrc70
Slots
used by
staticrc80
DW100c 4 4 1 2 4 4 4 4 4 4
DW200c 8 8 1 2 4 8 8 8 8 8
DW300c 12 12 1 2 4 8 8 8 8 8
DW400c 16 16 1 2 4 8 16 16 16 16
DW500c 20 20 1 2 4 8 16 16 16 16
DW1000c 32 40 1 2 4 8 16 32 32 32
DW1500c 32 60 1 2 4 8 16 32 32 32
DW2000c 48 80 1 2 4 8 16 32 64 64
DW2500c 48 100 1 2 4 8 16 32 64 64
DW3000c 64 120 1 2 4 8 16 32 64 64
DW5000c 64 200 1 2 4 8 16 32 64 128
DW6000c 128 240 1 2 4 8 16 32 64 128
DW7500c 128 300 1 2 4 8 16 32 64 128
DW10000c 128 400 1 2 4 8 16 32 64 128
DW15000c 128 600 1 2 4 8 16 32 64 128
DW30000c 128 1200 1 2 4 8 16 32 64 128

Maximum concurrent queries and Concurrency slots for each Dynamic Resource class
Service Level Maximum
concurrent
queries
Concurrency
slots
available
Slots
used by
smallrc
Slots
used by
mediumrc
Slots
used by
largerc
Slots
used by
xlargerc
DW100c 4 4 1 1 1 2
DW200c 8 8 1 1 1 5
DW300c 12 12 1 1 2 8
DW400c 16 16 1 1 3 11
DW500c 20 20 1 2 4 14
DW1000c 32 40 1 4 8 28
DW1500c 32 60 1 6 13 42
DW2000c 32 80 2 8 17 56
DW2500c 32 100 3 10 22 70
DW3000c 32 120 3 12 26 84
DW5000c 32 200 6 20 44 140
DW6000c 32 240 7 24 52 168
DW7500c 32 300 9 30 66 210
DW10000c 32 400 12 40 88 280
DW15000c 32 600 18 60 132 420
DW30000c 32 1200 36 120 264 840

Database Roles

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

SQL Script

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.

ATTENTION
Όσοι είναι service administrators ανήκουν στο smallrc και δεν μπορεί αυτό να αλλάξει. Αυτός είναι και λόγος που δεν χρησιμοποιούμε αυτά τα user accounts σε τίποτα άλλο εκτός από τις αμιγώς administrator εργασίες. Είναι κακή πρακτική να χρησιμοποιούνται αυτά τα user account σε διαδικασίες data loading καθώς έχουν ελάχιστα resources. Service Administrator είναι αυτός που δημιουργούμε όταν κάνουμε την διαδικασία του provisioning και αν έχουμε Active Directory όσοι είναι Administrators σε αυτό.

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

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

SQL Script

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

NOTE
Την χρονική περίοδο που γράφτηκε το συγκεκριμένο άρθρο η ALTER ROLE xxxxx ADD MEMBER mmmmm, δεν υποστηρίζεται στο Azure Synapse Analytics SQL pool.

Affected and Not Affected Operations by Resource Classes

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

Affected Operations Not Affected Operations
INSERT-SELECT, UPDATE, DELETE
SELECT (when querying user tables)
ALTER INDEX - REBUILD or REORGANIZE
ALTER TABLE REBUILD
CREATE INDEX
CREATE CLUSTERED COLUMNSTORE INDEX
CREATE TABLE AS SELECT (CTAS)
Data loading operations
Data movement operations μέσω του Data Movement Service (DMS)
CREATE or DROP TABLE ALTER TABLE ... SWITCH, SPLIT, or MERGE PARTITION
ALTER INDEX DISABLE
DROP INDEX
CREATE, UPDATE, or DROP STATISTICS
TRUNCATE TABLE
ALTER AUTHORIZATION
CREATE LOGIN
CREATE, ALTER, or DROP USER
CREATE, ALTER, or DROP PROCEDURE
CREATE or DROP VIEW
INSERT VALUES
SELECT from system views and DMVs
EXPLAIN
DBCC

NOTE
Τα DMV δεν υπόκεινται σε κανένα περιορισμό καθώς πρέπει να μπορούμε να κάνουμε monitor σε κάθε περίπτωση.

The New Workload Management

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


Data Warehouse Loads
image

Στην εικόνα βλέπουμε ότι τα φορτία που δέχεται το 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.

NOTE
Την χρονική περίοδο που γράφτηκε το συγκεκριμένο άρθρο, το συγκεκριμένο feature είναι σε preview.

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.

SQL Script

select * from sys.workload_management_workload_classifiers;
select * from sys.workload_management_workload_classifier_details;
Results of sys.workload_management_workload_classifiers
classifier_id group_name name importance create_time modify_time is_enabled
1 smallrc smallrc normal 2020-04-12 20:39:28.567 2020-04-12 20:39:28.567 1
2 mediumrc mediumrc normal 2020-04-12 20:39:28.570 2020-04-12 20:39:28.570 1
3 largerc largerc normal 2020-04-12 20:39:28.573 2020-04-12 20:39:28.573 1
4 xlargerc xlargerc normal 2020-04-12 20:39:28.577 2020-04-12 20:39:28.577 1
5 staticrc10 staticrc10 normal 2020-04-12 20:39:28.580 2020-04-12 20:39:28.580 1
6 staticrc20 staticrc20 normal 2020-04-12 20:39:28.583 2020-04-12 20:39:28.583 1
7 staticrc30 staticrc30 normal 2020-04-12 20:39:28.587 2020-04-12 20:39:28.587 1
8 staticrc40 staticrc40 normal 2020-04-12 20:39:28.590 2020-04-12 20:39:28.590 1
9 staticrc50 staticrc50 normal 2020-04-12 20:39:28.593 2020-04-12 20:39:28.593 1
10 staticrc60 staticrc60 normal 2020-04-12 20:39:28.597 2020-04-12 20:39:28.597 1
11 staticrc70 staticrc70 normal 2020-04-12 20:39:28.600 2020-04-12 20:39:28.600 1
12 staticrc80 staticrc80 normal 2020-04-12 20:39:28.603 2020-04-12 20:39:28.603 1

Results of sys.workload_management_workload_classifier_details
classifier_id classifier_type classifier_type
1 membername smallrc
2 membername mediumrc
3 membername largerc
4 membername xlargerc
5 membername staticrc10
6 membername staticrc20
7 membername staticrc30
8 membername staticrc40
9 membername staticrc50
10 membername staticrc60
11 membername staticrc70
12 membername staticrc80

NOTE
Οι classifiers που έχουν classifier_id<=12 είναι αυτοί που δημιουργούνται αυτόματα σε κάθε database και τους ονομάζουμε system classifiers.

Κάθε 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.

SQL Script

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.

SQL Script

sp_droprolemember '[Resource Class]', membername

CREATE WORKLOAD CLASSIFIER

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

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

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

Syntax

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.

SQL Script

SELECT * FROM sys.workload_management_workload_groups;
GO
Results of sys.workload_management_workload_groups
group_id name importance min_
percentage_
resource
cap_
percentage_
resource
request_
min_
resource_
grant_
percent
request_
max_
resource
_grant
_percent
query_
execution_
timeout_
sec
query_
wait_
timeout_
sec
1 smallrc normal 0 100 3.00 3.00 0 0
2 mediumrc normal 0 100 10.00 10.00 0 0
3 largerc normal 0 100 22.00 22.00 0 0
4 xlargerc normal 0 100 70.00 70.00 0 0
5 staticrc10 normal 0 100 0.40 0.40 0 0
6 staticrc20 normal 0 100 0.80 0.80 0 0
7 staticrc30 normal 0 100 1.60 1.60 0 0
8 staticrc40 normal 0 100 3.20 3.20 0 0
9 staticrc50 normal 0 100 6.40 6.40 0 0
10 staticrc60 normal 0 100 12.80 12.80 0 0
11 staticrc70 normal 0 100 25.60 25.60 0 0
12 staticrc80 normal 0 100 51.20 51.20 0 0

NOTE
Το παραπάνω αποτέλεσμα περιέχει μόνο τα system workload groups. Ο λόγος ύπαρξης τους είναι για σκοπούς migration από resource classes σε classifiers. Μπορούμε να δημιουργήσουμε τα δικά μας και θα το δούμε αυτό παρακάτω. Επίσης δεν περιλαμβάνονται τα πεδία create_time, modify_time καθώς δεν έχουν αξία για το άρθρο αυτό.

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) όπως για παράδειγμα:

OPTION(LABEL) Samples

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. Είναι από τις αγαπημένες μου παραμέτρους.

WLM_LABEL samples of usage

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 όπως στο παράδειγμα που ακολουθεί.

WLM_CONTEXT usage sample

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

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

WLM_CONTEXT usage sample

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

NOTE
Για όσους δεν το γνωρίζουν να πω ότι κάτι τέτοιο μπορούμε να κάνουμε και στο SQL Server και δείχνω το πως στο video που έχω κάνει στο παρελθόν, Session Context in SQL Server

START_TIME and END_TIME

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

START_TIME and END_TIME usage sample

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

ATTENTION
Το format για την ώρα πρέπει να είναι HH:MM (στρατιωτική μορφή) και η ώρα πρέπει να είσαι σε UTC time zone.

IMPORTANCE

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

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

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

How Classification works

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

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

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

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

Classification parameter weighting
Classifier Parameter Weight
MEMBERNAME:USER 64
MEMBERNAME:ROLE 32
WLM_LABEL 16
WLM_CONTEXT 8
START_TIME/END_TIME 4

Example of Classification parameters weighting
Weight 64 32 16 8 4
Classifier WORKLOAD_GROUP IMPORTANCE MEMBERNAME
USER
MEMBERNAME
ROLE
WLM_LABEL WLM_CONTEXT START/END_TIME SCORE
A mediumrc NORMAL userA salesreport 80
B xlargerc HIGH userA 20:00-07:00 68

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

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


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

Example of Classification parameters weighting
Weight 64 32 16 8 4
Classifier WORKLOAD_GROUP IMPORTANCE MEMBERNAME
USER
MEMBERNAME
ROLE
WLM_LABEL WLM_CONTEXT START/END_TIME SCORE
A mediumrc NORMAL userA salesreport 80
B xlargerc HIGH userA 20:00-07:00 68
C xlargerc HIGH userA salesreport 20:00-07:00 84

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

SQL Script - Monitor Classifier usage

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. Ο παρακάτω πίνακας δείχνει τα στοιχεία αυτά.

Concurrency maximums for workload groups per Service Level
Service Level Maximum concurrent queries
DW100c 4
DW200c 8
DW300c 12
DW400c 16
DW500c 20
DW1000c 32
DW1500c 32
DW2000c 48
DW2500c 48
DW3000c 64
DW5000c 64
DW6000c 128
DW7500c 128
DW10000c 128
DW15000c 128
DW30000c 128

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


Scheduler process
image

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

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

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

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

NOTE
Αν όλα τα queries στο queue έχουν την ίδιο importance level η λογική συνεχίζει να είναι FIFO.

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 που αυτό χρειάζεται για να εκτελεστεί.

ATTENTION
Αυτά είναι τα δύο βασικά κριτήρια που χρησιμοποιεί ο scheduler ήδη, πριν δηλαδή την εμφάνιση του importance level, αλλά είναι και η ισχύουσα λογική όταν όλα όσα είναι στο queue έχουν το ίδιο importance level.

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

  • Τα 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 επιβεβαιώνουμε αυτά.

Monitor - SQL Script

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.

SQL Script

SELECT * FROM sys.workload_management_workload_groups;
GO
Results of sys.workload_management_workload_groups
group_id name importance min_
percentage_
resource
cap_
percentage_
resource
request_
min_
resource_
grant_
percent
request_
max_
resource
_grant
_percent
query_
execution_
timeout_
sec
query_
wait_
timeout_
sec
1 smallrc normal 0 100 3.00 3.00 0 0
2 mediumrc normal 0 100 10.00 10.00 0 0
3 largerc normal 0 100 22.00 22.00 0 0
4 xlargerc normal 0 100 70.00 70.00 0 0
5 staticrc10 normal 0 100 0.40 0.40 0 0
6 staticrc20 normal 0 100 0.80 0.80 0 0
7 staticrc30 normal 0 100 1.60 1.60 0 0
8 staticrc40 normal 0 100 3.20 3.20 0 0
9 staticrc50 normal 0 100 6.40 6.40 0 0
10 staticrc60 normal 0 100 12.80 12.80 0 0
11 staticrc70 normal 0 100 25.60 25.60 0 0
12 staticrc80 normal 0 100 51.20 51.20 0 0

NOTE
Το παραπάνω αποτέλεσμα περιέχει μόνο τα system workload groups, είναι όσα έχουν group_id<=12. Επίσης δεν περιλαμβάνονται τα πεδία create_time, modify_time καθώς δεν έχουν αξία για το άρθρο αυτό.

ATTENTION
Δεν μπορούμε να έχουμε πάνω από 20 workload groups σε κάθε database. Αυτό σημαίνει ότι μπορούμε να δημιουργήσουμε μόνο 8 δικά μας.

ATTENTION
Tο κάθε workload group δεν είναι αυτόνομο αλλά συσχετίζεται άμεσα με όλα τα άλλα workload groups που υπάρχουν στο καταμερισμό των resources.

CREATE WORKLOAD GROUP

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

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 ] )
  [ ; ]

NOTE
Οι παράμετροι που η CREATE WORKLOAD GROUP δέχεται, και θα μπορούσα να πω ότι είναι "αυτόνομες" στο κάθε ένα, είναι οι παρακάτω:

IMPORTANCE

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

QUERY_EXECUTION_TIMEOUT_SEC

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

NOTE
Οι υπόλοιπες που ακολουθούν αλληλεξαρτώνται, είτε μεταξύ τους στο κάθε workload group, είτε σε σχέση με τα άλλα workload groups, αλλά και σε σχέση με το ενεργό service level (DWUs) που έχουμε στην κάθε database καθώς ισχύουν ελάχιστες τιμές για αυτές τις παραμέτρους είτε συνδιαστικά είτε μεμονωμένα με τις άλλες παραμέτρους ανά service level. Αυτή η πολυπλοκότητα δυσκολεύει την κατανόηση τους για αυτό και χρειάζεται λίγο περισσότερο προσοχή στα όσα θα ακολουθήσουν.

MIN_PERCENTAGE_RESOURCE

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

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

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

ATTENTION
Η τιμή της MIN_PERCENTAGE_RESOURCE σχετίζεται με την CAP_PERCENTAGE_RESOURCE με την σχέση
MIN_PERCENTAGE_RESOURCE < CAP_PERCENTAGE_RESOURCE

ATTENTION
Όταν η τιμή της MIN_PERCENTAGE_RESOURCE > 0 σχετίζεται με την REQUEST_MIN_RESOURCE_GRANT_PERCENT με την σχέση
MIN_PERCENTAGE_RESOURCE >= REQUEST_MIN_RESOURCE_GRANT_PERCENT

ATTENTION
Eπειδή η REQUEST_MIN_RESOURCE_GRANT_PERCENT έχει ελάχιστη ισχύουσα τιμή (δείτε παρακάτω) ανά service level, τότε αν
MIN_PERCENTAGE_RESOURCE < REQUEST_MIN_RESOURCE_GRANT_PERCENT
ΤΗΕΝ MIN_PERCENTAGE_RESOURCE = 0 κατά το runtime.
Εφόσον αυτό συμβεί τότε το ποσοστό που είχε ορισθεί την MIN_PERCENTAGE_RESOURCE είναι διαθέσιμο σε όλα τα workload groups που έχουμε. π.χ. Έαν έχουμε το WG1 με MIN_PERCENTAGE_RESOURCE = 5% και service level DW1000c τότε ισχύει το 5%. Αν όμως είχαμε DW100c που είναι παραπάνω (25%), τότε το MIN_PERCENTAGE_RESOURCE = 0 και το 5% που είχε οριστεί είναι διαθέσιμο σε όλα τα workload groups.

CAP_PERCENTAGE_RESOURCE

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

ATTENTION
Η CAP_PERCENTAGE_RESOURCE έχει μέγιστη ισχύουσα τιμή.
Αυτό σημαίνει ότι δεν είναι πάντα αυτό που έχουμε ορίσει καθώς εξαρτάται από την MIN_PERCENTAGE_RESOURCE που έχει οριστεί στα άλλα workload groups. π.χ.
Έστω ότι έχω τo WG1 στο οποίο έχω ορίσει σαν CAP_PERCENTAGE_RESOURCE = 100% και το WG2 έχω MIN_PERCENTAGE_RESOURCE = 20%, αυτό σημαίνει στο WG1 σαν CAP_PERCENTAGE_RESOURCE δεν έχω 100% αλλά 80%.

REQUEST_MIN_RESOURCE_GRANT_PERCENT

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

ATTENTION
Η τιμή της πρέπει να είναι μεταξύ 0,75 - 100,00 αλλά δεν μπορεί πχ να είναι 0,76. Πρέπει να είναι πολλαπλάσια του 0,25 δηλαδή 1,00 , 1,25, 1,50 κλπ.

ATTENTION
Η REQUEST_MIN_RESOURCE_GRANT_PERCENT έχει ελάχιστη ισχύουσα τιμή ανά service level όπως φαίνεται στο πίνακα παρακάτω.
Αυτό σημαίνει ότι εάν έχουμε ορίσει REQUEST_MIN_RESOURCE_GRANT_PERCENT = 5% και DW100c σαν service level τότε δεν ισχύει αυτό που ορίσαμε αλλά το 25%. Αν έχουμε DW1000c που έχει ελάχιστο 3% τότε ισχύει το 5%.

Lowest effective values of REQUEST_MIN_RESOURCE_GRANT_PERCENT per Service Level
Service Level REQUEST_MIN_RESOURCE_GRANT_PERCENT
DW100c 25%
DW200c 12.5%
DW300c 8%
DW400c 6.25%
DW500c 5%
DW1000c 3%
DW1500c 3%
DW2000c 2%
DW2500c 2%
DW3000c 1.5%
DW5000c 1.5%
DW6000c 0.75%
DW7500c 0.75%
DW10000c 0.75%
DW15000c 0.75%
DW30000c 0.75%
REQUEST_MAX_RESOURCE_GRANT_PERCENT

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

ATTENTION
Αν δεν οριστεί τιμή τότε παίρνει την τιμή της REQUEST_MIN_RESOURCE_GRANT_PERCENT.

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

Monitor Workload Groups runtime values

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

SQL Script

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 με την φόρμουλα

Formula

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.

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

Cases

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


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Relative Articles

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.