DBCC OPTIMIZER_WHATIF

Εισαγωγή

Για άλλη μια φορά σήμερα ευλογώ τον Θεό που μου έχει δώσει την δυνατότητα να κάνω αυτή την δουλειά καθώς μου δίνει την δυνατότητα σε τακτά χρονικά διαστήματα να μαθαίνω κάτι νέο.
Αυτό που έμαθα σήμερα κατά την καθημερινή μου ενασχόληση με τον SQL Server θα ήθελα να το μοιραστώ μαζί σας, και είμαι  αρκετά χαρούμενος για αυτό, καθώς το αγαπημένο μου προϊόν ο SQL Server μου χαρίζει αυτές τις συγκινήσεις.

Η ανάγκη

Ας έρθουμε όμως στο ζουμί. Σαν DBA / DB Dev καθημερινά γράφεις queries. Αρκετές φορές κάποια πάνε σφαίρα κάποια πάνε αργά και γενικά είσαι μέσα σε μια ατέρμονη διαδικασία τα αργά να τα κάνεις να πάνε γρήγορα και τα γρήγορα γρηγορότερα.

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

Αρκετές φορές εκτελώντας ένα query αναρωτιέσαι πως αυτό θα συμπεριφέρονταν αν είχες περισσότερους ή λιγότερους πόρους είτε σε CPU είτε σε Memory.

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

Με απλά λόγια θέλεις να κάνεις ένα what if σενάριο χωρίς όμως να μπεις στην βάσανο να προμηθευτείς hardware!!!

Μέχρι τώρα μπορούσα να κάνω κάτι τέτοιο όσον αφορά τον αριθμό των CPU cores που ο SQL Server θα χρησιμοποιεί με ένα τρυκ. Εκμεταλλευόμενος το γεγονός ότι γνωρίζω το πώς λειτουργεί ο SQL Server μέσω του SQLOS και των schedulers που δημιουργούνται, αλλά και της παραμέτρου –P που υπάρχει στο service του SQL Server με την οποία μπορείς να ορίσεις τον αριθμό των schedulers που θα φτιάχνει ο SQL Server όταν ξεκινάει.

Απλά για την ενημέρωση σας αναφέρω ότι ο SQL Server για κάθε cpu core που θα δει φτιάχνει ένα scheduler και αυτοί χρησιμοποιούνται κατά την εκτέλεση των εργασιών που κάνουμε πάνω σε αυτόν. Σας προτείνω να διαβάσετε τα post μου που αναφέρονται στο SQLOS και υπάρχουν σε αυτό το χώρο.

Μπορώ δηλαδή με το να πειράζω την συγκεκριμένη παράμετρο ακόμα και εάν έχω ένα σύστημα που έχει πχ δύο cpu cores να εξομοιώσω ότι έχω 4/8/16/… cores και να δοκιμάζω τα queries μου συγκρίνοντας σε κάθε περίπτωση τα execution plans. Αυτό είναι κάτι το οποίο γνώριζα και όπου μπορούσα το εφάρμοζα και λέω όπου μπορούσα διότι για να γίνει το πείραμα με τις διάφορες εναλλαγές έπρεπε κάθε φορά να κάνω restart το service του SQL Server και όπως είναι αυτονόητο αυτό δεν ήταν εύκολο να γίνει. Για το λόγο αυτό συνήθιζα να κάνω τέτοιες δοκιμές σε off hours ή σε δικά μου labs.

Η χρήση

Σήμερα ψάχνοντας για διάφορα πράγματα όμως ανακάλυψα την DBCC OPTIMIZER_WHATIF μια undocumented dbcc που όπως θα σας δείξω μου επιτρέπει να κάνω τέτοιου είδους πειράματα και συγκρίσεις χωρίς χρειάζεται να κάνω restart το service.

Ψάχνοντας στο Bing για αυτή καθώς όπως σας είπα δεν είναι documented δεν βρήκα πολλά πράγματα για αυτή αλλά τουλάχιστον βρήκα το πώς συντάσσεται και το τι παραμέτρους παίρνει και αυτό έγινε  μέσα από ένα άρθρο του Itzik Ben-Gan που σε κάποιο σημείο την αναφέρει.

