Κλείσιμο
Κλείσιμο
Αρχική » Databases » Microsoft SQL Server » Πώς κάνουμε force ένα πλάνο στον SQL Server και γιατί να μην το κάνουμε

Πώς κάνουμε force ένα πλάνο στον SQL Server και γιατί να μην το κάνουμε

Πώς κάνουμε force ένα πλάνο στον SQL Server και γιατί να μην το κάνουμε
, , , , Αφήστε ένα σχόλιο
Πώς κάνουμε force ένα πλάνο στον SQL Server και γιατί να μην το κάνουμε
Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

Κάποιες φορές μπορεί να τύχει ένα 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
Πώς κάνουμε force ένα πλάνο στον SQL Server και γιατί να μην το κάνουμε

Βλέπουμε με ένα 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
Πώς κάνουμε force ένα πλάνο στον SQL Server και γιατί να μην το κάνουμε

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

Αν ανοίξουμε το XML του πλάνου θα δούμε ότι αναφέρει ότι έκανε τη χρήση του planguide_orders που φτιάξαμε πριν.

Πώς κάνουμε force ένα πλάνο στον SQL Server και γιατί να μην το κάνουμε

Στην περίπτωση 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;
Πώς κάνουμε force ένα πλάνο στον SQL Server και γιατί να μην το κάνουμε
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 κακό πλάνο.

Πηγές

Μοιράσου το

Αφήστε μία απάντηση