Πώς κάνουμε force ένα πλάνο στον SQL Server και γιατί να μην το κάνουμε
Πιστοποιήσεις:
- Πώς αυτοματοποιούμε τη διαδικασία ελέγχου ακεραιότητας των βάσεων δεδομένων σε SQL Server χωρίς την χρήση maintenance plan - 10 Μάιος 2024
- Πώς μπορούμε να συνδέσουμε Oracle Database με SQL Server με χρήση Oracle Gateway σε Windows - 5 Απρίλιος 2024
- Πώς μπορούμε να λαμβάνουμε email κάθε φορά που ξεπερνάει ένα όριο η μνήμη PGA σε βάση δεδομένων της Oracle - 1 Μάρτιος 2024
Κάποιες φορές μπορεί να τύχει ένα query ανάλογα την τιμή που έχουμε δώσει σε μια παράμετρο να τρέχει με διαφορετικό πλάνο (execution plan) και να έχει διαφορετική διάρκεια. Αυτό το φαινόμενο ονομάζεται parameter sniffing. Αυτό μπορεί να οφείλεται σε διαφορετικό αριθμό εγγραφών του αποτελέσματος, σε outdated statistics ή στη μη χρήση ενός σωστού non-clustered index. Στο άρθρο θα δούμε πως μπορούμε να κάνουμε force ένα πλάνο πάνω σε μία procedure ή ένα sql statement και γιατί συνήθως δεν πρέπει να το κάνουμε.
Τι είναι το parameter sniffing
Parameter sniffing ονομάζεται η διαδικασία του SQL Server που όταν τρέχουμε μια procedure ή ένα sql statement που δέχεται μια άγνωστη παράμετρο, την διαβάζει ώστε να δημιουργήσει ένα πλάνο σύμφωνα με την τιμή που έχουμε ορίσει. Που είναι το πρόβλημα; Ότι ανάλογα με την παράμετρο, μπορεί το query να επιστρέφει 1 γραμμή είτε 100.000 και ο SQL Server θα χρησιμοποιήσει το πλάνο που είχε φτιαχτεί με την πρώτη παράμετρο που είχε δεχθεί. Αυτό θα μπορεί να οδηγήσει το δεύτερο query να τρέξει με μη efficient (optimized) τρόπο.
Ποια είναι η λύση
Η λύση θα μπορούσε να είναι να κάνουμε recompile το πλάνο κάθε φορά που όμως θα οδηγούσε σε υψηλή χρήση cpu μαζί με υψηλή χρήση μνήμης. Αυτό θα συνέβαινε διότι με κάθε παράμετρο θα φτιαχνόταν ένα άλλο πλάνο το οποίο θα έπιανε χώρο στην cache. Θα μπορούσαμε επίσης να χρησιμοποιήσουμε query hint με την εντολή να γίνει optimize σύμφωνά με άλλη παράμετρο ώστε να χρησιμοποιηθεί πάντα το πλάνο της συγκεκριμένης παραμέτρου.
Κάποιες άλλες φορές το πρόβλημα οφείλεται στα στατιστικά, καθώς όταν πάρθηκαν στατιστικά στον πίνακα, ο πίνακας είχε άλλον αριθμό εγγραφών από όταν εκτελέστηκε το query.
Τέλος το πρόβλημα θα μπορούσε να είναι στην έλλειψη ενός σωστού non-clustered index.
Force it hard way a.k.a Plan Guides!
Αν για κάποιο λόγο δεν μπορούμε να πειράξουμε τον κώδικα μας στην εφαρμογή είτε δεν έχουμε την άδεια να προσθέσουμε δικά μας indexes και θέλουμε μια λύση άμεσα υπάρχει τρόπος. Η χρήση Plan Guides!
Τα Plan Guides μας επιτρέπουν να κάνουμε optimize τα queries χωρίς να πειράξουμε τον κώδικα τους.
Έχουμε 3 κατηγορίες Plan Guides:
- Object: Για optimize σε queries μέσα σε Stored Procedure.
- Template: Για optimize σε stand-alone queries που δέχονται παραμέτρους.
- SQL: Για optimize σε stand-alone queries του τρόπου εκτέλεσης του.
Παράδειγμα
Το παράδειγμα θα είναι στην δοκιμαστική βάση της Microsoft WideWorldImporters.
Στην περίπτωση stored procedure που δέχεται μία παράμετρο
use WideWorldImporters; go create procedure sp_orders @stockitemid INT as select o.customerid, sum(ol.quantity*ol.unitprice) from sales.orders o inner join sales.orderlines ol on o.orderid=ol.orderid where ol.stockitemid=@stockitemid group by o.customerid order by o.customerid asc; go
Εκτελούμε την procedure δύο φορές με διαφορετική παράμετρο και με την επιλογή set statistics xml on θα μας φέρει και το πλάνο της κάθε μίας.
set statistics xml on; GO sp_orders 90 with recompile; GO sp_orders 224 with recompile; GO
Βλέπουμε με ένα compare ότι για το ίδιο query παρόλο που κάναμε recompile μας ήρθε με διαφορετικό πλάνο.
Αφού αποφασίσουμε ποιο από τα δύο πλάνα μας καλύπτει και για τις 2 περιπτώσεις θα φτιάξουμε το Plan Guide.
Αφού πρόκειται για stored procedure θα ορίσουμε ώς type=’OBJECT’, στο stmt το query και στα hints θα προσθέσουμε το query hint που θα το κάνει πάντα το query να τρέχει με το πλάνο που θα είχε αν ήτανε η τιμή ήταν 90 *.
* Σε περίπτωση που δεν γνωρίζουμε ποια τιμή καλύπτει την μέγιστη απόδοση στις περισσότερες εκτελέσεις του query. Μπορούμε αντί για να βάλουμε ως option την ίδια την τιμή, να ορίσουμε OPTION (OPTIMIZE FOR UNKNOWN) που θα χρησιμοποιήσει τα δεδομένα των στατιστικών για να δημιουργήσει ένα “μέτριο” πλάνο που θα προσπαθήσει να καλύπτει όλες τις τιμές που έχει δεχθεί. Ενδέχεται όμως να μην είναι αρκετά αποδοτικό.
EXEC sp_create_plan_guide
@name = N'planguide_orders',
@stmt = N'select
o.customerid,
sum(ol.quantity*ol.unitprice)
from sales.orders o
inner join sales.orderlines ol on o.orderid=ol.orderid
where ol.stockitemid=@stockitemid
group by o.customerid
order by o.customerid asc',
@type = N'OBJECT',
@module_or_batch = N'sp_orders',
@params = NULL,
@hints = N'OPTION (optimize for (@stockitemid = 90))';
Αφού το δημιουργήσαμε δοκιμάζουμε ξανά την ίδια εκτέλεση.
sp_orders 90 with recompile; GO sp_orders 224 with recompile; GO
Αυτή την φορά θα δούμε ότι το πλάνο παρέμεινε το ίδιο όπως ήτανε με την εκτέλεση με τιμή 90.
Αν ανοίξουμε το XML του πλάνου θα δούμε ότι αναφέρει ότι έκανε τη χρήση του planguide_orders που φτιάξαμε πριν.
Στην περίπτωση stand-alone query με παράμετρο
Θα εκτελέσουμε την sp_get_query_template ώστε το query που γνωρίζουμε εμείς με τιμές να χωριστεί όπως το γνωρίζει ο SQL Server με το SQLStatement και το όνομα της παραμέτρου.
Επίσης στο query hint θα ορίσουμε parameterization forced ώστε πάλι ότι παράμετρο και να έχουμε δώσει στο query να τρέχει με το πλάνο που έχει με την τιμή 90.
DECLARE @SQLStatement NVARCHAR(MAX);
DECLARE @Parameters NVARCHAR(MAX);
EXEC sp_get_query_template
N'select
o.customerid,
sum(ol.quantity*ol.unitprice)
from sales.orders o
inner join sales.orderlines ol on o.orderid=ol.orderid
where ol.stockitemid=90
group by o.customerid
order by o.customerid asc',
@SQLStatement OUTPUT,
@Parameters OUTPUT
EXEC sp_create_plan_guide
@name = N'planguide_orders_2',
@stmt = @SQLStatement,
@type = N'TEMPLATE',
@module_or_batch = NULL,
@params = @Parameters,
@hints = N'OPTION (PARAMETERIZATION FORCED)';
Πως βρίσκουμε τα Plan Guides που έχουμε
Υπάρχει το system view sys.plan_guides στο οποίο περιέχονται όλα τα Plan Guides. Μπορούμε αναπάσα στιγμή να σβήσουμε κάποιο συγκεκριμένο ή και όλα.
select * from sys.plan_guides;
sp_control_plan_guide 'DROP', 'planguide_orders_2' sp_control_plan_guide 'DROP ALL';
Τι πρέπει να προσέξουμε με τη χρήση Plan Guides
Πρέπει το SQL text να είναι ακριβώς ίδιο, αν είναι γραμμένο το query αλλιώς ή ακόμα και ένα απλό κενό παραπάνω να έχει δεν θα λειτουργήσει. Σε περίπτωση που μπορεί να μας έρθει με διαφορετικό κείμενο το query πρέπει να φτιάξουμε ένα δεύτερο Plan Guide.
O query optimizer γνωρίζει συνήθως ποιο είναι το καλύτερο πλάνο. Κάνοντας το εμείς force μπορεί να έχουμε το ίδιο πλάνο αλλά να έχουμε κάτα μέσο όρο χειρότερη απόδοση.
Εκτός αυτού μπορεί να αλλάξουν τα δεδομένα σε βάθος χρόνου και να έχουμε ένα outdated κακό πλάνο.