Η συγκεκριμένη παίρνει δύο παραμέτρους. Στην πρώτη πάντα περνάμε την τιμή 1 και στην δεύτερη περνάμε τον αριθμό των cpu cores.

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

Για το παράδειγμα μου θα χρησιμοποιήσω ένα virtual machine σε hyper-v το οποίο έχει ένα cpu core και 2GB μνήμη, φτωχό μεν αλλά μια χαρά για την περίπτωση μας.

Για το υλοποίηση του παραδείγματος θα χρησιμοποιήσω το παρακάτω script το οποίο όμως πριν το τρέξετε θα πρέπει να ορίζεται στο query windows να τρέξει σε SQLCMD Mode.

SET NOCOUNT ON;

USE tempdb;

IF OBJECT_ID('dbo.T') IS NOT NULL DROP TABLE dbo.T1;

CREATE TABLE dbo.T
(
  f1 INT NOT NULL,
  f2 INT NOT NULL,
  data char(200) NOT NULL CONSTRAINT DFT_data DEFAULT('ABCDEFGHIJKLMONPQRSTUVWXYZ')
);
GO

INSERT INTO T (f1,f2) VALUES (1,1);
GO 1000000

DECLARE @i1 int = 0 , @i2 int = 0;
UPDATE T set 
            @i1=f1=@i1+1,
            @i2=f2=@i2+2;
GO

ALTER TABLE T 
ADD CONSTRAINT PK_T PRIMARY KEY (f1);
GO

Αφού ολοκληρωθεί η εκτέλεση θα έχουμε στα χέρια μας ένα πίνακα (Τ) με 1.000.000 εγγραφές. Σε αυτόν ζητήσουμε να δούμε των παρακάτω queries τα execution plans με διαφορετικές ρυθμίσεις που αφορούν τον αριθμό των cpu cores.

SELECT f1, f2, data FROM dbo.T WHERE f2 <=  50000 OPTION (RECOMPILE);

SELECT f1, f2, data FROM dbo.T WHERE f2 <= 100000 OPTION (RECOMPILE);

SELECT f1, f2, data FROM dbo.T WHERE f2 <= 500000 OPTION (RECOMPILE);

Χωρίς να έχω κάνει κάποια ρύθμιση τα execution plans τα οποία έχω είναι τα παρακάτω, απλά να θυμίσω ότι είμαι virtual machine με ένα cpu core.
Το αποτέλεσμα μου είναι το παρακάτω:
 
Αναρωτιέμαι τώρα πως αυτά θα εκτελούνταν αν είχα δύο (2) cpu cores και για αυτό πλέον κάνοντας χρήση της DBCC όπως παρακάτω :

DBCC OPTIMIZER_WHATIF (1,2);
GO

Το αποτέλεσμα μου είναι
 

Είναι ομολογουμένως ενδιαφέρον δεν βρίσκετε;
Αν είχα τέσσερα (4) ή οκτώ (8) cpu cores πως θα ήταν τα πράγματα;
Αν επέλεγα 8 cores το αποτέλεσμα μου θα ήταν το παρακάτω
 

 

Παρατηρησεις – Επισημάνσεις

• Εάν τώρα θα ήθελα να γυρίσω στην αρχική μου κατάσταση το μόνο που έχω να κάνω είναι να εκτελέσω την DBCC ξανά απλά βάζω μηδέν στην δεύτερη παράμετρο στην οποία ορίζω τον αριθμό των cores  (DBCC OPTIMIZER_WHATIF(1,0)).
• Να επισημάνω ότι η DBCC αυτή δεν επηρεάζει την γενικότερη συμπεριφορά του SQL Server αλλά το συγκεκριμένο session στο οποίο γίνεται η εκτέλεση της.
• Τέλος θα πρέπει να τονίσω ιδιαίτερα ότι αυτή θα πρέπει να χρησιμοποιηθεί με ανάληψη του ρίσκου από εσάς και φυσικά δεν ενδείκνυται να χρησιμοποιείται για άλλους σκοπούς πλην της έρευνας.
   

 

/*antonch*/