Πριν από ένα μήνα είχα αναρτήσει μια δημοσκόπηση στο site του SqlSchool.gr με την εξής ερώτηση:

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

Τα αποτελέσματα μετά από ένα μήνα που ήταν ενεργή η συγκεκριμένη δημοσκόπηση ήταν τα παρακάτω

Η έκπληξη ήταν αρκετά μεγάλη βλέποντας το αποτέλεσμα και έτσι αποφάσισα να δω και τον αριθμό συμμετοχών στην δημοσκόπηση αυτή ώστε να εξασφαλίσω την εγκυρότητα του δείγματος. Ο αριθμός αυτό δεν ήταν καθόλου μικρός (173 μοναδικές συμμετοχές). Βλέποντας τον αριθμό και τα αποτελέσματα μου διαπίστωσα ότι καλά έκανα και μέτρησα το συγκεκριμένο θέμα.

Ας πάρουμε τα πράγματα όμως με την σειρά ώστε στο τέλος να έχουμε βγάλει μια άκρη σχετικά με το τι τελικά είναι το Optimize for Ad Hoc Workloads και αν πρέπει ή όχι να το έχουμε ενεργοποιημένο .

Τι είναι το Optimize for Ad Hoc Workloads;

Σήμερα όλο και περισσότερες εφαρμογές χρησιμοποιούν ORMs για να διαβάσουν δεδομένα από τις databases πχ Entity Framework , Linq to SQL, NHibernate κλπ. Είναι τα αγαπημένα εργαλεία των devs που θέλουν να γράφουν σε αυτό που ξέρουν πχ C# και object oriented programming. Γενικά είναι αγαπημένα εργαλεία σε αυτούς που θέλουν να βλέπουν την βάση σαν ένα κουβά (αυτό είναι κάτι το οποίο με κάνει να εξοργίζομαι). Μέσα από τα εργαλεία αυτά ο κάθε dev χωρίς να το βασανίσει ιδιαίτερα κάνει ad hoc queries χρησιμοποιώντας τα εργαλεία αυτά. Για κάθε ένα από αυτά ο SQL Server αποθηκεύει το execution plan του στην plan cache που είναι μέρος της buffer cache που είναι και στην ουσία αυτή που καταναλώνει memory στην μηχανή που είναι εγκατεστημένος ο SQL Server. Πολλά από αυτά το πιθανότερο είναι να μην χρησιμοποιηθούνε ποτέ ξανά, αυτό σημαίνει ότι μνήμη που έχει δεσμευτεί για τα execution plans τους παραμένει κατειλημμένη. Βέβαια το ίδιο ισχύει και για εφαρμογές που δεν είναι γραμμένες χρησιμοποιώντας τα εργαλεία αυτά αλλά είναι κακογραμμένες ή ο dev δεν έχει ανακαλύψει ακόμα την χρήση των stored procedure. Σημασία όμως έχει ότι είτε έτσι είτε αλλιώς έχω αυτό που ονομάζεται plan cache bloat όπως έχει καθιερωθεί να λέγεται τα φαινόμενο αυτό στην κοινότητα των SQL Server experts.

Το φαινόμενο αυτό μπορεί να εξαλειφθεί όταν ενεργοποιήσω το option optimize for ad hoc workloads, το οποίο by default είναι off. Με ενεργοποιημένο το option αυτό ο SQL Server αντί να αποθηκεύσει το full execution plan του ad hoc query αποθηκεύει ένα μικρό compiled plan stub όπως λέγεται κατά την πρώτη του εκτέλεση. Το full execution plan θα αποθηκευτεί όταν θα υπάρχει και επόμενη εκτέλεση του ίδιου ad hoc query.

Πώς βρίσκω αν πρέπει να ενεργοποιήσω το Optimize for Ad Hoc Workloads;

Για να εντοπίσω, σαν DBA, αν έχω το φαινόμενο του plan cache bloat o τρόπος είναι ένας και αυτός δεν είναι άλλος από το να χρησιμοποιήσω την DMV sys.dm_exec_cached_plans.

Για καλύτερα όμως αποτελέσματα καλό είναι να εκτελέσετε το παρακάτω script στο οποίο γίνεται με την χρήση της sys.dm_exec_cached_plans

declare @TotalPlanCacheSizeInMBs decimal(18,2), @TotalSingleUsedPlansInMBs decimal(18,2) 
select  @TotalPlanCacheSizeInMBs = (sum(cast(size_in_bytes as decimal(18,2)))/1024)/1024 
, @TotalSingleUsedPlansInMBs = (sum(cast((case when usecounts = 1 and objtype in ('Adhoc','Prepared') then size_in_bytes else 0 end) as decimal(12,2)))/1024)/1024 
from sys.dm_exec_cached_plans 
select  @TotalPlanCacheSizeInMBs AS [Total Plan Cache Size In MBs] 
, @TotalSingleUsedPlansInMBs AS [Total Single Used Plans In MBs] 
, cast((@TotalSingleUsedPlansInMBs * 100 ) / @TotalPlanCacheSizeInMBs as decimal(18,2)) AS [% of Wasted space from single used plans] 
go 

Αν το % of Wasted space from single used plans είναι πάνω από το 20% (αν και το ποσοστό αυτό μπορεί να αλλάξει ανάλογα με το περιβάλλον) τότε καλό είναι να ενεργοποιηθεί το option αν και βέβαια αυτή είναι μια εκτίμηση γίνεται σε βάση την σχέση του χαμένου χώρου και του συνολικό μέγεθος της plan cache.

Αν θέλουμε μπορούμε να δούμε πόσο χώρο χάνουμε σε σχέση με την γενικότερη μνήμη που ο SQL Server καταναλώνει με το παρακάτω script

declare @SQLConfiguration table 
(     [name]            nvarchar(35)
    , [minimum]            int
, [maximum]            int
, [config_value]    int                
, [run_value]        int                
);

insert @SQLConfiguration 
exec ('sp_configure ''max server memory''');

DECLARE @ConfiguredMemory        decimal(19,3)
        , @PhysicalMemory        decimal(19,3)
        , @InUseMemory            decimal(19,3)


SELECT @ConfiguredMemory = (run_value/1024)/1024 
FROM @SQLConfiguration 
WHERE name = 'max server memory (MB)'

SELECT @PhysicalMemory = total_physical_memory_kb/1024 
FROM sys.dm_os_sys_memory

SELECT @InUseMemory = physical_memory_in_use_kb/1024 
FROM sys.dm_os_process_memory

declare @TotalSingleUsedPlansInMBs decimal(18,2)
select  @TotalSingleUsedPlansInMBs = (sum(cast((case when usecounts = 1 and objtype in ('Adhoc','Prepared') then size_in_bytes else 0 end) as decimal(12,2)))/1024)/1024 
from sys.dm_exec_cached_plans
select  @ConfiguredMemory AS [sql Configured Memory]
, @PhysicalMemory AS [Physical Memory]
, @InUseMemory AS [used Memory by SQL Server]
, cast((@InUseMemory * 100 ) / @ConfiguredMemory as decimal(18,2)) AS [% of SQL Server used memory on Configured Memory]
, @TotalSingleUsedPlansInMBs AS [Total Single Used Plans In MBs]
, cast((@TotalSingleUsedPlansInMBs * 100 ) / @ConfiguredMemory as decimal(18,2)) AS [% of Wasted space from single used plans on Configured Memory]
, cast((@TotalSingleUsedPlansInMBs * 100 ) / @InUseMemory as decimal(18,2)) AS [% of Wasted space from single used plans on Used Memory]
go 

Εάν έχουμε [% of SQL Server used memory on Configured Memory] πάνω από το 50% και δούμε το [% of Wasted space from single used plans on Used Memory] να είναι πάνω από το 10-15% τότε είναι ένα καλό σημάδι να ενεργοποιήσουμε option.

Τέλος υπάρχει ακόμα και περίπτωση που αν θέλουμε μπορούμε να κοιτάξουμε σε σχέση όμως αυτή την φορά με την Plan Cache Pressure Limit . Για να το γίνει αυτό θα πρέπει να γνωρίζουμε πως δουλεύει το plan caching στον SQL Server. Για αυτό υπάρχει ένα εξαιρετικό white paper από τον Greg Low με τίτλο Plan Caching in SQL Server 2008 από το οποίο ιδιαίτερη μνεία θα πρέπει να δοθεί στην παράγραφο με τίτλο Memory Allocated To Plan Caching την οποία και μεταφέρω αυτούσια για την ευκολία στο διάβασμα του post.

Για την ευκολία σας θα αναφέρω το παρακάτω από το βιβλίο της Kalen Delaney SQL Server Internals όπου ορίζονται με εξαιρετικό τρόπο τα παρακάτω:

Από το ίδιο βιβλίο θα πάρω και ένα παράδειγμα

Αφού εφαρμόσουμε τον παραπάνω αλγόριθμο βρίσκουμε το plan cache pressure limit και το συσχετίζουμε με τον αποτελέσματα από τον πρώτο τρόπο. Αν το ποσοστό που θα βρούμε είναι μεγάλο τότε όχι μόνο θα πρέπει να ενεργοποιήσουμε το option αλλά να κάνουμε καθαρισμό της plan cache. Βέβαια αυτό θα πρέπει να γίνει και για τι άλλες περιπτώσεις που έχω αναφέρει παραπάνω.

Πώς καθαρίζω την Plan Cache

Ο εύκολος τρόπος για να γίνει αυτό είναι να χρησιμοποιήσω την

DBCC FREESYSTEMCACHE('SQL Plans') 

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

select plan_handle from  sys.dm_exec_cached_plans
where usecounts=1
go

DECLARE clearplancache CURSOR
READ_ONLY
FOR select plan_handle from  sys.dm_exec_cached_plans

DECLARE @plan_handle varbinary(8000)
OPEN clearplancache

FETCH NEXT FROM clearplancache INTO @plan_handle
WHILE (@@fetch_status  -1)
BEGIN
    IF (@@fetch_status  -2)
BEGIN
        DBCC FREEPROCCACHE (@plan_handle)
END
    FETCH NEXT FROM clearplancache INTO @plan_handle
END

CLOSE clearplancache
DEALLOCATE clearplancache
GO
select plan_handle from  sys.dm_exec_cached_plans
where usecounts=1
go

Πώς ενεργοποιώ το Optimize for Ad Hoc Workloads;

Για να ενεργοποιήσω το συγκεκριμένο option μπορώ να το κάνω με το εξής script

sp_configure 'show advanced options',1 
go 

reconfigure 
go 

sp_configure 'optimize for ad hoc workloads',1 
go 

reconfigure 
go